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