Solver for optimization in Excel - Cell-by-Cell Formula Trace

Sample Data

This data shows three products with their prices and quantities. Quantities start at zero. Revenue per product is price times quantity. Total Revenue sums all revenues.

Cell	Value
A1	Product
B1	Price
C1	Quantity
D1	Revenue
A2	Apples
B2	2
C2	0
D2	=B2*C2
A3	Bananas
B3	3
C3	0
D3	=B3*C3
A4	Cherries
B4	5
C4	0
D4	=B4*C4
A5	Total Revenue
D5	=SUM(D2:D4)

Formula Trace

=SUM(D2:D4)

Step 1: D2 = B2 * C2 = 2 * 0

Step 2: D3 = B3 * C3 = 3 * 0

Step 3: D4 = B4 * C4 = 5 * 0

Step 4: SUM(D2:D4) = 0 + 0 + 0

Cell Reference Map

    A       B       C       D
1 |Product| Price |Quantity|Revenue
2 |Apples |   2   |   0    |  0  
3 |Bananas|   3   |   0    |  0  
4 |Cherries|  5   |   0    |  0  
5 |       |       |        | =SUM(D2:D4)

Cells B2:C4 hold prices and quantities. D2:D4 calculate revenue per product. D5 sums revenues.

Result

    A       B       C       D
1 |Product| Price |Quantity|Revenue
2 |Apples |   2   |   4    |  8  
3 |Bananas|   3   |   3    |  9  
4 |Cherries|  5   |   2    | 10  
5 |       |       |        |  27 

After Solver runs, quantities in C2:C4 are set to maximize total revenue in D5. Revenues update accordingly. Total Revenue is 27.

Sheet Trace Quiz - 3 Questions

What does the formula in cell D2 calculate?

A Quantity of Bananas

B Sum of all revenues

C Price times Quantity for Apples

D Price of Cherries

Key Result

SUM(range) adds all numbers in the specified range to get a total.