Lecture 1.pdf
Document Details
Tags
Full Transcript
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