BX2016

Data Analytics and Business Modelling

Module 2 – Topic 1
Modelling Process

Learning Objectives
After completing this lecture, you
should able to –
 describe and demonstrate
modelling process
 illustrate the construction of model
using spreadsheet
2
 The Modeling Process
Analysis

Model Results

Interpretation
Abstraction

Symbolic
Worlds
Managerial Judgment
Real
World

Management
Decisions
Situation
Intuition

Note: Symbolic World is Model World (in lecture 1)

3
The Modelling Process
• Describe Problem / opportunity
Objective
1 • Identify Overall Objective
Hierarchies • Organize Sub-Objectives into a hierarchy

Variables • Identify Model’s Objective

2 •
and Attributes Determine all variables and their attributes
• Decide on Measurement / Data Collection

Influence • Graphically depict relationships among variables

3 •
Diagrams Distinguish between Decision and outcome
variables

Mathematical • Determine mathematical relationships among

4 variables
Representation • Develop mathematical model(s)

Testing and • Evaluate reliability and validity

5 •
Validation Understand limitations

• Implement models
6 Implementation • Clarify assumptions, inputs, and outputs
and use
4
Reasons for Using Models

 Models force you to:

 Be explicit about your objectives
 Think carefully about variables to include and their
definitions in terms that are quantifiable
 Identify and record the decisions that influence
those objectives
 Identify and record interactions and trade-offs
among those decisions

5
Reasons (cont.)

 Consider what data are pertinent for quantification

of those variables and determining their
interactions
 Recognize constraints (limitations) on the values
that those quantified variables may assume
 Allow communication of your ideas and
understanding to facilitate teamwork

6
Types of Models

7
Building Models

The “Black Box” View of a Model

Endogenous
Exogenous

Decisions Performance

Variables
Variables

(Controllable) Measure(s)
Model
Parameters Consequence
(Uncontrollable) Variables

8
MODELLING VARIABLES
Management
Modeling Term Lingo Formal Definition Example
Decision Variable Lever Controllable Exogenous Investment
Input Quantity Amount

Parameter Gauge Uncontrollable Exogenous Interest Rate

Input Quantity

Consequence Outcome Endogenous Output Commissions

Variable Variable Paid

Performance Yardstick Endogenous Variable Return on

Measure Used for Evaluation Investment
(Objective Function Value)

9
 Examples of Decision Model
Assumptions - Profit Models
 If it is beyond your control, do not consider it!
 Overhead costs - a convenient fiction - we ignore
 Sunk costs - we ignore
 Depreciation - only include if we can use to shield
future taxes
 Costs are linear in the short term

10
Building Models

Symbolic Model Construction

Mathematical relationships are developed from data.

Graphing the variables may help define the relationship.

Co A+B
st B
Var. Y

Cost A

Var. X

11
 Modeling with Data
Consider the following data.
Graphs are created to view any relationship(s) between the variables.
This is the first step in formulating the equations in the model.

12
Creating the Symbolic Model
Predicting Sales Based on Marketing Expenditures
3000

2500
y = 3.5853x + 357.7
R2 = 0.9316
Sales Revenue (y)

2000

1500

1000

500

0
0 100 200 300 400 500 600 700

Marketing Expenses (x)

13
 Modelling made simple (4 steps)
where do we begin?
 Framing the situation (process 1)
 Moving from a symptom to a clear
problem statement
 Problem statement = possible
decisions and method of measuring
their effectiveness
 Formulate a selective
representation (process 2 & 3)
 Black Box view
 Constructing a symbolic model
(process 4)
 Using EXCEL!
 Performing Analysis & Validating the
model (process 5 & 6)
 Verifying its usefulness 14
Frame What We Understand
(model) - The Black Box

15
Influence Diagram
 Formulating the relationships
 Pictures the connection between the inputs to the
model and a performance measure variable
(output).
 How to build an influence diagram
1. Pick a performance measure variable
2. Decompose the performance measure variable into 2 or more
intermediate variables that combine mathematically to
define the performance measure
– These intermediate variables reside inside the black box
3. Further decompose each intermediate variable into more
intermediate variables until we pop out of the black box (the
input decision variable is defined

16
Modelling Process – Illustration
Simon Pie Company
 A startup company, Simon Pie Company,
generates profits from combining two purchased
ingredients (fruit and frozen dough) into apple
pies, processing the pies (cooking, packaging,
delivery, etc.) and selling them to the local
grocery stores.

 The company's founder, Samuel Simon intends

to build an Excel model to explore his options.

17
Simon Pie’s Model

18
 Simon Pie’s Model Construction

Simon Pie – Influence Diagram

performance
Start here: Profit measure
variable

Decompose this variable into the intermediate

variables Revenue and Total Cost

19
Simon Pie’s Model Construction

Profit

Revenue Total Cost

Now, further decompose each of these

intermediate variables into more related
intermediate variables ...

20
Simon Pie’s Model Construction
 Model Construction
• Based on the previous Influence Diagram, create the
equations relating the variables to be specified in the
spreadsheet.

Profit

Revenue Total Cost

Profit = Revenue – Total Cost

21
 Simon Pie’s Model Construction
Profit

Revenue

Revenue = Pie Price * Pies Demanded

Pies Demanded

Pie Price 22
 Simon Pie’s Model Construction
Profit
Total Cost

Processing Ingredient
Cost Cost

Total Cost =
Processing Cost + Ingredients Cost + Fixed Cost

23 Fixed Cost
Simon Pie’s Model Construction
Profit
Total Cost

Processing
Cost

Processing Cost =
Pies Demanded *
Pies Demanded Unit Pie Processing Cost

Unit Pie
Processing Cost 24
 Simon Pie’s Model Construction
Profit
Total Cost
Ingredients Cost =
Qty Filling * Unit Cost Filling + Ingredient
Cost
Qty Dough * Unit Cost Dough
Required
Ingredient
Quantities

Unit Cost Unit Cost

Filling Dough 25
 Simon Pie’s Model Construction
Profit

Revenue Total Cost

Processing Ingredient
Cost Cost

Required
Ingredient
Quantities
Pies Demanded

Unit Pie Unit Cost Unit Cost

Pie Price Processing Cost Filling Dough Fixed Cost
26
Simon Pie’s Model Construction
Simon’s Initial Model Input Values
Pie Price $8.00
Pies Demanded and sold 16
Unit Pie Processing Cost ($ per pie) $2.05
Unit Cost, Fruit Filling ($ per pie) $3.48
Unit Cost, Dough ($ per pie) $0.30
Fixed Cost ($000’s per week) $12

27
Simon Pie’s Model Construction

 To represent this model in an Excel spreadsheet,

we should adhere to the following
recommendations:
 Present input variables together and label them.
 Clearly label the model results.
 Give the units of measure where appropriate. Store
parameters in separate cells as data and refer to them
in formulas by cell references.
 Use bold fonts, cell indentations, cell underlines and
other Excel formatting options to facilitate
interpretation.
28
Simon Pie’s Model Construction
Initial Simon Pie Weekly Profit Model

See spreadsheet Simon Pie Initial Model 29

Simon Pie’s Model Construction

 “What if?” Projection Allows you to determine

what would happen if you used alternative
inputs.
 For example, what would the resulting Profit
be if the Profit for Pie Price and Pies
Demanded changed to $7.00 and 20,000 or
$9.00 and 12,000, respectively.
 Simply change the values of these
parameters in the spreadsheet to view the
resulting Profit.

30
 Simon Pie’s Model Implementation
What if we change the
values of two parameters?

What effect will that have

on Total Cost and Profit? 31
Simon’s Pie – Validating the model

 Validating the model

 use influence diagram to examine that ALL the
formulas are correct
 Test the model by setting decision variables (e.g.,
pie demand = 1) or parameters (e.g., unit cost =
$1) and check the outcome is correct (profit)
 Use test data and predicted value to further
validate the model

32
Simon’s Pie – Performing Analysis

 Exploring Samuel Simon options.

 What is current profit?
 What is the break-even number of pies sold?
 What is the break-even price of pie for current
demand?
 What effects will it have on total profit if there is
changes on for example selling price, demand,
unit costs?
 What price to sell to maximize profit?

33
Performing Analysis

 To answer and evaluate Simon’s options we

will develop spreadsheet (Excel) skills and
apply the right tools to explore the options and
perform some the following analysis
 what-if analysis
 Trend and relationship (chart & graph)
 best, worst case scenarios
 demand forecast - price projections
 optimization using the solver
 model output sensitivity analysis
34
Performing Analysis
 We will perform some the following analysis after
we have acquired the required Excel skills.
 what-if analysis
 Trend and relationship (chart & graph)
 best, worst case scenarios
 demand forecast - price projections
 optimization using the solver
 model output sensitivity analysis
 A complete analysis of Simon Pie will be discussed
and presented at the summary/last lecture.

See spreadsheet Simon Pie Initial Model 35

Summary
 After completing this lecture you have learned
 describe and demonstrate modelling
process
 illustrate the construction of model using
spreadsheet

 You also learn to apply Excel skills

• Formatting cell
• Cell referencing
• Copy formula (auto filled)

36