Forecasting the outcome of a financial model can lead to better planning and positive results for an organization. You can use scenarios to forecast the outcome of a financial model. A scenario is a set of values that Microsoft® Excel® saves and can substitute automatically in your worksheet. You can create and save different groups of values on a worksheet and then use slicers to switch to any of these new scenarios.
We have previously shown you how to insert scenarios using the What-If analysis tool. However this week we shall do it differently, by setting up the scenarios in a table. A PivotTable will then be inserted so that slicers can be used to switch between the best case and worst case scenarios.
Note: Download the sample workbook to practice this exercise
Applies To: Microsoft Excel 2007, 2010 and 2013
1. The first step is to create a table for the Best Case and Worst Case scenarios for the 4th quarter which has been created in the example workbook.
2. Then create a Pivot Table from the scenario data:
a) Select any cell within the scenario table
b) From the Insert Tab, select PivotTable
c) In the Location box, select any cell on the existing worksheet where you would like to place the PivotTable.
d) Drag Account Name and place it in the Row Labels and Value in the Values area.
3. Insert a slicer
a) Select any cell on the PivotTable.
b) From the Analyze tab, select Insert Slicer in Excel 2013. If you’re using Excel 2010, go to Options and select Insert Slicer.
4. Select the scenario field from the slicer list and click OK.
5. Select cell F8 in the financial model.
6. Press “=” and click on the value representing Sales in the Pivot Table.
7. Repeat steps 5 and 6 until you have linked the cells in the financial model to the corresponding values in the Pivot Table.
8. Click on the slicer and select Options.
9. Change the number of columns to two.
10. Right click on the slicer and select slicer settings. Then uncheck the display header check box and click OK.
11. Resize and move the slicer above the financial model.
As can be seen one can quickly switch between the best case and worst case scenarios. This is also another method of creating scenarios without using the What if analysis option under the data tab.
