Spreadsheets Modeling for Managerial Decision Making - PowerPoint PPT Presentation


PPT – Spreadsheets Modeling for Managerial Decision Making PowerPoint presentation | free to download - id: 98330-MzRmZ


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

Spreadsheets Modeling for Managerial Decision Making


Spreadsheets Modeling for Managerial Decision Making. OPNS 450 ... Model = Decision variables , Objective, Assumptions and ... of the model structure ... – PowerPoint PPT presentation

Number of Views:97
Avg rating:3.0/5.0
Slides: 22
Provided by: canansa


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Spreadsheets Modeling for Managerial Decision Making

Spreadsheets Modeling for Managerial Decision
  • OPNS 450 WINTER 2008

Prof. Canan Savaskan-Ebert Operations Management
Agenda for today
  • Introductions
  • Introduction to course material and course
  • The Objective of this class
  • Application of goal seek and data tables
  • Introduction to optimization with Solver

Course Material
  • Course Pack
  • Book
  • Course Web-page

Weekly Schedule / Class Work
  • Case Assignments (Group Work max. 3 - 4 people)
  • Term Project (Group project)
  • Grading 50 Assignments / 35 Project
  • Peer Evaluation and Attendance (15)

Weekly Assignment Guidelines
What is important in assignment
reports? Correctness of the answer is not
sufficient ….you should demonstrate the
comprehension of the problem, the logic of the
model and application of the excel tools we will
see in class. Assignment Report
PART 2. Spreadsheets (60) Spreadsheet
Organization (20) clarity documentation using
formula list, bordering grouping color
coding. Questions Answered (40 ) specific
questions asked.
PART 1. Write-up (40 ) Executive Summary key
issue, solution method, solution insights Model
Decision variables , Objective, Assumptions and
Formulation /Structure Analysis Excel tools and
methods used. Conclusions Answer to problem
its interpretation.
See Sample Breakeven Analysis, Important
formula list and printing assignments
Your spreadsheet should be structured
Input parameters for the problem
Decision / Choice Variables
Formulation Output Mathematical relationship
between performance measures, input parameters
and choice variables.
Presentation of the model structure Use Formula
List to display formulas / Use File- Print
Preview-Setup-Sheet Tab grid lines
row-column heading to display cell address in
Final Project
  • Work Content 2-3 weeks of assignment load.
  • Topic A problem at your work / daily life or a
    case from a class you have taken previously.
  • Evaluation will be based on i) how effectively
    the excel tools are applied to the problem, how
    much insight the model provides to the problem.
  • ii) the clarity of the analysis and the

Making good managerial decision can be
challenging because…

What is Modeling ?
Modeling is the process of creating a simplified
representation of reality and working with this
representation in order to understand and control
some aspects of the world.
B. Mathematical Model Demand 1000.01Competitor
s Price 0.5 Own Price
A. Mental Models
Own price
C. Spreadsheets Model
Competitor price

D. Visual Models, e.g. Maps, Organizational
Charts. E. Physical Models Models used by
Architectures, Engineers
F. Simulation Models Monte Carlo Simulation
Discreet Event Simulation
The Art of Decision Modeling
Real World
Model World
Problem Definition
Assumptions and Model Structures
Choice of Performance Criteria and Analysis
Results and Sensitivity
Course Outline
Week 1 Essential Excel Goal Seek and Data Tables
and Intro to Solver
Week 6 Discreet Probability Models
Week 2 Optimization with Linear
Constraints Resource Allocation Sensitivity
Week 7 Continuous Probability Models
Week 3 Optimization with Non-linear
Constraints Wine Blending Portfolio Planning
Week 8 _at_RISK software and Excel Modeling
Week 4 Integer Non-Linear Optimization Workforce
Planning Risk Minimization
Week 9 Pivot Tables - Data Filters Precision Tree
Week 5 Introducing Uncertainty In Excel
Week 10 Forecasting
Session 1 Essentials of Excel
  • Structuring Spreadsheets Basic Excel Commands
  • Application of Goal Seek
  • Application of Data Tables (One-way / Two-way
  • Introduction to optimization with Solver

Cases Break Even Analysis Monopoly Pricing
Goal Seek
  • The goal seek feature in Excel enables you to
    compute a value for a spreadsheet input that
    makes the value of a given formula match the goal
    you specify. Goal seek embeds a powerful equation
    solver in Excel.
  • To use GOAL seek, you need to provide Excel, 3
    pieces of information
  • Set Cell Contains the formula that calculates
    the value you are seeking.
  • To Value The numerical value of the goal.
  • By Changing Cell The input cell Excel changes
    until the set cell calculates the goal defined in
    the To Value cell.

Goal Seek
  • Goal seek is an equation solver!
  • To start insert any number for the changing
  • Choose Tools, Goal Seek and then fill in the
    dialog box.
  • If the problem has more than one solution goal
    seek will display only one solution.
  • Go to Tools/ Options/calculation tab. Set max.
    change option to a very small number (0.000001).
  • This ensures that Excel finds the value of the
    changing cell that yields a set cell value
    sufficiently close to the goal.

Data Tables
  • Most spreadsheets contain assumptions about
    certain parameters or inputs to the model. Based
    on input assumptions we often compute the output
    of interest. A data table in Excel makes it easy
    to vary one or two inputs simultaneously and
    perform a sensitivity analysis.
  • One-way table allows you to determine how
    changing one variable will change the value of
    several output measures.
  • Two-way table allows you to determine how
    changing two variables would change a single

Data Tables
One- Way Table
Two- Way Table
Left Col. Contains values of the input parameter
for which you want to conduct sensitivity of
performance measures revenue, cost and
profit. Top row contains the formulas for
revenue, cost and profits
The two input variables are varied to calculate
the performance measure , profits. Left col. has
the values for input variable 1 Top row has the
values for input variable 2. Upper left corner
contains the formula for profits
Optimization with Excel Solver
  • If you are trying to chose the best course of
    action that would maximize/minimize (optimize) a
    certain objective, Excel Solver helps you do
    that. More formally, using Solver, you will find
    the values of certain cells in a spreadsheet that
    optimizes your objective function which depends
    on these particular cell values.
  • An optimization spreadsheet has three parts The
    Target Cell (your objective to max/min), The
    Changing Cell (your choice variables) and the
    Constraints on choice variables (optional).
  • Any specification of the Changing Cell, which
    satisfy the problem constraints is a feasible
    solution. The optimal solution is found among the
    feasible solutions.

Problem formulation determines the solution
technique for Solver
Solver uses …
Simplex Method
Linear Problems
Non-Linear Problems
GRG2 Method
Problems with Integer Variables
Branch Bound Method
Not so efficient
Solver Messages
  • Solver could not find a feasible solution No
    solution satisfies all constraints. Then Look
    into your constraints and see what is driving
  • The set of values do not converge This
    means if the target cell is to be maximized, the
    changing cell values can be set arbitrarily
    large, and this leads to unlimited optimal
    objective function value. This means your model
    has an error. Check your constraints and ensure
    you are not missing a constraint on a choice

Useful Excel Functions and Commands
F4 This function key allows you to easily
change the reference of a particular cell from
absolute to relative (or vice verse).
How to Fill in a series of Data in a column/row
Method 1 Input two data values to provide the
desired trend in the data. Highlight rows (col.s)
where you want the data to be inserted. Go to
EDIT/FILL/SERIES/Choose LINEAR/ Autofill. Method
2 Input the first data point. EDIT/FILL/SERIES/Ch
oose Linear/ Provide step value and stop value.
(useful if you have to fill in a large list of
In this class…
  • You have seen how to set up a spreadsheet for
  • You have seen how to use different techniques to
    achieve the same outcome. The enumeration
    technique, goal seek and data tables.
  • You have seen how to set up an optimization
    model for solver.

Useful Excel Functions and Commands
Copying a formula to a a large range Double
click the drag icon For this function, you need
to have the drag icon function on (go to tools/
options to put it on). There should be data to
the left of the formula range. Or Highlight
the range to copy the formula, write the formula
, then press CTRLENTER
About PowerShow.com