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.

Practice

1. What is the main purpose of a Data Table in Excel's What-If Analysis?

easy

A. To quickly see how changing input values affects the result

B. To create charts from data automatically

C. To sort data in ascending or descending order

D. To protect cells from editing

Solution

1. Step 1: Understand What-If Analysis

   What-If Analysis helps explore different input values to see their effect on results.

2. Step 2: Identify Data Table's role

   Data Tables let you test many input values quickly to observe changes in output.

3. Final Answer:

   To quickly see how changing input values affects the result -> Option A

4. Quick Check:

   Data Tables show output changes for input variations [OK]

Hint: Data Tables show results for many inputs fast [OK]

Common Mistakes:

• Confusing Data Tables with chart creation
• Thinking Data Tables sort data
• Believing Data Tables protect cells

2. Which of the following is the correct way to create a one-variable Data Table in Excel?

easy

A. Apply conditional formatting to the input cells

B. Type multiple formulas manually for each input value

C. Use the Sort feature under Data tab

D. Select the output cell and input values, then use Data > What-If Analysis > Data Table

Solution

1. Step 1: Recall Data Table creation steps

   Data Tables are created via Data > What-If Analysis > Data Table, not by typing formulas manually.

2. Step 2: Identify correct method

   Select the output formula cell and input values, then use the Data Table option to generate results.

3. Final Answer:

   Select the output cell and input values, then use Data > What-If Analysis > Data Table -> Option D

4. Quick Check:

   Data Table creation uses menu, not manual formulas [OK]

Hint: Use Data > What-If Analysis > Data Table to create tables [OK]

Common Mistakes:

• Typing formulas manually for each input
• Using Sort instead of Data Table
• Confusing conditional formatting with Data Tables

3. Given a one-variable Data Table where the formula in cell B2 is =A2*10 and the input values in column A are 1, 2, 3, what will be the output values in the Data Table?

medium

A. [0, 0, 0]

B. [10, 20, 30]

C. [100, 200, 300]

D. [1, 2, 3]

Solution

1. Step 1: Understand the formula and inputs

   The formula multiplies the input value in A2 by 10.

2. Step 2: Calculate outputs for each input

   For inputs 1, 2, 3: outputs are 1*10=10, 2*10=20, 3*10=30.

3. Final Answer:

   [10, 20, 30] -> Option B

4. Quick Check:

   Multiply inputs by 10 to get outputs [OK]

Hint: Multiply each input by 10 to find outputs [OK]

Common Mistakes:

• Confusing inputs with outputs
• Multiplying by 100 instead of 10
• Assuming outputs are zero

4. You created a two-variable Data Table but all output cells show #REF! error. What is the most likely cause?

medium

A. The formula cell reference is missing or incorrect in the Data Table setup

B. You forgot to enter input values

C. The worksheet is protected

D. You used a one-variable Data Table instead of two-variable

Solution

1. Step 1: Understand #REF! error in Data Tables

   #REF! usually means a broken or invalid cell reference in the formula.

2. Step 2: Check Data Table setup

   If the formula cell reference is missing or incorrect, the Data Table cannot calculate results, causing #REF! errors.

3. Final Answer:

   The formula cell reference is missing or incorrect in the Data Table setup -> Option A

4. Quick Check:

   Broken formula references cause #REF! errors [OK]

Hint: Check formula cell reference in Data Table setup [OK]

Common Mistakes:

• Assuming missing inputs cause #REF!
• Blaming worksheet protection without checking references
• Confusing one-variable and two-variable tables

5. You want to analyze how changing both price and quantity affects total revenue using a two-variable Data Table. Price values are in row 2 (cells B2:E2) and quantity values in column A (cells A3:A6). The total revenue formula is =B1*C1 where B1 is price and C1 is quantity. How should you set up the Data Table to get correct results?

hard

A. Place the formula in cell A1, select range A1:E5, then use Data Table with Row input cell = C1 and Column input cell = B1

B. Place the formula in cell B1, select range B1:E6, then use Data Table with Row input cell = C1 and Column input cell = B1

C. Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = B1 and Column input cell = C1

D. Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = C1 and Column input cell = B1

Solution

1. Step 1: Position formula correctly

   Formula must be at the intersection of input values: cell A2 (top-left corner of data table range).

2. Step 2: Select correct range and input cells

   Select A2:E6 to cover all price (row) and quantity (column) values. Set Row input cell = B1 (price) and Column input cell = C1 (quantity) matching formula references.

3. Final Answer:

   Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = B1 and Column input cell = C1 -> Option C

4. Quick Check:

   Formula at top-left, correct input cells for row and column [OK]

Hint: Formula top-left; row input = price; column input = quantity [OK]

Common Mistakes:

• Placing formula outside data table range
• Swapping row and column input cells
• Selecting wrong range for Data Table