Прогнозирование принятия управленческих решений

Задание контрольной работы

Организации, занимающейся производством продуктов питания, необходимо спланировать свою дальнейшую деятельность. Для этого, помимо всего прочего, необходимо спрогнозировать объемы продаж своей продукции на весь планируемый период времени. Исходными данными для прогноза служат накопленные данные по продажам произведенных продуктов за предыдущие месяцы.

Часть 1

По данным, характеризующим изменение объемов продаж (Таблица 1):

1. Построить график изменения объема продаж во времени.

2. Применить метод трехчленной скользящей средней.

3. Построить систему нормальных уравнений и рассчитать константы прогнозирующей функции.

4. Определить наиболее вероятные объемы продаж в 13, 14, 15 месяцы.

5. Рассчитать возможные ошибки прогноза, определив доверительные интервалы для индивидуальных значений объема продаж в 13, 14, 15 месяцы.

6. Построить графики скользящей средней и прогнозирующей функции вида y_t=f (t)

Таблица 1

Исходные данные для выполнения заданий

Прогнозирующая функция — экспоненциальная.

Сравниваемая прогнозирующая функция — линейная.

Часть 2

По данным, характеризующим изменение объема продаж (таблица 2), требуется выполнить следующие задания, используя программу Excel:

1. Построить графики исходной кривой, трехчленной скользящей средней, выбрать линию тренда, указать уравнение этой функции.

2. Используя функции ТЕНДЕНЦИЯ или РОСТ построить прогнозирующую функцию.

3. Используя функции программы Excel, посчитать доверительные интервалы для 25-ого месяца.

Таблица 2

Исходные данные для выполнения заданий

Решение

1. Построим график изменения объема продаж во времени по данным таблицы 1. (Рис. 1)

Таблица 3

2. Применим метод трехчленной скользящей средней Рис. 1. График изменения объема продаж во времени Для этого вычисляем значения трехчленных скользящих средних по формуле:

=(y_t-1+ y_t+ y_t+1)/3, t = 2, 3,…, (n — 1),

Например, Расчеты всех трехчленных скользящих средних представлены в табл. 3.

Рис. 2. Сглаживание ряда с помощью скользящей средней

1. Построим систему нормальных уравнений и рассчитаем константы прогнозирующей экспоненциальной функции Для построения системы нормальных уравнений необходимо линеаризовать исходную функцию. С этой целью осуществляется замена переменных путем логарифмирования (в нашем случае):

Логарифмируем

lny_t = lna + bt

Обозначим

y_t¹= lny_ta₁ = ln a

Линеаризованное уравнение имеет вид:

y_t¹= a₁ +bt.

Система нормальных уравнений:

y_t¹= a₁n + bt

y_t¹t= a₁t + bt²

где n = 12 (объем выборочной совокупности) Определим все суммы, включенные в систему нормальных уравнений. Результаты вычислений в таблице 4.

прогнозирование продажа доверительный интервал Таблица 4

Таким образом,

Подставим полученные результаты в систему:

45,04 = 12a₁+ 78b

328,61 = 78a₁ +650b

И найдем константыa₁ и b

50,555=45,04−78b+100b

5,515=22b

b=0,25

Так как

a₁ =lna, то

lna=2,13

a=e^2,13

Таким образом,

a?8,4 и b=0,25

Следовательно, уравнение прогноза имеет вид:

Зная параметры уравнения тренда, можно определить расчетные значения переменной yдля всех месяцев предпрогнозного периода. Так расчетная величина (t= 1) составляет:

y_t=1 = 8,4*e^0.25*1 = 10,75

Результаты вычислений по всем месяцам приведены в табл. 4.

2. После того как мы получили прогнозирующую функцию, можно прогнозировать развитие процесса в будущем. Для этого подставим в полученное уравнение функции значения t = 13, 14, 15.

тыс.руб тыс. руб тыс.руб

3. Рассчитаем правильность подбора прогнозирующей функции (в нашем случае — экспоненциальной кривой), сравнив её с другой прогнозной функцией — прямой линией.

3.1 Линейная функция дана формулой

y_t =a+bt

система нормальных уравнений:

y_t=na + bt,

y_tt =at + bt²

Определим все суммы, включенные в систему нормальных уравнений. Результаты вычислений в таблице 5.

Таблица 5

Решим полученную систему линейных уравнений:

740=12a + b78,

5854 =a78 + b650

b=7,3

a=14,22

Линейный тренд имеет вид:

= 14,22 + 7,3Чt

Таким образом, искомые уравнения тренда:

Экспоненциальная функция:

= 8,4Чe^0.25Чt

Линейная функция:

= 14,22+7,3Чt

3.2 Вычислим значение средней арифметической y_ср:

y_ср = = 740: 12= 61,67

Рассчитаем статистические показатели, для чего промежуточные данные вычислений (для суммарных значений) запишем в табличной форме:

Таблица 6

3.3 Вычислим значения у²_ост, у_{ост ,}V:

Для экспоненциальной функции:

у²_ост= =8115,01: 12 = 676,25;

у_ост = = 26;

V= ()* 100%= 26/61,67*100% = 42,16

Для линейной функции:

у²_ост = 656,73:12 =54,73;

у_ост== 7,398;

V=7,398/61,67*100% =11,97

Сравнив эти три показателя между собой мы видим, что для линейной функции они значительно меньше, чем для экспоненциальной. Следовательно, линейная функция в нашем случае лучше подходит для уравнения прогноза.

3.4 Чтобы вычислить индекс корреляцииR_y/t, необходимо вычислить общую дисперсию у²_общ по формуле:

у²_общ= =8278,68: 12 =689,89

Причем она одинакова для любой прогнозирующей функции (в нашем случае — для экспоненциальной и линейной).

Рассчитаем значение индекса корреляции R_y/t:

Для экспоненциальной функции:

Ry/t = = 0,14

Для линейной функции:

Ry/t = = = 0,96

Чем больше индекс корреляции, тем сильнее взаимодействие между переменными tи y_t .Как видно значение индекса корреляции для линейной функции приближается к 1, т. е. очень высоко, что указывает на значительную тесноту связи между переменными. Для экспоненциальной функции оно достаточно низкое. Таким образом, и по критерию корреляции линейная функция подходит больше, нежели экспоненциальная.

3.5 Для определения возможной ошибки прогноза доверительные интервалы для индивидуальных значений объема продаж в 13,14 15 месяца рассчитываются по формулам:

y_t^{в (н)} = ± ?t ,

где y_t^{в (н)} — максимально (в) и минимально (н) возможные значения объема продаж в момент времени t,

?t= t_ТD_ост,

t_Т — табличное значение t-критерия Стьюдента.

D_ост — остаточное среднее квадратическое отклонение:

D_ост =,

N — количество констант в уравнении прогноза.

a. Рассчитаем доверительный интервал для прогнозного значения объема продаж на момент времени t = 13

Перейдем к переменной

t₁=lnt= 2,565;

t_1ср= = 19,987/12 = 1,67

(t_1ср)² = 2,79

t₁²/n =39,575/12 = 3,30

Вычислим D_ост — остаточное среднее квадратическое отклонение:

D_ост = = 28,487, где

Nколичество констант в уравнении прогноза (в нашем случае их 2 — a и b).

t_Т — табличное значение t-критерия Стьюдента. Определяется по таблице для параметра k = n-2 и доверительной вероятности 0,99;

t_Т = 3,17 для k = 12−2=10 и P = 0,99

Определим верхние и нижние значения доверительного интервала:

y₁₃^в = 216,64 + 3,17*28,487 v (2,565−1,67)²/(12*(3,30−2,79)) + 1/12 +1 =

216,64 + 99,515 = 316,155

y₁₃^н = 216,64 — 3,17*28,487 v (2,565−1,67)²/(12*(3,30−2,79)) + 1/12 +1 =

216,64 — 99,515 = 117,124

Точно также вычисляются значения доверительных интервалов для

t = 14 и t = 15

b. для t = 14

Перейдем к переменной

t₁=lnt= 2,639;

y₁₄^в = 278,21 +100,418 = 378,628

y₁₄^н = 278,21 — 100,418 = 177,792

c. для t = 15

t₁= lnt = 2,708

y₁₅^в = 357,17 + 189,957 = 547,127

y₁₅^н = 357,17 — 189,957 = 167,213

На основании этих вычислений можно с вероятностью 0,99 утверждать, что объем продаж в 13-й месяц будет находиться в интервале 117,12 — 316,16; в 14-й месяц объем продаж будет находиться в интервале 177,79 — 378,63; в 15-й месяц объем продаж будет находиться в интервале 167,21 — 547,13.

Часть 2

По данным, характеризующим изменение объема продаж (таблица 2), требуется выполнить следующие задания, используя программу Excel:

1. Построить графики исходной кривой, трехчленной скользящей средней, выбрать линию тренда, указать уравнение этой функции В таблице 7 представлены данные для построения графика объема продаж.

Для того, чтобы построить графики, используя программу Excel, необходимо в документе Excel ввести столбец, А — месяцы (1−24), ввести столбец B — объем продаж, в столбце С посчитать скользящие средние. Затем щелкнуть на кнопке Мастер диаграмм, расположенной на стандартной панели инструментов.

Таблица 7

Используя ряды данных А, В и С можно построить График. Чтобы построить Линию тренда, необходимо выделить ряд данных диаграммы, а затем выбрать команду Вставка и Линия тренда. Для того, чтобы вывести на график уравнение тренда, необходимо в меню Линии тренда в параметрах отметить пункт показывать уравнение на диаграмме.

На рисунке 3 представлены графики исходной кривой, трехчленной скользящей средней, линия тренда, указано уравнение этой функции.

Наибольшую достоверность аппроксимации даёт полиномиальная линия со степенью шесть (R² = 0,4346), которую и выбираем в качестве линии тренда.

Рис. 3

2. Используя функции ТЕНДЕНЦИЯ или РОСТ построить прогнозирующую функцию В нашем случае линия, отражающая зависимость объема продаж от времени, резко изгибается в одном из направлений, — это означает, что взаимосвязь показателей носит нелинейный характер.

В случае нелинейной взаимосвязи функция Exel РОСТ поможет получить более точный прогноз.

Чтобы использовать функцию РОСТ, необходимо выделить ячейки D2: D25 и ввести следующую формулу, используя формулу массива:

= РОСТ (В2:В25;А2:А25)

Для ввода формулы массива надо нажать комбинацию клавиш .

Рис. 4

3. Используя функции программы Excel, посчитать доверительные интервалы для 25-ого месяца Вычислим по формуле прогнозирующей функции для t = 25

y₂₅ = 844,23

Доверительные интервалы для индивидуальных значений объема продаж рассчитываются по формуле:

y_t^{в (н)} = ± ?t ,

то есть необходимо посчитать? t.

Для того, чтобы посчитать доверительные интервалы, воспользуемся функцией ДОВЕРИТ из программы Exel. Формат функции ДОВЕРИТ записывается следующим образом:

ДОВЕРИТ (альфа; стандартное отклонение; размер),

ь где (1- альфа) — значение вероятности, с которой значение y_t+1 попадет в доверительный интервал, для нашего примера P = 0,99 следовательно 1 — альфа =0,99; альфа = 0,01;

ь стандартное отклонение — это у_общ, где

у²_общ =

— общая дисперсия, учитывающая отклонения исходных значений y_tот средней арифметической y_ср.

ь размер — это размер выборки.

Вычислим

y_ср = = 10 758: 24= 448,25;

у²_общ = = 661 546,9 /24= 27 564,45

у_общ = v27564,45 = 166,02

n = 24

В ячейке вводим функцию ДОВЕРИТ (0,01; 166,02; 24);

В результате? t =95,17

Тогда

y_t^{в (н)} = 844,23 ±95,17 ,

= 844,23 + 95,17 = 939,4,

= 844,23 — 95,17 = 749,06.