Tutorial 10: Performing What-If Analyses

1. Tutorial 10: Performing What-If Analyses

2. Objectives • Review the principles of cost-volume-profit relationships • Discuss Excel what-if analysis tools

3. 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

4. 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

5. 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

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

7. 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

8. 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

9. Performing a What-If Analysis with Goal Seek

10. 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

11. 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

12. 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

13. Creating a Two-Variable Data Table

14. 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

15. 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

16. 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

17. Using the Scenario Manager • Edit Scenario dialog box

18. Using the Scenario Manager • Scenario Values dialog box

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

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

21. 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

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

23. 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

24. Setting Up Solver Constraints • Add Constraint dialog box

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

26. Choosing a What-If Analysis Tool