In this lesson
Use Goal Seek and a data table, then check a Solver result against the resource limits.
Example
A workshop target and a product-mix decision
The workshop has Price 25, UnitCost 15, Units 20 and fixed cost 100. Profit is margin per unit times units minus fixed cost. In a separate mix, Standard earns 30 using 2 hours and Premium earns 50 using 4 hours; 16 hours are available.
How it works
Goal Seek changes one input to reach a target result. A data table compares several inputs. Use Solver when you need to maximize or minimize an objective subject to limits.
Give Solver an objective cell, decision cells and constraints. After solving, recalculate the resources and profit yourself to check that the proposed choice fits the model.
Steps
Enter Price, UnitCost, Units and Profit in A2:A5. Set B2=25, B3=15, B4=20 and B5=(B2-B3)*B4-100. Check starting profit 100.
On desktop, choose Data > What-If Analysis > Goal Seek. Set B5 to 200 by changing B4. Check Units 30 and Profit 200, then restore Units 20.
For a one-variable data table, leave E2 blank, enter =$B$5 in F2 and prices 20, 25 and 30 in E3:E5. Select E2:F5, choose Data Table and set Column input cell to B2. Leave Row input cell blank.
Check price-scenario profits 0, 100 and 200. If Scenario Manager is available, save Base with B2=25 and B4=20 and HigherVolume with B2=25 and B4=30. Compare them and restore Base.
Build the separate mix: H2 Standard, I2 Premium; decisions H3=1 and I3=1; hours H4=2 and I4=4; profits H5=30 and I5=50. Enter =SUMPRODUCT(H3:I3,H4:I4) in K3 and =SUMPRODUCT(H3:I3,H5:I5) in K4.
If desktop Solver is available, maximize K4 by changing H3:I3. Add K3<=16, H3<=6, I3<=3, nonnegative decisions and integer restrictions. Choose Simplex LP for this linear model.
Solve and check Standard 6, Premium 1. They use 16 hours and earn 230. Compare the feasible choice 4 and 2, which earns 220.
Save the limits, objective, choice and arithmetic check beside the model. In web Excel, calculate the price scenarios directly and list the small feasible integer mixes instead of using desktop-only menus.
Try it yourself
- Change the workshop target profit to 250 and calculate the required units independently before using Goal Seek. Keep the unit price, unit cost and fixed cost unchanged.
- For the product mix, reduce available hours to 12 while keeping all other bounds. Enumerate the small integer choices and determine whether six Standard units remain a feasible optimum.
Show expected result
- A target profit of 250 requires 35 workshop units because each contributes 10 above variable cost and the fixed cost is 100.
- With 16 hours the checked mix is Standard 6 and Premium 1, profit 230. With 12 hours, Standard 6 and Premium 0 uses all hours and earns 180.
If something goes wrong
- Every data-table row shows the same profit.
- Check that F2 links to B5 and Column input cell is Price B2. Recalculate and confirm that the profit formula uses B2 rather than a typed constant.
- Solver returns fractions or more than 16 hours.
- Check the integer restrictions, resource constraint and decision cells. Recalculate K3 and K4 after solving before keeping the result.
Quick check
Try these questions after the exercise. Check the result in your app before marking the lesson complete.
Choose an answer for all 3 questions to check your understanding.
Optional: try it with Copilot
If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.
Check the workshop and product-mix formulas. List the objective, decision cells and constraints, then compare the stated feasible choices.
More from Microsoft
These Microsoft guides cover the tools used in this lesson.
Loading your progress…