Show All

Hide All

Projecting expense totals under various scenarios can be an important part of allocating training resources. It's often helpful to project more than one set of values to see how they affect results. You can do this easily with what-if scenarios—sets of input values that Office Excel can substitute automatically in your worksheet.

What-if scenarios can help you answer questions such as:

• How would training only new employees affect training costs?
• What would my estimated total training expense be if I outsourced training?
• Which is more cost effective—training all employees at the same time or training one group at a time?

You can create and save different scenarios on a worksheet and then switch among scenarios to view different results. You can also create a summary or PivotTable® list (PivotTable list: A Microsoft Office Web Component used to create an interactive list that summarizes and analyzes data, such as database records, from various sources. Users can view the list in a Web browser and change its layout.) with the results of all your scenarios.

Try it with a training budget analysis

To do a training budget analysis and see how changing the method, location, group of employees, or price affects your total training expense, you can define different values for these variables and then switch between scenarios to compare the results.

The following illustration shows a current training budget analysis for this example.

Training budget analysis: Current scenario

Note  The scenario summary or report is easier to read if you name the cells or ranges of cells in your worksheet that contain variable data. In this example, you can name cell B2 Emp_to_train.

Create scenarios

1. On the Tools menu, click Scenarios.
2. Click Add.

   

3. In the Scenario name box, type a name for the scenario, such as Train New Employees Only.
4. In the Changing cells box, enter the references for the cells that you want to change, or hold down CTRL and click each cell.

   In this example, to try a scenario of training only 200 new employees, click cell B2.

   Note  To preserve the original values for the changing cells, create a scenario that uses the original cell values before you create scenarios that change the values.

5. Under Protection, select the options you want.
6. Click OK.
7. In the Scenario Values dialog box, type the values you want for the changing cells. For example, for $B$2 (number of employees to train), type 200.
8. To create the scenario, click OK.

9. If you want to create more scenarios, click Add again, and then repeat the procedure. When you are finished creating scenarios, click OK, and then click Close in the Scenario Manager dialog box.

Display a scenario

1. On the Tools menu, click Scenarios.
2. Select the scenario you want to see, and then click Show.

   The values for the scenario are automatically substituted in your worksheet, so you can see how they affect results.

   Training budget analysis

   
3. When you are finished, click Close.

Create a scenario summary report or PivotTable report

1. On the Tools menu, click Scenarios.
2. Click Summary.
3. Click Scenario summary or Scenario PivotTable report.

   

4. In the Result cells box, enter the references to the cells that contain results that are changed by the scenarios. In this case, click cells B5 and B7 to see how the scenarios affect the total cost to train internally and the total cost to outsource training. Separate multiple references with commas.

Note  You don't have to specify result cells to generate a scenario summary report, but you must enter them for a scenario PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.).

The scenario summary report displays the variables for each scenario and the result cells.

Scenario summary

The PivotTable report displays the result cells for each scenario. You can use the drop-down arrows to choose the results you want to be displayed.

PivotTable report

What else can I do with what-if scenarios?

Modify a scenario

Protect a scenario

Merge scenarios from another worksheet