1 / 26

Tutorial 10: Performing What-If Analyses

Tutorial 10: Performing What-If Analyses. 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

jada
Download Presentation

Tutorial 10: Performing What-If Analyses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 New Perspectives on Microsoft Excel 2010

  3. Visual Overview New Perspectives on Microsoft Excel 2010

  4. Data Tables and What-If Analysis New Perspectives on Microsoft Excel 2010

  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 • Sometimes called break-even analysis New Perspectives on Microsoft Excel 2010

  6. 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 • Mixed expense is part variable and part fixed New Perspectives on Microsoft Excel 2010

  7. Determining the Break-Even Point • Break-even point: revenue equals expenses • A CVP chart shows the relationship between expenses and revenue New Perspectives on Microsoft Excel 2010

  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 New Perspectives on Microsoft Excel 2010

  9. Performing a What-If Analysis with Goal Seek New Perspectives on Microsoft Excel 2010

  10. 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 New Perspectives on Microsoft Excel 2010

  11. Charting a One-Variable Data Table • Gives a better picture of relationship between sales volume, revenue, and total expenses New Perspectives on Microsoft Excel 2010

  12. Creating a Two-Variable Data Table • 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 New Perspectives on Microsoft Excel 2010

  13. Creating a Two-Variable Data Table New Perspectives on Microsoft Excel 2010

  14. Charting a Two-Variable Data Table New Perspectives on Microsoft Excel 2010

  15. Tutorial • Steps 1 - 12 New Perspectives on Microsoft Excel 2010

  16. Visual Overview New Perspectives on Microsoft Excel 2010

  17. What-If Scenarios New Perspectives on Microsoft Excel 2010

  18. Using the Scenario Manager • 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 • 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 New Perspectives on Microsoft Excel 2010

  19. 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 New Perspectives on Microsoft Excel 2010

  20. Using the Scenario Manager • Edit Scenario dialog box New Perspectives on Microsoft Excel 2010

  21. Using the Scenario Manager • Scenario Values dialog box New Perspectives on Microsoft Excel 2010

  22. Using the Scenario Manager • View the effect of each scenario by selecting it in the Scenario Manager dialog box New Perspectives on Microsoft Excel 2010

  23. Using the Scenario Manager • Editing a Scenario • Edit the assumptions to view other possibilities • Worksheet calculations are automatically updated to reflect the new scenario New Perspectives on Microsoft Excel 2010

  24. 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 New Perspectives on Microsoft Excel 2010

  25. Scenario Summary Report New Perspectives on Microsoft Excel 2010

  26. Tutorial • Steps 13 - 23 New Perspectives on Microsoft Excel 2010

More Related