11. What-If Analysis
Chala mitrano, aaj aapan "jar-tar" che prashna sodvuya – jar orders 10% vadhle tar profit kiti? Break-even sathi roj kiti orders lagtil? Goal Seek, Scenario Manager, Data Tables aani Solver he Excel che what-if tools aahet. Business manager la he prashna nehmi padtat, aani interview madhe pan vicharle jaatat.
What you will learn in this module
- Building a small, input-driven model
- Goal Seek to find the input needed for a target
- Scenario Manager for named best/normal/worst cases
- One- and two-variable Data Tables for sensitivity analysis
- Solver (brief) for optimisation (इष्टतम उत्तर शोधणे) with constraints
The model (fictional monthly P&L of the Wakad dark store, sheet Model):
| Cell | Item | Value / Formula |
|---|---|---|
| B2 | Orders per day (input) | 400 |
| B3 | Average order value, AOV (input) | ₹320 |
| B4 | Days in month (input) | 30 |
| B5 | Gross margin % (input) | 18% |
| B6 | Delivery cost per order (input) | ₹28 |
| B7 | Fixed cost per month – rent, staff (input) | ₹4,50,000 |
| B9 | Orders per month | =B2*B4 → 12,000 |
| B10 | Revenue | =B9*B3 → ₹38,40,000 |
| B11 | Gross margin | =B10*B5 → ₹6,91,200 |
| B12 | Delivery cost | =B9*B6 → ₹3,36,000 |
| B13 | Profit | =B11-B12-B7 → -₹94,800 |
Inputs are typed values; everything else is a formula referring to the inputs. That is the golden rule for any what-if model.
Concepts in this chapter
- 11.1Goal Seek
- 11.2Scenario Manager
- 11.3One-variable Data Table
- 11.4Two-variable Data Table
- 11.5Solver (Brief)
The chapter recap is at the end of the last concept page.