11.1 Goal Seek
Goal Seek changes one input until a formula reaches a target value. Behind the scenes it tries value after value – an iteration (पुनरावृत्ती) process – until the result is close enough.
Steps in Excel – break-even orders per day
- Data › Forecast › What-If Analysis › Goal Seek…
- Set cell:
B13(Profit) › To value:0› By changing cell:B2(Orders per day) › OK. - Goal Seek shows the solution: 506.76 orders/day. Click OK to keep it or Cancel to restore 400.
- Round up for the business answer: about 507 orders per day to break even.
Check by hand: margin per order = 320 × 18% − 28 = ₹29.60; per month 30 × 29.60 = ₹888 per daily order; 4,50,000 ÷ 888 = 506.76 ✓.
Another question: at 400 orders/day, what AOV is needed to break even? Set B13 to 0 by changing B3 → about ₹363.89.
Ravindra Bagale's Tip
If you give Goal Seek's By changing cell a cell that contains a formula, Excel gives an error – it must be an input cell (a typed value). Many students make this mistake. And Goal Seek changes only one input – if you need to change two or three inputs together, use Solver. If you don't want to keep the result, don't forget to press Cancel.
Ravindra Bagale's Tip – मराठी
Goal Seek च्या By changing cell मध्ये formula असलेली cell दिली तर Excel error देतो – ती input cell (typed value) असायला हवी. बरेच students ही चूक करतात. आणि Goal Seek एकच input बदलतो – दोन-तीन inputs एकत्र बदलायचे असतील तर Solver वापरा. Result कायम ठेवायचा नसेल तर Cancel दाबायला विसरू नका.
Ravindra Bagale's Tip – हिंदी
Goal Seek के By changing cell में formula वाली cell दी तो Excel error देता है – वह input cell (typed value) होनी चाहिए. बहुत से students यह गलती करते हैं. और Goal Seek सिर्फ़ एक input बदलता है – दो-तीन inputs एक साथ बदलने हों तो Solver इस्तेमाल करो. Result हमेशा के लिए नहीं रखना हो तो Cancel दबाना मत भूलना.
Practice task
Using Goal Seek find: the delivery cost per order that makes profit ₹50,000 at 400 orders/day, and the gross margin % needed to break even.