MGS3100 Spreadsheet Modeling Chapter 2

1. MGS3100Spreadsheet ModelingChapter 2 Slides 2a: Introduction

2. Models and Data • Useful (quantitative) models are developed based on relevant data (numbers); models without data are at best theoretical abstractions • Data are often collected according to the requirements of models • time series vs. cross-sectional • aggregated vs. disaggregated

3. Count Measure Rank Results Constant Variable Coefficient Precision Numbers in Models

4. Price Volume Sales volume Production volume Demand Revenue Profit Cost Overhead cost Sunk cost Fixed cost Variable cost Total cost Breakeven point Crossover point Terminology and Relationships

5. Basic Deterministic Models • Profit Model • Profit = Revenue - Total Cost • Profit = Price*Units - (FC + VC*units) • Break-even point • Profit = 0, or Revenue = Total Cost • Units = Fixed Cost/(Price - Var. Cost) • Crossover Point • Total Costa = Total Costb • Units = (FCa - FCb)/(VCb - VCa)

6. Model Validation: Dimensional analysis • Multiplication : Apples x Apples = Apples2 • Division: Apples/Days = Apples/Days • Cancellation: (Apples/Day) x Days= Apples • Dissimilar addition: Apples + Oranges = Apples + Oranges • Similar addition: Apples + Apples = Apples • Congruity: Apples = Apples • Incongruity: Apples/Oranges2Oranges

7. Spreadsheet Modeling • Inputs should be logically grouped • Primary outputs should be easy to read • Input and output data should be labeled • Don’t embed parameters in a formula: use cell references • Use range names • Use fonts and color but don’t overuse them

8. Sensitivity Analysis • The business world is a dynamic environment - stock market, interest rates, currency, oil prices, politicians! • How do changes in constants affect the model solution? • Look to both sides before you cross the business street! (upper and lower bounds)

9. What-if Analysis • Decision variables are under the control of the manager • Testing different values will give the decision maker a sense of how they will affect the outcome • What happens if this value is changed?

10. Graphical and Numerical Methods • Generate a range of possible changes • Input changes into the model and examine the outcome • If there are too many variables, use graphs

11. What Next? • You have analyzed the process • You have determined the factors important to your situation • You have examined the relationships • You have represented the model in a usable form • You have checked for the sensitivity of the model

12. Model Validation • Logical test • Are the assumptions realistic? • Do the model results reflect reality? • Obtain test data and “test drive” the model • Split the data (holdout sample) • Make model using one part • Test the validity using the other data set

13. Intelligent Spreadsheet Use • Organization • Work smart, not hard • Design • Appearance

14. Organization • Home screen should identify and describe the subject model • Inputs should be logically grouped • Primary outputs should be easy to read • Use tabs (new sheets) to group work • Model should be well documented

15. Work Smart, Not Hard • Never embed a parameter in a formula - always use a named cell (range) • Name a cells (ranges) that are referenced in other formulas • Use absolute references to avoid problems when moving cells • Use range names that anyone will understand

16. Design • Clean and professional in appearance • Easy to use and understand • Keep a steady flow in your model • It must be accurate!

17. Appearance • Use a spell checker! • Don’t overuse fonts and color • Don’t try to put too much on a page