In this example, cell B3 is used for x1, cell C3 for x2 and cell D3 for x3. These are the number of each product to produce. The profit per unit for each product has been entered into cells B13:D13, i.e., B13 = 75, C13 = 50 and D13 = 35. The objective function has been entered in cell D18 using:
F13: =B13 * B3 + C13 * C3 + D13 * D3
The parts needed to produce each product have been entered into cells B7:D11. To make a TV set, five parts are needed: a chassis, a picture tube, two speaker cones, a power supply and two electronics units. As a result, a 1 is entered in cell B7 for the number of chassis required, a 1 has been entered into cell B8 for the number of LCD Screens required, a 2 in cell B9 for the number of Speaker Cones required, a 1 for the number of power supplies and a 2 for the number of electronic units.
Cells F7:F11 hold formulas calculating the left hand side of the five constraints which calculate the total number of parts used.
F7: = SUMPRODUCT(B3:D3, B7:D7)
F8: = SUMPRODUCT (B3:D3, B8:D8)
F9: = SUMPRODUCT(B3:D3, B9:D9)
F10: = SUMPRODUCT(B3:D3, B10:D10)
F11: = SUMPRODUCT(B3:D3, B11:D11)
Finally, the constraint right hand side values have been entered into cells G7:G11:
G7: 450
G8: 250
G9: 800
G10: 450
G11: 600
We now have a simple spreadsheet model that we can use to play ‘what if.’ For any values we enter for the decision variables in cells B3, C3 and D3 the objective in cell F13 (Total Profit) and the corresponding values of the constraint left hand sides (the numbers of parts used) will be calculated.
You can use this example "as is" to perform a ‘what if’ analysis by changing the values in cells B3, C3, and D3. Now let's convert this spreadsheet model into an optimization model, where the Solver will automatically find optimal values for the cells B3:D3, so that the objective function at F13 is maximized, and the constraints are satisfied.
Click Reset All in the Solve pane in order to clear the problem from the task pane. Now we can define the optimization problem step by step.
We’ll use the Task Pane to point out to the Solver (i) the cells that we’ve reserved for the decision variables, (ii) the cell that calculates the value of the objective function, and (iii) the cells that calculate the constraint left hand sides. We’ll also enter values for the constraint right hand sides, and non-negativity constraints on the variables. We can perform these steps in any order.