Expert Excel · Interactive lesson 7 of 12
Optimization, forecasting & statistical tools
Some questions aren't "what happened" — they're "what's the best choice available." Solver finds the best mix under real constraints; a Forecast Sheet projects what's likely next. Both are only as trustworthy as the limits you're honest about.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialSolver, Forecasting and Statistics Tools in ExcelSkill check
Try these first:
- Set up decision variables, constraints, and an objective for Solver.
- Build a scenario or sensitivity table for a key assumption.
- Generate a Forecast Sheet and explain what it's actually projecting.
- Explain one limitation of any model you build, out loud, before someone else finds it.
All familiar? Skip ahead to E8 — Dashboard Application Design.
Concept teach
What you're learning
- Solver setup
- Data → Solver: pick an objective cell, the decision-variable cells Solver is allowed to change, and Max, Min, or a target value.
- Decision variables & constraints
- The variables are what Solver is free to choose (units to produce); constraints are the hard limits it can't cross (capacity, demand, non-negativity).
- Scenario & sensitivity design
- Scenario Manager saves named "what-if" sets of assumptions to flip between; a sensitivity table shows how the result changes as one assumption moves across a range.
- Forecast Sheet
- Data → Forecast Sheet projects future values from a time series automatically, including a confidence interval — worth reading, not just the number.
- Regression awareness
- A trendline or
TREND()fits a line to past data — useful for spotting a relationship, dangerous if trusted beyond the range the data actually covers. - Outlier handling
- One freak data point can drag an average or a trendline off course — decide deliberately whether to exclude it, and say so, rather than silently including or excluding it.
- Explaining model limitations
- Every forecast and every optimization assumes conditions stay roughly like the past — say what would break the model before someone else discovers it the hard way.
Where to find it in Excel
Data›Analyze›Solver
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Units | Profit/unit | Labor hrs | |||||
| 2 | Product A | 120 | 14 | 2 | |||||
| 3 | Product B | 80 | 22 | 3 | |||||
| 4 | |||||||||
| 5 | Labor used | 480 | |||||||
| 6 | Labor limit | 480 | |||||||
| 7 | |||||||||
| 8 | Total profit | 3,440 | |||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 |
Swipe sideways to see the whole window.
- 1Data › Analyze › Solver. If it's missing: File › Options › Add-ins › Excel Add-ins › Solver Add-in.
- 2The objective cell is the formula to maximize or minimize.
- 3Variable cells are the decisions Solver may change.
- 4Constraints keep the answer realistic.
- 5Forecast Sheet projects a time series with a confidence band.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Two products, limited labor hours, limited demand. Set the mix, then let Solver find the one that maximizes profit.
Independent extension
Optimize a real constraint
Set up a real Solver problem: an objective to maximize or minimize, at least two decision variables, and at least two real constraints. Solve it, then build a one-row sensitivity check showing how the answer changes if one constraint loosens by 10%.
Spot a mistake or have an idea for this lesson? Send feedback