Chapter 2: Analytics On Spreadsheets

Chapter 2: Analytics on Spreadsheets
Objectives:
At the end of this Chapter, the students will be able:
1. to discuss the impact or importance of Analytics in Business

2. to identify the different tools used in Analytics
3. to discuss the scope of business Analytics
4. to use analytics to solve business problems
Why Spreadsheets?
 Many commercial software packages can be used for Business Analytics.

 Spreadsheet software, such as Microsoft Excel, is widely available and used across all areas of
business.
 Spreadsheets provide a flexible modeling environment for manipulating data and developing
and solving models.
Basic Excel Skills
 Opening, saving, and printing files
 Using workbooks and worksheets
 Moving around a spreadsheet
 Selecting cells and ranges
 Inserting/deleting rows and columns
 Entering and editing text, data, and formulas
 Formatting data (number, currency, decimal)
 Working with text strings
 Formatting data and text
 Modifying the appearance of a spreadsheet
Excel 2013 Ribbon
 Tabs - Home, Insert, Page Layout, Formulas, …
 Groups - Font, Alignment, Number, Styles, …
 Buttons and Menus

o Buttons appear as small icons.
o Menus of additional choices are indicated by small triangles.
Excel Formulas
 Common mathematical operators are used.

 For example:
a − bP5 + c would be entered into Excel as:
d
=a− b*P^5 + c/d
Relative and Absolute References
 Cell references can be relative or absolute. Using a dollar sign before a row and/or column label
creates an absolute reference
o Relative references: A2, C5, D10
o Absolute references: $A$2, $C5, D$10
 Using a $ sign before a row label (for example, B$4) keeps the reference fixed to row 4 but
allows the column reference to change if the formula is copied to another cell.
 Using a $ sign before a column label (for example, $B4) keeps the reference to column B fixed
but allows the row reference to change.
 Using a $ sign before both the row and column labels (for example, $B$4) keeps the reference to
cell B4 fixed no matter where the formula is copied.
Implementing Price-Demand Models in Excel
 Two models for predicting demand as a function of price

 Linear
D = a – bP
Formula in cell B8:
=$B$4-$B$5*$A8
 Nonlinear
D = cP-d
Formula in cell E8:
=$E$4*D8^-$E$5
 Note how the absolute addresses are used so that as these formulas are copied down, the
demand is computed correctly.
Copying formulas
Formulas in cells can be copied in many ways.
 Use the Copy button in the Home tab, then use the Paste button
 Use Ctrl-C, then Ctrl-V
 Drag the bottom right corner of a cell (the fill handle) across a row or column
Other useful Excel tips
 Split Screen
 Paste Special
 Column and Row Widths
 Displaying Formulas in Worksheets
 Displaying Grid Lines and Column Headers for Printing
 Filling a Range with a Series of Numbers
Basic Excel Functions
 =MIN(range)
o the Excel MIN functions returns the smallest numeric value in a range of values.
o the MIN functions ignores empty cells, the logical values TRUE and FALSE and text
values.
 =MAX(range)
o the Excel MAX functions returns the largest numeric value in a range of values.
o the MAX functions ignores empty cells, the logical values TRUE and FALSE and text
values.
 =SUM(range)
o add all the numbers in a range of cells
 =AVERAGE(range)
o returns the average of its arguments, which can be numbers or names, arrays or
references that contain numbers
 =COUNT(range)
o count the number of cells in a range that contain numbers
 =COUNTIF(range,criteria)
o counts the number of cells within a range that meet the given condition.
 Excel has other useful COUNT-type functions: COUNTA counts the number of nonblank cells in a
range, and COUNTBLANK counts the number of blank cells in a range. In addition,
COUNTIFS(range1, criterion1, range2, criterion2,… range_n, criterion_n)finds the number of
cells within multiple ranges that meet specific criteria for each range.
other if-type Functions
 SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS can be used to embed IF logic within
mathematical functions.
 For instance, the syntax of SUMIF is
o SUMIF(range, criterion, [sum range]). "Sum range" is an optional argument that allows
you to add cells in a different range.
 Example: In the Purchase Orders database, to find the total cost of all airframe fasteners, use
=SUMIF(D4:D97,"Airframe fasteners", G4:G97)
functions for Specific Applications
 Net Present Value (or discounted cash flow) measures the worth of a stream of cash flows,
taking into account the time value of money.
 Excel function: =NPV(rate,value1,value2,…)
o F is the cash flow ($)
o Rate (i) is the discount rate
o value1, value2,…are equally-spaced payments or income values
o t is a time period
Using the NPV Function

 Cell B8:
 =NPV(B6, C4:H4) – B5

Insert Function
 Click the Insert function button fx.

 You may type in a description or search.
 Example for COUNTIF
 function


Chapter 2: Analytics On Spreadsheets

Uploaded by

Copyright:

Available Formats

Chapter 2: Analytics On Spreadsheets

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter 2: Analytics On Spreadsheets

Uploaded by

Copyright:

Available Formats

Chapter 2: Analytics on Spreadsheets

At the end of this Chapter, the students will be able:

1. to discuss the impact or importance of Analytics in Business

 Many commercial software packages can be used for Business Analytics.

Basic Excel Skills

 Opening, saving, and printing files

 Using workbooks and worksheets

 Moving around a spreadsheet

 Selecting cells and ranges

 Inserting/deleting rows and columns

 Entering and editing text, data, and formulas

 Formatting data (number, currency, decimal)

 Working with text strings

 Formatting data and text

 Modifying the appearance of a spreadsheet

Excel 2013 Ribbon

 Tabs - Home, Insert, Page Layout, Formulas, …

 Groups - Font, Alignment, Number, Styles, …

 Buttons and Menus

o Menus of additional choices are indicated by small triangles.

 Common mathematical operators are used.

Relative and Absolute References

Implementing Price-Demand Models in Excel

 Two models for predicting demand as a function of price

Formulas in cells can be copied in many ways.

 Use Ctrl-C, then Ctrl-V

Other useful Excel tips

Basic Excel Functions

functions for Specific Applications

Using the NPV Function

 Click the Insert function button fx.

You might also like