Reference no: EM13214760

In cost accounting determining the relationships between cost, volume and profit are very important. This assignment will have you use scenarios and goal seek to calculate breakeven and changes in cost and volume.
Introduction to what-if analysis (using Excel 2007) By using what-if analysis tools in Microsoft Office Excel, you can use several different
sets of values in one or more formulas to explore all the various results.
For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs.
Overview
What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.
Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.
Use scenarios to consider many different variables A scenario is a set of values that Excel saves and can substitute automatically in cells
on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.
For example, suppose you have two budget scenarios: a worst case and a best case.
You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different changing cell values.
Worst case scenario
Changing cells
Result cell
Best case scenario
Changing cells
Result cell
After you have created all the scenarios that you need, you can create a scenario summary report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a new worksheet.
Scenario summary report
NOTE Scenario reports are not automatically recalculated. If you change the values of a scenario, those changes will not show up in an existing summary report. Instead, you must create a new summary report.
Create a scenario
Before you create a scenario, you should have an initial set of values already on the worksheet. To make scenario summary reports easier to read, you should also consider naming the cells that you plan to use in scenarios.