एक्सेल में तकनीकी विश्लेषण। एमएस एक्सेल में मूविंग एवरेज और एक्सपोनेंशियल स्मूथिंग

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

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

विधि 1: विश्लेषण पैकेज

विश्लेषण पैकेजएक एक्सेल ऐड-इन है जो डिफ़ॉल्ट रूप से अक्षम है। इसलिए, सबसे पहले, आपको इसे सक्षम करने की आवश्यकता है।

इस चरण के बाद, पैकेज "डेटा विश्लेषण"सक्रिय, और संबंधित बटन टैब में रिबन पर दिखाई दिया "जानकारी".

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

1. टैब पर जाएं "जानकारी"और बटन पर क्लिक करें "डेटा विश्लेषण", जो ब्लॉक में औजारों के रिबन पर स्थित है "विश्लेषण".



2. में उपलब्ध उपकरणों की एक सूची विश्लेषण पैकेज. उनमें से एक नाम चुनें "सामान्य गति"और बटन पर क्लिक करें ठीक है.



3. मूविंग एवरेज फोरकास्टिंग के लिए डेटा एंट्री विंडो लॉन्च की गई है।

   खेत मेँ "इनपुट अंतराल"उस सीमा के पते को इंगित करें जहां मासिक राजस्व की राशि उस सेल के बिना स्थित है जिसमें डेटा की गणना की जानी चाहिए।

   खेत मेँ "मध्यान्तर"चौरसाई विधि द्वारा प्रसंस्करण मूल्यों के लिए अंतराल निर्दिष्ट करें। आरंभ करने के लिए, आइए स्मूथिंग मान को तीन महीने पर सेट करें, और इसलिए संख्या दर्ज करें "3".

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

   आपको इसके आगे वाले बॉक्स को भी चेक करना चाहिए "मानक त्रुटियाँ".

   यदि आवश्यक हो, तो आप . के बगल में स्थित बॉक्स को भी चेक कर सकते हैं "ग्राफ आउटपुट"दृश्य प्रदर्शन के लिए, हालांकि हमारे मामले में यह आवश्यक नहीं है।

   सभी सेटिंग्स हो जाने के बाद, बटन पर क्लिक करें ठीक है.



4. कार्यक्रम प्रसंस्करण परिणाम प्रदर्शित करता है।



5. आइए अब दो महीने की अवधि में स्मूथिंग चलाते हैं ताकि पता लगाया जा सके कि कौन सा परिणाम अधिक सही है। इन उद्देश्यों के लिए, हम टूल को फिर से चलाते हैं "सामान्य गति" विश्लेषण पैकेज.

   खेत मेँ "इनपुट अंतराल"हम पिछले मामले की तरह ही मान छोड़ते हैं।

   खेत मेँ "मध्यान्तर"एक नंबर डाल दो "2".

   खेत मेँ "बाहर निकलें अंतराल"नई खाली श्रेणी का पता निर्दिष्ट करें, जो फिर से, इनपुट अंतराल से एक सेल अधिक होना चाहिए।

   बाकी सेटिंग्स को वही छोड़ दिया गया है। इसके बाद बटन पर क्लिक करें ठीक है.



6. उसके बाद, प्रोग्राम स्क्रीन पर परिणाम की गणना और प्रदर्शित करता है। यह निर्धारित करने के लिए कि दोनों में से कौन सा मॉडल अधिक सटीक है, हमें मानक त्रुटियों की तुलना करने की आवश्यकता है। यह संकेतक जितना छोटा होगा, परिणाम की सटीकता की संभावना उतनी ही अधिक होगी। जैसा कि आप देख सकते हैं, सभी मूल्यों के लिए, दो महीने की चलती औसत की गणना में मानक त्रुटि 3 महीने के लिए समान संकेतक से कम है। इस प्रकार, दिसंबर के लिए अनुमानित मूल्य को स्लाइडिंग-फॉरवर्ड विधि द्वारा परिकलित मूल्य माना जा सकता है। पिछली अवधि. हमारे मामले में, यह मान 990.4 हजार रूबल है।

विधि 2: औसत फ़ंक्शन का उपयोग करें

एक्सेल में, चलती औसत पद्धति को लागू करने का एक और तरीका है। इसका उपयोग करने के लिए, आपको कई मानक कार्यक्रम कार्यों को लागू करने की आवश्यकता है, जिनमें से मूल हमारे उद्देश्य के लिए है औसत. उदाहरण के लिए, हम पहले मामले में उसी उद्यम आय तालिका का उपयोग करेंगे।

पिछली बार की तरह, हमें एक सुचारू समय श्रृंखला बनाने की आवश्यकता होगी। लेकिन इस बार कार्रवाई इतनी स्वचालित नहीं होगी। परिणामों की तुलना करने में सक्षम होने के लिए प्रत्येक दो और फिर तीन महीनों के लिए एक औसत मूल्य की गणना की जानी चाहिए।

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

1. मार्च के लिए पंक्ति में एक खाली कॉलम में एक सेल का चयन करें। इसके बाद, आइकन पर क्लिक करें "फ़ंक्शन डालें", जिसे फॉर्मूला बार के पास रखा जाता है।



2. विंडो सक्रिय है फंक्शन विजार्ड्स. श्रेणी "सांख्यिकीय"अर्थ की तलाश में "औसत", इसे चुनें और बटन पर क्लिक करें ठीक है.



3. ऑपरेटर तर्क विंडो लॉन्च औसत. इसका सिंटैक्स निम्नलिखित है:

   औसत(नंबर1, नंबर2,…)

   केवल एक तर्क की आवश्यकता है।

   हमारे मामले में, क्षेत्र में "संख्या 1"हमें उस सीमा का उल्लेख करना चाहिए, जहां पिछली दो अवधियों (जनवरी और फरवरी) की आय इंगित की गई है। हम कर्सर को फ़ील्ड में सेट करते हैं और कॉलम में शीट पर संबंधित सेल का चयन करते हैं "आय". इसके बाद बटन पर क्लिक करें ठीक है.



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



5. हमें वर्ष के अंत तक पिछले दो महीनों के औसत मूल्य के परिणामों की गणना मिलती है।



6. अब अप्रैल के लिए पंक्ति में अगले खाली कॉलम में सेल का चयन करें। फ़ंक्शन तर्क विंडो को कॉल करना औसतउसी तरह जैसा कि पहले बताया गया है। खेत मेँ "संख्या 1"कॉलम में कक्षों के निर्देशांक दर्ज करें "आय"जनवरी से मार्च तक। फिर बटन पर क्लिक करें ठीक है.



7. भरण हैंडल का उपयोग करके, सूत्र को नीचे तालिका कक्षों में कॉपी करें।



8. इसलिए, हमने मूल्यों की गणना की। अब, पिछली बार की तरह, हमें यह पता लगाना होगा कि किस प्रकार का विश्लेषण बेहतर है: 2 या 3 महीने की चौरसाई के साथ। ऐसा करने के लिए, मानक विचलन और कुछ अन्य संकेतकों की गणना करें। आरंभ करने के लिए, आइए गणना करें पूर्ण विचलन, मानक का उपयोग कर एक्सेल फ़ंक्शन पेट, जो धनात्मक या ऋणात्मक संख्याओं के बजाय अपना मापांक लौटाता है। यह मान चुने गए महीने की वास्तविक आय और अनुमानित आय के बीच के अंतर के बराबर होगा. हम कर्सर को मई के लिए पंक्ति में अगले खाली कॉलम पर सेट करते हैं। कॉलिंग फंक्शन विजार्ड.



9. श्रेणी "गणितीय"फ़ंक्शन का नाम हाइलाइट करें पेट. बटन पर क्लिक करें ठीक है.



10. फ़ंक्शन तर्क विंडो लॉन्च की गई है पेट. एकमात्र क्षेत्र में "संख्या"कॉलम में कोशिकाओं की सामग्री के बीच अंतर निर्दिष्ट करें "आय"तथा "2 महीने"मई के लिए। फिर बटन पर क्लिक करें ठीक है.



11. भरण मार्कर का उपयोग करते हुए, इस सूत्र को नवंबर सहित तालिका की सभी पंक्तियों में कॉपी करें।



12. हम पहले से परिचित फ़ंक्शन का उपयोग करके पूरी अवधि के लिए पूर्ण विचलन के औसत मूल्य की गणना करते हैं औसत.



13. 3 महीने के लिए चलती औसत के लिए पूर्ण विचलन की गणना करने के लिए हम एक समान प्रक्रिया करते हैं। पहले हम फ़ंक्शन लागू करते हैं पेट. केवल इस बार हम 3 महीने के लिए चलती औसत पद्धति का उपयोग करके गणना की गई वास्तविक आय और नियोजित एक के साथ कोशिकाओं की सामग्री के बीच अंतर पर विचार करते हैं।



14. अगला, हम फ़ंक्शन का उपयोग करके सभी निरपेक्ष विचलन डेटा के औसत मूल्य की गणना करते हैं औसत.



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



16. लेकिन सापेक्ष विचलन आमतौर पर प्रतिशत के रूप में प्रदर्शित होता है। इसलिए, शीट पर उपयुक्त श्रेणी का चयन करें, टैब पर जाएँ "घर", जहां टूलबॉक्स में "संख्या"एक विशेष स्वरूपण फ़ील्ड में, प्रतिशत स्वरूप सेट करें। सापेक्ष विचलन गणना का परिणाम तब प्रतिशत के रूप में प्रदर्शित होता है।



17. हम 3 महीने के लिए स्मूथिंग का उपयोग करके डेटा के साथ सापेक्ष विचलन की गणना करने के लिए एक समान ऑपरेशन करते हैं। केवल इस मामले में, गणना के लिए, लाभांश के रूप में, हम तालिका के दूसरे कॉलम का उपयोग करते हैं, जिसका हमारे पास नाम है "एब्स। बंद (3मी)". फिर हम संख्यात्मक मानों को प्रतिशत रूप में अनुवादित करते हैं।



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



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

    SQRT(SUMDIFF(B6:B12,C6:C12)/COUNT(B6:B12))

    हम इसे भरण मार्कर का उपयोग करके मानक विचलन की गणना के साथ कॉलम के अन्य कक्षों में कॉपी करते हैं।



20. मानक विचलन की गणना के लिए एक समान ऑपरेशन 3 महीने के लिए चलती औसत के लिए किया जाता है।



21. उसके बाद, हम फ़ंक्शन को लागू करके इन दोनों संकेतकों के लिए पूरी अवधि के लिए औसत मूल्य की गणना करते हैं औसत.



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



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

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

1. गणना मौसमी कारक;
2. चुनना औसत की गणना के लिए अवधिमूल्य;
3. पूर्वानुमान की गणना करें, अर्थात। औसत मूल्य को मौसमी कारक से गुणा करें;
4. विचार करना अतिरिक्त कारकजो बिक्री को महत्वपूर्ण रूप से प्रभावित करता है;

गणना चलती औसत पूर्वानुमानऔसत बहुत केवल. इसके लिए हम लेते हैं अर्थ, उदाहरण के लिए, पिछले 3 महीनों की औसत बिक्री और मौसमी कारक से गुणा करें 3 महीने तक - और महीने का पूर्वानुमान तैयार है। हम अगले महीने के लिए भी ऐसा ही करते हैं, केवल पिछले पूर्वानुमान महीने को ही गणना में शामिल किया जाएगा।

1. चलती औसत पद्धति का उपयोग करके पूर्वानुमान के लिए मौसमी गुणांक की गणना करें।

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

1 महीने तक:

• जनवरी का गुणांक - जनवरी के अनुपात में मौसम के गुणांक को दिसंबर में वृद्धि से मंजूरी दी गई;

  फरवरी - फरवरी गुणांक जनवरी के लिए;

  मार्च - मार्च से फरवरी;

2 महीने तक:

• जनवरी के लिए - जनवरी मौसमी कारक का दिसंबर और नवंबर के औसत मूल्य का अनुपात

  फरवरी - फरवरी के लिए जनवरी और दिसंबर के गुणांकों के औसत मूल्य से विभाजित किया जाता है

  मार्च-मार्च के लिए फरवरी और जनवरी गुणांक के औसत तक

3 महीने तक:

• जनवरी के मौसम के कारक को 3 महीने तक निर्धारित करने के लिए, हम जनवरी के मौसम कारक को विकास से मुक्त करते हैं, दिसंबर, नवंबर, अक्टूबर के लिए मौसमी कारक के औसत मूल्य से विकास को मंजूरी देते हैं;

  फरवरी के लिए, हम फरवरी के गुणांक को नवंबर, दिसंबर और जनवरी के गुणांकों के औसत मूल्य से विभाजित करते हैं;

  मार्च के लिए - दिसंबर, जनवरी और फरवरी में वृद्धि से मुक्त मौसमी गुणांक के औसत मूल्य के लिए मार्च का अनुपात;

हमने पिछली अवधियों के लिए मौसमी गुणांक की गणना की है, अब हम निर्धारित करेंगे किस अवधि के लिए अधिक सटीक पूर्वानुमान के लिए औसत मान लेना बेहतर है.साथ ही, आप पूर्वानुमान4एसी प्रोग्राम का उपयोग करके आसानी से और शीघ्रता से मौसमी गुणांकों की गणना कर सकते हैं - पूर्वानुमान के सभी चरणों में एक विश्वसनीय सहायक।

2. चलती औसत पद्धति का उपयोग करके पूर्वानुमान के लिए औसत मूल्य की गणना के लिए अवधि का चयन करें।

ऐसा करने के लिए, हम अंतिम और अंतिम अवधियों के लिए एक पूर्वानुमान बनाते हैं, जिसके लिए हम तीन या अधिक तरीकों से डेटा जानते हैं उचित औसत गणना अवधि निर्धारित करने के लिए(संलग्न फाइल शीट "अवधि चयन" देखें)। और हम देखते हैं कि कौन सा विकल्प अधिक सटीक पूर्वानुमान लगाता है:

1. आइए पहले महीने तक चलती औसत पद्धति का उपयोग करके बिक्री पूर्वानुमान की गणना करें:

दिसंबर \u003d नवंबर की बिक्री की मात्रा को दिसंबर के मौसमी कारक से पिछले महीने से गुणा किया गया।

1. 2 महीने के लिए चलती औसत पद्धति का उपयोग करके बिक्री पूर्वानुमान की गणना करें:

दिसंबर \u003d अक्टूबर और नवंबर के लिए औसत बिक्री, दिसंबर मौसमी कारक से 2 महीने गुणा करें।

1. हम 3 महीने के लिए चलती औसत पद्धति का उपयोग करके पूर्वानुमान की गणना करते हैं:

दिसंबर \u003d सितंबर, अक्टूबर और नवंबर के लिए औसत बिक्री, दिसंबर के मौसमी कारक से 3 महीने से गुणा करें।

अब हमने दिसंबर के लिए तीन तरीकों से पूर्वानुमान की गणना की है। चलो नवंबर के लिए भी ऐसा ही करते हैं।

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

3. चलती औसत पद्धति का उपयोग करके बिक्री पूर्वानुमान की गणना करें।

इसलिये हमने पिछले 2 महीनों के औसत के आधार पर पूर्वानुमान चुना, फिर जनवरी के पूर्वानुमान के लिए, हम नवंबर और दिसंबर के लिए औसत बिक्री जनवरी मौसमी कारक को 2 महीने से गुणा करती है.

फरवरी के पूर्वानुमान के लिएहम जनवरी और दिसंबर की औसत बिक्री को फरवरी के मौसमी कारक से गुणा करते हैं।

इस तर्क का पालन करते हुए, हम पूर्वानुमान की गणना को वर्ष के अंत तक बढ़ाते हैं। वर्ष के लिए बिक्री पूर्वानुमान तैयार है।

4. बिक्री पूर्वानुमान की गणना करते समय विचार करने के लिए अतिरिक्त कारक।

पूर्वानुमान की सटीकता में सुधार करने के लिए, यह महत्वपूर्ण है:

1. पिछली अवधियों से कारक घटाएं, कौन सा बिक्री पर खासा असर, लेकिन पूर्वानुमान महीनों में दोहराया नहीं जाएगा(बिक्री प्रचार, एक बड़े सामयिक ग्राहक का एकमुश्त शिपमेंट, एक बड़ी खुदरा श्रृंखला से निकासी, आदि)।
2. पूर्वानुमान महीनों में कारक जोड़ेंजो बिक्री को महत्वपूर्ण रूप से प्रभावित करेगा - बड़े नेटवर्क के साथ काम की शुरुआत, बड़े बिक्री प्रचारों का आयोजन, नए उत्पादों का शुभारंभ, विज्ञापन अभियान आदि।

आपके लिए सटीक भविष्यवाणियां!

पूर्वानुमान4एसी प्रो चलती औसत पद्धति का उपयोग करके पूर्वानुमान की गणना करता है एक साथ 1000 से अधिक बार श्रृंखला के लिएएक बटन के स्पर्श पर, महत्वपूर्ण रूप से आपका समय बचा रहा है 4 तरीकों में से एक:

पिछली दो अवधियों के औसत के लिए

पिछली तीन अवधियों के औसत के लिए

पिछली 4 अवधियों के औसत के लिए

3 और 4 पिछली अवधियों के औसत से दोगुना

हमसे जुड़ें!

डाउनलोड मुक्त एप्लिकेशन्सपूर्वानुमान और व्यापार विश्लेषण के लिए:

• नोवो पूर्वानुमान लाइट- स्वचालित पूर्वानुमान गणनामें एक्सेल.
• 4एनालिटिक्स- एबीसी-एक्सवाईजेड विश्लेषणऔर उत्सर्जन का विश्लेषण एक्सेल।
• क्लिक सेंसडेस्कटॉप और क्विक व्यूव्यक्तिगत संस्करण - डेटा विश्लेषण और विज़ुअलाइज़ेशन के लिए बीआई सिस्टम।

सशुल्क समाधानों की सुविधाओं का परीक्षण करें:

• नोवो पूर्वानुमान प्रो- बड़े डेटा सरणियों के लिए एक्सेल में पूर्वानुमान।

व्यापार में, किसी भी अन्य गतिविधि की तरह, एक व्यक्ति जानना चाहता है कि आगे क्या होगा। उस भाग्यशाली व्यक्ति के धन की कल्पना करना और भी मुश्किल है जो 100% सटीकता के साथ भविष्य का अनुमान लगा सके। लेकिन, दुर्भाग्य से (या, सौभाग्य से), दूरदर्शिता का उपहार अत्यंत दुर्लभ है। लेकिन ... कम से कम कोशिश करें सामान्य शब्दों मेंउद्यमी बस भविष्य की व्यावसायिक स्थिति की कल्पना करने के लिए बाध्य है।

सबसे पहले, मैं एक पोस्ट में एक साथ कई सरल और सुविधाजनक तकनीकों के बारे में लिखना चाहता था, लेकिन पोस्ट बहुत बड़ी होने लगी। और इसलिए कई पोस्ट होंगे विषय के लिए समर्पितपूर्वानुमान इस पोस्ट में, हम एक्सेल सुविधाओं का उपयोग करते हुए सबसे सरल पूर्वानुमान विधियों में से एक का वर्णन करेंगे - चलती औसत विधि।

विपणन अनुसंधान के अभ्यास में अक्सर निम्नलिखित मूल्यों की भविष्यवाणी की जाती है:

• बिक्री की मात्रा
• बाजार का आकार और क्षमता
• उत्पादन मात्रा
• आयात मात्रा
• मूल्य गतिशीलता
• और इसी तरह।

पूर्वानुमान के लिए, जिस पर हम इस पोस्ट में विचार करते हैं, मैं आपको निम्नलिखित सरल एल्गोरिथम से चिपके रहने की सलाह देता हूं:

1. संग्रह माध्यमिक जानकारीमुद्दे पर(अधिमानतः मात्रात्मक और गुणात्मक दोनों)। इसलिए, उदाहरण के लिए, यदि आप अपने बाजार के आकार की भविष्यवाणी करते हैं, तो आपको बाजार (उत्पादन की मात्रा, आयात, मूल्य की गतिशीलता, बिक्री की मात्रा, आदि) और प्रवृत्तियों, समस्याओं या बाजार के अवसरों पर सांख्यिकीय जानकारी एकत्र करने की आवश्यकता है। यदि आप बिक्री की मात्रा का अनुमान लगा रहे हैं, तो आपको अवधि के लिए बिक्री डेटा की आवश्यकता है। पूर्वानुमान के लिए, आप जितना अधिक ऐतिहासिक डेटा देखेंगे, उतना ही बेहतर होगा। पूर्वानुमानित घटना को प्रभावित करने वाले कारकों के विश्लेषण के साथ पूर्वानुमान को पूरक करना वांछनीय है (आप SWOT, कीट विश्लेषण या किसी अन्य का उपयोग कर सकते हैं)। यह आपको विकास के तर्क को समझने की अनुमति देगा, और इस तरह आप किसी विशेष प्रवृत्ति मॉडल की व्यवहार्यता की जांच करने में सक्षम होंगे।

2. आगे वांछनीय मात्रात्मक डेटा की जाँच करें. ऐसा करने के लिए, आपको समान संकेतकों के मूल्यों की तुलना करने की आवश्यकता है, लेकिन इससे प्राप्त विभिन्न स्रोत. यदि सब कुछ परिवर्तित हो जाता है, तो आप डेटा को एक्सेल में "ड्राइव" कर सकते हैं। डेटा को निम्नलिखित आवश्यकताओं को भी पूरा करना चाहिए:

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

3. डेटा की जांच के बाद, आप कर सकते हैं विभिन्न पूर्वानुमान तकनीकों को लागू करें. मैं बहुत से शुरू करना चाहूंगा सरल विधि – मूविंग एवरेज मेथड

मूविंग एवरेज मेथड

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

आम तौर पर, एक चलती औसत पूर्वानुमान को अवलोकन अवधि के तुरंत बाद की अवधि के लिए पूर्वानुमान के रूप में माना जाता है। उसी समय, ऐसा पूर्वानुमान तब लागू होता है जब अध्ययन के तहत घटना क्रमिक रूप से विकसित होती है, अर्थात। कुछ रुझान हैं, और मूल्य वक्र चार्ट के चारों ओर पागलों की तरह नहीं कूदता है।

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

तो आप इसे कैसे करते हैं?एक्सेल

1. मान लें कि आपके पास पिछले 29 महीनों की मासिक बिक्री है। और आप यह निर्धारित करना चाहते हैं कि 30वें महीने में कितनी बिक्री होगी। लेकिन, ईमानदार होने के लिए, 30 . को संचालित करना बिल्कुल भी आवश्यक नहीं है ऐतिहासिक मूल्य, क्योंकि यह विधि औसत की गणना के लिए केवल पिछले कुछ महीनों का उपयोग करेगी। इसलिए, गणना के लिए केवल पिछले कुछ महीने ही पर्याप्त हैं।

2. हम इस तालिका को एक्सेल द्वारा समझने योग्य रूप में लाते हैं, अर्थात। ताकि सभी मान एक ही पंक्ति में हों।

3. अगला, हम पिछले तीन (चार, पांच? जैसा कि आप चुनते हैं) मानों (देखें) के लिए औसत की गणना के लिए सूत्र दर्ज करते हैं। गणना के लिए अंतिम 3 मानों का उपयोग करना सबसे सुविधाजनक है, क्योंकि यदि अधिक को ध्यान में रखा जाता है, तो डेटा अत्यधिक औसत हो जाएगा; यदि कम है, तो यह सटीक नहीं होगा।

4. पूर्वानुमान माह 30 तक के बाद के सभी मानों के लिए स्वत: पूर्ण फ़ंक्शन का उपयोग करना। इस प्रकार, फ़ंक्शन जून 2010 के पूर्वानुमान की गणना करेगा। पूर्वानुमान मानों के अनुसार, जून में बिक्री लगभग 408 इकाई होगी। लेकिन ध्यान दें कि यदि नीचे की ओर रुझान स्थिर है, जैसा कि हमारे उदाहरण में है, औसत पूर्वानुमान की गणना को थोड़ा अधिक अनुमानित किया जाएगा, या, जैसा कि यह था, वास्तविक मूल्यों को "पीछे" कर देगा।

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

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

मूविंग एवरेज टूल को टूल मेनू से डेटा एनालिसिस कमांड डायलॉग बॉक्स से एक्सेस किया जा सकता है।

मूविंग एवरेज टूल का उपयोग करते हुए, मैं तालिका 1.1 (तालिका 3.1) के आर्थिक प्रदर्शन का पूर्वानुमान लगाता हूं।

मेज3 .1 चलती औसत पद्धति का उपयोग करके अध्ययन की गई गतिशील श्रृंखला के संकेतकों के व्यवहार में प्रवृत्ति का मूल्यांकन

नोट - स्रोत: .

तालिका में डेटा के आधार पर, मैं एक चलती औसत चार्ट बनाता हूं।

चित्र 3.1 - मूविंग एवरेज

नोट - स्रोत: .

श्रृंखला की वृद्धि दर और चलती औसत की सामान्य गतिशीलता को ग्राफ पर प्रदर्शित किया जाता है, जिससे यह देखा जा सकता है कि चलती औसत संकेतक में वृद्धि होती है, फिर घटती है, फिर फिर से बढ़ती है, अर्थात। हर महीने व्यापार की मात्रा लगातार बदल रही है।

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

1. एक्सेल का उपयोग करके रैखिक पूर्वानुमान तैयार करना

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

1. ट्रेंड मॉडल बनाने के लिए लीनियर का उपयोग करना

LINEST कार्यपत्रक फ़ंक्शन अवलोकन के परिणामों और उनके निर्धारण के समय के बीच रैखिक संबंध की प्रकृति को निर्धारित करने में मदद करता है और इसे गणितीय विवरण देता है, सबसे अच्छा तरीकामूल डेटा का अनुमान लगाना। एक मॉडल बनाने के लिए, यह y=mx+b फॉर्म के समीकरण का उपयोग करता है, जहां y अध्ययन के तहत संकेतक है; x = टी समय की प्रवृत्ति है; बी, एम समीकरण पैरामीटर हैं जो वाई-क्रॉसिंग और ढलान की विशेषता रखते हैं प्रवृत्ति रेखा, क्रमशः। LINEST मॉडल के मापदंडों की गणना विधि के आधार पर की जाती है कम से कम वर्गों.

आप मानक टूलबार पर स्थित फ़ंक्शन विज़ार्ड संवाद बॉक्स (सांख्यिकीय श्रेणी) में LINEST फ़ंक्शन को कॉल कर सकते हैं।

तालिका 3.2 - LINEST फ़ंक्शन का उपयोग करके एक रैखिक प्रवृत्ति मॉडल की गणना और मूल्यांकन

एक्सट्रपलेशन एक तरीका है वैज्ञानिक अनुसंधान, जो भविष्यवाणी वस्तु के भविष्य के विकास के लिए अतीत और वर्तमान प्रवृत्तियों, पैटर्न, संबंधों के वितरण पर आधारित है। एक्सट्रपलेशन विधियों में शामिल हैं चलती औसत विधि, घातीय चौरसाई विधि, कम से कम वर्ग विधि।

मूविंग एवरेज मेथड व्यापक में से एक है ज्ञात तरीकेसमय श्रृंखला चौरसाई। इस पद्धति का उपयोग करके, यादृच्छिक उतार-चढ़ाव को समाप्त करना और मुख्य कारकों के प्रभाव के अनुरूप मूल्य प्राप्त करना संभव है।

चलती औसत की मदद से चौरसाई इस तथ्य पर आधारित है कि यादृच्छिक विचलन एक दूसरे को औसत में रद्द कर देते हैं। यह चयनित समय अंतराल के भीतर अंकगणितीय माध्य के साथ समय श्रृंखला के प्रारंभिक स्तरों के प्रतिस्थापन के कारण है। परिणामी मान चयनित समय अंतराल (अवधि) के मध्य को संदर्भित करता है।

फिर अवधि को एक अवलोकन द्वारा स्थानांतरित कर दिया जाता है, और औसत की गणना दोहराई जाती है। इस मामले में, औसत निर्धारित करने की अवधि हर समय समान मानी जाती है। इस प्रकार, विचाराधीन प्रत्येक मामले में, माध्य केंद्रित होता है, अर्थात, चौरसाई अंतराल के मध्य बिंदु को संदर्भित करता है और इस बिंदु के लिए स्तर का प्रतिनिधित्व करता है।

चलती औसत के साथ एक समय श्रृंखला को सुचारू करते समय, श्रृंखला के सभी स्तर गणना में शामिल होते हैं। चौरसाई अंतराल जितना चौड़ा होगा, प्रवृत्ति उतनी ही चिकनी होगी। चिकनी श्रृंखला प्रारंभिक एक से (n-1) टिप्पणियों से छोटी है, जहां n चौरसाई अंतराल का मान है।

n के बड़े मूल्यों के लिए, चिकनी श्रृंखला का उतार-चढ़ाव काफी कम हो जाता है। इसी समय, टिप्पणियों की संख्या काफ़ी कम हो जाती है, जिससे कठिनाइयाँ पैदा होती हैं।

चौरसाई अंतराल का चुनाव अध्ययन के उद्देश्यों पर निर्भर करता है। इस मामले में, किसी को उस समय अवधि द्वारा निर्देशित किया जाना चाहिए जिसमें कार्रवाई होती है, और, परिणामस्वरूप, यादृच्छिक कारकों के प्रभाव को समाप्त करना।

यह विधिअल्पकालिक पूर्वानुमान के लिए उपयोग किया जाता है। इसका कार्य सूत्र है:

पूर्वानुमान विकसित करने के लिए चलती औसत पद्धति का उपयोग करने का एक उदाहरण

एक कार्य . इस क्षेत्र में बेरोजगारी के स्तर को दर्शाने वाले आंकड़े हैं, %

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

मूविंग एवरेज सॉल्यूशन

चलती औसत पद्धति का उपयोग करके पूर्वानुमान मूल्य की गणना करने के लिए, आपको यह करना होगा:

1. चौरसाई अंतराल का मान निर्धारित करें, उदाहरण के लिए 3 (n = 3) के बराबर।

2. पहले तीन अवधियों के लिए चलती औसत की गणना करें
मी फरवरी \u003d (उयानव + उफेव + यू मार्च) / 3 \u003d (2.99 + 2.66 + 2.63) / 3 \u003d 2.76
परिणामी मान तालिका में ली गई अवधि के बीच में दर्ज किया गया है।
इसके बाद, हम अगले तीन अवधियों फरवरी, मार्च, अप्रैल के लिए m की गणना करते हैं।
मी मार्च \u003d (उफेव + उमार्ट + यूप्र) / 3 \u003d (2.66 + 2.63 + 2.56) / 3 \u003d 2.62
इसके अलावा, सादृश्य द्वारा, हम प्रत्येक तीन आसन्न अवधियों के लिए m की गणना करते हैं और एक तालिका में परिणाम दर्ज करते हैं।

3. सभी अवधियों के लिए चलती औसत की गणना करने के बाद, हम सूत्र का उपयोग करके नवंबर के लिए एक पूर्वानुमान बनाते हैं:

जहां टी + 1 पूर्वानुमान अवधि है; टी पूर्वानुमान अवधि (वर्ष, माह, आदि) से पहले की अवधि है; t+1 - अनुमानित संकेतक; एमटी-1 - पूर्वानुमान से पहले दो अवधियों के लिए चलती औसत; n चौरसाई अंतराल में शामिल स्तरों की संख्या है; यूटी - पिछली अवधि के लिए अध्ययन के तहत घटना का वास्तविक मूल्य; t-1 पूर्वानुमान अवधि से पहले दो अवधियों के लिए अध्ययन के तहत घटना का वास्तविक मूल्य है।

नवंबर = 1.57 + 1/3 (1.42 - 1.56) = 1.57 - 0.05 = 1.52
अक्टूबर के लिए चलती औसत एम निर्धारित करें।
मी = (1.56+1.42+1.52) /3 = 1.5
हम दिसंबर के लिए एक पूर्वानुमान बना रहे हैं।
दिसंबर = 1.5 + 1/3 (1.52 - 1.42) = 1.53
नवंबर के लिए चलती औसत एम निर्धारित करें।
मी = (1.42+1.52+1.53) /3 = 1.49
हम जनवरी के लिए एक पूर्वानुमान बना रहे हैं।
जनवरी = 1.49 + 1/3 (1.53 - 1.52) = 1.49
हम परिणाम को तालिका में रखते हैं।

हम औसत की गणना करते हैं रिश्तेदारों की गलतीसूत्र के अनुसार:

= 9.01/8 = 1.13% पूर्वानुमान सटीकताउच्च।

अगला, हम विधियों का उपयोग करके इस समस्या को हल करते हैं एक्स्पोनेंशियल स्मूदिंग तथा कम से कम वर्गों . आइए निष्कर्ष निकालें।