LECTURE 1
INTRODUCTION TO SPREADSHEET
BUSINESS MODELING
1
AGENDA
Course outline
Mathematical models to aid decision making
Basic spreadsheet skills
2
MANAGERIAL DECISION MAKING
Analysis
Model Results
Symbolic
Interpretation
Abstraction
World
Managerial
Real Judgment
World
Management Intuition
Decisions
Situation
3
A SCIENTIFIC APPROACH
Define the Problem
Identify the Alternatives
Determine the Criteria Modeling
Problem Analysis
Solving Evaluate the Alternatives
Recommend an Alternative
Implement the Decision Decision
Evaluate the Results
4
EXAMPLE
You operate a bakery and need to determine advertising expenses for November and
December.
Previous years’ experience tells you that the monthly demand is correlated with the
monthly advertising expenditure. The effectiveness of advertising depends on the
month: Demand
𝑑𝑒𝑚𝑎𝑛𝑑 𝑑𝑜𝑧𝑒𝑛 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡 ln 𝑎𝑑𝐸𝑥𝑝𝑒𝑛𝑠𝑒
Past experience tells us that 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡 50, 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡 75
Selling price: $96/dozen
Cost of ingredients = $50/dozen
Expense
Other fixed costs = $10,000 per month
5
MATHEMATICAL MODELS
Model
Abstraction of a real thing or process; some elements must be omitted
Contains decision variables and represents a system in mathematical terms
Finds values of decision variables which will improve system performance
Used as a guide for aiding decision making
Uses of Models
To improve the existing decision
To increase the understanding of a system
To explicitly make trade-offs for a decision
6
ELEMENTS OF MODELS
Decisions: choices, possible actions, controllable variables; decision variables
Outcomes: performance measures, criteria, eventual consequences; objective
function
Data: information, environmental conditions, uncontrollable variables;
parameters
Structure: relations, causes and effects, logics; equations or inequalities
7
DECISIONS, OUTCOMES, DATA - EXAMPLE
Decisions: choices, possible actions, controllable variables; decision variables
How much should we spend on advertising in November and December?
First attempt: $1,000 per month
Outcomes: performance measures, criteria, eventual consequences; objective
function
Maximize monthly income (profit) in November and December
Data: information, environmental conditions, uncontrollable variables; parameters
Selling price (per dozen cookies)
Cost of ingredients (per dozen cookies)
Advertising effectiveness factor 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡
8
STRUCTURE OF MODEL - EXAMPLE
Net income
Revenue Cost
$96 Production cost +
Other fixed costs +
Selling price Production Monthly advertising
expense = Cost
(dozen) cost
Monthly Cost of Other fixed
demand chocolate (lb) costs
9
Monthly
advertising
expense
STRUCTURE OF MODEL - EXAMPLE
Structure: relations, causes and effects, logics; equations or inequalities
Monthly income = 𝑟𝑒𝑣𝑒𝑛𝑢𝑒 – 𝑐𝑜𝑠𝑡𝑠
Revenue = 𝑠𝑒𝑙𝑙𝑖𝑛𝑔 𝑝𝑟𝑖𝑐𝑒 𝑝𝑒𝑟 𝑑𝑜𝑧𝑒𝑛 𝑑𝑒𝑚𝑎𝑛𝑑 𝑑𝑜𝑧𝑒𝑛)
Cost of production = 𝑐𝑜𝑠𝑡 𝑝𝑒𝑟 𝑑𝑜𝑧𝑒𝑛 𝑑𝑒𝑚𝑎𝑛𝑑 𝑑𝑜𝑧𝑒𝑛𝑠
Operating costsmonth = 𝑎𝑑𝐸𝑥𝑝𝑒𝑛𝑠𝑒 𝑜𝑡ℎ𝑒𝑟 𝑓𝑖𝑥𝑒𝑑 𝑐𝑜𝑠𝑡𝑠
Demand(dozen)month = 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡 ln 𝑎𝑑𝐸𝑥𝑝𝑒𝑛𝑠𝑒
10
SPREADSHEET MODEL
Decisions Can change
Outputs
Our goal
Inputs What we know
Calculation
Dont type in numbers
Refer to inputs
11
SPREADSHEET MODEL
Highlight the output to increase user-friendliness.
=$C$13*C24; Use of $ in “C13” creates an “absolute
reference” that doesn’t change when we copy the formula
to the next column.
When we copy this cell to column D, we will see
“=$C$13*D24”
12
BEST PRACTICE GUIDELINES IN SPREADSHEET
DESIGN
Sketch the spreadsheet
Organize the spreadsheet into modules
Isolate input parameters
Keep it simple
Avoid long formulas
Formulas only contain cell references (i.e. not numbers); this simplifies
spreadsheet auditing
Design for communication
Use Excel formatting options (outlines, colours, bold font, etc) to highlight
certain cells for quick visual recognition
Document important data and formulas 13
“WHAT-IF” ANALYSIS
Questions:
What if we vary advertising expenses in November and December between $1,000 to
$10,000 (can be different in each month)? Modify the corresponding decision cell (C5,
D5).
What selling price would result in a total income of $0 (break-even)? Try different
values for the parameter cell C13.
Better solution: Use scenario analysis functions in Excel.
Data Tab ⇒ Forecast ⇒ What-if Analysis
Data table – Re-compute the output as we vary ONE or TWO parameters along a
range of values.
Scenario manager – Re-compute the output for different scenarios where we may vary
any number of parameters.
Goal Seek – Determine the parameters that would give us the desired output.
See “ModelAnalysis.xlsx” 14
WHY SPREADSHEET MODELING ?
Examples of spreadsheets:
Microsoft Excel – still the industry standard…
Numbers
Google Spreadsheets
Open office spreadsheet
Advantages:
Ubiquity
Availability of Add-ins
Low cost alternative to more specialized tools (statistics, optimization)
Disadvantages:
Data size limitations, slow calculation speeds (large models)
Difficult to document and organize models 15
Often insufficient for advanced analysis
