МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ «ЛЬВІВСЬКА ПОЛІТЕХНІКА» Інститут економіки і менеджменту Кафедра технічної механіки та динаміки машин ЛАБОРАТОРНА РОБОТА №1 з курсу «Маркетингові дослідження» на тему: «Аналіз тимчасового ряду і розрахунок лінії тренда» Варіант № 11 Прийняла: Довгунь О.С. Львів – 2017 Мета роботи: отримати відомості та навички роботи з основними елементами тимчасового ряду. Навчитися основним прийомам розрахунків тренду, індексів сезонності. Завдання: Використовуючи методику прогнозування, за даними таблиці 1.1 розрахувати індекси сезонності, побудувати сезонну хвилю та зробити прогноз обсягу реалізації продукції відповідно на 6 місяців. Таблиця 1.1 Обсяг реалізації хлібобулочних виробів, тис.т.. Місяць Рік   2007 2008 2009 2010  1 2 3 4 5  1 16,3 16,7 16,9 17,4  2 17,4 17,5 17,7 17,8  3 18,2 17,9 17,9 18,1  4 18,6 18,8 18,9 18,9  5 19 19,2 19,5 19,7  6 18,5 18,7 19 19,2  7 18 19 19,3 19,4  8 18,5 19,2 19,5 19,9  9 19,6 20 20,2 20,5  10 20,1 20,5 20,7 20,9  11 20,7 20,9 21,1 21,4  12 21 21,4 21,6 21,8   Хід роботи Алгоритм розрахунку тренду Оформляю таблицю 1.1 у більш зручний формат (див. табл.1.2) Таблиця 1.2 Обсяг реалізації хлібобулочних виробів, тис.т.. РІК Квартал 1 Квартал 2 Квартал 3 Квартал 4  2007 51,9 56,1 56,1 61,8  2008 52,1 56,7 58,2 62,8  2009 52,5 57,4 59 63,4  2010 53,3 57,8 59,8 64,1   Вводимо дані в екселівську таблицю. Вводимо формули для розрахунку чотирьох квартальної ковзаючої середньої в клітинку С5: = SUM(B3:B6)/4. Скопіюйте цю формулу вниз аж до клітинки С17. Знаходимо суму двох сусідніх значень. В клітинку D5 вводимо формулу: = С5 + С6. Центруємо набуті значення, в клітинку Е5 вводимо формулу: =D5/2 і скопіюємо її вниз до клітинки Е16. Центрована середня і є значенням розрахованого нами тренда. Таблиця 1.3 Розрахунок прогнозу обсягу реалізації хлібобулочних виробів, тис.т. Період (рік/квартал) Обсяг продажів тис. грн. Ковзна середня за 4 квартали Сума двох сусідніх значень Центрована середня, тренд тис. грн Витрати / Тренд *100  1 2 3 4 5 6  2007/1 51,9          2007/2 56,1          2007/3 56,1 56,5 113,0 56,5 99,3  2007/4 61,8 56,5 113,2 56,6 109,2  2008/1 52,1 56,7 113,9 56,9 91,5  2008/2 56,7 57,2 114,7 57,3 98,9  2008/3 58,2 57,5 115,0 57,5 101,2  2008/4 62,8 57,6 115,3 57,6 109,0  2009/1 52,5 57,7 115,7 57,8 90,8  2009/2 57,4 57,9 116,0 58,0 99,0  2009/3 59 58,1 116,4 58,2 101,4  2009/4 63,4 58,3 116,7 58,3 108,7  2010/1 53,3 58,4 117,0 58,5 91,2  2010/2 57,8 58,6 117,3 58,7 98,5  2010/3 59,8 58,8        2010/4 64,1          Сезонна варіація            Рік Квартал 1 Квартал 2 Квартал 3 Квартал 4    2007     99,3 109,2    2008 91,5 98,9 101,2 109,0    2009 90,8 99,0 101,4 108,7    2010 91,2 98,5     РАЗОМ  Нескоректована середня 91,1 98,8 100,6 108,9 399,5  Скоректована середня 91,3 98,9 100,8 109,1 400,0   2. Розраховуємо сезонну варіацію 2.1. В стовпці F розраховуємо значення тимчасового ряду, які очищені від трендової складової, це і є індексом сезонності. Для цього виділяємо клітинку F5 і вводимо в неї формулу: =В5/Е5*100. Копіюємо формулу вниз до F16. 2.2 .Для усереднення сезонних компонентів будуємо табличку для обчислень, заповнюємо дані по кварталах. 2.3. Знаходимо середні значення по кожному стовпцю. Введемо в комірку В25 формулу:=СРЗНАЧ(В22:В24). Цю ж операцію повторюємо для стовпців С, D, E. 2.4. В комірку F25 вводимо формулу:=SUM(B25:E25). 2.5. Сума сезонної варіації, розрахована по кварталах, повинна дорівнювати 400. Для коректування компонентів пересуваємось в комірку В26 і вводимо формулу: =В25*400/$F$25. 2.6. Копіюємо формулу аж до комірки F26 і перевіряємо чи це значення рівне 400. Дані в комірках з B26 до E26 є сезонними варіаціями. Таблиця 1.4 Вигляд робочого листа в режимі демонстрації формул Період (рік/квартал) Обсяг продажів тис. грн. Ковзна середня за 4 квартали Сума двох сусідніх значень Центрована середня, тренд тис. грн Витрати / Тренд *100  1 2 3 4 5 6  2007/1 51,9          2007/2 56,1          2007/3 56,1 =СУММ(B3:B6)/4 =C5+C6 =D5/2 =B5/E5*100  2007/4 61,8 =СУММ(B4:B7)/4 =C6+C7 =D6/2 =B6/E6*100  2008/1 52,1 =СУММ(B5:B8)/4 =C7+C8 =D7/2 =B7/E7*100  2008/2 56,7 =СУММ(B6:B9)/4 =C8+C9 =D8/2 =B8/E8*100  2008/3 58,2 =СУММ(B7:B10)/4 =C9+C10 =D9/2 =B9/E9*100  2008/4 62,8 =СУММ(B8:B11)/4 =C10+C11 =D10/2 =B10/E10*100  2009/1 52,5 =СУММ(B9:B12)/4 =C11+C12 =D11/2 =B11/E11*100  2009/2 57,4 =СУММ(B10:B13)/4 =C12+C13 =D12/2 =B12/E12*100  2009/3 59 =СУММ(B11:B14)/4 =C13+C14 =D13/2 =B13/E13*100  2009/4 63,4 =СУММ(B12:B15)/4 =C14+C15 =D14/2 =B14/E14*100  2010/1 53,3 =СУММ(B13:B16)/4 =C15+C16 =D15/2 =B15/E15*100  2010/2 57,8 =СУММ(B14:B17)/4 =C16+C17 =D16/2 =B16/E16*100  2010/3 59,8 =СУММ(B15:B18)/4        2010/4 64,1          Сезонна варіація            Рік Квартал 1 Квартал 2 Квартал 3 Квартал 4    2007     =F5 =F6    2008 =F7 =F8 =F9 =F10    2009 =F11 =F12 =F13 =F14    2010 =F15 =F16     РАЗОМ  Нескоректована середня =СРЗНАЧ(B22:B24) =СРЗНАЧ(C22:C24) =СРЗНАЧ(D21:D23) =СРЗНАЧ(E21:E23) =СУММ(B25:E25)  Скоректована середня =B25*400/$F$25 =C25*400/$F$25 =D25*400/$F$25 =E25*400/$F$25 =F25*400/$F$25   За цими прогнозами можна зробити припущення, що динаміка тренда збережеться незмінною у порівнянні з минулими періодами, а при цьому сезонна варіація збереже свою поведінку. Якщо ми допустимо, що це припущення може виявитися невірним, то доведеться внести корективи в прогноз, враховуючи експертну очікувану зміну ситуації. Наприклад, на ринок проникне інший великий роздрібний торговець одягом і зіб'є наші ціни — тренд обсягу продажів зміниться і нам доведеться відповідно змінити наш прогноз. 3.Побудова лінії тренда 3.1. Виділяємо стовпчик Е (від Е3 до Е16 ), екселівської таблиці, і вставляємо діаграму. 3.2. Додаємо лінію тренду, за допомогою правої кнопки мишки. У вікні тип вибираємо найбільш коректну функцію, орієнтуючись на величину коефіцієнта детермінації R, ближчу до 1. Для цього у вікні параметри вибираємо «помістити на діаграмі R». Побудувавши трендову криву, переносимо відомі прогнози тренда на потрібний рік. Далі, використовуючи вищезазначені сезонні чинники, визначаємо скоректований прогноз на наступний рік. Найбільшим коефіцієнт детермінації є при поліноміальній лінії тренду: / Рис. 1 Поліноміальна лінія тренда / Рис. 2 Експоненційна лінія тренда / Рис. 3 Лінійна лінія тренда / Рис. 4 Логарифмічна лінія тренда / Рис. 5 Степенева лінія тренда 3.3 Побудувавши трендову криву, переносимо відомі прогнози тренда на потрібний рік. Далі, використовуючи вищезазначені сезонні чинники, визначаємо скоректований прогноз на наступний рік. Таблиця 1.5 Прогноз обсягу продажу на 2011 р. Прогноз тренда, тис. грн Квартал 1 Квартал 2 Квартал 3 Квартал 4  2011 рік 59,05 59,09 59,12 59,14  Сезонний чинник,% 91,25 98,92 100,76 109,07  Прогноз обсягу продаж на 2011 р., тис. грн 64,7 59,7 58,7 54,2   Висновок: Виконавши дану лабораторну роботу, я набула навиків розрахунку тренду, розрахунку сезонної варіації, побудову лінії тренду. Здійснивши розрахунки, для прогнозу обрано рівняння поліноміальної лінії тренда, оскільки саме в цьому рівнянні найбільше значення R2 (0,9931). Прогноз обсягу споживання соку однією сім’єю на 2011 рік становить: 1 квартал – 67,4 тис.грн 2 квартал – 59,7 тис.грн 3 квартал – 58,7 тис.грн 4 квартал – 54,2 тис.грн