Operations Management Spreadsheet Modeling Notes
What is a Model?
• A model is a purposeful representation of the key factors of an object or
system and the relationships among those factors
➢ Abstract representation of a real situation
➢ Enough detail so that key factors and relationships are accurately
represented
➢ Omit unnecessary details
• “Everything should be made as simple as possible, but not simpler” (Albert
Einstein)
Types of Models
• Mental models
• Visual models
• Physical models
• Mathematical models
Characteristics of a Mathematical Model
• Usually motivated by a decision
• Inputs
➢ Quantities or factors that affect a decision
➢ Controllable inputs (decision variables)
➢ Uncontrollable inputs (parameters)
• Outputs
➢ Primary
➢ Secondary
The Spreadsheet Modeling Process
1. Turn off the computer. Draw a picture/diagram, identify controllable &
uncontrollable inputs, outputs (Influence Chart)
2. Sketch out overall plan for spreadsheet model. Determine where inputs,
intermediate calculations, and outputs will go
3. Develop the base case spreadsheet model
4. Test the model using trial values
5. Use the model to perform the needed analysis
6. Document the model so others can understand it
Influence Chart
• You should start the spreadsheet modeling process by drawing an influence
chart
• It is a simple diagram to show the relationships between inputs and outputs
in a spreadsheet model
• The goal is to define problem structure
• The chart ignores all available numerical data • It identifies the main elements of a model
• The chart helps to define the assumptions of the model
Evaluating Spreadsheet Models
• Correct
➢ Correct numerical answer for base case (i.e., “given” information)
• Flexible
➢ Accurate results if any of the input values are changed
➢ Each input value entered only once in the model
➢ Formulas contain only cell references, not numerical values
o Good: =B1+C1
o Bad: =B1+55
• Documented
➢ Descriptive labels, unit of measure, numerical formatting, cell formatting,
cell comments
➢ Printouts: include row/column headings, gridlines, footer
Finding the Breakeven Point
• What quantity results in a profit of $0?
➢ Calculate the breakeven point by hand

More
Less