BUS125 Final Study Guide.docx - Chapter 17 Chapter 64...

This preview shows page 1 - 2 out of 2 pages.

Chapter 17, Chapter 64 begins left columnChapter 64, 73, Crystal Ball Right columnChapter 17: Sensitivity analysis with data tablesMost worksheet models contain assumptions about certain parameters or inputs to the model; in lemonade example, inputs includeThe price for which a glass of lemonade is soldThe unit cost of producing a glass of lemonadeThe sensitivity of demand for lemonade to price chargedThe annual fixed cost of running a lemonade standBased on input assumptions, we can compute outputs of interest; in lemonade example, outputs of interest includeAnnual profitAnnual revenueAnnual variable costAssumptions about input values can be in errorSensitivity analysis determines how a spreadsheet’s outputs vary in response to changes to its inputsWith one-way data table, we can determine how changing one input changes any number of outputsWith two-way data table, we can determine how changing two inputs changes asingle outputQuestions:I’m thinking of starting a store in the local mall to sell gourmet lemonade. Before opening the store, I’m curious about how my profit, revenue, and variable costs will depend on the price I charge and the unit cost.Suppose we want to know how changes in price affect annual profit, revenue, and variable cost (use one-way data table since we’re only changing one input)Setting up one-way data table1.List input values in one column2.Move over a column and up one row; in these cells, list the formulas we want the data table to calculate3.Select table range a.Begins one row above first input; its last row is the row containing the last input valueb.The first column in the table range is the column containing the inputs; its last column is the last column containing an output4.Display the Data taba.In Forecast group, click What-If analysis, and then click Data TableFor column input cell, use the cell in which you want the listed inputs (i.e., values listed in the first columnof the data table range) to be assignedWhen we click OK, Excel creates one-way data tableSuppose we want to determine how annual profit varies as price varies ($1.50 through $5.00, in $0.25 increments) and unit cost varies (from $0.30 through $0.60, in $0.05 increments) (use two-way data table since we’re changing two inputs)Setting up two-way data table1.List values for one input down the first column of the table range and the valuesfor the other input in the first row of the table rangea.Two-way data table can have only one output cell, and the formula for the output must be placed in the upper-left corner of the table range2.Select the table range3.Display Data taba.In the Forecast group, click What-If analysis, and then click Data Tablei.Select respective inputs for column and row1.Ensure that the values in the first column of the table ranges are used as(prices), and the values in the first row are used as (unit variable costs)Notes on this problem:As we change input values in a worksheet, the values calculated by a data table change too

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture