Mozgóátlag és exponenciális simítás MS Excelben. Az értékpapírpiaci részvényárfolyamok előrejelzése Excelben

A mozgóátlag számítása mindenekelőtt egy olyan módszer, amely lehetővé teszi a mérések időintervallumonkénti simításán alapuló idősorok alakulásának trendjeinek meghatározását és elemzését. Ezek az ingadozások véletlenszerű hibák miatt következhetnek be, amelyek gyakran az egyéni számítási és mérési technikák mellékhatásai vagy eltérő időzítési viszonyok eredménye.

A Mozgóátlag eszköz az Eszközök menü Adatelemzés parancs párbeszédpaneljéből érhető el.

A mozgóátlag eszközzel előrejelzést készítek a gazdasági mutatókról az 1.1. táblázatban (3.1. táblázat).

asztal3 .1 — A vizsgált dinamikus sorozatok mutatóinak viselkedési trendjének értékelése mozgóátlag módszerrel

Megjegyzés – Forrás: .

A táblázat adatai alapján mozgóátlag diagramot készítek.

3.1. ábra – Mozgóátlag

Megjegyzés – Forrás: .

A grafikonon a láncnövekedési ütemek általános dinamikája és a mozgóátlag jelenik meg, amelyből látható, hogy a mozgóátlag mutató hajlamos növekedni, majd csökkenni, majd ismét növekedni, i.e. A kereskedelmi forgalom volumene havonta folyamatosan változik.

A mozgóátlag kiszámítása gyors és egyszerű módja a gazdasági teljesítmény rövid távú előrejelzésének. Egyes esetekben még hatékonyabbnak tűnik, mint más, hosszú távú megfigyeléseken alapuló módszerek, mivel szükség esetén lehetővé teszi a vizsgált mutató dinamikus sorozatának olyan számú tagjának csökkentését, amely csak a legújabb trendet tükrözi. fejlődésében. Így az előrejelzés nem torzul a korábban előforduló kiugró értékek, torzulások stb. miatt, és sokkal pontosabban tükrözi az előre jelzett mutató lehetséges értékét a közeljövőben.

1. Lineáris előrejelzések készítése Excel segítségével

Az exogén változók funkcionális függőségének típusa szerint a trendmodellek lehetnek lineárisak és nemlineárisak. A gazdasági folyamatok összetettsége és a gazdasági rendszerek nyitottsága a legtöbb esetben meghatározza a gazdasági mutatók alakulásának nemlineáris jellegét. A lineáris modellek megalkotása azonban műszaki és matematikai szempontból is sokkal kevésbé munkaigényes eljárás. Ezért a gyakorlatban gyakran megengedett a nemlineáris folyamatok részleges transzformációja (feltéve, hogy az adatok előzetes grafikus elemzése ezt lehetővé teszi), és a vizsgált mutató viselkedésének modellezése annak lineáris egyenletének összeállításához és becsléséhez vezet. dinamika.

1. A lineáris függvény használata trendmodell létrehozásához

A LINEST munkalap funkció segít meghatározni a megfigyelések eredményei és a rögzítés időpontja közötti lineáris összefüggés természetét, és az eredeti adatokat legjobban közelítő matematikai leírást adni. A modell felépítéséhez egy y=mx+b alakú egyenletet használ, ahol y a vizsgált mutató, x=t az időbeli trend, b, m az egyenlet y metszéspontját jellemző paraméterei és a trendvonal meredeksége, ill. A LINEST modell paramétereit a legkisebb négyzetek módszerével számítjuk ki.

A LINEST függvény meghívható a "Funkcióvarázsló" párbeszédpanelen ("Statisztikai" kategória), amely a "Standard" eszköztáron található.

3.2 táblázat - Lineáris trendmodell számítása és értékelése a LINEST függvény használatával

Válasszon a menüből Szolgáltatás bekezdés Adatelemzés, megjelenik egy azonos nevű ablak, melynek fő eleme a terület Elemző eszközök. Ez a terület a Microsoft Excelben megvalósított statisztikai adatfeldolgozási módszerek listáját tartalmazza. A felsorolt ​​módszerek mindegyike külön működési módként valósul meg, amelynek aktiválásához az egérmutatóval ki kell választani a megfelelő módszert, és az OK gombra kell kattintani. Miután megjelenik a meghívott mód párbeszédpanele, elkezdheti a munkát.

Üzemmód " Mozgóátlag» az egyszerű mozgóátlag módszeren alapuló empirikus idősorok szintjeinek simítására szolgál.

Üzemmód " Exponenciális simítás» az empirikus idősorok szintjeinek simítását szolgálja az egyszerű exponenciális simítás módszerével.

Ezen módok párbeszédpaneleiben (2. és 3. ábra) a következő paraméterek vannak beállítva:

2. Jelölőnégyzet Címkék– az aktív állapot akkor van beállítva, ha a beviteli tartomány első sora (oszlopa) fejléceket tartalmaz. Ha nincsenek fejlécek, a jelölőnégyzetet ki kell kapcsolni. Ebben az esetben a kimeneti tartomány adatainak szabványos nevei automatikusan létrejönnek.

3. Intervallum(csak a Mozgóátlag párbeszédpanelen) – adja meg a simítóablak méretét R. Alapértelmezett p=3.

2. ábra – Mozgóátlag párbeszédpanel

4. Csillapítási tényező(csak az Exponenciális simítás párbeszédpanelen) – írja be az exponenciális simítási együttható értékét p. Alapértelmezett, p=0,3.

5. Kimeneti intervallum / Új munkalap / Új munkafüzet– az Output interval (Kimeneti intervallum) pozícióban egy mező aktiválódik, amelyben meg kell adnia a kimeneti tartomány bal felső cellájára mutató hivatkozást. A kimeneti tartomány mérete automatikusan meghatározásra kerül, és egy üzenet jelenik meg a képernyőn, ha a kimeneti tartomány átfedésben lehet a forrásadatokkal. Az Új munkalap pozícióban megnyílik egy új munkalap, amelyben a cellától kezdve A1 az elemzési eredmények beillesztésre kerülnek. Ha meg kell adni egy nevet a megfelelő kapcsolóállással szemben található mezőben. Az Új munkafüzet pozícióban egy új munkafüzet nyílik meg, melynek első lapján a cellától kezdve A1 az elemzési eredmények beillesztésre kerülnek.

6. Grafikon kimenet– aktív állapotba van állítva a munkalapon lévő dinamikus sorozatok tényleges és elméleti szintjei grafikonjainak automatikus generálásához.

7. Standard hibák– állítsa aktívra, ha szabványos hibákat tartalmazó oszlopot szeretne a kimeneti tartományba felvenni.

3. ábra – Exponenciális simítás párbeszédpanel

1. példa

A város fogyasztói szövetkezeti üzletei mezőgazdasági termékértékesítésének (millió rubel) adatait egy Microsoft Excel munkalapon generált táblázat mutatja (4. ábra). A meghatározott időszakban (2009-2012) meg kell határozni e gazdasági folyamat fejlődésének fő trendjét.

4. ábra – Kiindulási adatok

A probléma megoldásához a „ üzemmódot használjuk Mozgóátlag" Az azonos nevű párbeszédpanelen beállított paraméterek értékei az 5. ábrán, az ebben a módban számított mutatók a 6. ábrán, a megszerkesztett diagramok a 7. ábrán láthatók.

5. ábra – A párbeszédpanel kitöltése

6. ábra – Elemzési eredmények

7. ábra – Mozgóátlag

A D oszlop (5. ábra) kiszámítja a simított szintek értékeit. Például az első simított szint értéke a D5 cellában az =ÁTLAG(C2:C5) képlettel, a második simított szint értéke a D6 cellában az =ÁTLAG(C5:C8) képlettel számítható ki stb. .

Az E oszlop a standard hibákat a =ROOT(SUMAVARNA (tényleges érték blokk; előrejelzett érték blokk) / simítóablak mérete) képlet alapján számítja ki.

Például az E10 cellában lévő érték kiszámítása a =GYÖKÉR(SZUMQVAR(C7:C10,O7:B10)/4 képlettel történik.

Azonban, mint fentebb megjegyeztük, ha a simítóablak mérete páros szám ( р=2m), akkor a számított átlagérték nem hasonlítható össze egyetlen t időpontban sem, ezért szükséges a központosítási eljárás alkalmazása.

Ehhez a példához p=4, ezért szükséges a központosítási eljárás. Így az első simított szintet (265,25) a II-III. negyedév között rögzítjük. 2009 stb. A központosítási eljárást alkalmazva (ehhez az AVERAGE függvényt használjuk) simított szinteket kapunk központosítással. III kV-ra. 2009, az első és a második simított szint közötti felezőpont meghatározása: (265,25 + 283,25)/2 = 274,25; IV negyedévre 2009, a második és a harmadik simított szint középre van állítva: (283,25 + 292,00)/2 = 287,6 stb. A számított értékeket az 1. táblázat mutatja be. A korrigált mozgóátlag diagram a 8. ábrán látható.

1. táblázat – A termékértékesítés simított szintjeinek dinamikája

Év	Negyed	Eladási összeg, millió rubel.	Sima szintek központosítással
		274,25
		287,63
			297,00
		307,50
		334,63
		374,13
			402,88
		421,00
		429,00
		430,75
			435,38
		446,63

8. ábra – Korrigált mozgóátlag diagram

2. példa

A vizsgált probléma az egyszerű exponenciális simítás módszerével is megoldható. Ehhez az „Exponenciális simítás” üzemmódot kell használni. Az azonos nevű párbeszédpanelen beállított paraméterek értékeit a 9. ábra, az ebben a módban számított mutatókat a 10. ábra, a megszerkesztett grafikonokat a 11. ábra mutatja.

9. ábra – Az Exponenciális simítás párbeszédpanel kitöltése

10. ábra – Elemzési eredmények

11. ábra – Exponenciális simítás

A D oszlop (10. ábra) a simított szintek értékeit számítja ki az ismétlődő kapcsolatok alapján.

Az E oszlopban a standard hibák kiszámítása a =ROOT(SUMVARE (tényleges érték blokk; előrejelzett érték blokk) / 3) képlettel történik. Amint az jól látható (hasonlítsa össze a 8. és 11. ábrát), az egyszerű exponenciális simítási módszer alkalmazásakor, az egyszerű mozgóátlagos módszerrel ellentétben, a kis hullámok megmaradnak.

A munka célja : Sajátítson el jártasságot a frekvenciaelemzési problémák megoldásában az elemzési munkalap funkció segítségével MS Excel.

Rövid elmélet

A gazdasági mutatók elemzésekor gyakran felmerül a kérdés, hogy adott értékintervallumokban milyen gyakran fordulnak elő mutatók.

Elemző munkalap FREQUENCY funkció MS Excel statisztikai függvényként van besorolva, és a gyakorisági eloszlást függőleges tömbként adja vissza. Egy adott értékkészlethez és egy adott zsebhalmazhoz (intervallumhoz) a gyakorisági eloszlás számolja, hogy hány érték esik az egyes intervallumokba.

Az adattömb lehet egydimenziós vagy kétdimenziós tömb (pl. A 4: D 15).

Szintaxis: FREQUENCY (data_array; pocket_array)

Frekvenciaelemzéshez használhatja a parancsotSzolgáltatás/Adatelemzés.Az adatelemzés az egyik kiegészítő Excel . Ha ez a parancs nincs a menüben, akkor futtassa a parancsotSzolgáltatás/Kiegészítőkés jelölje be a megfelelő négyzetet az ablakban Kiegészítők.

1. Feladat

A funkció használata Frekvencia a rendelési összegek halmazának kiválasztásához () adja meg a tartományt, számolja meg, hogy hány érték esik az adott értékintervallumba. Például 0-tól 1000-ig, 1001-től 1500-ig, 1501-től 2000-ig, 2001-től 2500-ig, 2500 felett.

Eljárás:

1. A munkalapon MS Excel táblázat formájában adja meg a cég 20 fiókjában leadott rendelésállomány adatait szeptemberre, melynek töredéke az ábrán látható.

	Fiókszám	szeptember
		1230		1000
				1500
		….		2000
				2500

1. A cellák szabad tartományában (oszlopban) adja meg az intervallumok felső határát (például D 2=1000, D 3=1500, D 4=2000, D 5=2500).
2. Jelöljön ki egy cellablokkot az intervallum oszlop melletti oszlopban ( E2:E 21). Az intervallum alsó határát meghaladó értékek számának megszámlálásához egy olyan tartomány kerül kiválasztásra, amely egy cellával nagyobb, mint az intervallumok tartománya.
3. E 2 tartományhoz: E 6 írja be a képletet ( =FREQUENCY( E 2: E 15; J 2: J 6)).

Ezért használja a függvényvarázslót (Beszúrás/Funkció). A Statisztikai kategóriában válassza ki a Gyakoriság függvényt a listából. A FREQUENCY függvény párbeszédpanelen töltse ki a mintatömb és az intervallumtömb mezőit (1. ábra).A párbeszédablak elhagyása nélkülnyomja meg a billentyűkombinációt< Ctrl / Shift / Enter > tömbelemek kiszámításához.

1. ábra Példa a függvény párbeszédpanel kitöltésére Frekvencia.

1. Az eredmények alapján készítsen diagramot!
2. Mentse el a fájlt.

2. feladat

Hozzon létre egy kétdimenziós tömböt egy munkalapon, amely statisztikai adatokat tartalmaz a különböző korosztályú emberek magasságáról. Végezze el az eredmények gyakorisági elemzését a FREQUENCY és Data Analysis funkcióval (menüpontAdatelemzés/Hisztogram).

Amikor az adatelemző eszközt használja a mezőben lévő párbeszédpanelenBeviteli intervallumadja meg a kezdeti intervallumot, amelyre a hisztogram a mezőben épülZsebköz- tartomány az intervallumok felső határának értékeivel. A hisztogram egy új vagy aktuális munkalapra épül.

2. rész

Előrejelzési feladatok megoldása MS Excelben. Mozgóátlag módszer

A munka célja : Sajátítson el ismereteket egy vállalkozás gazdasági tevékenységének előrejelzésében az MS Excel statisztikai szoftvercsomag segítségével.

Rövid elmélet

Az előrejelzés egy rendszer jövőbeni lehetséges állapotainak tudományosan megalapozott leírása. A rövid és hosszú távú tervek kidolgozásakor minden szervezet kénytelen előre jelezni a gazdasági tevékenység legfontosabb mutatóinak értékét, mint például a termelési mennyiség, az értékesítés, a termelési költségek stb. Jelenleg az előrejelzési problémák megoldására modern információs technológiákat alkalmaznak, amelyek szoftverei statisztikai szoftvercsomagokat tartalmaznak.

Előrejelzési problémák megoldása a környezetben Az MS Excel a használt elemző csomag , beleértve az elemző eszközöket is. Az adatelemzéshez szükséges eszköz kiválasztásával és a szükséges paraméterek beállításával gyorsan megoldható bonyolult statisztikai problémák, azokat grafikus értelmezéssel kísérve.

A gazdasági mutatók idősorainak előzetes elemzése magában foglalja a sorozatszintek abnormális értékeinek azonosítását, amelyek megsértik a trend jelenlétének meghatározását. Az indikátorok abnormális értékeinek kiküszöbölésére idősoros simítási eljárást alkalmaznak. Ebben az esetben a sorozat trendjének azonosításához a tényleges értékeket számított értékekkel helyettesítjük.

Az előrejelzési módszer kiválasztásakor figyelembe veszik az idősorok valószínűségi változójának változásának jellegét. Ha az átlagértékek változása jelentéktelen, és az idősor összes megfigyelése egyenlő jelentőségű az előrejelzés szempontjából, akkor a mozgóátlag módszert alkalmazzuk. A mozgóátlagok lehetővé teszik az idősorok véletlenszerű és periodikus ingadozásainak kisimítását (szűrését). Az egyszerű mozgóátlagos simítás a leggyakoribb simítási eljárás.

Az elemző eszközben MS Excel Mozgóátlaga becsült érték kiszámításában részt vevő értékek számát a paraméter határozza meg Intervallum . Minél nagyobb a simítási intervallum, annál inkább ki kell simítani a sorozat értékeinek kis ingadozásait. MódszerEgyszerű mozgóátlaglineáris fejlődési trendű idősorokban ad jó eredményeket.

Ha a legfrissebb megfigyelési eredmények a legjelentősebbek az előrejelzés szempontjából, akkor az exponenciális simítási módszert alkalmazzuk. Az exponenciális simítási módszerben minden érték változó súllyal vesz részt az előrejelzett értékek kialakításában, amelyek az adatok elavulásával csökkennek. Az elemző eszközben MS Excel « Exponenciális simítás"a súlyozási együtthatót vagy simítási paramétert a paraméter határozza megCsillapítási tényező. A gazdasági problémák idősorainál jellemzően a simítási paraméter értéke 0,1 és 0,3 közötti tartományban van beállítva. Kezdeti számított érték az eljárásbanExponenciális simítás MS Excel elemző csomag egyenlőnek vesszük a sorozat első tagjának szintjét. A módszer jó egyezést biztosít a kezdeti és a számított adatok között a sorozat első értékeire vonatkozóan. Ha a végső számított értékek jelentősen eltérnek a megfelelő kezdeti értékektől, akkor célszerű a simítási paraméter értékét megváltoztatni. Az eltérések nagysága standard hibák és grafikonok alapján értékelhető, amelyeket az Analysis csomag lehetővé tesz a sorozat számított értékeivel együtt megjeleníteni.

Tekintsük a kommunikációs szolgáltatásokat nyújtó vállalkozás teljesítménymutatóinak előrejelzési lehetőségeit.

Laboratóriumi feladat (2. rész)

1. Feladat : A vállalkozás termékei (szolgáltatásai) mennyiségének előrejelzett értékének kiszámítása mozgóátlag módszerrel.

A feladat végrehajtásának menete:

Hozzon létre egy oszlopot a munkalapon, amely tartalmazza a vállalat által az elmúlt 10 évben nyújtott szolgáltatások millió rubelben kifejezett mennyiségét. Határozzuk meg a mutató változásának trendjét mozgóátlag segítségével. Három éves mozgóátlagos periódust választunk, mivel rövidebb távon a mozgóátlag nem feltétlenül tükrözi a trendet, hosszabb távon viszont kisimítja azt.

A számításokhoz a képlet közvetlen bevezetésének módszerét fogjuk használni. Ahhoz, hogy három éves mozgóátlagot kapjunk a példánkban teljesített szolgáltatások mennyiségére vonatkozóan, beírjuk a cellába B 5 képlet az =AVERAGE( A2:A 4). Másoljuk be a képletet az intervallumba B 6: B 11.

1. ábra Egyszerű mozgóátlag számítása

Az eredményeket szemléltessük a kiindulási adatok és a mozgóátlag változásának dinamikáját tükröző grafikonnal.

2. ábra Az egyszerű mozgóátlag módszerrel kapott szolgáltatási volumen változási trendjének grafikonja

Egy másik megoldás, ha mozgó egész számot használunk a definiáláshoz Elemző csomag . Az elemzőcsomag egy kiegészítő MS Excel (válassza ki a menüpontotSzolgáltatás/Kiegészítőkés jelölje be a négyzetet Elemző csomag).

Eljárás

1. Futtassa a parancsotSzolgáltatás/Adatelemzésés válasszon az elemző eszközök listájábólMozgóátlag.
2. A párbeszédpanelen adja meg a mozgóátlag kiszámításának paramétereit:

• Beviteli intervallumként válasszon ki egy cellablokkot, amely a szolgáltatások mennyiségére vonatkozó adatokat tartalmaz.
• Intervallum megadása - 3 (az alapértelmezett 3), mint a kimeneti intervallum bármely munkalap cellája (csak kattintson arra a munkalapcellára, amelyből az eredményeket ki kell írni);

Excel elvégzi az értékek bevitelét a mozgóátlag kiszámításának képletébe. Mivel az első megfigyelések átlagának kiszámításakor nem áll rendelkezésre elegendő adat, a kimeneti tartomány kezdeti celláiban a #N/A hibaérték jelenik meg. Kérjük, vegye figyelembe, hogy a sorozatban kapott első érték nem a harmadik, hanem a negyedik időszak előrejelzési értéke. Ezért, ha a kimenethez megadott cella megfelel a megfigyelési oszlop elejének, akkor a számított értékek oszlopát egy cellával lejjebb kell mozgatnia. Ez a művelet pontosan azokhoz az időszakokhoz csatolja az előrejelzéseket, amelyekre kiszámításra kerültek.

Elemezze az alkalmazott számítási képleteket és a kapott eredményeket!

Hasonló módon számítsa ki az ötéves egyszerű mozgóátlagokat. Hasonlítsa össze a két számítási lehetőség simítási eredményeit.

2. feladat: Számítsa ki a vállalkozás termékei (szolgáltatásai) mennyiségének várható értékét exponenciális simítási módszerrel!

Eljárás:

1. MS Excel lapon hozzon létre egy listát, amely tartalmazza a cég alkalmazottainak számát az elmúlt 10 évben. Adja meg az adatokat véletlenszerűen, de úgy, hogy a trend nyomon követhető legyen.
2. Simítsa az idősort exponenciális átlaggal 0,1, majd 0,3 simítási paraméterekkel. A számítási eredmények alapján készítsen grafikont, és határozza meg, hogy a kapott idősorok közül melyik a simább.

Használja a parancsotSzolgáltatás/Adatelemzésés válasszon az elemző eszközök listájábólExponenciális simítás.Adja meg a mozgóátlag kiszámításához szükséges paramétereket:

• Beviteli intervallumként válasszon ki egy populációs adatokat tartalmazó cellablokkot.
• Adja meg Csillapítási tényező. Kimeneti intervallumként a munkalap bármely cellája.
• Állítsa be a grafikon kimenetét és a standard hibákat.

1. Adjon hozzá trendvonalakat a kapott grafikonokhoz. Ehhez válassza ki a grafikon vonalát (csak kattintson a jobb gombbal a grafikon vonalára), és válassza ki az elemet a helyi menübenAdjon hozzá egy trendvonalat. A párbeszédpanelen válassza ki az adatainak leginkább megfelelő trendtípust (például lineáris szűrés), és jelölje be a jelölőnégyzetet a görbeillesztési egyenlet megjelenítéséhez a grafikonon.
2. Ellenőrizze és mentse el az eredményeket.

3. rész

Előrejelzési problémák megoldása munkalap és kitöltésjelölő függvények használatával

Rövid elmélet

A gazdasági előrejelzésekben különféle növekedési modelleket használnak. A növekedési görbe egy bizonyos függvény, amely egy adott idősort közelít. A növekedési görbék felhasználásával történő előrejelzés kialakítása során olyan görbéket választunk ki, amelyek alakja megfelel az idősor dinamikájának, értékeljük a paramétereiket, ellenőrizzük a kiválasztott görbék megfelelőségét az előre jelzett folyamatnak, és pont- vagy intervallum-előrejelzést számolunk.

Számos módszer létezik a görbék illesztésére. Az egyik legegyszerűbb a vizuális módszer. Ha a fejlődési trend (trend) nem látható kellően a grafikonon, akkor a sorozat a fent leírtak szerint simításra kerül, majd az új sorozatnak megfelelő görbét választjuk ki. Ebben az esetben a számítógépes rendszerek modern szoftvereit is használják. BAN BEN MS Excel speciális funkciók vannak beépítve, amelyek lehetővé teszik az előrejelzett értékek kiszámítását egy bizonyos időszakra.

Az Excel lineáris extrapolációt végez, azaz. kiszámítja a legjobban illeszkedő egyenest, amely adott pontok sorozatán halad át. A feladat egy ponthalmaz felrajzolása grafikonon, majd egy olyan egyenes kiválasztása, amely mentén a legkisebb hibával nyomon követhető a függvény fejlődése. Ezt a vonalat TREND vonalnak nevezik. A felhasználó a számítási eredményt trendelemzéshez és rövid távú előrejelzéshez használhatja fel.

Az Excel automatikusan képes különféle típusú trendvonalakat rajzolni közvetlenül a diagramra. A számításokat kétféleképpen lehet elvégezni:

• Kitöltő fogantyú használata
• Munkalapfüggvények használata

Első út

Lineáris közelítés

• A bal egérgombbal húzza a kitöltő fogantyút úgy, hogy azok a cellák is ki legyenek jelölve, amelyekhez ki szeretné számítani a várható értékeket. Az így számított értékek megfelelnek a lineáris előrejelzésnek.

Exponenciális közelítés

• Válassza ki a megfigyelési eredményeket tartalmazó cellákat.
• Húzza el a kitöltő fogantyút a jobb egérgombbal, hogy azok a cellák is ki legyenek jelölve, amelyekhez a becsült értékeket ki szeretné számítani.
• A megjelenő helyi menüben válassza ki az „Exponenciális illeszkedés” parancsot.

Második út

MS Excelben A munkalap statisztikai függvényei be vannak építve.

IRÁNYZAT() - a lineáris legkisebb négyzetek közelítésének megfelelően értékeket ad vissza.

MAGASSÁG() - exponenciális trendnek megfelelő értékeket ad vissza.

Ezen függvények használata egy másik módja a regressziós elemzés kiszámításának.

Formátum

TREND (rev_érték_Y; keres_érték_X; új_érték_X; állandó)

A GROWTH függvény exponenciális trend szerint ad vissza értékeket.

Laboratóriumi feladat (3. rész)

1. Feladat:

Számítsa ki a lineáris és exponenciális előrejelzést egy évre és a következő három időszakra (2011-ig) a kitöltési marker segítségével.

2. feladat:

Számítsa ki a lineáris és exponenciális előrejelzéseket egy évre, majd a következő három időszakra a TREND és GROWTH munkalapfüggvények segítségével. Az intervallum-előrejelzés kiszámításához a függvény párbeszédpanel paramétereinek kitöltése után, anélkül, hogy elhagyná azt, nyomja meg a Ctrl/Shift/Enter billentyűkombinációt.

A tömbelemek kiszámítására szolgáló képletnek meg kell jelennie a munkalap képletsávjában, például

(= TREND (B 3: G 3; B 2: G 2; B 2: H 2))

Határozza meg, melyik modell a legpontosabb.

Készítsen grafikonokat és trendvonalakat az első és a második feladathoz.

Az üzleti életben, mint minden más tevékenységben, az ember tudni akarja, mi fog ezután történni. Még elképzelni is nehéz annak a szerencsés embernek a gazdagságát, aki 100%-os pontossággal meg tudja tippelni a jövőt. De sajnos (vagy szerencsére) az előrelátás ajándéka rendkívül ritka. DE... egy vállalkozónak egyszerűen meg kell próbálnia legalább általánosságban elképzelni a jövőbeli üzleti helyzetet.

Először egy bejegyzésben akartam írni több egyszerű és kényelmes technikáról egyszerre, de a bejegyzés nagyon hosszúra sikeredett. Ezért több bejegyzés is lesz az előrejelzés témájának szentelve. Ebben a bejegyzésben az egyik legegyszerűbb előrejelzési módszert ismertetjük, amely az Excel képességeit használja, a mozgóátlag módszert.

A marketingkutatás gyakorlatában leggyakrabban a következő értékeket jósolják:

• Eladási mennyiségek
• Piac mérete és kapacitása
• Termelési mennyiségek
• Import mennyiségek
• Az árak dinamikája
• Stb.

Az ebben a bejegyzésben vizsgált előrejelzéshez azt tanácsolom, hogy tartsa be a következő egyszerű algoritmust:

1. Másodlagos információk gyűjtése a problémáról(lehetőleg mennyiségi és minőségi egyaránt). Így például, ha megjósolja a piac méretét, statisztikai információkat kell gyűjtenie a piacról (termelési mennyiségek, import, árdinamika, értékesítési mennyiségek stb.), valamint a trendekről, problémákról vagy piaci lehetőségekről. Ha eladásokat prognosztizál, akkor az időszakra vonatkozó értékesítési adatokra van szüksége. Az előrejelzéshez minél több előzményadatot vesz figyelembe, annál jobb. Az előrejelzést célszerű kiegészíteni az előrejelzett jelenséget befolyásoló tényezők elemzésével (használhat SWOT-, PEST-analízist vagy bármilyen mást). Ez lehetővé teszi a fejlődés logikájának megértését, és így ellenőrizheti egy adott trendmodell elfogadhatóságát.

2. Továbbá kívánatos ellenőrizze a mennyiségi adatokat. Ehhez össze kell hasonlítania ugyanazon mutatók értékeit, de különböző forrásokból. Ha minden stimmel, akkor „behajthatja” az adatokat Excelbe. Az adatoknak az alábbi követelményeknek is meg kell felelniük:

• Az alapvonal a megfigyelések eredményeit tartalmazza – a legkorábbitól a legújabbig.
• Minden kiindulási időszak azonos időtartamú. Az egy nap adatait például nem szabad összekeverni háromnapos átlagokkal.
• A megfigyeléseket minden időperiódusban ugyanazon a ponton rögzítik. Például a forgalmat egyszerre kell mérni.
• Az adatok átugrása nem megengedett. Egyetlen megfigyelési eredmény kihagyása sem kívánatos az előrejelzésnél”, ezért, ha a megfigyelésekből rövid ideig hiányoznak az eredmények, próbálja meg azokat legalább megközelítő adatokkal kitölteni.

3. Az adatok ellenőrzése után megteheti különböző előrejelzési technikák alkalmazása. A legegyszerűbb módszerrel szeretném kezdeni - MOZGÓÁTLAG MÓDSZER

MOZGÓÁTLAG MÓDSZER

A mozgóátlag módszer használata meglehetősen egyszerű, de túl egyszerű a pontos előrejelzés elkészítéséhez. Ezzel a módszerrel egy tetszőleges időszakra vonatkozó előrejelzés nem más, mint az idősor több korábbi megfigyelésének átlaga. Például, ha három hónapos mozgóátlagot választott, a májusi előrejelzés február, március és április átlaga lesz. Ha előrejelzési módszerként négyhavi mozgóátlagot választ, a májusi adatot a januári, februári, márciusi és áprilisi adatok átlagaként értékelheti.

A mozgóátlag előrejelzést általában a megfigyelési időszakot közvetlenül követő időszakra vonatkozó előrejelzésnek tekintik. Ugyanakkor egy ilyen előrejelzés akkor alkalmazható, ha a vizsgált jelenség szekvenciálisan fejlődik, pl. Vannak bizonyos trendek, és az értékgörbe nem ugrik őrülten a diagram körül.

Annak meghatározásához, hogy hány megfigyelést kíván belefoglalni a mozgóátlagba, korábbi tapasztalatokra és az adatkészlettel kapcsolatos információkra kell támaszkodnia. Meg kell találni az egyensúlyt a mozgóátlagnak a legutóbbi néhány megfigyelésre adott megnövekedett válasza és az átlag nagy változékonysága között.

Tehát hogyan kell csinálniExcel

1. Tegyük fel, hogy rendelkezik az elmúlt 29 hónap havi értékesítési volumenével. És szeretné meghatározni, hogy mekkora lesz az értékesítési volumen a 30. hónapban. De, hogy őszinte legyek, egyáltalán nem szükséges 30 történelmi értékkel operálni az előrejelzési értékek kiszámításakor, mert ez a módszer csak az utolsó néhány hónapot fogja használni az átlag kiszámításához. Ezért csak az elmúlt néhány hónap elegendő a számításhoz.

2. Ezt a táblázatot közérthető Excel formába hozzuk, azaz. hogy minden érték ugyanabban a sorban legyen.

3. Ezután bemutatjuk az átlag kiszámításának képletét az előző három (négy, öt? tetszés szerint) érték alapján (lásd ben). A legkényelmesebb az utolsó 3 értéket használni a számításokhoz, mert ha többet veszel figyelembe, az adatok túlságosan átlagosak lesznek, ha kevesebbet veszel figyelembe, akkor nem lesz pontos.

4. Az automatikus kiegészítés funkció használata minden további értéknél 30-ig, az előrejelzési hónapig. Így a függvény kiszámítja a 2010. júniusi előrejelzést. Az előrejelzési értékek szerint a júniusi értékesítés körülbelül 408 egységnyi áru lesz. De vegye figyelembe, hogy ha a csökkenő tendencia állandó, mint a példánkban, akkor az átlagon alapuló előrejelzés számítása kissé túlbecsült lesz, vagy úgy tűnik, hogy „elmarad” a tényleges értékektől.

Megnéztük az egyik legegyszerűbb előrejelzési technikát - a mozgóátlag módszert. A következő bejegyzésekben más, pontosabb és összetettebb technikákkal fogunk foglalkozni. Remélem, hogy a bejegyzésem hasznos lesz számodra.