Tutorial 10: Performing What-If Analyses

1. Tutorial 10: Performing What-If Analyses

2. Objectives • Explore the principles of cost-volume-profit relationships • Perform a basic what-if analysis • Use Goal Seek to calculate a solution • Create a one-variable data table • Create a two-variable data table • Create and apply different Excel scenarios • Generate a scenario summary report • Generate a scenario PivotTable report • Explore the principles of price elasticity • Run Solver to calculate optimal solutions • Create and apply constraints to a Solver model • Save and load a Solver model

3. Visual Overview

4. Data Tables and What-If Analysis

5. Understanding Cost-Volume-ProfitRelationships • Cost-volume-profit (CVP) analysis • Studies the relationship between expenses, sales volume, and profitability • Helps predict the effect of cutting overhead or raising prices on a company’s net income

6. CVP (Break – Even Analysis Cont. • Used to determine at what point a product or activity becomes profitable • In break even analysis, there are fixed costs that do not change • There are variable costs based on the number of units sold • These are the costs of raw materials and direct labor • Break-Even Analysis determines how to just pay for costs (net income of zero) • The break-even point is that point where the total net profit is 0

7. Comparing Expenses and Revenue • Types of expenses • Variable expenses change in proportion to the amount of business a company does • Fixed expense must be paid regardless of sales volume • Total Expenses = Fixed expenses + variable expenses

8. Determining the Break-Even Point • Break-even point: revenue equals expenses • A CVP chart shows the relationship between expenses and revenue

9. Ways to Perform What-If Analysis in Excel • Goal Seek • Automates trial-and-error process • One-variable data tables • Works by changing the value of one input variable • Two-variable data tables • Works by changing the value two input variables • The Scenario manager • Input variables are changed using two or more scenarios

10. Performing a What-If Analysis with Goal Seek • What-if analysis lets you explore the impact of changing different values in a worksheet • Goal Seek automates trial-and-error process • Allows you to specify a value for a calculated item • Excel returns input value needed to reach the goal • Goal Seek dialog box – determining break-even point

11. Performing a What-If Analysis with Goal Seek

12. One-variable Data Tables (Introduction) • Use to calculate different expected outcomes by changing the value of a single variable • Note that we could do this by hand using mixed formula references • When creating data tables, the structure of the data must be in a specific form

13. Working with Data Tables • Display results from several what-if analyses • One-variable data table • Specify one input cell and any number of result cells • Useful in business to explore how changing a single input cell can impact several result cells

14. One-variable Data Tables (Creating 1) • Set up the worksheet for a data table

15. One-variable Data Tables (Creating 2) • Select the columnar grid for the data table Formula to copy Inputvalues

16. One-variable Data Tables (Creating 3) • Select the input variable to be modified (interest rate) • Select Column input cell because the data are columnar

17. One-variable Data Tables (Result)

18. Charting a One-Variable Data Table • Gives a better picture of relationship between sales volume, revenue, and total expenses

19. Creating a Two-Variable Data Table • Conceptually, they work like one-variable data tables • Two inputs are changed instead of one input • The resulting output is a grid • Analyzes a variety of combinations simultaneously • Uses two input cells, but displays only a single result value • Must identify the row input cell and the column input cell

20. Two-variable Data Tables (Creating 1) • Set up the worksheet for a data table

21. Two-variable Data Tables (Creating 2) • Select the two-dimensional grid for the data table

22. Two-variable Data Tables (Creating 3) • Select the two input variables to be modified

23. Two-variable Data Tables (Result)

24. Creating a Two-Variable Data Table

25. Charting a Two-Variable Data Table

26. Visual Overview Scenario Manager

27. What-If Scenarios

28. The Scenario Manager (Introduction) • Use the Scenario Manager to vary one or more input value • Each unique combination of input values is called a scenario • Input cells are called changing cells • There can be many scenarios • Scenarios usually vary from best possible case to worst possible case • Scenario input must be well-structured

29. The Scenario Manager (Cont) • Using the Scenario Manager can be confusing as you don’t see the input values to the scenarios • Create scenarios to perform a what-if analysis with more than two input cells • Define names for all input and result cells that you intend to use in the analysis • Use named ranges for the input values to simplify the creation of scenarios • Defined names automatically appear in reports generated by the Scenario Manager • Using defined names makes it easier to work with scenarios and understand the scenario reports

30. Using the Scenario Manager • Use the Scenario Manager to define scenarios • Each scenario includes a scenario name, input cells, and values for each input cell • Number of scenarios is limited only by computer’s memory • Input cells are referred to as changing cells • Contain values that are changed under the scenario • Can be located anywhere in the worksheet

31. Using the Scenario Manager • Edit Scenario dialog box

32. Using the Scenario Manager • Scenario Values dialog box

33. Using the Scenario Manager • View the effect of each scenario by selecting it in the Scenario Manager dialog box

34. Using the Scenario Manager • Editing a Scenario • Edit the assumptions to view other possibilities • Worksheet calculations are automatically updated to reflect the new scenario

35. Creating a Scenario Summary Report • Displays the values of the input cells and result cells under each scenario • Tabular layout makes it simpler to compare results of each scenario • Automatic formatting makes it useful for reports and meetings

36. Scenario Summary Report

37. Creating a Scenario PivotTable Report • Displays results from each scenario as a pivot field in a PivotTable

38. Creating a Scenario PivotTable Report • Results for the table can be displayed in a PivotChart

39. Visual Overview (Solver)

40. Using Solver

41. Finding an Optimal Solution Using Solver • Solver searches for the optimal solution to a problem involving several variables • Arrives at optimal solutions through an iterative procedure • Because it is an add-in, Solver might need to be activated

42. Setting Up Solver to Find a Solution • Specify three Solver parameters • Objective cell • Variable (or changing) cells • Constraints

43. Setting Up Solver Constraints • Constraints confine the solution within a reasonable set of defined limits • Constraints supported by Solver • <=, >=, and = • integer or int • binary or bin • dif or AllDifferent

44. Setting Up Solver Constraints • Add Constraint dialog box

45. Setting Up Solver Constraints • Completed Solver Parameters dialog box

46. Creating a Solver Answer Report • Solver can create three different reports • Answer report (the most useful) • Sensitivity report • Limits report

47. Sections of a Solver Answer Report • Titles • Information about the objective cell: location, cell label, and cell’s original value and final values • Information about the changing cells (variable cells): location, column and row label, original value, and final value of each cell • Information about the constraints: not binding and binding, and slack

48. Creating a Solver Answer Report

49. Choosing a What-If Analysis Tool

50. Saving and Loading Solver Models • Save parameters in cells in the worksheet • Reload the parameters from the worksheet cells without having to reformulate the problem • Create dozens of models that you can load and apply to your analysis as new data is entered • Load/Save Model dialog box