Luați în considerare utilizarea formulelor "SUMIFIT" și "ACCOUNTS"
utilizând exemplul de calculare a prognozei folosind modelul "Medie mobilă la 3 luni cu aditiv sezonier".
Calculăm:
- Mutarea medie la 3 luni;
- Diferența dintre valorile seriei și valorile medii până în a treia lună (paragraful 1);
- Distribuim media diferenței în serie și media pentru fiecare lună, obținem sezonalitate în mărire absolută - pentru aceasta folosim funcțiile de SUMMER și ACCOUNT;
- Vom extinde valorile seriei cu ajutorul mediei mobile în a treia lună și o vom ajusta cu sezonalitate adițională.
1. Mutarea medie la 3 luni pentru fiecare dată din seria de timp începând cu a patra perioadă:
2. Diferența dintre valorile seriei și valorile medii pentru a treia lună pentru fiecare moment t (elementul 1):
Descărcați fișier exemplu în Excel
3. În medie, diferența dintre serie și media (paragraful 2) timp de 12 luni - primim sezonalitate în mărire absolută - sezonalitate adițională.
Pentru a face acest lucru, selectați mai întâi numerele de luni utilizând funcția Excel = luna (data).
Obținem o serie cu lunile numerotate de la 1 la 12 pentru fiecare timp t:
Apoi, folosind funcția Excel = SUMMER, rezumăm pentru fiecare lună de la 1 la 12 abateri, pe care le-am considerat la punctul 2.
Pentru a face acest lucru, trecem următoarele valori în formula REZUMAT:
- intervalul - $ G $ 8: $ AY $ 8 - variază de la numărul de luni pentru întreaga perioadă;
- criteriu - D9 - numărul lunii pentru care rezumăm abaterile și calculam sezonul;
- intervalul de sumare este $ G $ 7: $ AY $ 7 - intervalul cu abaterile calculate în clauza 2).
Fix o serie de link-uri la numărul de luni și intervalul cu handicap (cum să stabilească link-ul descris în articolul „Cum de a stabili o legătură în Excel?“), Și se extinde cu formula pentru 12 luni de la 1 la 12.
Obținem cantitatea de abateri pentru fiecare lună.
Apoi, folosind formula Excel = COUNTRIES (), luați în considerare numărul de luni din seria de timp la care împărțim suma abaterilor și obțineți deviația absolută medie pentru fiecare lună - sezonalitate adițională.
Și astfel vom trece în formula = COUNTRIES (
- intervalul - $ G $ 8: $ AY $ 8 - fixați linkul - numerele lunilor din seria de timp;
- criteriu - D9 - numărul de luni pentru sezonalitate, numărul cărora trebuie să fie calculat în serii cronologice pentru a calcula deviația medie.
Descărcați fișier exemplu în Excel
Extindem formula Excel = COUNTRIES de la 1 la 12 si obtinem o serie cu numarul de luni pentru fiecare luna de sezonalitate:
Apoi, cantitatea de abateri împărțită la numărul de luni în calculul sezonalității, obținem o sezonalitate absolută (aditivă) pentru fiecare lună a anului:
Am făcut acest calcul în 3 pași
- Abaterile au fost rezumate pentru fiecare lună de sezonalitate utilizând SUMMER,
- Am calculat numărul de perioade pentru fiecare lună de sezonalitate folosind COUNTRY,
- Și a divizat alineatul 1 în paragraful 2, adică suma abaterilor prin numărul de perioade, adică a găsit deviația medie pentru fiecare lună.
Dar acești 3 pași pot fi combinați într-una și imediat primiți 12 valori ale sezonalității adiționale.
Pentru aceasta, împărțim SUMMER pe COUNTRY, ca în imagine:
Formula se obține după cum urmează:
Intrăm în formulă, intrăm timp de 12 luni, obținem sezonalitate:
Încă o dată analiza = SUMIF ($ G $ 8: $ AY $ 8; D9; $ G $ 7: $ AY $ 7) / COUNTIF ($ G $ 8: $ AY $ 8; D9)
SUMIF ($ G $ 8: $ AY $ 8 alineatul (intervalul fix luni numerotate), D9 (luna pentru care ne așteptăm la caracterul sezonier); $ G $ 7: $ AY 7 $ (interval fix de valori ale diferenței dintre mediul apropiat și - alineatul 2) )
ȚĂRI ($ G $ 8: $ AY $ 8 (interval fix cu numere de luni), D9 (numărul lunii pentru care se calculează sezonalitatea))
Acum, să calculăm prognoza.
Pentru a face acest lucru, extindem valorile seriei cu ajutorul mediei mobile în a treia lună și ajustăm-o cu sezonalitate adițională.
Vom extinde media mobilă în a treia lună în viitor timp de 12 luni:
Ajustați sezonalitatea.
Pentru aceasta, adăugați sezonalitatea aditivului la media mobilă. Sezonalitatea pentru fiecare lună va fi strânsă cu ajutorul funcției Excel Excel.
Prognoza = vânzările medii pentru ultimele 3 luni + sezonalitate:
Descărcați fișier exemplu în Excel
Pentru a evita în mod deliberat această greșeală și pentru a face mașina să gândească, utilizați formulele Excel = LUMII și COUNT. O combinație a acestor formule vă va ajuta să calculați valoarea medie pentru un anumit criteriu.
Și dacă doriți să evitați greșelile în calculul previziunilor de 100% și chiar să alegeți automat modelul potrivit pentru fiecare serie de timp, lucrați cu Forecast4AC PRO.
Forecast4AC PRO vă va economisi mult timp vă va permite să faceți predicții mai exacte.
Previziuni exacte pentru tine!
Soluții pentru analiza și prognozarea afacerilor
Dacă vă alăturați, primiți:
Trimiteți-le prietenilor: