Excel में What-If Analysis: Goal Seek, Scenario Manager, और Data Tables

आप इसे तीन चीजें देते हैं:

• Set cell: वह सेल जिसमें आपका फ़ॉर्मूला है (आउटपुट)
• To value: वह लक्षित परिणाम जो आप चाहते हैं
• By changing cell: वह इनपुट सेल जिसे Excel बदल सकता है

आइए इसे व्यवहार में देखें. 

आप नोटबुक बेचने वाला एक छोटा व्यवसाय चलाते हैं। आपका वर्तमान राजस्व $25,000 है, लेकिन आपका लक्ष्य $50,000 है। कीमत $25 तय है, इसलिए आपको पता लगाना है कि आपको कितनी इकाइयाँ बेचनी होंगी। 

ऐसा कैसे करें:

1. Data > What-If Analysis > Goal Seek पर जाएँ

2. Set cell में, B4 सेल पर क्लिक करें (आपके राजस्व फ़ॉर्मूला वाला सेल)

3. To value में, 50000 टाइप करें

4.  By changing cell में, B2 चुनें (Units Sold)

5. OK पर क्लिक करें

Goal Seek B2 को 1,000 से 2,000 इकाइयों में समायोजित करता है — $25 प्रति इकाई की कीमत पर $50,000 राजस्व हासिल करने के लिए आवश्यक सटीक संख्या।

किसी विशिष्ट परिणाम के लिए Goal Seek का उपयोग। छवि: लेखक।

Excel में Scenario Manager कैसे उपयोग करें

Scenario Manager एक What-If Analysis टूल है जो आपको एक ही स्प्रेडशीट में इनपुट मानों के कई सेट सेव करके उनके बीच स्विच करने देता है। हर सेव किया गया संस्करण एक परिदृश्य कहलाता है। 

आप जितने चाहें उतने बना सकते हैं और परिणामों की तुलना के लिए तुरंत उनके बीच स्विच कर सकते हैं। 

यह तब उपयोगी है जब आप बेस्ट-केस, वर्स्ट-केस और अपेक्षित परिणामों की योजना बनाना चाहते हैं—वह भी तीन अलग फाइलें रखे बिना। 

आप इसे तीन चीजें देते हैं:

• Scenario name: प्रत्येक मान्यताओं के सेट के लिए एक लेबल
• Changing cells: वे इनपुट सेल जो परिदृश्यों के बीच अलग होते हैं
• Values: प्रत्येक परिदृश्य के लिए विशिष्ट संख्याएँ

परिदृश्य बनाएँ

मान लीजिए आप अपने नोटबुक व्यवसाय के लिए तिमाही बिक्री की योजना बना रहे हैं।

मान लें आपका वर्तमान लाभ $15,000 है। आप तीन परिदृश्य बनाना चाहते हैं: बेस्ट केस, वर्स्ट केस, और अपेक्षित केस। 

परिदृश्य ऐसे बनाएँ:

1. Data > What-If Analysis > Scenario Manager पर जाएँ

2. Scenario Manager डायलॉग बॉक्स में, Add पर क्लिक करें

3. Scenario name में Best Case टाइप करें।

4.  Changing cells में, B2:B4 चुनें।

5.  Scenario Value डायलॉग बॉक्स खुलेगा। दिए गए फ़ील्ड में ये मान दर्ज करें:

• B2 = 2000, 

• B3 = $30

• B4 = $10

6.  OK पर क्लिक करें। 

शेष दो परिदृश्यों के लिए ये चरण नीचे दिए मानों के साथ दोहराएँ:

तीन मामलों के साथ परिदृश्य बनाएँ। छवि: लेखक।

परिदृश्यों के बीच स्विच करने के लिए, सूची से एक चुनें और Show पर क्लिक करें। Excel तुरंत मान बदल देता है और आपका लाभ अपडेट होता है।

प्रत्येक परिदृश्य के मान देखने के लिए Show पर क्लिक करें। छवि: लेखक।

परिदृश्यों को संपादित करें

सूची से कोई भी परिदृश्य चुनें और Edit पर क्लिक करें। आप उसका नाम बदल सकते हैं, किन सेल्स को कवर करता है यह बदल सकते हैं, या मान अपडेट कर सकते हैं। जब तक आप Show पर क्लिक नहीं करते, आपकी स्प्रेडशीट में कुछ नहीं बदलेगा।

परिदृश्य रिपोर्ट जनरेट करें

Scenario Manager सभी सेव किए परिदृश्यों की साइड-बाय-साइड तुलना वाली रिपोर्ट भी बना सकता है। 

परिदृश्य रिपोर्ट बनाने के लिए:

1.  Scenario Manager में, Summary पर क्लिक करें

2.  Scenario Summary में Scenario Summary चुनें

3.  Result cells में B7 चुनें (आपका लाभ वाला सेल)

4. OK पर क्लिक करें

Excel नए शीट पर एक सारांश तालिका बनाता है जो तीनों परिदृश्यों को साइड-बाय-साइड दिखाती है

परिदृश्य सारांश रिपोर्ट जनरेट करें। छवि: लेखक।

Excel में Data Tables कैसे उपयोग करें

Data Tables किसी इनपुट मानों की श्रृंखला पर बार-बार की गणनाओं को स्वचालित करती हैं। इनपुट मैन्युअल रूप से बदलने, आउटपुट नोट करने और दोहराने के बजाय, आप एक बार रेंज तय करते हैं, और Excel सभी परिणाम एक साथ भर देता है।

आप इसे तीन चीजें देते हैं:

• Input range: वे मान जिन्हें आप परखना चाहते हैं
• Row/column input cell: आपके फ़ॉर्मूला में वह सेल जिसे ये मान प्रतिस्थापित करते हैं
• Formula cell: वह आउटपुट जिसके इर्द-गिर्द आपकी तालिका बनी है

एक-वेरिएबल Data Tables

एक-वेरिएबल Data Table यह परखती है कि एक इनपुट बदलने पर किसी फ़ॉर्मूला का परिणाम कैसे बदलता है।

उदाहरण के लिए, आपके पास ऋण भुगतान का फ़ॉर्मूला है और आप देखना चाहते हैं कि 5% से 10% तक की ब्याज दरों पर मासिक भुगतान कैसे बदलता है।

एक-वेरिएबल Data Table बनाने के लिए:

1. अपना डेटासेट सेट करें। एक बात नोट करें: मेरे डेटासेट में, सेल E1 में, ठीक पहले दर के ऊपर, कॉलम हेडर के पास नहीं, मैं =B5 दर्ज करता/करती हूँ। यदि E1 खाली रहा, या फ़ॉर्मूला संदर्भ किसी और सेल में गया, तो डेटा टेबल चलेगी लेकिन भुगतान मानों के बजाय सभी शून्य लौटाएगी।

एक-वेरिएबल Data Table बनाएँ। छवि: लेखक।

2. अब, पूरा रेंज D1:E9 चुनें (जिसमें E1 भी शामिल हो)

3. Data > What-If Analysis > Data Table पर जाएँ

4. Row input cell खाली छोड़ें

5. Column input cell में B4 चुनें (ब्याज दर)

6. OK पर क्लिक करें।

Excel प्रत्येक इनपुट मान के लिए गणना किए गए परिणामों से तालिका भर देता है। ध्यान दें कि भुगतान समान रूप से नहीं बढ़ता। 3% से 4% का उछाल $49 है, लेकिन 9% से 10% का $61। जैसे-जैसे ब्याज दरें ऊपर जाती हैं, बदलाव का असर ज़्यादा होता है।

प्रत्येक इनपुट मान के लिए गणना परिणाम। छवि: लेखक।

दो-वेरिएबल Data Tables

दो-वेरिएबल Data Table यह परखती है कि एक साथ दो इनपुट बदलने पर किसी फ़ॉर्मूला का परिणाम कैसे बदलता है।

उदाहरण के लिए, किसी प्राइसिंग मॉडल का निर्भर होना उत्पाद की कीमत और बिकी हुई इकाइयों दोनों पर हो सकता है। दो-वेरिएबल Data Table एक ही दृश्य में अलग-अलग मूल्य बिंदुओं और बिक्री मात्रा पर लाभ दिखा सकती है।

पहले, डेटासेट सेट करें. 

मैं जो डेटासेट उपयोग कर रहा/रही हूँ, उसे देखें:

• D1 सेल में =B5 दर्ज करें। यह फ़ॉर्मूला संदर्भ है, और इसे ग्रिड के ऊपर-बाएँ कोने में ही जाना चाहिए, किसी लेबल के पास नहीं

• पंक्ति 1 में, E1 से शुरू करते हुए, परखने हेतु बिकी हुई इकाइयों के मान सूचीबद्ध करें: 500, 1000, 1500, 2000

• स्तंभ D में, D2 से शुरू करते हुए, परखने हेतु मूल्य बिंदु सूचीबद्ध करें: $20, $25, $30, $35

दो-वेरिएबल Data Table के लिए डेटासेट सेट करें। छवि: लेखक।

दो-वेरिएबल Data Table बनाने के लिए:

1. पूरा रेंज D1:H5 चुनें।

2.  Data > What-If Analysis > Data Table पर जाएँ

3.  Row input cell में B3 चुनें (बिकी हुई इकाइयाँ)

4.  Column input cell में B2 चुनें (प्रति इकाई मूल्य)

5.  OK पर क्लिक करें।

Excel हर कीमत और बिकी हुई इकाइयों के संयोजन के लिए लाभ भर देता है

इनपुट मानों के लिए गणना परिणाम। छवि: लेखक।

दो-वेरिएबल Data Tables तब खास तौर पर उपयोगी होती हैं जब अकेली एक मान्यता पूरी कहानी नहीं बताती। कीमत अपने आप में अच्छी दिख सकती है, लेकिन लाभ इस पर भी निर्भर है कि ग्राहक वास्तव में कितनी इकाइयाँ खरीदते हैं। यही दूसरा वेरिएबल मॉडल को अधिक उपयोगी बनाता है।

Goal Seek बनाम Scenario Manager बनाम Data Tables

Goal Seek, Scenario Manager और Data Tables सब Excel के What-If Analysis टूलकिट के हिस्से हैं, लेकिन ये अलग समस्याएँ हल करते हैं। 

यहाँ इनके बीच एक त्वरित तुलना दी गई है 

What-If Analysis बनाम Solver

What-If Analysis और Solver दोनों Excel में अलग-अलग परिणामों की पड़ताल करने में मदद करते हैं, लेकिन ये अलग प्रकार की समस्याएँ सुलझाते हैं।

• What-If Analysis मान्यताओं का परीक्षण करता है। आप एक या अधिक इनपुट बदलते हैं और देखते हैं कि उनका परिणाम पर क्या असर होता है। लक्ष्य संभावित परिणामों को समझना होता है, सर्वोत्तम ढूंढना नहीं।
• Solver अलग तरह से काम करता है। आप इसे कोई लक्ष्य देते हैं, जैसे लाभ अधिकतम करना या लागत न्यूनतम करना, और यह उस लक्ष्य को सर्वोत्तम रूप से पूरा करने वाले इनपुट संयोजन की खोज करता है। यह एक मान्यता नहीं परख रहा—यह इष्टतम को सुलझा रहा है। 

उदाहरण के लिए, कोई ऋण आवेदक $500 मासिक भुगतान देने वाला ऋण राशि पता करने के लिए Goal Seek का उपयोग कर सकता है। लेकिन कोई विनिर्माण कंपनी श्रम और सामग्री सीमाओं के भीतर रहते हुए सबसे अधिक लाभ देने वाले उत्पादन मिश्रण का निर्धारण करने के लिए Solver का उपयोग कर सकती है। 

ध्यान दें: Solver मानक What-If Analysis टूल्स का हिस्सा नहीं है। यह अलग ऐड-इन है। Excel के अधिकांश संस्करणों में, इसे Data टैब पर दिखने से पहले Excel Options में मैन्युअली चालू करना पड़ता है।

What-If Analysis सर्वोत्तम अभ्यास

What-If Analysis के साथ काम करते समय कुछ सर्वोत्तम अभ्यास इस प्रकार हैं: 

1. मान्यताओं को फ़ॉर्मूलों से अलग रखें

इनपुट मान समर्पित सेल्स में रखें और फ़ॉर्मूलों में उन्हीं का संदर्भ लें। गणनाओं में सीधे संख्याएँ हार्डकोड करने से बचें। 

उदाहरण के लिए, यह फ़ॉर्मूला अपडेट करना कठिन है:

=(50*1000)-10000

लेकिन यह संस्करण बनाए रखना बहुत आसान है:

=(B2*B3)-B4

जब मान्यताएँ बदलती हैं, तो आप पूरे वर्कबुक में फ़ॉर्मूला एडिट करने के बजाय केवल इनपुट सेल्स अपडेट करते हैं।

2. परिदृश्यों को स्पष्ट रूप से लेबल करें

परिदृश्य के नाम तुरंत यह बता दें कि वे क्या दर्शाते हैं। "Scenario 1", "Scenario 2", और "Scenario 3" छह महीने बाद कुछ नहीं बताते। उन्हें वास्तविक अर्थ के अनुसार नाम दें, जैसे "Best Case: 15% Growth." 

3. इनपुट मान्य करें

What-If Analysis उतना ही भरोसेमंद है जितने भरोसेमंद उसके इनपुट। विश्लेषण चलाने से पहले जाँचें कि:

• प्रतिशत सही तरह से दर्ज किए गए हों
• तिथियाँ एकसमान फ़ॉर्मेट में हों
• माप की इकाइयाँ एकसमान हों
• इनपुट मान यथार्थवादी सीमाओं में हों

500% बिक्री वृद्धि पर आधारित राजस्व पूर्वानुमान तकनीकी रूप से सही हो सकता है, लेकिन निर्णय लेने के लिए फिर भी बेकार।

4. मान्यताओं का दस्तावेजीकरण करें

आज जो मॉडल आपको समझ में आता है, वह तीन महीने बाद आपको या किसी और को ज़रूरी नहीं समझ आए। 

हर मान्यता के पास छोटा सा नोट, भले ही बस "Q3 के वास्तविक आँकड़ों पर आधारित", बाद में बहुत-सा अनुमान लगाने से बचाता है। उन्हें दस्तावेजीकृत करना दूसरों को समझने में मदद करता है कि संख्याएँ कहाँ से आईं और भविष्य के अपडेट आसान बनाता है।

What-If Analysis की आम समस्याएँ

अधिकांश What-If Analysis समस्याएँ टूल्स से नहीं बल्कि वर्कशीट सेटअप से आती हैं। यदि Goal Seek, Scenario Manager या Data Tables अप्रत्याशित परिणाम देते हैं, तो कुछ सामान्य क्षेत्रों की जाँच करें; अक्सर वहीं समाधान मिल जाता है।

1. फ़ॉर्मूले अपडेट नहीं हो रहे 

आपने इनपुट बदला और कुछ नहीं हुआ। आमतौर पर इसका मतलब है कि Excel का गणना मोड कहीं मैनुअल पर चला गया। 

इसे ठीक करने के लिए: Formulas > Calculation Options पर जाएँ और सुनिश्चित करें कि यह Automatic पर सेट हो। अगर पहले से है, तो पुनर्गणना मजबूर करने के लिए F9 दबाएँ।

2. गलत सेल संदर्भ

What-If Analysis टूल सटीक सेल संदर्भों पर निर्भर करते हैं। यदि गलत सेल चुने गए, तो Excel गणना पूरी कर देने पर भी परिणाम गलत होंगे। 

उदाहरण के लिए, Goal Seek गलत इनपुट सेल बदल सकता है। Data Table गलत Row या Column input cell संदर्भित कर सकती है। Scenario Manager गलत सेल्स के लिए मान सहेज सकता है।

इसे ठीक करने के लिए: विश्लेषण चलाने से पहले अपने इनपुट सेल्स की समीक्षा करें, हर टूल के डायलॉग बॉक्स में चुनी गई चीज़ों को दोबारा जाँचें, और वर्णनात्मक लेबल उपयोग करें ताकि हर सेल क्या दर्शाता है यह स्पष्ट रहे। 

3. Goal Seek की सीमाएँ समझना

कई उपयोगकर्ता उम्मीद करते हैं कि Goal Seek कई बदलते वेरिएबल्स वाली समस्याएँ हल कर देगा। यह नहीं कर सकता। Goal Seek एक समय में केवल एक इनपुट सेल बदलकर लक्षित मान तक पहुँचता है। 

उदाहरण के लिए, Goal Seek $15,000 लाभ पाने के लिए आवश्यक बिक्री मात्रा पता कर सकता है। यह एक साथ बिक्री मात्रा, कीमत और लागत समायोजित करके उस लक्ष्य तक नहीं पहुँचा सकता।

इसे ठीक करने के लिए: जब केवल एक वेरिएबल बदलना हो तब Goal Seek का उपयोग करें। जब कई वेरिएबल्स हों तो Scenario Manager या Solver का उपयोग करें। 

4. अत्यधिक जटिल परिदृश्य मॉडल

Scenario Manager तकनीकी रूप से प्रति परिदृश्य 32 बदलते सेल्स की अनुमति देता है, लेकिन केवल इसलिए कि आप कर सकते हैं, इसका मतलब यह नहीं कि आपको करना चाहिए। 

चार-पाँच वेरिएबल्स से आगे परिदृश्यों की तुलना करना कठिन और किसी और को समझाना उससे भी कठिन हो जाता है। यदि आपके मॉडल में इतने चलते-फिरते हिस्से चाहिए, तो सब कुछ एक परिदृश्य में ठूँसने की बजाय शायद उसे पुनर्संरचित करने का समय है।

अंतिम विचार 

हर स्प्रेडशीट में कहीं न कहीं मान्यताएँ होती हैं। यह वृद्धि दर, कीमत, या ब्याज दर हो सकती है।

What-If Analysis उन मान्यताओं को हटाता नहीं। यह दिखाता है कि अगर वे गलत निकलीं तो क्या होगा—और अच्छा निर्णय लेना अधिकतर इसी पर आकर टिकता है।

यदि आप इसमें नए हैं, तो पाँच वेरिएबल्स और तीन नेस्टेड परिदृश्यों वाले मॉडल से शुरू न करें। पहले कुछ छोटा बनाएँ—एक इनपुट, एक आउटपुट, एक Goal Seek—और समझें कि इनपुट और परिणाम साथ में कैसे बदलते हैं। जटिल चीजें बाद में आ सकती हैं, जब बुनियादी बातें समझ में आ जाएँ।