Scenario Manager in Excel - Dashboard Guide

Dashboard Mode - Scenario Manager

Goal

See how changing sales price and cost affects profit in different scenarios.

Sample Data

Product	Units Sold	Sales Price	Cost	Profit
Widget A	100	20	12	=B2*(C2-D2)
Widget B	150	25	15	=B3*(C3-D3)
Widget C	200	30	18	=B4*(C4-D4)

Dashboard Components

• KPI Card: Total Profit
  Formula: =SUM(E2:E4)
  Shows total profit for all products.
• Scenario Manager
  Scenarios:
  • Base Case: Original prices and costs.
  • Best Case: Increase sales price by 10%, reduce cost by 5%.
  • Worst Case: Decrease sales price by 10%, increase cost by 5%.
  
  Use Excel's Scenario Manager to switch between these and see profit update.
• Profit Table
  Shows profit per product using formula =B2*(C2-D2) copied down.
  Updates automatically when scenario changes.

Dashboard Layout

+----------------------+--------------------+
|      Total Profit     |   Scenario Manager  |
|      (KPI Card)       |  (Scenario Picker)  |
+----------------------+--------------------+
|                  Profit Table               |
|          (Product profits by row)           |
+--------------------------------------------+

Interactivity

When you select a scenario in Scenario Manager, it changes the sales price and cost values in the data table. This automatically updates the profit calculations and the total profit KPI card.

Self Check

If you switch to the Best Case scenario, what happens to the Total Profit value? (It should increase because prices go up and costs go down.)

Key Result

Dashboard shows how different sales price and cost scenarios affect total and per-product profit.