Wygładzanie średniej kroczącej i wykładniczej w MS Excel. Prognozowanie cen akcji na rynku papierów wartościowych w programie Excel

Obliczanie średniej ruchomej jest przede wszystkim metodą pozwalającą na uproszczenie wyznaczania i analizy trendów rozwoju szeregu czasowego w oparciu o wygładzanie wahań pomiarów w przedziałach czasowych. Wahania te mogą wystąpić na skutek błędów przypadkowych, które często są efektem ubocznym indywidualnych technik obliczeniowych i pomiarowych lub efektem odmiennych warunków czasowych.

Dostęp do narzędzia Średnia ruchoma można uzyskać z okna dialogowego polecenia Analiza danych z menu Narzędzia.

Korzystając z narzędzia średniej ruchomej, dokonuję prognozy wskaźników ekonomicznych w tabeli 1.1 (tabela 3.1).

Tabela3 .1 — Ocena trendu zachowania wskaźników badanych szeregów dynamicznych metodą średniej ruchomej

Uwaga – Źródło: .

Na podstawie danych z tabeli buduję wykres średniej ruchomej.

Rysunek 3.1 – Średnia krocząca

Uwaga – Źródło: .

Ogólną dynamikę tempa wzrostu łańcucha oraz średnią ruchomą przedstawiono na wykresie, z którego widać, że wskaźnik średniej ruchomej ma tendencję do wzrostu, następnie spadku, a następnie ponownego wzrostu, tj. Wielkość obrotów handlowych ulega ciągłym zmianom w każdym miesiącu.

Obliczanie średniej ruchomej to szybki i łatwy sposób prognozowania wyników gospodarczych w krótkim okresie. W niektórych przypadkach wygląda nawet bardziej efektywnie niż inne metody oparte na obserwacjach długoterminowych, gdyż pozwala w razie potrzeby zawęzić szereg czasowy badanego wskaźnika do takiej liczby jego wyrazów, która będzie odzwierciedlała jedynie najnowszy trend w jego rozwoju. Dzięki temu prognoza nie zostanie zniekształcona na skutek wcześniej występujących wartości odstających, załamań itp., a znacznie dokładniej odzwierciedli możliwą wartość przewidywanego wskaźnika w najbliższej przyszłości.

1. Tworzenie prognoz liniowych w programie Excel

W zależności od rodzaju zależności funkcjonalnych zmiennych egzogenicznych modele trendów mogą być liniowe i nieliniowe. Złożoność procesów gospodarczych i otwartość systemów gospodarczych determinują w większości przypadków nieliniowy charakter rozwoju wskaźników ekonomicznych. Jednak konstruowanie modeli liniowych jest procedurą znacznie mniej pracochłonną, zarówno z technicznego, jak i matematycznego punktu widzenia. Dlatego w praktyce często dopuszcza się częściową transformację procesów nieliniowych (o ile pozwala na to wstępna analiza graficzna danych), a modelowanie zachowania badanego wskaźnika sprowadza się do zestawienia i oszacowania liniowego równania jego dynamika.

1. Wykorzystanie funkcji liniowej do stworzenia modelu trendu

Funkcja arkusza REGLINP pomaga określić charakter liniowej zależności między wynikami obserwacji a czasem ich rejestracji i nadać jej opis matematyczny najbardziej zbliżony do danych oryginalnych. Do budowy modelu wykorzystuje równanie w postaci y=mx+b, gdzie y to badany wskaźnik, x=t to trend czasowy, b, m to parametry równania charakteryzujące przecięcie y oraz odpowiednio nachylenie linii trendu. Parametry modelu LINEST obliczane są metodą najmniejszych kwadratów.

Funkcję REGLINP możesz wywołać w oknie dialogowym „Kreator funkcji” (kategoria „Statystyczne”), znajdującym się na pasku narzędzi „Standard”.

Tabela 3.2 – Obliczanie i ocena modelu trendu liniowego przy użyciu funkcji REGLINP

Wybierz z menu Praca ustęp Analiza danych, pojawi się okno o tej samej nazwie, którego głównym elementem jest obszar Narzędzia analityczne. W tym obszarze znajduje się lista metod przetwarzania danych statystycznych zaimplementowanych w programie Microsoft Excel. Każda z wymienionych metod jest zaimplementowana jako oddzielny tryb pracy, aby go aktywować należy wybrać odpowiednią metodę za pomocą wskaźnika myszy i kliknąć przycisk OK. Po wyświetleniu okna dialogowego wywołanego trybu możesz przystąpić do pracy.

Tryb pracy " Średnia krocząca» służy do wygładzania poziomów empirycznych szeregów czasowych w oparciu o prostą metodę średniej ruchomej.

Tryb pracy " Wygładzanie wykładnicze» służy do wygładzania poziomów empirycznych szeregów czasowych w oparciu o metodę prostego wygładzania wykładniczego.

W oknach dialogowych tych trybów (rysunek 2 i 3) ustawiane są następujące parametry:

2. Pole wyboru Tagi– stan aktywny jest ustawiony, jeśli pierwszy wiersz (kolumna) zakresu wejściowego zawiera nagłówki. Jeżeli nie ma nagłówków, pole wyboru powinno być dezaktywowane. W takim przypadku automatycznie zostaną utworzone standardowe nazwy danych zakresu wyjściowego.

3. Interwał(tylko w oknie dialogowym Średnia ruchoma) – podaj wielkość okna wygładzania R. Domyślny p=3.

Rysunek 2 – Okno dialogowe średniej ruchomej

4. Współczynnik tłumienia(tylko w oknie dialogowym Wygładzanie wykładnicze) – wpisz wartość współczynnika wygładzania wykładniczego P. Domyślny, p=0,3.

5. Interwał wyjściowy / Nowy arkusz / Nowy skoroszyt– w pozycji Przedział wyjściowy aktywowane jest pole, w którym należy wpisać link do lewej górnej komórki zakresu wyjściowego. Rozmiar zakresu wyjściowego zostanie określony automatycznie, a na ekranie pojawi się komunikat, jeśli zakres wyjściowy może pokrywać się z danymi źródłowymi. W pozycji Nowy arkusz otwiera się nowy arkusz, w którym zaczynamy od komórki A1 wstawiane są wyniki analizy. Jeśli chcesz podać nazwę w polu znajdującym się naprzeciwko odpowiedniej pozycji przełącznika. W pozycji Nowy skoroszyt otwierany jest nowy skoroszyt, którego pierwszy arkusz rozpoczyna się od komórki A1 wstawiane są wyniki analizy.

6. Wyjście wykresu– zostaje ustawiony w stan aktywny do automatycznego generowania wykresów poziomów rzeczywistych i teoretycznych szeregu dynamicznego na arkuszu.

7. Standardowe błędy– ustaw na aktywną, jeżeli w zakresie wyjściowym chcesz uwzględnić kolumnę zawierającą błędy standardowe.

Rysunek 3 – Okno dialogowe wygładzania wykładniczego

Przykład 1.

Dane o sprzedaży (w milionach rubli) produktów rolnych przez sklepy kooperacji konsumenckiej w mieście przedstawia tabela wygenerowana w arkuszu programu Microsoft Excel (ryc. 4). W wyznaczonym okresie (2009 – 2012) należy zidentyfikować główny trend rozwoju tego procesu gospodarczego.

Rysunek 4 – Dane wstępne

Aby rozwiązać problem, używamy trybu pracy „ Średnia krocząca" Wartości parametrów ustawionych w oknie dialogowym o tej samej nazwie przedstawiono na rysunku 5, wskaźniki obliczone w tym trybie na rysunku 6, a zbudowane wykresy na rysunku 7.

Rysunek 5 – Wypełnianie okna dialogowego

Rysunek 6 – Wyniki analizy

Rysunek 7 – Średnia krocząca

Kolumna D (rysunek 5) oblicza wartości wygładzonych poziomów. Na przykład wartość pierwszego wygładzonego poziomu jest obliczana w komórce D5 przy użyciu formuły =ŚREDNIA(C2:C5), wartość drugiego wygładzonego poziomu jest obliczana w komórce D6 przy użyciu formuły =ŚREDNIA(C5:C8) itd. .

Kolumna E oblicza błędy standardowe przy użyciu wzoru =ROOT(SUMAVARNA (blok wartości rzeczywistej; blok wartości przewidywanej) / rozmiar okna wygładzającego).

Na przykład wartość w komórce E10 jest obliczana przy użyciu formuły =ROOT(SUMQVAR(C7:C10,O7:B10)/4).

Jednakże, jak zauważono powyżej, jeśli rozmiar okna wygładzania jest liczbą parzystą ( р=2m), wówczas obliczonej wartości średniej nie można porównać z żadnym konkretnym momentem czasu t, dlatego konieczne jest zastosowanie procedury centrowania.

Dla tego przykładu p=4, dlatego konieczna jest procedura centrowania. Tym samym pierwszy wygładzony poziom (265,25) notowany jest pomiędzy II i III kwartałem. 2009 itd. Stosując procedurę centrowania (używamy w tym celu funkcji ŚREDNIA) uzyskujemy wygładzone poziomy z wycentrowaniem. Dla III kV. 2009 wyznacza się punkt środkowy pomiędzy pierwszym a drugim poziomem wygładzonym: (265,25 + 283,25)/2 = 274,25; za IV kwartał 2009, drugi i trzeci wygładzony poziom są wyśrodkowane: (283,25 + 292,00)/2 = 287,6 itd. Obliczone wartości przedstawiono w tabeli 1. Skorygowany wykres średniej ruchomej przedstawiono na rysunku 8.

Tabela 1 – Dynamika wygładzonych poziomów sprzedaży produktów

Rok	Kwartał	Kwota sprzedaży, milion rubli.	Gładkie poziomy z centrowaniem
		274,25
		287,63
			297,00
		307,50
		334,63
		374,13
			402,88
		421,00
		429,00
		430,75
			435,38
		446,63

Rysunek 8 – Skorygowany wykres średniej ruchomej

Przykład 2.

Rozważany problem można również rozwiązać stosując metodę prostego wygładzania wykładniczego. W tym celu należy skorzystać z trybu pracy „Wygładzanie wykładnicze”. Wartości parametrów ustawionych w oknie dialogowym o tej samej nazwie przedstawiono na rysunku 9, wskaźniki obliczone w tym trybie pokazano na rysunku 10, a skonstruowane wykresy pokazano na rysunku 11.

Rysunek 9 – Wypełnianie okna dialogowego Wygładzanie wykładnicze

Rysunek 10 – Wyniki analizy

Rysunek 11 – Wygładzanie wykładnicze

Kolumna D (Rysunek 10) oblicza wartości wygładzonych poziomów na podstawie powtarzających się zależności.

W kolumnie E błędy standardowe obliczane są ze wzoru =ROOT(SUMVARE (blok wartości rzeczywistej; blok wartości przewidywanej) / 3). Jak łatwo zauważyć (porównaj rysunki 8 i 11), przy stosowaniu prostej metody wygładzania wykładniczego, w przeciwieństwie do prostej metody średniej ruchomej, zachowywane są małe fale.

Cel pracy : Zdobądź umiejętności rozwiązywania problemów związanych z analizą częstotliwości, korzystając z funkcji arkusza analizy MS Excel.

Krótka teoria

Analizując wskaźniki ekonomiczne często pojawia się pytanie jak często wskaźniki występują w danych przedziałach wartości.

Arkusz analizy, funkcja CZĘSTOTLIWOŚĆ MS Excel jest klasyfikowany jako funkcja statystyczna i zwraca rozkład częstotliwości w postaci tablicy pionowej. Dla danego zbioru wartości i danego zbioru kieszeni (przedziałów) rozkład częstotliwości zlicza, ile wartości przypada na każdy przedział.

Tablica danych może być tablicą jednowymiarową lub dwuwymiarową (na przykład A 4: D 15).

Składnia: FREQUENCY (tablica_danych; tablica_kieszonkowa)

Do analizy częstotliwości można użyć poleceniaAnaliza usług/danych.Analiza danych jest jednym z dodatków Przewyższać . Jeśli tego polecenia nie ma w menu, należy je uruchomićUsługa/dodatkii zaznacz odpowiednie pole w oknie Dodatki.

Ćwiczenie 1

Korzystanie z funkcji Częstotliwość aby wybrać zestaw kwot zamówienia () wejdź w zakres, policz, ile wartości mieści się w podanych przedziałach wartości. Na przykład od 0 do 1000, od 1001 do 1500, od 1501 do 2000, od 2001 do 2500, powyżej 2500.

Procedura:

1. Na arkuszu MS Excel wprowadź dane dotyczące wielkości zamówień w 20 oddziałach firmy na wrzesień w formie tabeli, której fragment pokazano na rysunku.

	Numer oddziału	Wrzesień
		1230		1000
				1500
		….		2000
				2500

1. W wolnym zakresie komórek (kolumnie) wprowadź górne granice przedziałów (np. D 2=1000, D 3=1500, D 4=2000, D 5=2500).
2. Wybierz blok komórek w kolumnie sąsiadującej z kolumną przedziału ( E2:E 21). Aby policzyć liczbę wartości przekraczającą dolną granicę przedziału, wybierany jest zakres o jedną komórkę większy niż zakres przedziałów.
3. Do zakresu E 2: E 6 wprowadź formułę ( =CZĘSTOTLIWOŚĆ( E 2: E 15; J 2: J 6)).

Dla tego użyj kreatora funkcji (Wstaw/Funkcja). W kategorii Statystyka wybierz z listy funkcję Częstotliwość. W oknie dialogowym funkcji FREQUENCY wypełnij pola tablicy próbek i tablicy przedziałów (Rysunek 1).Bez wychodzenia z okna dialogowegonaciśnij kombinację klawiszy< Ctrl / Shift / Enter > do obliczenia elementów tablicy.

Rysunek 1 Przykład wypełnienia okna dialogowego funkcji Częstotliwość.

1. Na podstawie uzyskanych wyników utwórz diagram.
2. Zapisz plik.

Zadanie 2

Utwórz na arkuszu dwuwymiarową tablicę zawierającą dane statystyczne dotyczące wzrostu osób w różnych kategoriach wiekowych. Przeprowadź analizę częstotliwości wyników za pomocą funkcji CZĘSTOTLIWOŚĆ i Analiza danych (pozycja menuAnaliza danych/histogram).

Podczas korzystania z narzędzia analizy danych w oknie dialogowym w terenieInterwał wejściowyw polu podaj początkowy przedział, dla którego budowany jest histogramRozstaw kieszeni- zakres z wartościami górnych granic przedziałów. Histogram jest tworzony na podstawie nowego lub bieżącego arkusza.

Część 2

Rozwiązywanie problemów prognostycznych w programie MS Excel. Metoda średniej ruchomej

Cel pracy : Nabycie umiejętności prognozowania działalności gospodarczej przedsiębiorstwa z wykorzystaniem pakietu programów statystycznych MS Excel.

Krótka teoria

Przez prognozę rozumie się naukowy opis możliwych stanów systemu w przyszłości. Opracowując plany krótko- i długoterminowe, każda organizacja zmuszona jest przewidzieć wartość najważniejszych wskaźników działalności gospodarczej, takich jak wielkość produkcji, sprzedaż, koszty produkcji itp. Obecnie do rozwiązywania problemów prognostycznych wykorzystuje się nowoczesne technologie informacyjne, których oprogramowanie obejmuje pakiety programów statystycznych.

Rozwiązywanie problemów prognostycznych w środowisku Używany jest pakiet MS Excel , w tym narzędzia analityczne. Wybierając narzędzie do analizy danych i ustawiając niezbędne parametry, można szybko rozwiązać złożone problemy statystyczne, dołączając do nich graficzną interpretację.

Wstępna analiza szeregów czasowych wskaźników ekonomicznych polega na identyfikacji nieprawidłowych wartości poziomów szeregów, które naruszają stwierdzenie obecności trendu. Aby wyeliminować nieprawidłowe wartości wskaźników, stosuje się procedurę wygładzania szeregów czasowych. W tym przypadku, aby zidentyfikować trend szeregu, wartości rzeczywiste zastępuje się wartościami obliczonymi.

Przy wyborze metody prognozowania uwzględnia się charakter zmiany zmiennej losowej szeregu czasowego. Jeżeli zmienność wartości średnich jest nieznaczna, a wszystkie obserwacje w szeregu czasowym mają jednakowe znaczenie dla prognozy, wówczas stosuje się metodę średniej ruchomej. Średnie kroczące pozwalają wygładzić (filtrować) losowe i okresowe wahania w szeregach czasowych. Najpopularniejszą procedurą wygładzania jest proste wygładzanie średnią ruchomą.

W narzędziu analitycznym MS Excel Średnia kroczącaliczba wartości biorących udział w obliczaniu wartości przewidywanej jest określona przez parametr Interwał . Im większy przedział wygładzania, tym bardziej konieczne jest wygładzenie małych wahań wartości szeregu. metodaProsta średnia ruchomadaje dobre wyniki w szeregach czasowych z liniowym trendem rozwoju.

Jeżeli dla prognozy najbardziej istotne są najnowsze wyniki obserwacji, wówczas stosuje się metodę wygładzania wykładniczego. W metodzie wygładzania wykładniczego każda wartość uczestniczy w tworzeniu wartości przewidywanych o zmiennej wadze, która maleje wraz z dezaktualizacją danych. W narzędziu analitycznym MS Excel « Wygładzanie wykładnicze”współczynnik ważenia lub parametr wygładzania jest określany przez parametrWspółczynnik tłumienia. Zazwyczaj dla szeregów czasowych w problemach ekonomicznych wartość parametru wygładzania ustalana jest w przedziale od 0,1 do 0,3. Początkowa wartość obliczona w procedurzeWygładzanie wykładnicze Pakiet analityczny MS Excel przyjmuje się, że jest to poziom pierwszego wyrazu szeregu. Metoda zapewnia dobrą zgodność pomiędzy danymi początkowymi i obliczonymi dla pierwszych wartości szeregu. Jeżeli ostateczne obliczone wartości znacznie różnią się od odpowiednich wartości początkowych, zaleca się zmianę wartości parametru wygładzania. Wielkość rozbieżności można ocenić na podstawie błędów standardowych oraz wykresów, które pakiet Analiza pozwala wyświetlić wraz z obliczonymi wartościami serii.

Rozważmy możliwości prognozowania wskaźników wydajności przedsiębiorstwa zajmującego się świadczeniem usług komunikacyjnych.

Zadanie laboratoryjne (część 2)

Ćwiczenie 1 : Oblicz przewidywaną wartość wolumenu produktów (usług) przedsiębiorstwa, stosując metodę średniej ruchomej.

Procedura wykonania zadania:

Utwórzmy kolumnę w arkuszu zawierającą dane dotyczące wolumenu usług w milionach rubli świadczonych przez przedsiębiorstwo w ciągu ostatnich 10 lat. Zidentyfikujmy trend zmian wskaźnika za pomocą średniej kroczącej. Wybierzemy trzyletni okres średniej ruchomej, ponieważ w krótszym okresie średnia ruchoma może nie odzwierciedlać trendu, ale w dłuższym okresie go wygładzi.

Do obliczeń zastosujemy metodę bezpośredniego wprowadzenia wzoru. Aby uzyskać trzyletnią średnią ruchomą wolumenu wykonanych usług dla naszego przykładu, wchodzimy w komórkę B 5 wzorów do obliczenia =ŚREDNIA( A2:A 4). Skopiujmy formułę do przedziału B 6: B 11.

Rysunek 1 Obliczenie prostej średniej kroczącej

Zilustrujmy wyniki wykresem obrazującym dynamikę zmian danych początkowych i średnią ruchomą.

Rysunek 2 Wykres trendu zmian wolumenu usług uzyskiwanych metodą prostej średniej ruchomej

Innym rozwiązaniem jest użycie ruchomej liczby całkowitej do zdefiniowania Pakiet analityczny . Pakiet analityczny jest dodatkiem MS Excel (wybierz pozycję menuUsługa/dodatkii zaznacz pole Pakiet analityczny).

Procedura

1. Uruchom polecenieAnaliza usług/danychi wybierz z listy narzędzi analitycznychŚrednia krocząca.
2. W oknie dialogowym określ parametry obliczania średniej ruchomej:

• Jako przedział wejściowy wybierz blok komórek zawierający dane o wolumenie usług.
• Określ interwał- 3 (domyślnie jest to 3), jako interwał wyjściowy dowolna komórka arkusza (wystarczy kliknąć na komórkę arkusza, z której mają zostać wyprowadzone wyniki);

Przewyższać wykona pracę polegającą na wprowadzeniu wartości do wzoru na obliczenie średniej ruchomej. Ze względu na niewystarczającą ilość danych przy obliczaniu średniej z pierwszych obserwacji, w początkowych komórkach zakresu wyjściowego zostanie wyświetlona wartość błędu #N/A. Należy pamiętać, że pierwsza wartość uzyskana w szeregu jest wartością prognozowaną nie dla trzeciego, ale czwartego okresu. Dlatego jeśli komórka określona dla wyniku odpowiada początkowi kolumny obserwacji, należy przesunąć kolumnę obliczonych wartości o jedną komórkę w dół. Ta czynność spowoduje dołączenie prognoz dokładnie do okresów, dla których są obliczane.

Przeanalizuj zastosowane wzory obliczeniowe i uzyskane wyniki.

Podobnie oblicz pięcioletnie proste średnie kroczące. Porównaj wyniki wygładzania dla dwóch opcji obliczeń.

Zadanie 2: Oblicz przewidywaną wartość wolumenu produktów (usług) przedsiębiorstwa za pomocą metody wygładzania wykładniczego.

Procedura:

1. Na arkuszu MS Excel utwórz listę zawierającą dane o liczbie pracowników firmy na przestrzeni ostatnich 10 lat. Wprowadź dane losowo, ale tak, aby można było prześledzić trend.
2. Wygładź szereg czasowy za pomocą średniej wykładniczej z parametrami wygładzania 0,1, a następnie 0,3. Na podstawie wyników obliczeń skonstruuj wykres i określ, który z otrzymanych szeregów czasowych jest gładszy.

Użyj poleceniaAnaliza usług/danychi wybierz z listy narzędzi analitycznychWygładzanie wykładnicze.Określ parametry do obliczania średniej ruchomej:

• Jako interwał wejściowy wybierz blok komórek zawierający dane populacji.
• Sprecyzować Współczynnik tłumienia. Jako przedział wyjściowy dowolna komórka arkusza.
• Ustaw wynik wykresu i błędy standardowe.

1. Dodaj linie trendu do powstałych wykresów. W tym celu zaznacz linię wykresu (wystarczy kliknąć prawym przyciskiem myszy na linię wykresu) i wybierz opcję z menu kontekstowegoDodaj linię trendu. W oknie dialogowym wybierz typ trendu, który najlepiej pasuje do Twoich danych (na przykład filtrowanie liniowe) i zaznacz pole, aby wyświetlić równanie dopasowania krzywej na wykresie.
2. Sprawdź i zapisz wyniki.

Część 3

Rozwiązywanie problemów związanych z prognozowaniem przy użyciu funkcji arkusza kalkulacyjnego i znacznika wypełnienia

Krótka teoria

W prognozowaniu gospodarczym wykorzystuje się różne modele wzrostu. Krzywa wzrostu jest pewną funkcją przybliżającą dany szereg czasowy. Przy opracowywaniu prognozy z wykorzystaniem krzywych wzrostu wybiera się krzywe, których kształt odpowiada dynamice szeregu czasowego, ocenia się ich parametry, sprawdza się adekwatność wybranych krzywych do przewidywanego procesu i oblicza prognozę punktową lub przedziałową.

Istnieje kilka metod dopasowywania krzywych. Jedną z najprostszych jest metoda wizualna. Jeżeli trend rozwojowy (trend) nie jest dostatecznie widoczny na wykresie, wówczas szereg wygładza się w sposób opisany powyżej, a następnie wybiera się krzywą odpowiadającą nowemu szeregowi. W tym przypadku wykorzystuje się także nowoczesne oprogramowanie systemów komputerowych. W MS Excel wbudowane są specjalne funkcje, które pozwalają obliczyć przewidywane wartości dla określonego okresu.

Excel dokonuje ekstrapolacji liniowej, tj. oblicza najlepiej dopasowaną linię prostą przechodzącą przez szereg podanych punktów. Zadanie polega na naniesieniu na wykres zbioru punktów, a następnie wybraniu linii, wzdłuż której można prześledzić rozwój funkcji z najmniejszym błędem. Linia ta nazywana jest linią TREND. Użytkownik może wykorzystać wynik obliczeń do analizy trendów i prognozowania krótkoterminowego.

Excel może automatycznie rysować linie trendu różnego typu bezpośrednio na wykresie. Obliczeń można dokonać na dwa sposoby:

• Korzystanie z uchwytu wypełniania
• Korzystanie z funkcji arkusza

Pierwszy sposób

Przybliżenie liniowe

• Lewym przyciskiem myszy przeciągnij uchwyt wypełniania tak, aby zaznaczone zostały również komórki, dla których chcesz obliczyć przewidywane wartości. Obliczone w ten sposób wartości odpowiadają prognozie liniowej.

Przybliżenie wykładnicze

• Wybierz komórki z wynikami obserwacji.
• Przeciągnij uchwyt wypełniania prawym przyciskiem myszy, tak aby zaznaczone zostały również komórki, dla których chcesz obliczyć przewidywane wartości.
• W wyświetlonym menu kontekstowym wybierz polecenie „Dopasowanie wykładnicze”.

Drugi sposób

W MS Excelu Funkcje statystyczne arkusza są wbudowane.

TENDENCJA() - zwraca wartości według przybliżenia liniowego najmniejszych kwadratów.

WYSOKOŚĆ() - zwraca wartości zgodnie z trendem wykładniczym.

Użycie tych funkcji to kolejny sposób obliczenia analizy regresji.

Format

TREND (wartość_rew_Y; wartość_znajdźX; nowa_wartość_X; stała)

Funkcja GROWTH zwraca wartości zgodnie z trendem wykładniczym.

Zadanie laboratoryjne (część 3)

Ćwiczenie 1:

Oblicz prognozę liniową i wykładniczą na jeden rok i trzy kolejne okresy (do 2011 r.) za pomocą znacznika wypełnienia.

Zadanie 2:

Oblicz prognozy liniowe i wykładnicze na jeden rok, a następnie na kolejne trzy okresy, korzystając z funkcji arkusza TREND i WZROST. Aby obliczyć prognozę przedziałową, po wypełnieniu parametrów okna dialogowego funkcji i bez jego opuszczania należy nacisnąć kombinację klawiszy Ctrl/Shift/Enter.

Na pasku formuły arkusza powinna pojawić się formuła obliczania elementów tablicy, np.

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

Określ, który model jest najdokładniejszy.

Utwórz wykresy i linie trendu dla pierwszego i drugiego zadania.

W biznesie, jak w każdej innej działalności, człowiek chce wiedzieć, co będzie dalej. Trudno nawet wyobrazić sobie bogactwo tego szczęściarza, który potrafił odgadnąć przyszłość ze 100% dokładnością. Ale niestety (lub na szczęście) dar przewidywania jest niezwykle rzadki. ALE... przedsiębiorca ma po prostu obowiązek spróbować przynajmniej ogólnie wyobrazić sobie przyszłą sytuację biznesową.

Na początku chciałam napisać w jednym poście o kilku prostych i wygodnych technikach na raz, ale post zaczął okazywać się bardzo długi. Dlatego też pojawi się kilka postów poświęconych tematowi prognozowania. W tym poście opiszemy jedną z najprostszych metod prognozowania wykorzystującą możliwości Excela – metodę średniej ruchomej.

Najczęściej w praktyce badań marketingowych przewiduje się następujące wartości:

• Wolumeny sprzedaży
• Wielkość i pojemność rynku
• Wielkość produkcji
• Importuj wolumeny
• Dynamika cen
• I tak dalej.

W przypadku prognozowania, które rozważamy w tym poście, radzę zastosować się do następującego prostego algorytmu:

1. Zbieranie wtórnych informacji na temat problemu(najlepiej ilościowe i jakościowe). Jeśli więc na przykład przewidujesz wielkość swojego rynku, musisz zebrać informacje statystyczne na temat rynku (wielkość produkcji, import, dynamika cen, wielkość sprzedaży itp.), a także trendy, problemy lub możliwości rynkowe. Jeśli prognozujesz sprzedaż, potrzebujesz danych sprzedażowych za dany okres. Jeśli chodzi o prognozowanie, im więcej danych historycznych weźmiesz pod uwagę, tym lepiej. Wskazane jest uzupełnienie prognozowania o analizę czynników wpływających na przewidywane zjawisko (można zastosować analizę SWOT, PEST lub dowolną inną). Pozwoli to zrozumieć logikę rozwoju, a co za tym idzie, będzie można sprawdzić wiarygodność konkretnego modelu trendu.

2. Ponadto jest to pożądane sprawdź dane ilościowe. Aby to zrobić, musisz porównać wartości tych samych wskaźników, ale uzyskane z różnych źródeł. Jeśli wszystko się zgadza, możesz „wprowadzić” dane do Excela. Dane muszą także spełniać następujące wymagania:

• Linia bazowa obejmuje wyniki obserwacji – od najwcześniejszych do najnowszych.
• Wszystkie okresy bazowe mają ten sam czas trwania. Na przykład danych z jednego dnia nie należy mieszać ze średnimi z trzech dni.
• Obserwacje są rejestrowane w tym samym momencie w każdym okresie. Na przykład w tym samym czasie należy mierzyć ruch.
• Pomijanie danych jest niedozwolone. Pominięcie choć jednego wyniku obserwacji jest niepożądane w prognozowaniu”, dlatego też, jeśli w Twoich obserwacjach przez krótki czas brakuje wyników, postaraj się uzupełnić je przynajmniej przybliżonymi danymi.

3. Po sprawdzeniu danych możesz stosować różne techniki prognozowania. Chciałbym zacząć od najprostszej metody - METODA ŚREDNIEJ KROKUJĄCEJ

METODA ŚREDNIEJ KROKUJĄCEJ

Metoda średniej ruchomej jest dość łatwa w użyciu, ale zbyt proste jest zbudowanie dokładnej prognozy. Stosując tę ​​metodę prognoza na dowolny okres to nic innego jak wzięcie średniej z kilku poprzednich obserwacji szeregu czasowego. Na przykład, jeśli wybierzesz trzymiesięczną średnią ruchomą, prognoza na maj będzie średnią z lutego, marca i kwietnia. Wybierając czteromiesięczną średnią ruchomą jako metodę prognozowania, możesz ocenić dane z maja jako średnią danych ze stycznia, lutego, marca i kwietnia.

Zazwyczaj prognozę średniej ruchomej postrzega się jako prognozę na okres bezpośrednio następujący po okresie obserwacji. Jednocześnie prognoza taka ma zastosowanie, gdy badane zjawisko rozwija się sekwencyjnie, tj. Są pewne trendy i krzywa wartości nie skacze po wykresie jak szalona.

Aby określić, ile obserwacji chcesz uwzględnić w średniej ruchomej, musisz oprzeć się na wcześniejszych doświadczeniach i informacjach, które posiadasz na temat zbioru danych. Należy znaleźć równowagę pomiędzy zwiększoną reakcją średniej ruchomej na kilka ostatnich obserwacji a dużą zmiennością tej średniej.

Jak więc to zrobić wPrzewyższać

1. Załóżmy, że masz miesięczne wolumeny sprzedaży z ostatnich 29 miesięcy. Chcesz określić, jaki będzie wolumen sprzedaży w 30 miesiącu. Ale szczerze mówiąc, przy obliczaniu wartości prognozowanych wcale nie jest konieczne operowanie 30 wartościami historycznymi, ponieważ ta metoda do obliczenia średniej wykorzysta tylko kilka ostatnich miesięcy. Dlatego do obliczeń wystarczy tylko kilka ostatnich miesięcy.

2. Tę tabelę sprowadzamy do zrozumiałej postaci Excela, tj. tak, aby wszystkie wartości znajdowały się w tym samym wierszu.

3. Następnie wprowadzamy wzór na obliczenie średniej na podstawie trzech poprzednich (czterech, pięciu? według uznania) wartości (patrz). Do obliczeń najwygodniej jest użyć 3 ostatnich wartości, ponieważ jeśli uwzględnisz więcej, dane będą zbyt uśrednione, jeśli uwzględnisz mniej, nie będą dokładne.

4. Korzystanie z funkcji autouzupełniania dla wszystkich kolejnych wartości aż do 30, miesiąca prognozy. Tym samym funkcja obliczy prognozę na czerwiec 2010. Według wartości prognoz, sprzedaż w czerwcu wyniesie około 408 jednostek towaru. Należy jednak pamiętać, że jeśli tendencja spadkowa będzie stała, jak w naszym przykładzie, obliczenie prognozy na podstawie średniej będzie nieco zawyżone lub będzie sprawiało wrażenie „opóźniającego się” w stosunku do wartości rzeczywistych.

Przyjrzeliśmy się jednej z najprostszych technik prognozowania – metodzie średniej ruchomej. W kolejnych postach przyjrzymy się innym, dokładniejszym i złożonym technikom. Mam nadzieję, że mój post będzie dla Ciebie przydatny.