Data Tables (What-If) in Excel - Dashboard Guide

Dashboard Mode - Data Tables (What-If)

Goal

Find out how changing the price and quantity sold affects total revenue.

Sample Data

Price per Item ($)	Quantity Sold	Total Revenue ($)
10	100	=A2*B2
12	100	=A3*B3
14	100	=A4*B4
10	120	=A5*B5
12	120	=A6*B6
14	120	=A7*B7

Note: The Total Revenue column uses the formula =Price * Quantity.

Dashboard Components

• KPI Card: Base Total Revenue shows revenue for default price $10 and quantity 100.
  Formula: =A2*B2 Result: 1000
• Data Table: Shows total revenue for combinations of prices ($10, $12, $14) and quantities (100, 120).
  Formula for each cell: =Price * Quantity (e.g., =A2*B2)
• What-If Table: Uses Excel's Data Table feature with Price as row input and Quantity as column input to calculate total revenue dynamically.

Dashboard Layout

+----------------------+-------------------------+
|      KPI Card        |      What-If Table       |
|  Base Total Revenue  |  Price vs Quantity Table |
+----------------------+-------------------------+

Interactivity

The What-If Table updates automatically when you change the base price or quantity values. Changing these inputs recalculates total revenue for all combinations.

Self Check

If you change the base price from $10 to $15, which components update?

• The KPI Card updates to show new base revenue.
• The What-If Table recalculates total revenue for all price and quantity combinations.

Key Result

Dashboard shows how total revenue changes with different prices and quantities using a What-If Data Table.