Data Tables (What-If) in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial analyst at a retail company.

📋 Request: Your manager wants to understand how changes in sales price and sales volume affect total revenue.

📊 Data: You have the base sales price per unit and the base sales volume. You want to analyze total revenue for different combinations of price and volume.

🎯 Deliverable: Create a two-variable data table showing total revenue for various sales prices and sales volumes.

Sample Data

Item	Base Price per Unit ($)	Base Sales Volume (Units)
Product A	20	1000

1

Step 1: Enter the base sales price in cell B2 and base sales volume in cell C2 as given.

B2 = 20 C2 = 1000

Expected Result

B2 shows 20, C2 shows 1000

2

Step 2: Calculate total revenue in cell D2 by multiplying price and volume.

=B2*C2

Expected Result

D2 shows 20000

3

Step 3: Set up the data table layout: Enter different sales prices horizontally starting from cell E1 (e.g., 15, 18, 20, 22, 25). Enter different sales volumes vertically starting from cell D2 (e.g., 800, 900, 1000, 1100, 1200). Enter the total revenue formula (=B2*C2) in cell D1.

E1: 15, F1: 18, G1: 20, H1: 22, I1: 25 D2: 800, D3: 900, D4: 1000, D5: 1100, D6: 1200 D1: =B2*C2

Expected Result

Row 1 from E1 to I1 shows prices 15,18,20,22,25 Column D from D2 to D6 shows volumes 800,900,1000,1100,1200 Cell D1 shows 20000

4

Step 4: Select the range D1:I6 including the formula reference, prices, and volumes.

Select D1:I6

Expected Result

Range D1:I6 is selected

5

Step 5: Open the Data Table dialog: Go to Data tab > What-If Analysis > Data Table.

No formula, just navigation

Expected Result

Data Table dialog opens

6

Step 6: In the Data Table dialog, set 'Row input cell' to the sales price cell (B2) and 'Column input cell' to the sales volume cell (C2). Then click OK.

Row input cell: B2 Column input cell: C2

Expected Result

Data table fills with total revenue values for each price and volume combination

7

Step 7: Review the data table results showing total revenue for each combination of sales price and sales volume.

No formula

Expected Result

Table shows total revenue values, e.g., at price 20 and volume 1000 revenue is 20000

Final Result

       |  15   18   20   22   25
-------+--------------------------
  800  | 12000 14400 16000 17600 20000
  900  | 13500 16200 18000 19800 22500
 1000  | 15000 18000 20000 22000 25000
 1100  | 16500 19800 22000 24200 27500
 1200  | 18000 21600 24000 26400 30000

✓Total revenue increases as either sales price or sales volume increases.

✓The data table helps quickly see revenue impact of different pricing and volume strategies.

✓For example, increasing price to $22 and volume to 1100 units yields $24,200 revenue.

Bonus Challenge

Create a one-variable data table to analyze how total revenue changes with sales price only, keeping volume fixed at 1000 units.

Show Hint

Set up a column of different prices and use the Data Table feature with only the Column input cell set to the price cell.