# 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
Title:

## Spreadsheets Modeling for Managerial Decision Making

Description:

### 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
Category:
Tags:
Transcript and Presenter's Notes

Title: Spreadsheets Modeling for Managerial Decision Making

1
Making
• OPNS 450 WINTER 2008

2
Agenda for today
• Introductions
• Introduction to course material and course
requirements
• The Objective of this class
• Application of goal seek and data tables
• Introduction to optimization with Solver

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

4
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)

5
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
Organization (20) clarity documentation using
formula list, bordering grouping color
coding. Questions Answered (40 ) specific
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
6
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
printing
7
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
presentation.

8
Questions?
9
Making good managerial decision can be
challenging because

10
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
-
Demand
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
11
The Art of Decision Modeling
Real World
Model World
Problem Definition
Assumptions and Model Structures
FORMULATION
Choice of Performance Criteria and Analysis
INTERPRETATION
Results and Sensitivity
Solution
12
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
13
Session 1 Essentials of Excel
• Structuring Spreadsheets Basic Excel Commands
• Application of Goal Seek
• Application of Data Tables (One-way / Two-way
Tables)
• Introduction to optimization with Solver

Cases Break Even Analysis Monopoly Pricing
14
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.

15
Goal Seek
• Goal seek is an equation solver!
• To start insert any number for the changing
cell
• 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.

16
Data Tables
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
output.

17
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
18
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.

19
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
Efficient
20
Solver Messages
• Solver could not find a feasible solution No
solution satisfies all constraints. Then Look
into your constraints and see what is driving
this.
• 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
variables.

21
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
data)
22
In this class
• You have seen how to set up a spreadsheet for
analysis
• 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.

23
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