Chapter 11 Spreadsheet Modeling and Analysis - PowerPoint Presentation

1. Chapter 11Spreadsheet Modeling and Analysis

2. Predictive modeling is the heart and soul of business decisions.Building decision models is more of an art than a science.Creating good decision models requires: - solid understanding of business functional areas - knowledge of business practice and research - logical skillsIt is best to start simple and enrich models as necessary.Predictive Decision Modeling

3. Simple mathematics“Back-of-the-envelope” calculations can provide the basis for a more formal modelInfluence diagramsIntroduced in Chapter 1, an influence diagram is a logical and visual representation of key model relationshipsStrategies for Predictive Decision Modeling

4. A restaurant customer dines 6 times a year and spends an average of $50 per visit.The restaurant realizes a 40% margin on the average bill for food and drinks.Annual gross profit on a customer = $50(6)(0.40) = $12030% of customers do not return each year.Average lifetime of a customer = 1/0.3 = 3.33 yearsAverage gross profit during a customer’s lifetime = $120(3.33) = $400Example 11.1: The Economic Value of a Customer

5. V = value of a loyal customerR = revenue per purchaseF = purchase frequency (number of visits per year)M = gross profit margin (expressed as a fraction)D = defection rate (fraction of customers not returning each year)Developing a General Model for Example 11.1

6. Example 11.2: Developing a Decision Model Using an Influence Diagram P = profitR = revenueC = costp = unit pricec = unit costF = fixed costS = quantity soldD = demandQ = quantity producedCost = fixed cost + variable cost C = F + cQ Revenue = price times quantity sold R = pSQuantity sold = Minimum{demand, quantity sold} S = min{D, Q}Profit = Revenue − Cost P = p*min{D, Q} − (F + cQ) (11.2)

7. Principles of good spreadsheet design:Separate the data, model calculations, and model outputs clearly in designing a spreadsheet. Do not use input data in model formulas, but reference the spreadsheet cells that contain the data. In this way, if the data change or you want to experiment with the model, you need not change any of the formulas, which can easily result in errors.Implementing Models on Spreadsheets

8. (Example 1.8 in Chapter 1)The scenario involves a manufacturer who can produce a part for $125/unit with a fixed cost of $50,000. The alternative is to outsource production to a supplier at a unit cost of $175.Models for total cost:Example 11.3: A Spreadsheet Model for the Outsourcing Decision

9. Excel spreadsheetExample 11.3 Continued

10. A firm wishes to determine the best pricing for one of its products to maximize revenue.Sales = -2.9485 × price + 3,240.9Total revenue = price × sales= price × (-2.9485 × price + 3,240.9)= -2.9485 × price2 + 3,240.9 × priceExample 11.4: Pricing Decision Spreadsheet Model

11. Example 11.5: Spreadsheet Implementation of the Profit ModelAssume that unit price = $40, unit cost = $24, fixed cost = $400,000, and demand = 50,000. The decision variable is the quantity produced; assume a value of 40,000 units.

12. Building spreadsheet models (called spreadsheet engineering) is part art and part science.Spreadsheets need to be accurate, understandable, and user friendly.Verification is the process of ensuring that a model is accurate and free from logical errors.Spreadsheet Quality

13. Improve the design and format of the spreadsheet itself.Sketch a logical design of the spreadsheet.Break complex formulas into smaller piecesDesign the spreadsheet in a form that the end user can easily interpret and understandImprove the process used to develop a spreadsheet.Work on one part at a timeCheck formula results with simple numbersInspect your results carefully and use appropriate tools available in Excel.Use Excel auditing toolsImproving Spreadsheet Quality

14. Gross profit = sales – cost of goods soldOperating expenses = administrative expenses + selling expenses + depreciation expensesNet operating income = gross profit – operating expensesEarnings before taxes = net operating income – interest expenseNet income = earnings before taxes – taxesExample 11.6: Modeling Net Income on a Spreadsheet

15. Example 11.6 ContinuedSimple model: net income = sales - cost of goods sold - administrative expenses - selling expenses – depreciation expenses - interest expense - taxes

16. Data-model formatExample 11.6 Continued

17. Pro forma income statementExample 11.6 Continued

18. A wide variety of practical problems in business analytics can be modeled using spreadsheets.A useful spreadsheet model need not be complex; often, simple models can provide managers with the information they need to make good decisions.Spreadsheet Applications in Business Analytics

19. The manager of a loan processing department wants to know how many employees will be needed over the next several months to process a certain number of loan files per month so she can better plan capacity. Different types of products, such as a 30-year fixed rate mortgage, 7/1 ARM, FHA loan, or a construction loan, each varying in complexity and time to complete.Data:Forecasts: 700 loan applications in May, 750 in June, 800 in July, and 825 in August. Each employee works productively for 6.5 hours each day, and there are 22 working days in May, 20 in June, 22 in July, and 22 in August. Example 11.7: A Predictive Model for StaffingPredict the number of full time equivalent (FTE) staff needed each month to ensure that all loans can be processed.

20. Spreadsheet ModelExample 11.7 Continued

21. Many practical models incorporate multiple time periods that are logically linked together.Taking a systematic approach to putting the pieces together logically can often make a seemingly difficult problem much easier.Models Involving Multiple Time Periods

22. Moore Pharmaceuticals needs to decide whether to conduct clinical trials and seek FDA approval for a newly developed drug.R&D cost = $700 millionClinical trials cost = $150 millionMarket size = 2 million peopleMarket size growth = 3% per yearMarket share = 8%Market share growth = 20% per yearMonthly revenue/prescription = $130Monthly variable cost/prescription = $40Discount rate = 9%Example 11.8: New-Product Development

23. Example 11.8 ContinuedSpreadsheet Model

24. Example 11.8 ContinuedSpreadsheet Formulas

25. One-time purchase decisions often must be made in the face of uncertain demand.Newsvendor Problem: How many newspapers to purchase each day?C = cost to purchase a newspaperQ = number of newspapers the vendor purchasesD = number of newspapers demandedR = revenue from selling a newspaperS = salvage value of unsold newspapersNet profit = R × quantity sold + S × surplus quantity – C ×Q= R(min{Q,D}) + S(max{0,Q−D}) − CQSingle-Period Purchase Decisions

26. A small candy store makes Valentine’s Day gift boxes that cost $12 and sell for $18.In the past, at least 40 boxes have sold by Valentine’s Day but the actual amount is unknown.After the holiday, boxes are discounted 50%. C = $12, R = $18, S = $9Net profit = R(min{Q,D}) + S(max{0,Q−D}) − CQ =18(min{Q,D}) + 9(max{0,Q−D}) − 12Q Note that D is actually uncertain and can be modeled using a probability distribution. Example 11.9: A Single-Period Purchase Decision Model

27. Spreadsheet modelExample 11.9 Continued

28. Find the number of reservations to accept to effectively fill capacity knowing that some customers may not use their reservations.A common practice in these industries is to overbook reservations.When more customers arrive than can be handled, the business usually incurs some cost to satisfy them. Therefore, the decision becomes how much to overbook to balance the costs of overbooking against the lost revenue for underuse.Overbooking Decisions

29. A popular resort hotel has 300 rooms.The room rate is $120 per night.Reservations can be cancelled by 6:00 p.m.Cost of overbooking is $100 per occurrence.Decision variable = number of reservations to accept.Customer demand and cancellations are actually random variables.Example 11.10: A Hotel Overbooking Model

30. Models cannot capture every detail of the real problem, and managers must understand the limitations of models and their underlying assumptions.Validity refers to how well a model represents reality.Judging validity:Identify and examine the assumptions made in a model to see how they agree with our perception of the real worldCompare model results to observed resultsModel Assumptions, Complexity, and Realism

31. Start work at age 22, earning $50,000 per year.Expect a salary increase of 3% per year.Required to contribute 8% to retirement.Employer contributes 35% of that amount.Expect an annual return of 8% on the portfolio.Example 11.11: A Retirement-Planning Model

32. Validity issues:Whether the assumptions of the annual salary increase and return on investment are reasonable Whether they should be assumed to be the same each yearHow the model calculates the return on investment – Current year balance only? End-of-year balance plus current-year contributions? Monthly?Example 11.11 Continued

33. Sources of dataSubjective judgmentExisting databasesAnalysis of historical dataSurveys, experiments, or other methods of data collectionData and Models

34. Many predictive models in business analytics use empirical data in conjunction with logical model development. In other words, we observe some phenomenon and then try to develop a model that best reflects the data that we observe.Using Empirical Data in Models

35. In the spring, a department store introduces a new line of bathing suits that sell for $70.The store purchases 1000 of these bathing suits.During the prime selling season (50 days), the store sells an average of 7 units per day at full price.Around July 4th, the price is marked down 70% to sell off remaining inventory.During a special sale, the price was reduced to $49 and sales increased to 32.2 units per day.If the stored decides to sell at full retail price for x days and then discounts the price by y% for the remainder of the selling season, followed by the clearance sale, what total revenue could they predict?Example 11.12: Modeling Retail Markdown Pricing Decisions

36. Modeling daily sales as a function of priceAssume a linear trend model between sales and price:Example 11.12 Continueddaily sales = a – b × price 7 = a – b × 70 32.2 = a – b × 49Daily sales = 91 – 1.2 × price

37. Modeling total revenueSell at full retail price for x days:Full retail price revenue = 7 units/day × x days ×$70.00 = $490.00xMarkdown revenue = units sold x markdown priceMarkdown price = $70(100% - y%)Daily sales = a - b x markdown price = 91 - 1.2 x $70 x (100% - y%)Units sold at markdown = daily sales x (50 - x), as long as this is less than or equal to the number of units remaining in inventory from full retail sales. Clearance revenueClearance inventory = 1000 - units sold at full retail - units sold at markdown = 1,000 - 7x - [91 - 1.2 × $70.00 × (100% - y%)] × (50 - x)Clearance price revenue = [1,000 - 7x - [91 - 1.2 × $70.00 × (100% - y%)] × (50 – x)] × $21.00Total revenue = sum of full retail, markdown, and clearance revenuesExample 11.12 Continued

38. Spreadsheet modelExample 11.12 Continued

39. Useful Excel toolsData validationRange NamesForm Controls Developing User-Friendly Applications

40. Outsourcing Decision ModelSuppose that we know that the unit cost of any item is at least $10 but no more than $100. If a cost is $47.50, for instance, a misplaced decimal would result in either $4.75 or $475, which would clearly be out of range.Data > Data Tools > Data ValidationExample 11.13 Using Data ValidationError alert message

41. Suppose that the unit price is stored in cell B13 and quantity sold is in cell B14. Suppose you wish to calculate revenue in cell C15. Instead of writing the formula =B13*B14, you could define the name of cell B13 as “UnitPrice” and the name of cell B14 as “QuantitySold.” Then in cell C15, write the formula =UnitPrice*QuantitySold.Range Names

42. Form controls are buttons, boxes, and other mechanisms for inputting or changing data on spreadsheets easily that can be used to design user-friendly spreadsheets. Activate the Developer tab on the Excel ribbon: File > Options > Customize Ribbon and check the box for Developer.Form Controls

43. ButtonCombo boxCheck boxSpin buttonList boxOption buttonGroup boxLabelScroll barTypes of Form Controls

44. Spin button for the supplier unit cost (which we will assume might vary between $150 and $200 in increments of $5) Scroll bar for the production volume (in unit increments between 500 and 3000 units) Developer > Controls > Insert; then click in the spreadsheet. Right click and set Format Control options.Example 11.14 Using Form Controls for the Outsourcing Decision Model

45. Predictive analytical models are based on assumptions about the future and incorporate variables that most likely will not be known with certainty. It is usually important to investigate how these assumptions and uncertainty affect the model outputs.Analyzing Uncertainty and Model Assumptions

46. Spreadsheet models allow you to easily evaluate what-if questions by changing input values and recalculating model outputs.Systematic approaches to what-if analysis that are available in Excel make the process easier and more useful.Data tablesScenario ManagerGoal SeekWhat-If Analysis

47. Uncertain demand in the Profit Model.Excel table using formulas:Example 11.15: Using Excel for What-If AnalysisVisualization of profit:

48. Data Tables summarize the impact of one or two inputs on a specified output.A one-way data table evaluates an output variable over a range of values for a single input variable. Two-way data tables evaluate an output variable over a range of values for two different input variables.Data Tables

49. Create a range of values for some input cell in your model that you wish to vary.Enter the cell reference for the output variable in your model that you wish to evaluate in the row above the first value and one cell to the right of the column of input values.Select the range of cells that contains both the formulas and values you want to substitute.Data > What-If Analysis > Data TableType the cell reference for the input cell in your model in the Column input cell box.Creating (Column) Data Tables in Excel

50. Create a column of demand values (column E).Enter =C22 in cell F3 (to reference the output cell).Highlight the range E3:F11.Data > What-If Analysis > Data TableEnter B8 for Column input cell in the Data Table dialog (tells Excel that column E refers to demand)Example 11.16: A One-Way Data Table for Uncertain Demand5123

51. Create a second output column for revenue.Enter =C15 in cell G3.Highlight E3:G11.Data > What-If Analysis > Data TableEnter B8 for Column input cell in the Data Table dialog Example 11.17: One-Way Data Tables with Multiple Outputs

52. Type a list of values for one input variable in a column and a list of input values for the second input variable in a row, starting one row above and one column to the right of the column list. In the cell in the upper left-hand corner immediately above the column list and to the left of the row list, enter the cell reference of the output variable you wish to evaluate. Select the range of cells that contain this cell reference and both the row and column of values.In the Row input cell of the dialog box, enter the reference for the input cell in the model that corresponds to the input values in the row. In the Column input cell box, enter the reference for the input cell in the model that corresponds to the input values in the column.Two-Way Data Tables

53. Evaluate the impact of both unit price and unit costCreate a column of unit prices.Create a row of unit costs.Enter =C22 in upper left corner.Select range of data (not including headings).In Data Table dialogEnter B6 for Row input cell. Enter B5 for Column input cell.Example 11.18: A Two-Way Data Table for the Profit Model

54. Allows creation of scenarios – sets of values that are saved and can be substituted in worksheets.Data > What-If Analysis > Scenario ManagerClick Add button to open Add Scenario dialogEnter a Scenario NameIn Changing Cells box, enter cell references to include in scenario.In the Scenario Values dialog enter values for each of the changing cells.Click Show button to display the resultsScenario Manager

55. Evaluate 4 strategies for pricing and discounts on the bathing suits in the Markdown Pricing Model. Example 11.19: Using the Scenario Manager for the Markdown Pricing Model

56. If you know the result that you want from a formula but are not sure what input value the formula needs to get that result, use the Goal Seek feature in Excel.Data > What-If Analysis > Goal SeekSet cell contains the cell reference for the formula you want to resolveTo value is the target value you want the formula to return.By changing cell is the reference of the input value that you want to change to achieve the set cell value.Goal Seek

57. Find the value of demand for which manufacturing cost equals purchased costSet cell: B19To value: 0By changing cell: B12.Example 11.20: Finding the Break-Even Point in the Outsourcing ModelBreak-even volume

58. Parametric sensitivity analysis is the term used by Analytic Solver Platform for systematic methods of what-if analysis. A parameter is simply a piece of input data in a model.You can create data tables and tornado charts.Parametric Sensitivity Analysis Using Analytic Solver Platform

59. Create a one-way data table to evaluate the profit as the unit price in cell B5 is varied between $35 and $45 in the profit model.Define cell B5 as a parameter by selecting B5, click Parameters and select Sensitivity.In the Function Arguments dialog, specify the range of values.Example 11.21: Creating Data Tables with Analytic Solver Platform

60. Click the Reports button and click on Parameter Analysis from the Sensitivity menu. This displays a Sensitivity Report dialog; use the arrows to move cells into the panes on the right.Example 11.21 ContinuedOne-way data table result

61. Example 11.21 ContinuedTo create a two-way data table, define both inputs as parameters and in the Sensitivity Report dialog, be sure to check the box Vary Parameters Independently near the bottom.To create charts to visualize the data tables, click the Charts button, and then click Parameter Analysis from the Sensitivity menu.Replace the cell references ($B$5, $B$6, and $C$22) by descriptive names to facilitate understanding the results.

62. A tornado chart shows the impact that variation in a model input has on some output while holding all other inputs constant.Shows which inputs are the least and most influential on the output.Helps you select the inputs that you would want to further analyze.Analytic Solver Platform automatically identifies all the data input cells on which the output cell depends and creates the chart.Tornado Charts

63. In the Profit Model spreadsheet, select cell C22; then click the Parameters button and choose Identify. Example 11.22: Creating a Tornado Chart in Analytic Solver Platform