Lecture 1.pdf

LECTURE 1 INTRODUCTION TO SPREADSHEET BUSINESS MODELING 1 AGENDA  Course outline  Mathematical models to aid decision making  Basic spreadsheet skills 2 MANAGERIAL DECISION MAKING Ana...

LECTURE 1 INTRODUCTION TO SPREADSHEET BUSINESS MODELING 1 AGENDA  Course outline  Mathematical models to aid decision making  Basic spreadsheet skills 2 MANAGERIAL DECISION MAKING Analysis Model Results Symbolic Interpretation Abstraction World Managerial Real Judgment World Management Intuition Decisions Situation 3  A SCIENTIFIC APPROACH Define the Problem Identify the Alternatives Determine the Criteria Modeling Problem Analysis Solving Evaluate the Alternatives Recommend an Alternative Implement the Decision Decision Evaluate the Results 4 Approach could be iterative with a feedback loop EXAMPLE  You operate a bakery and need to determine advertising expenses for November and December.  Previous years’ experience tells you that the monthly demand is correlated with the monthly advertising expenditure. The effectiveness of advertising depends on the month:  𝑑𝑒𝑚𝑎𝑛𝑑(𝑑𝑜𝑧𝑒𝑛)𝑚𝑜𝑛𝑡ℎ = 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡𝑚𝑜𝑛𝑡ℎ × ln 𝑎𝑑𝐸𝑥𝑝𝑒𝑛𝑠𝑒𝑚𝑜𝑛𝑡ℎ  Past experience tells us that 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡𝑁𝑜𝑣 = 50, 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡𝐷𝑒𝑐 = 75  Selling price: $96/dozen  Cost of ingredients = $50/dozen  Other fixed costs = $10,000 per month 5  MATHEMATICAL MODELS  Model  Abstraction of a real thing or process; some elements must be omitted  Contains decision variables and represents a system in mathematical terms  Finds values of decision variables which will improve system performance  Used as a guide for aiding decision-making  Uses of Models  To improve the existing decision  To increase the understanding of a system  To explicitly make trade-offs for a decision 6 ELEMENTS OF MODELS  Decisions: choices, possible actions, controllable variables; decision variables  Outcomes: performance measures, criteria, eventual consequences; objective function  Data: information, environmental conditions, uncontrollable variables; parameters  Structure: relations, causes and effects, logics; equations or inequalities 7 DECISIONS, OUTCOMES, DATA - EXAMPLE  Decisions: choices, possible actions, controllable variables; decision variables  How much should we spend on advertising in November and December?  First attempt: $1,000 per month  Outcomes: performance measures, criteria, eventual consequences; objective function  Maximize sum of monthly income (profit) in November and December  Data: information, environmental conditions, uncontrollable variables; parameters  Selling price (per dozen cookies)  Cost of ingredients (per dozen cookies)  Advertising effectiveness factor (𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡𝑚𝑜𝑛𝑡ℎ ) 8 STRUCTURE OF MODEL - EXAMPLE Net income Revenue Cost Selling price Production (dozen) cost Monthly Cost of Other fixed demand chocolate (lb) costs 9 Monthly advertising expense  STRUCTURE OF MODEL - EXAMPLE  Structure: relations, causes and effects, logics; equations or inequalities  Monthly income = 𝑟𝑒𝑣𝑒𝑛𝑢𝑒 – 𝑐𝑜𝑠𝑡𝑠  Revenue = 𝑠𝑒𝑙𝑙𝑖𝑛𝑔 𝑝𝑟𝑖𝑐𝑒 𝑝𝑒𝑟 𝑑𝑜𝑧𝑒𝑛 × 𝑑𝑒𝑚𝑎𝑛𝑑(𝑑𝑜𝑧𝑒𝑛)  Cost of production = 𝑐𝑜𝑠𝑡 𝑝𝑒𝑟 𝑑𝑜𝑧𝑒𝑛 × 𝑑𝑒𝑚𝑎𝑛𝑑(𝑑𝑜𝑧𝑒𝑛𝑠)  Operating costsmonth = 𝑎𝑑𝐸𝑥𝑝𝑒𝑛𝑠𝑒𝑚𝑜𝑛𝑡ℎ + 𝑜𝑡ℎ𝑒𝑟 𝑓𝑖𝑥𝑒𝑑 𝑐𝑜𝑠𝑡𝑠  Demand(dozen)month = 𝑎𝑑𝐸𝑓𝑓𝑒𝑐𝑡𝑚𝑜𝑛𝑡ℎ × ln 𝑎𝑑𝐸𝑥𝑝𝑒𝑛𝑠𝑒𝑚𝑜𝑛𝑡ℎ 10  SPREADSHEET MODEL Decisions Outputs Inputs Calculation 11 SPREADSHEET MODEL Highlight the output to increase user-friendliness. =$C$13*C24; Use of $ in “C13” creates an “absolute reference” that doesn’t change when we copy the formula to the next column. When we copy this cell to column D, we will see “=$C$13*D24” 12  BEST PRACTICE GUIDELINES IN SPREADSHEET DESIGN  Sketch the spreadsheet  Organize the spreadsheet into modules  Isolate input parameters  Keep it simple  Avoid long formulas  Formulas only contain cell references (i.e. not numbers); this simplifies spreadsheet auditing  Design for communication  Use Excel formatting options (outlines, colours, bold font, etc) to highlight certain cells for quick visual recognition  Document important data and formulas 13  “WHAT-IF” ANALYSIS  Questions:  What if we vary advertising expenses in November and December between $1,000 to $10,000 (can be different in each month)? Modify the corresponding decision cell (C5, D5).  What selling price would result in a total income of $0 (break-even)? Try different values for the parameter cell C13.  Better solution: Use scenario analysis functions in Excel.  Data Tab ⇒ Forecast ⇒ What-if Analysis  Data table – Re-compute the output as we vary ONE or TWO parameters along a range of values.  Scenario manager – Re-compute the output for different scenarios where we may vary any number of parameters.  Goal Seek – Determine the parameters that would give us the desired output.  See “ModelAnalysis.xlsx” 14  WHY SPREADSHEET MODELING ?  Examples of spreadsheets:  Microsoft Excel – still the industry standard…  Numbers  Google Spreadsheets  Open office spreadsheet  Advantages:  Ubiquity  Availability of Add-ins  Low cost alternative to more specialized tools (statistics, optimization)  Disadvantages:  Data size limitations, slow calculation speeds (large models)  Difficult to document and organize models 15  Often insufficient for advanced analysis