Financial Modeling in Excel For Dummies
Book image
Explore Book Buy On Amazon
The major limitation of drop-down scenarios in financial modeling is that you can’t see multiple scenarios side-by-side. To show the cost per customer under different scenarios, you need to change the scenario drop-down box in cell F1 — but you’ll only be able to look at one scenario at a time.

To add a data table that will allow you to see the cost per customer of all three scenarios side by side, follow these steps:

  1. Add the three scenario names — “Best Case,” “Base Case,” and “Worst Case” — below the Cost per Customer.

    Make sure that you spell the names correctly, and don’t add trailing spaces or the data table won’t work.

  2. Select cells C2:E2 and press Ctrl+C.
  3. Select cell A25, right-click, and select Paste Special → Transpose to paste the names in cells A25:A17 with exactly the same spelling.
  4. Highlight cells A24:F2.
  5. On the Data tab of the Ribbon, in the Forecast group, select Data Table under the What-If Analysis button to display the Data Table dialog box.

    Because the variable you’re changing is arranged in column A, you need to tell the Data Table dialog box where the original input is for the column, which is the Scenario cell in F1.

  6. Under the Column input cell field, select cell F1.
completing the data table financial modeling
Completing the data table.

You can only show one output in a data table, so you chose to show the cost per customer only. If you want to show other values, you need to create additional data tables.

Now that you have the scenario results, they can be displayed in a line chart.

completed scenario analysis
Completed scenario analysis with chart.

The key message from this model can be seen in this chart. You can see that the cost per customer varies depending on the scenario, and the best case scenario doesn’t necessarily mean that you’ll experience a lower cost per customer.

Because the data table needs to be arranged in a single block, you can’t insert a row above the scenario outputs to show that these are the results of the scenario analysis. You can change the formatting of row 24 and add the title “Scenario Analysis” in row 23 for clarity.

About This Article

This article is from the book:

About the book author:

Danielle Stein Fairhurstis a Sydney-based financial modeling consultant who helps her clients create meaningful financial models for business analysis. She is regularly engaged around Australia and globally as a speaker and course facilitator. She received the Microsoft MVP Award in 2021 in recognition of her technical expertise and contributions to the community.

This article can be found in the category: