Excel में सेंसिटिविटी एनालिसिस कैसे करें: स्टेप-बाय-स्टेप

Excel में sensitivity analysis करने के लिए, अपने मॉडल के प्रत्येक इनपुट को उसके अपने सेल में रखें। फिर उन इनपुट्स को बदलें और रिकॉर्ड करें कि परिणाम कैसे बदलता है। एक इनपुट के लिए one-variable data table का उपयोग करें, दो के लिए two-variable data table का, और पूर्ण मामलों के लिए Scenario Manager का उपयोग करें। अंत में एक tornado chart के साथ समाप्त करें जो प्रभाव के आधार पर इनपुट्स को रैंक करता है।

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

sensitivity analysis क्या है

एक sensitivity analysis यह दिखाता है कि एक धारणा बदलने पर परिणाम कितना बदलता है। यह "यदि कीमत 10% गिरती है तो लाभ का क्या होगा?" जैसे सवालों के जवाब देता है।

अधिकांश व्यावसायिक मॉडल अनुमानों पर टिके होते हैं। बिक्री की मात्रा, कीमत, लागत और विकास दर सभी अनुमान हैं। एक sensitivity analysis यह परीक्षण करता है कि उनमें से कौन से अनुमान सबसे अधिक मायने रखते हैं।

Excel इन टूल्स को What-If Analysis के अंतर्गत समूहित करता है। Microsoft इसे "सेल्स में मानों को बदलने की प्रक्रिया" के रूप में परिभाषित करता है ताकि यह देखा जा सके कि फ़ार्मुलों के परिणाम कैसे बदलते हैं।

Microsoft तीन अंतर्निहित टूल्स को सूचीबद्ध करता है: "Scenarios, Goal Seek, और Data Tables।" प्रत्येक का एक अलग काम है:

• Data tables एक या दो इनपुट्स के कई मानों पर एक परिणाम दिखाते हैं।
• Scenario Manager इनपुट्स के पूरे सेट को सहेजता है, जैसे कि सबसे खराब स्थिति (worst case) और सबसे अच्छी स्थिति (best case)।
• Goal Seek पीछे की ओर काम करता है। यह लक्ष्य परिणाम तक पहुँचने के लिए आवश्यक इनपुट ढूँढता है।

एक sensitivity analysis आमतौर पर पहले दो का उपयोग करता है। Goal Seek संबंधित प्रश्नों में मदद करता है, जैसे कि break-even price।

शुरू करने से पहले मॉडल सेट अप करें

Excel के टूल्स तभी काम करते हैं जब मॉडल स्पष्ट रूप से बनाया गया हो। पहले इन बिंदुओं की जाँच करें।

प्रत्येक इनपुट को उसके अपने सेल में रखें। यूनिट्स, कीमत, लागत और दरों में से प्रत्येक को एक सेल की आवश्यकता होती है। फ़ार्मुलों के अंदर नंबर टाइप न करें।

परिणाम को इनपुट्स से लिंक करें। परिणाम सेल, जैसे कि लाभ, एक फ़ॉर्मूला होना चाहिए जो इनपुट सेल्स को संदर्भित करता हो। यदि ऐसा नहीं है, तो Excel द्वारा नए मान डालने पर कुछ भी नहीं बदलेगा।

इनपुट्स को एक साथ रखें। उन्हें शीट के शीर्ष पर एक ब्लॉक में समूहित करें। प्रत्येक को लेबल करें, और इनपुट सेल्स को रंग दें ताकि वे अलग दिखें।

परीक्षण के लिए एक परिणाम चुनें। sensitivity analysis तब पढ़ना आसान होता है जब यह एक आउटपुट को ट्रैक करता है, जैसे कि लाभ, net present value, या वर्ष के अंत में नकदी।

अपने बेस केस (base case) को नोट करें। शुरुआती मानों को लिख लें। विश्लेषण के प्रत्येक परिणाम की तुलना इस बेस से की जाती है।

यहाँ अनुसरण करने के लिए एक सरल लाभ मॉडल है। यह एक काल्पनिक बाइक की दुकान, Northgate Cycles है:

सेल	लेबल	मान
B1	बेची गई यूनिट्स	2,400
B2	प्रति यूनिट कीमत	$850
B3	प्रति यूनिट परिवर्तनीय लागत	$510
B4	निश्चित लागत	$520,000
B5	लाभ	=B1*(B2-B3)-B4

इन बेस मानों पर, लाभ $296,000 है।

data table के साथ one-variable sensitivity analysis कैसे करें

एक one-variable data table एक इनपुट के लिए मानों की एक श्रृंखला का परीक्षण करता है। Microsoft कहता है कि यह दिखाता है कि "एक या अधिक फ़ार्मुलों में एक वेरिएबल के विभिन्न मान परिणामों को कैसे बदलेंगे।"

उदाहरण मॉडल में कीमत का परीक्षण करने का तरीका यहाँ दिया गया है:

1. D2 से D6 में, परीक्षण के लिए कीमतें टाइप करें: $800, $825, $850, $875, और $900।
2. E1 में, पहली कीमत के एक रो ऊपर और एक कॉलम दाईं ओर, =B5 टाइप करें।
3. D1:E6 को चुनें।
4. Data टैब पर, What-If Analysis > Data Table चुनें।
5. Column input cell में, B2 दर्ज करें, जो कीमत वाला सेल है। Row input cell को खाली छोड़ दें।
6. OK पर क्लिक करें।

Excel प्रत्येक कीमत के लिए लाभ के साथ E2:E6 भरता है। फ़ॉर्मूला प्लेसमेंट मायने रखता है। Microsoft इसे "मानों के कॉलम के एक रो ऊपर और एक सेल दाईं ओर" टाइप करने के लिए कहता है।

Northgate Cycles के परिणाम:

कीमत	लाभ
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

कीमत में प्रत्येक $25 का बदलाव लाभ को $60,000 तक ले जाता है। यही कीमत के प्रति लाभ की संवेदनशीलता (sensitivity) है।

two-variable sensitivity analysis कैसे करें

एक two-variable data table एक साथ दो इनपुट्स का परीक्षण करता है। Microsoft कहता है कि यह दिखाता है कि "एक फ़ॉर्मूले में दो वेरिएबल्स के विभिन्न मान उस फ़ॉर्मूले के परिणामों को कैसे बदलेंगे।"

कीमत और यूनिट्स का एक साथ परीक्षण करने का तरीका यहाँ दिया गया है:

1. एक खाली सेल में, जैसे कि D10, =B5 टाइप करें।
2. इसके नीचे, D11 से D13 में, यूनिट वॉल्यूम टाइप करें: 2,000, 2,400, और 2,800।
3. इसके दाईं ओर, E10 से G10 में, कीमतें टाइप करें: $800, $850, और $900।
4. D10:G13 को चुनें।
5. What-If Analysis > Data Table चुनें।
6. Row input cell में, B2 दर्ज करें। Column input cell में, B1 दर्ज करें।
7. OK पर क्लिक करें।

परिणाम लाभ का एक ग्रिड है:

यूनिट्स \ कीमत	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

ग्रिड दिखाता है कि दुकान परीक्षण किए गए प्रत्येक संयोजन में लाभदायक रहती है। सबसे कमजोर कोना, $800 पर 2,000 यूनिट्स, फिर भी $60,000 कमाता है।

एक कड़ी सीमा है। Microsoft कहता है "एक data table दो से अधिक वेरिएबल्स को समायोजित नहीं कर सकता है।" अधिक इनपुट्स के लिए, Scenario Manager का उपयोग करें।

जब भी शीट पुनर्गणना (recalculate) करती है, data tables भी पुनर्गणना करते हैं। एक बड़ी वर्कबुक में, यह Excel को धीमा कर सकता है। Microsoft गणना को सेट करने का सुझाव देता है ताकि data tables केवल तभी पुनर्गणना करें जब आप F9 दबाएं।

Scenario Manager के साथ पूर्ण मामलों की तुलना कैसे करें

Scenario Manager इनपुट्स के पूर्ण सेट को सहेजता है और उन्हें बदलता है। Microsoft एक परिदृश्य (scenario) को "मानों के एक सेट के रूप में वर्णित करता है जिसे Excel सहेजता है और आपकी वर्कशीट पर स्वचालित रूप से प्रतिस्थापित कर सकता है।"

तीन मामले सेट करने का तरीका यहाँ दिया गया है:

1. Data टैब पर, What-If Analysis > Scenario Manager चुनें।
2. Add पर क्लिक करें, परिदृश्य का नाम Base रखें, और बदलते सेल्स के रूप में B1:B4 दर्ज करें। वर्तमान मानों को बनाए रखें।
3. एक Worst case जोड़ें: 2,000 यूनिट्स, $825 कीमत, $530 परिवर्तनीय लागत, और $540,000 निश्चित लागत।
4. एक Best case जोड़ें: 2,800 यूनिट्स, $875 कीमत, $495 परिवर्तनीय लागत, और $510,000 निश्चित लागत।
5. एक परिदृश्य चुनें और उसके मानों को लोड करने के लिए Show पर क्लिक करें।
6. Summary पर क्लिक करें और रिपोर्ट बनाने के लिए परिणाम सेल के रूप में B5 चुनें।

Northgate Cycles के लिए, सारांश तीन लाभ दिखाता है। सबसे खराब स्थिति $50,000, बेस केस $296,000, और सबसे अच्छी स्थिति $554,000 कमाती है।

Microsoft कुछ सीमाओं को नोट करता है। प्रत्येक परिदृश्य में 32 तक बदलते मान हो सकते हैं। सारांश रिपोर्ट स्वचालित नहीं है: "Scenario reports स्वचालित रूप से पुनर्गणना नहीं की जाती हैं।" यदि आप किसी परिदृश्य को बदलते हैं, तो एक नया सारांश बनाएँ।

Goal Seek के साथ break-even point कैसे खोजें

एक sensitivity analysis दिखाता है कि परिणाम कितनी दूर तक जाता है। Goal Seek विपरीत प्रश्न का उत्तर देता है: कौन सा इनपुट मान एक लक्ष्य परिणाम देता है?

Microsoft कहता है कि Goal Seek "एक परिणाम लेता है और संभावित इनपुट मान निर्धारित करता है जो उस परिणाम को उत्पन्न करते हैं।" इसका उदाहरण ऋण भुगतान के लिए आवश्यक ब्याज दर ढूँढता है।

Northgate Cycles के लिए break-even price खोजने का तरीका यहाँ दिया गया है:

1. Data टैब पर, What-If Analysis > Goal Seek चुनें।
2. Set cell में, B5 दर्ज करें, जो लाभ सेल है।
3. To value में, 0 दर्ज करें।
4. By changing cell में, B2 दर्ज करें, जो कीमत सेल है।
5. OK पर क्लिक करें।

Excel कीमत को तब तक बदलता है जब तक कि लाभ शून्य न हो जाए। 2,400 यूनिट्स और बेस लागतों पर, break-even price लगभग $726.67 है। यह वर्तमान $850 की कीमत से लगभग 15% कम है।

Goal Seek की एक सीमा है। Microsoft कहता है कि यह "केवल एक वेरिएबल इनपुट मान के साथ काम करता है।" एक से अधिक इनपुट के लिए, Microsoft Solver ऐड-इन की ओर इशारा करता है।

tornado chart कैसे बनाएँ

एक tornado chart इनपुट्स को इस आधार पर रैंक करता है कि वे परिणाम को कितना बदलते हैं। sensitivity analysis प्रस्तुत करने का यह सबसे स्पष्ट तरीका है।

इसे बनाने का तरीका यहाँ दिया गया है:

1. एक परीक्षण सीमा चुनें, जैसे कि प्रत्येक इनपुट 10% कम और 10% अधिक।
2. एक बार में एक इनपुट बदलें, और कम और उच्च परिणाम रिकॉर्ड करें। अन्य इनपुट्स को बेस पर रखें।
3. परिणामों को प्रति इनपुट एक रो वाली तालिका में रखें।
4. रो को सॉर्ट करें ताकि सबसे विस्तृत सीमा वाला इनपुट सबसे ऊपर हो।
5. कम और उच्च परिणामों का एक बार चार्ट (bar chart) डालें।

Northgate Cycles के लिए, प्रत्येक इनपुट को 10% ऊपर और नीचे ले जाने पर:

इनपुट	−10% पर लाभ	+10% पर लाभ	सीमा (Range)
कीमत	$92,000	$500,000	$408,000
परिवर्तनीय लागत	$418,400	$173,600	$244,800
बेची गई यूनिट्स	$214,400	$377,600	$163,200
निश्चित लागत	$348,000	$244,000	$104,000

कीमत सबसे अधिक मायने रखती है, एक बड़े अंतर से। कीमत में 10% की गिरावट लाभ को $296,000 से घटाकर $92,000 कर देती है। निश्चित लागतें सबसे कम मायने रखती हैं। यह मालिक को बताता है कि धारणाओं की जाँच करने में समय कहाँ बिताना है।

Excel में एक बार चार्ट बाएं से दाएं बार खींचता है। टोरनेडो आकार प्राप्त करने के लिए, कच्चे मानों के बजाय बेस लाभ से प्रत्येक इनपुट के बदलाव को प्लॉट करें। फिर कम केस वाले बार बाईं ओर और उच्च केस वाले बार दाईं ओर इशारा करते हैं।

AI के साथ sensitivity analysis कैसे करें

एक बड़े मॉडल के लिए data tables, scenarios, और tornado chart बनाना समय लेता है। एक AI टूल आपकी वर्कबुक से वही परीक्षण चला सकता है।

यहाँ Powerdrill Bloom का उपयोग करके तीन चरणों में विधि दी गई है।

चरण 1: अपना मॉडल अपलोड करें

उस स्प्रेडशीट से शुरू करें जिसमें आपका मॉडल है। AI Sensitivity Analysis Generator आपसे "DCF, business case, operating plan या कोई भी स्प्रेडशीट मॉडल" अपलोड करने के लिए कहता है।

सुनिश्चित करें कि इनपुट्स और परिणाम सेल स्पष्ट रूप से लेबल किए गए हैं। टूल को यह जानने की जरूरत है कि कौन से नंबर धारणाएं हैं।

चरण 2: प्राकृतिक भाषा में sensitivity व्यू चुनें

उस परीक्षण का वर्णन करें जो आप चाहते हैं। उदाहरण के लिए: "प्रत्येक इनपुट को 10% ऊपर और नीचे ले जाने के साथ लाभ के लिए एक tornado chart दिखाएं। कीमत और बेची गई यूनिट्स के लिए एक two-way table जोड़ें।"

टूल पेज चार व्यू सूचीबद्ध करता है: "tornado, two-way, Monte Carlo या break-even।" यह टोरनेडो आउटपुट को एक चार्ट के रूप में वर्णित करता है जो "उन इनपुट्स को रैंक करता है जो उत्तर को सबसे अधिक बदलते हैं।" यह "दो धारणाओं पर Sensitivity table जो मायने रखती हैं" भी प्रदान करता है।

चरण 3: परिणामों की जाँच करें और विश्लेषण निर्यात करें

साझा करने से पहले अपने स्वयं के data table के साथ एक या दो परिणामों की तुलना करें। बेस केस आपके मॉडल से बिल्कुल मेल खाना चाहिए।

फिर निर्यात करें। टूल पेज "Excel workbook, branded PDF या canvas link" सूचीबद्ध करता है। एक डेक के लिए, Excel to PPT tool उसी फ़ाइल को स्लाइड्स में बदल देता है। AI Financial Modeling पेज स्वयं मॉडल बनाने को कवर करता है।

परिणामों को कैसे पढ़ें और प्रस्तुत करें

एक sensitivity analysis तभी उपयोगी होता है जब लोग उस पर कार्रवाई करें। ये आदतें मदद करती हैं।

रैंकिंग के साथ नेतृत्व करें। tornado chart के साथ शुरुआत करें। यह एक तस्वीर में दिखाता है कि कौन सी धारणाएं परिणाम को संचालित करती हैं।

सीमाओं को निर्णयों में बदलें। "यदि कीमत 10% गिरती है तो लाभ $92,000 तक गिर जाता है" एक तथ्य है। "लीज पर हस्ताक्षर करने से पहले हमें कीमत का परीक्षण करना चाहिए" एक निर्णय है।

break-even point दिखाएं। विश्लेषण को उस मान के साथ जोड़ें जहाँ परिणाम शून्य हो जाता है। Northgate Cycles के लिए, बेस कीमत और लागत पर break-even लगभग 1,530 यूनिट्स है। Excel में break-even analysis करने के लिए हमारी गाइड उस गणना को कवर करती है।

यथार्थवादी सीमाओं का उपयोग करें। 10% का बदलाव स्थिर इनपुट्स के लिए उपयुक्त है। एक नए उत्पाद की मात्रा को बहुत व्यापक सीमा की आवश्यकता हो सकती हैं।

बताएं कि क्या स्थिर रखा गया था। प्रत्येक एक-एक करके किया गया परीक्षण अन्य इनपुट्स को बेस पर रखता है। स्लाइड पर या फुटनोट में ऐसा कहें। पाठकों को तब पता चलता है कि 10% कीमत परिवर्तन समान मात्रा, लागत और निश्चित खर्च को मानता है। यह एक लाइन विधि के बारे में अधिकांश अनुवर्ती प्रश्नों को रोकती है।

परिणाम आने के बाद दोबारा गौर करें। एक बार वास्तविक नंबर आने के बाद, उनकी तुलना अपनी सीमाओं से करें। एक budget vs. actual report एक स्वाभाविक अनुवर्ती कार्रवाई है।

सामान्य sensitivity analysis गलतियाँ

ये गलतियाँ आम हैं और इनसे बचना आसान है।

फ़ार्मुलों में हार्ड-कोडेड नंबर। यदि किसी फ़ॉर्मूले में B2 के बजाय 850 है, तो data table इसे बदल नहीं सकता है। प्रत्येक इनपुट को एक सेल में रखें।

एक साथ बहुत सारे इनपुट्स का परीक्षण करना। एक data table एक या दो को संभालता है। अधिक के लिए Scenario Manager या tornado chart पर जाएँ।

प्रत्येक इनपुट के लिए एक ही सीमा का उपयोग करना। कीमत और मात्रा में शायद ही कभी समान अनिश्चितता होती है। ऐसी सीमाएं चुनें जो वास्तव में आपके ज्ञान को दर्शाती हों।

इनपुट्स के बीच लिंक को अनदेखा करना। कम कीमत मात्रा बढ़ा सकती है। एक-एक करके किया गया परीक्षण इसे छोड़ देता है, इसलिए एक ऐसा परिदृश्य जोड़ें जो दोनों को बदलता हो।

पुराने scenario summaries। Scenario reports अपने आप अपडेट नहीं होती हैं। किसी भी बदलाव के बाद उन्हें फिर से बनाएँ।

बेस केस को भूल जाना। प्रत्येक तालिका को बेस परिणाम दिखाना चाहिए, ताकि पाठक प्रत्येक परिवर्तन के आकार को देख सकें।

जब आप अपने अगले मॉडल का स्ट्रेस-टेस्ट करने के लिए तैयार हों, तो योजना बनाने के लिए उपयोग की जाने वाली वर्कबुक के साथ Powerdrill Bloom आज़माएँ।

अक्सर पूछे जाने वाले प्रश्न

Excel में sensitivity analysis क्या है?

यह परीक्षण करने का एक तरीका है कि इनपुट्स बदलने पर परिणाम कैसे बदलता है। Excel में, आप नए इनपुट मान डालने और परिणामों को रिकॉर्ड करने के लिए What-If Analysis टूल्स, मुख्य रूप से data tables और Scenario Manager का उपयोग करते हैं।

मैं Excel में one-variable sensitivity analysis कैसे करूँ?

एक कॉलम में इनपुट मानों को सूचीबद्ध करें। एक फ़ॉर्मूला डालें जो एक रो ऊपर और एक कॉलम दाईं ओर परिणाम को संदर्भित करता हो। रेंज चुनें, What-If Analysis > Data Table चुनें, और इनपुट सेल को column input cell के रूप में दर्ज करें।

data table और Scenario Manager में क्या अंतर है?

एक data table कई मानों पर एक या दो वेरिएबल्स का परीक्षण करता है और सभी परिणामों को एक ग्रिड में दिखाता है। Scenario Manager इनपुट्स के पूर्ण सेट को सहेजता है, जिसमें प्रत्येक में 32 तक बदलते मान होते हैं, और उन्हें एक-एक करके बदलता है।

मैं Excel में tornado chart कैसे बनाऊं?

प्रत्येक इनपुट को एक-एक करके समान मात्रा में ऊपर और नीचे ले जाएं, और परिणाम रिकॉर्ड करें। सीमा के आकार के आधार पर इनपुट्स को सॉर्ट करें, फिर बेस परिणाम से प्रत्येक इनपुट के बदलाव को क्षैतिज बार (horizontal bars) के रूप में चार्ट करें।

क्या AI sensitivity analysis कर सकता है?

हाँ। एक AI टूल आपके स्प्रेडशीट मॉडल को पढ़ सकता है, टोरनेडो और two-way परीक्षण चला सकता है, और परिणामों को वर्कबुक या रिपोर्ट के रूप में निर्यात कर सकता है। आपको अभी भी बेस केस की जाँच करने और यथार्थवादी सीमाएं चुनने की आवश्यकता है।

स्रोत: Microsoft Support, Introduction to What-If Analysis · Microsoft Support, Calculate multiple results by using a data table · Microsoft Support, Switch between various sets of values by using scenarios.