एक्सेल में संवेदनशीलता विश्लेषण (नमूना डेटाशीट)

वित्त के क्षेत्र में प्रक्रियाएं हमेशा परस्पर जुड़ी रहती हैं - एक कारक दूसरे पर निर्भर करता है और उसके साथ बदलता है। इन परिवर्तनों को ट्रैक करें और समझें कि भविष्य में क्या उम्मीद की जाए, शायद एक्सेल फ़ंक्शन और स्प्रेडशीट विधियों का उपयोग करके।

डेटा तालिका के साथ अनेक परिणाम प्राप्त करना

डेटाशीट क्षमताएं क्या-क्या विश्लेषण के तत्व हैं-अक्सर माइक्रोसॉफ्ट एक्सेल के माध्यम से किया जाता है। संवेदनशीलता विश्लेषण का यह दूसरा नाम है।

अवलोकन

डेटा टेबल एक प्रकार की कोशिकाओं की श्रेणी होती है जिसका उपयोग कुछ कोशिकाओं में मूल्यों को बदलकर समस्याओं को हल करने के लिए किया जा सकता है। यह तब बनाया जाता है जब इन परिवर्तनों के अनुसार, सूत्र के घटकों में परिवर्तनों का ट्रैक रखना और परिणामों के अपडेट प्राप्त करना आवश्यक होता है। आइए जानें कि शोध में डेटा तालिकाओं का उपयोग कैसे किया जाता है, और वे किस प्रकार के होते हैं।

डेटा तालिकाओं के बारे में मूल बातें

डेटा टेबल दो प्रकार के होते हैं, वे घटकों की संख्या में भिन्न होते हैं। आपको uXNUMXbuXNUMXb मानों की संख्या पर ध्यान देने के साथ एक तालिका संकलित करने की आवश्यकता है जिसे आपको इसके साथ जांचने की आवश्यकता है।

सांख्यिकीविद एकल चर तालिका का उपयोग तब करते हैं जब एक या अधिक व्यंजकों में केवल एक चर होता है जो उनके परिणाम को बदल सकता है। उदाहरण के लिए, इसे अक्सर PMT फ़ंक्शन के संयोजन में उपयोग किया जाता है। सूत्र नियमित भुगतान की राशि की गणना करने के लिए डिज़ाइन किया गया है और समझौते में निर्दिष्ट ब्याज दर को ध्यान में रखता है। ऐसी गणनाओं में, चर एक कॉलम में लिखे जाते हैं, और गणना के परिणाम दूसरे में। 1 चर वाली डेटा प्लेट का एक उदाहरण:

इसके बाद, 2 चर वाली प्लेटों पर विचार करें। उनका उपयोग उन मामलों में किया जाता है जहां दो कारक किसी संकेतक में परिवर्तन को प्रभावित करते हैं। दो चर ऋण से जुड़ी एक अन्य तालिका में समाप्त हो सकते हैं, जिसका उपयोग इष्टतम पुनर्भुगतान अवधि और मासिक भुगतान की राशि निर्धारित करने के लिए किया जा सकता है। इस गणना में, आपको पीएमटी फ़ंक्शन का उपयोग करने की भी आवश्यकता है। 2 चर वाली तालिका का एक उदाहरण:

एक चर के साथ एक डेटा तालिका बनाना

स्टॉक में केवल 100 पुस्तकों के साथ एक छोटी किताबों की दुकान के उदाहरण का उपयोग करके विश्लेषण पद्धति पर विचार करें। उनमें से कुछ को अधिक महंगा ($ 50) बेचा जा सकता है, बाकी के लिए खरीदारों को कम ($ 20) खर्च होंगे। सभी सामानों की बिक्री से कुल आय की गणना की जाती है - मालिक ने फैसला किया कि वह 60% पुस्तकों को उच्च कीमत पर बेचेगा। आपको यह पता लगाने की आवश्यकता है कि यदि आप बड़ी मात्रा में माल की कीमत में वृद्धि करते हैं तो राजस्व कैसे बढ़ेगा - 70%, और इसी तरह।

ध्यान दो! कुल राजस्व की गणना एक सूत्र का उपयोग करके की जानी चाहिए, अन्यथा डेटा तालिका संकलित करना संभव नहीं होगा।

1. शीट के किनारे से दूर एक मुक्त सेल का चयन करें और उसमें सूत्र लिखें: = कुल राजस्व का सेल। उदाहरण के लिए, यदि आय सेल C14 में लिखी गई है (यादृच्छिक पदनाम इंगित किया गया है), तो आपको यह लिखना होगा: =एस14.
2. हम इस सेल के बाईं ओर के कॉलम में माल की मात्रा का प्रतिशत लिखते हैं - इसके नीचे नहीं, यह बहुत महत्वपूर्ण है।
3. हम उन कक्षों की श्रेणी का चयन करते हैं जहां प्रतिशत स्तंभ और कुल आय का लिंक स्थित है।

4. हम "डेटा" टैब पर आइटम "व्हाट इफ एनालिसिस" पाते हैं और उस पर क्लिक करते हैं - खुलने वाले मेनू में, "डेटा टेबल" विकल्प का चयन करें।

5. एक छोटी सी खिड़की खुलेगी जहां आपको "पंक्तियों द्वारा स्थानापन्न मान ..." कॉलम में शुरू में उच्च कीमत पर बेची गई पुस्तकों के प्रतिशत के साथ एक सेल निर्दिष्ट करने की आवश्यकता है। बढ़ते प्रतिशत को ध्यान में रखते हुए कुल राजस्व की पुनर्गणना करने के लिए यह कदम उठाया गया है।

विंडो में "ओके" बटन पर क्लिक करने के बाद जहां तालिका को संकलित करने के लिए डेटा दर्ज किया गया था, गणना के परिणाम लाइनों में दिखाई देंगे।

एकल चर डेटा तालिका में सूत्र जोड़ना

केवल एक चर के साथ किसी क्रिया की गणना करने में मदद करने वाली तालिका से, आप एक अतिरिक्त सूत्र जोड़कर एक परिष्कृत विश्लेषण उपकरण बना सकते हैं। इसे पहले से मौजूद सूत्र के बगल में दर्ज किया जाना चाहिए - उदाहरण के लिए, यदि तालिका पंक्ति-उन्मुख है, तो हम मौजूदा के दाईं ओर सेल में अभिव्यक्ति दर्ज करते हैं। जब कॉलम ओरिएंटेशन सेट किया जाता है, तो हम पुराने के नीचे नया फॉर्मूला लिखते हैं। अगला, एल्गोरिथ्म का पालन करें:

1. कक्षों की श्रेणी फिर से चुनें, लेकिन अब इसमें नया सूत्र शामिल होना चाहिए।
2. "क्या होगा" विश्लेषण मेनू खोलें और "डेटाशीट" चुनें।
3. हम प्लेट के उन्मुखीकरण के आधार पर पंक्तियों या स्तंभों में संबंधित फ़ील्ड में एक नया सूत्र जोड़ते हैं।

दो चर के साथ एक डेटा तालिका बनाएं

ऐसी तालिका की शुरुआत थोड़ी अलग है - आपको कुल राजस्व के लिए प्रतिशत मूल्यों के ऊपर एक लिंक डालना होगा। अगला, हम इन चरणों का पालन करते हैं:

1. आय के लिंक के साथ एक पंक्ति में मूल्य विकल्प लिखें - प्रत्येक मूल्य के लिए एक सेल।
2. कोशिकाओं की एक श्रेणी का चयन करें।

3. डेटा तालिका विंडो खोलें, जैसे कि एक चर के साथ तालिका संकलित करते समय - टूलबार पर "डेटा" टैब के माध्यम से।
4. प्रारंभिक उच्च मूल्य वाले सेल में "कॉलम द्वारा मूल्यों को ..." में प्रतिस्थापित करें।
5. महंगी पुस्तकों की बिक्री के प्रारंभिक प्रतिशत के साथ एक सेल को "पंक्तियों द्वारा प्रतिस्थापित करें ..." कॉलम में जोड़ें और "ओके" पर क्लिक करें।

नतीजतन, पूरी तालिका माल की बिक्री के लिए विभिन्न शर्तों के साथ संभावित आय की मात्रा से भर जाती है।

डेटा टेबल वाली वर्कशीट के लिए गणना में तेजी लाएं

यदि आपको ऐसी डेटा तालिका में त्वरित गणना की आवश्यकता है जो संपूर्ण कार्यपुस्तिका के पुनर्गणना को ट्रिगर नहीं करती है, तो कुछ चीजें हैं जो आप प्रक्रिया को गति देने के लिए कर सकते हैं।

1. विकल्प विंडो खोलें, दाईं ओर मेनू में "सूत्र" आइटम का चयन करें।
2. "कार्यपुस्तिका में गणना" अनुभाग में आइटम "स्वचालित, डेटा तालिकाओं को छोड़कर" का चयन करें।

3. आइए तालिका में परिणामों को मैन्युअल रूप से पुनर्गणना करें। ऐसा करने के लिए, सूत्रों का चयन करें और F कुंजी दबाएं।

संवेदनशीलता विश्लेषण करने के लिए अन्य उपकरण

संवेदनशीलता विश्लेषण करने में आपकी मदद करने के लिए कार्यक्रम में अन्य उपकरण हैं। वे कुछ क्रियाओं को स्वचालित करते हैं जिन्हें अन्यथा मैन्युअल रूप से करना होगा।

1. वांछित परिणाम ज्ञात होने पर "पैरामीटर चयन" फ़ंक्शन उपयुक्त है, और ऐसा परिणाम प्राप्त करने के लिए आपको चर के इनपुट मान को जानने की आवश्यकता है.
2. "समाधान की खोज" समस्याओं को हल करने के लिए एक ऐड-ऑन है। सीमा निर्धारित करना और उन्हें इंगित करना आवश्यक है, जिसके बाद सिस्टम उत्तर ढूंढेगा। समाधान मूल्यों को बदलकर निर्धारित किया जाता है।
3. परिदृश्य प्रबंधक का उपयोग करके संवेदनशीलता विश्लेषण किया जा सकता है। यह टूल डेटा टैब के अंतर्गत क्या-अगर विश्लेषण मेनू में पाया जाता है। यह कई कोशिकाओं में मूल्यों को प्रतिस्थापित करता है - संख्या 32 तक पहुंच सकती है। प्रेषक इन मूल्यों की तुलना करता है ताकि उपयोगकर्ता को उन्हें मैन्युअल रूप से बदलना न पड़े। स्क्रिप्ट प्रबंधक का उपयोग करने का एक उदाहरण:

एक्सेल में एक निवेश परियोजना का संवेदनशीलता विश्लेषण

क्या-अगर विश्लेषण विशेष रूप से उन स्थितियों में उपयोगी होता है जहां पूर्वानुमान की आवश्यकता होती है, जैसे कि निवेश। विश्लेषक इस पद्धति का उपयोग यह पता लगाने के लिए करते हैं कि कुछ कारकों में परिवर्तन के परिणामस्वरूप किसी कंपनी के स्टॉक का मूल्य कैसे बदलेगा।

निवेश संवेदनशीलता विश्लेषण विधि

"क्या होगा" का विश्लेषण करते समय गणना का उपयोग करें - मैनुअल या स्वचालित। मानों की श्रेणी ज्ञात है, और उन्हें एक-एक करके सूत्र में प्रतिस्थापित किया जाता है। परिणाम मूल्यों का एक सेट है। उनमें से उपयुक्त संख्या का चयन कीजिए। आइए चार संकेतकों पर विचार करें जिनके लिए वित्त के क्षेत्र में संवेदनशीलता विश्लेषण किया जाता है:

1. शुद्ध वर्तमान मूल्य - निवेश की राशि को आय की राशि से घटाकर परिकलित किया जाता है।
2. प्रतिफल/लाभ की आंतरिक दर - यह इंगित करती है कि एक वर्ष में किसी निवेश से कितना लाभ प्राप्त करना आवश्यक है।
3. पेबैक अनुपात प्रारंभिक निवेश के सभी लाभों का अनुपात है।
4. डिस्काउंटेड प्रॉफिट इंडेक्स - निवेश की प्रभावशीलता को दर्शाता है।

सूत्र

एम्बेडिंग संवेदनशीलता की गणना इस सूत्र का उपयोग करके की जा सकती है: % में आउटपुट पैरामीटर में परिवर्तन /% में इनपुट पैरामीटर में परिवर्तन।

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

1. आपको मानक शर्तों के तहत परिणाम जानने की जरूरत है।
2. हम एक चर को बदलते हैं और परिणाम में होने वाले परिवर्तनों की निगरानी करते हैं।
3. हम स्थापित स्थितियों के सापेक्ष दोनों मापदंडों के प्रतिशत परिवर्तन की गणना करते हैं।
4. हम प्राप्त प्रतिशत को सूत्र में सम्मिलित करते हैं और संवेदनशीलता निर्धारित करते हैं।

एक्सेल में एक निवेश परियोजना के संवेदनशीलता विश्लेषण का एक उदाहरण

विश्लेषण पद्धति की बेहतर समझ के लिए, एक उदाहरण की आवश्यकता है। आइए निम्नलिखित ज्ञात डेटा के साथ परियोजना का विश्लेषण करें:

1. इस पर परियोजना का विश्लेषण करने के लिए तालिका भरें।

2. हम OFFSET फ़ंक्शन का उपयोग करके नकदी प्रवाह की गणना करते हैं। प्रारंभिक चरण में, प्रवाह निवेश के बराबर है। अगला, हम सूत्र लागू करते हैं: =आईएफ (ऑफसेट (संख्या, 1;) = 2; एसयूएम (इनफ्लो 1: आउटफ्लो 1); एसयूएम (इनफ्लो 1: आउटफ्लो 1)+$B5 $)
   तालिका के लेआउट के आधार पर सूत्र में सेल पदनाम भिन्न हो सकते हैं। अंत में, प्रारंभिक डेटा से मूल्य जोड़ा जाता है - बचाव मूल्य।

3. हम उस अवधि का निर्धारण करते हैं जिसके लिए परियोजना भुगतान करेगी। प्रारंभिक अवधि के लिए, हम इस सूत्र का उपयोग करते हैं: = संक्षेप में (G7: जी17;»<0″). सेल रेंज कैश फ्लो कॉलम है। आगे की अवधि के लिए, हम इस सूत्र को लागू करते हैं: =आरंभिक अवधि+आईएफ(प्रथम ई.स्ट्रीम>0; प्रथम ई.स्ट्रीम;0)। यह परियोजना 4 वर्षों में ब्रेक-ईवन बिंदु पर है।

4. हम उन अवधियों की संख्या के लिए एक कॉलम बनाते हैं जब परियोजना भुगतान करती है।

5. हम निवेश पर रिटर्न की गणना करते हैं। एक अभिव्यक्ति बनाना आवश्यक है जहां एक विशेष अवधि में लाभ को प्रारंभिक निवेश से विभाजित किया जाता है।

6. हम इस सूत्र का उपयोग करके छूट कारक निर्धारित करते हैं: =1/(1+डिस्क.%) ^नंबर.

7. हम गुणन का उपयोग करके वर्तमान मूल्य की गणना करते हैं - नकद प्रवाह को छूट कारक से गुणा किया जाता है।

8. आइए पीआई (लाभप्रदता सूचकांक) की गणना करें। समय के साथ वर्तमान मूल्य को परियोजना की शुरुआत में निवेश से विभाजित किया जाता है।

9. आइए आईआरआर फ़ंक्शन का उपयोग करके वापसी की आंतरिक दर को परिभाषित करें: = आईआरआर (नकदी प्रवाह की सीमा)।

डेटाशीट का उपयोग करके निवेश संवेदनशीलता विश्लेषण

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

गणना स्वचालन के साथ एक्सेल में कारक और फैलाव विश्लेषण

संवेदनशीलता विश्लेषण का एक अन्य प्रकार है कारक विश्लेषण और विचरण का विश्लेषण। पहला प्रकार संख्याओं के बीच संबंध को परिभाषित करता है, दूसरा प्रकार दूसरे पर एक चर की निर्भरता को प्रकट करता है।

एक्सेल में एनोवा

इस तरह के विश्लेषण का उद्देश्य मूल्य की परिवर्तनशीलता को तीन घटकों में विभाजित करना है:

1. अन्य मूल्यों के प्रभाव के परिणामस्वरूप परिवर्तनशीलता।
2. इसे प्रभावित करने वाले मूल्यों के संबंध के कारण परिवर्तन।
3. यादृच्छिक परिवर्तन।

आइए एक्सेल ऐड-इन "डेटा विश्लेषण" के माध्यम से विचरण का विश्लेषण करें। यदि यह सक्षम नहीं है, तो इसे सेटिंग्स में सक्षम किया जा सकता है।

प्रारंभिक तालिका को दो नियमों का पालन करना चाहिए: प्रत्येक मान के लिए एक कॉलम होता है, और इसमें डेटा आरोही या अवरोही क्रम में व्यवस्थित होता है। संघर्ष में व्यवहार पर शिक्षा के स्तर के प्रभाव की जाँच करना आवश्यक है।

1. डेटा टैब में डेटा विश्लेषण टूल ढूंढें और उसकी विंडो खोलें। सूची में, आपको विचरण के एकतरफा विश्लेषण का चयन करना होगा।

2. डायलॉग बॉक्स की पंक्तियों को भरें। इनपुट अंतराल हेडर और नंबरों को छोड़कर सभी सेल हैं। कॉलम द्वारा समूह। हम परिणामों को एक नई शीट पर प्रदर्शित करते हैं।

चूंकि पीले सेल में मान एक से अधिक है, इसलिए इस धारणा को गलत माना जा सकता है - संघर्ष में शिक्षा और व्यवहार के बीच कोई संबंध नहीं है।

एक्सेल में फैक्टर एनालिसिस: एक उदाहरण

आइए बिक्री के क्षेत्र में डेटा के संबंध का विश्लेषण करें - लोकप्रिय और अलोकप्रिय उत्पादों की पहचान करना आवश्यक है। प्रारंभिक जानकारी:

1. हमें यह पता लगाने की जरूरत है कि दूसरे महीने में किस सामान की मांग सबसे ज्यादा बढ़ी। हम मांग में वृद्धि और गिरावट को निर्धारित करने के लिए एक नई तालिका तैयार कर रहे हैं। इस सूत्र का उपयोग करके वृद्धि की गणना की जाती है: =IF((मांग 2-मांग 1)>0; मांग 2-मांग 1;0)। सूत्र घटाएं: =IF(ग्रोथ = 0; डिमांड 1- डिमांड 2;0)।

2. प्रतिशत के रूप में माल की मांग में वृद्धि की गणना करें: =IF(विकास/परिणाम 2 = 0; कमी/परिणाम 2; वृद्धि/परिणाम 2)।

3. आइए स्पष्टता के लिए एक चार्ट बनाएं - सेल की एक श्रेणी का चयन करें और "इन्सर्ट" टैब के माध्यम से एक हिस्टोग्राम बनाएं। सेटिंग्स में, आपको भरण को हटाने की आवश्यकता है, यह प्रारूप डेटा श्रृंखला उपकरण के माध्यम से किया जा सकता है।

एक्सेल में विचरण का दो-तरफ़ा विश्लेषण

विचरण का विश्लेषण कई चरों के साथ किया जाता है। एक उदाहरण के साथ इस पर विचार करें: आपको यह पता लगाने की आवश्यकता है कि विभिन्न मात्राओं की ध्वनि की प्रतिक्रिया पुरुषों और महिलाओं में कितनी जल्दी प्रकट होती है।

1. हम "डेटा विश्लेषण" खोलते हैं, सूची में आपको दोहराव के बिना विचरण का दो-तरफ़ा विश्लेषण खोजने की आवश्यकता होती है।
2. इनपुट अंतराल - वे सेल जिनमें डेटा होता है (बिना हेडर के)। हम परिणामों को एक नई शीट पर प्रदर्शित करते हैं और "ओके" पर क्लिक करते हैं।

एफ मान एफ-क्रिटिकल से अधिक है, जिसका अर्थ है कि फर्श ध्वनि की प्रतिक्रिया की गति को प्रभावित करता है।

निष्कर्ष

इस लेख में, एक्सेल स्प्रेडशीट में संवेदनशीलता विश्लेषण पर विस्तार से चर्चा की गई, ताकि प्रत्येक उपयोगकर्ता इसके आवेदन के तरीकों को समझ सके।