Прогнозирование принятия управленческих решений
Задание контрольной работы
Организации, занимающейся производством продуктов питания, необходимо
спланировать свою дальнейшую деятельность. Для этого, помимо всего прочего,
необходимо спрогнозировать объемы продаж своей продукции на весь планируемый
период времени. Исходными данными для прогноза служат накопленные данные по
продажам произведенных продуктов за предыдущие месяцы.
Часть 1
По данным, характеризующим изменение объемов продаж (Таблица 1):
1. Построить график изменения объема продаж во времени.
. Применить метод трехчленной скользящей средней.
. Построить систему нормальных уравнений и рассчитать константы
прогнозирующей функции.
. Определить наиболее вероятные объемы продаж в 13, 14, 15 месяцы.
5. Рассчитать возможные ошибки прогноза, определив доверительные
интервалы для индивидуальных значений объема продаж в 13, 14, 15 месяцы.
6. Построить графики скользящей средней и прогнозирующей функции
вида yt=f(t)
Таблица 1
Исходные данные для выполнения заданий
|
Месяцы
|
Объем продаж (тыс. руб.)
|
Месяцы
|
Объем продаж (тыс. руб.)
|
|
1
|
25
|
7
|
72
|
|
2
|
37
|
8
|
65
|
|
3
|
32
|
9
|
83
|
|
4
|
48
|
10
|
95
|
|
5
|
35
|
11
|
87
|
|
6
|
53
|
12
|
108
|
Прогнозирующая функция - экспоненциальная.
Сравниваемая прогнозирующая функция - линейная.
Часть 2
По данным, характеризующим изменение объема продаж (таблица 2), требуется
выполнить следующие задания, используя программу Excel:
1. Построить графики исходной кривой, трехчленной скользящей средней,
выбрать линию тренда, указать уравнение этой функции.
2. Используя функции ТЕНДЕНЦИЯ или РОСТ построить прогнозирующую
функцию.
. Используя функции программы Excel, посчитать доверительные интервалы для 25-ого месяца.
Таблица 2
Исходные данные для выполнения заданий
|
Месяцы
|
Объем продаж (тыс. руб.)
|
Месяцы
|
Объем продаж (тыс. руб.)
|
|
1
|
308
|
13
|
368
|
|
2
|
129
|
14
|
640
|
|
3
|
206
|
15
|
503
|
|
4
|
363
|
16
|
322
|
|
5
|
542
|
17
|
411
|
|
6
|
344
|
18
|
393
|
|
7
|
567
|
19
|
615
|
|
8
|
596
|
20
|
603
|
|
9
|
189
|
21
|
575
|
|
10
|
265
|
22
|
716
|
|
11
|
521
|
23
|
473
|
|
12
|
487
|
24
|
622
|
Решение
1. Построим
график изменения объема продаж во времени по данным таблицы 1. (Рис. 1)
Таблица 3
|
Месяцы, t
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
11
|
12
|
|
Объем продаж, V
|
25
|
37
|
32
|
48
|
35
|
53
|
72
|
65
|
83
|
95
|
87
|
108
|
|
Трехчленные скользящие средние
|
-
|
31,3
|
39
|
38,3
|
45,3
|
53,3
|
63,3
|
73,3
|
81
|
88,3
|
96,7
|
-
|
2. Применим метод трехчленной скользящей средней
Рис. 1. График изменения объема продаж во времени
Для этого вычисляем значения трехчленных скользящих средних по формуле:
=(yt-1+ yt+ yt+1)/3, t =
2, 3,…, (n - 1),
Например,
Расчеты
всех трехчленных скользящих средних представлены в табл. 3.
Рис.
2. Сглаживание ряда с помощью скользящей средней
1. Построим систему нормальных уравнений и рассчитаем
константы прогнозирующей экспоненциальной функции
Для построения системы нормальных уравнений необходимо линеаризовать
исходную функцию. С этой целью осуществляется замена переменных путем
логарифмирования (в нашем случае):
Логарифмируем
lnyt = lna + bt
Обозначим
yt1=
lnyta1 = ln a
Линеаризованное уравнение имеет вид:
yt1=
a1 +bt.
Система нормальных уравнений:
Syt1=
a1n + bSt
Syt1t= a1St + bSt2
где n = 12 (объем выборочной совокупности)
Определим все суммы, включенные в систему нормальных уравнений.
Результаты вычислений в таблице 4.
прогнозирование продажа доверительный интервал
Таблица 4
Месяцы Объем
продаж (yt) тыс. руб. 
|
  Экспоненциальный тренд тыс. руб.
|
|
|
|
|
|
1
|
25
|
0
|
0
|
1
|
10,75
|
|
2
|
37
|
3,61
|
7,22
|
4
|
13,86
|
|
3
|
32
|
3,47
|
10,41
|
9
|
18,23
|
|
4
|
48
|
3,87
|
15,48
|
16
|
22,83
|
|
5
|
35
|
3,55
|
17,75
|
25
|
29,32
|
|
6
|
53
|
3,97
|
23,82
|
36
|
37,63
|
|
7
|
72
|
4,28
|
29,96
|
49
|
48,3
|
|
8
|
65
|
4,17
|
33,36
|
64
|
62,08
|
|
9
|
83
|
4,42
|
39,78
|
81
|
79,72
|
|
10
|
95
|
4,55
|
45,5
|
100
|
102,31
|
|
11
|
87
|
4,47
|
49,17
|
121
|
131,38
|
|
12
|
108
|
4,68
|
56,16
|
144
|
168,67
|
|
Всего:
|
74045,04328,61650725,08
|
|
|
|
|
Таким образом,
Подставим
полученные результаты в систему:
,04
= 12a1+ 78b
,61
= 78a1 +650b
И
найдем константыa1 и b
,555=45,04-78b+100b
,515=22b=0,25
Так как
a1
=lna, то
lna=2,13
a=e2,13
Таким образом,
a≈8,4
и b=0,25
Следовательно, уравнение прогноза имеет вид:
Зная параметры уравнения тренда, можно определить расчетные значения
переменной yдля всех месяцев предпрогнозного
периода. Так расчетная величина (t= 1)
составляет:
yt=1 = 8,4*e0.25*1 = 10,75
Результаты вычислений по всем месяцам приведены в табл. 4.
2. После
того как мы получили прогнозирующую функцию, можно прогнозировать развитие
процесса в будущем. Для этого подставим в полученное уравнение функции значения
t = 13, 14, 15.
тыс.руб
тыс.руб
тыс.руб
3. Рассчитаем
правильность подбора прогнозирующей функции (в нашем случае - экспоненциальной
кривой), сравнив её с другой прогнозной функцией - прямой линией.
3.1 Линейная функция дана формулой
yt =a+bt
система нормальных уравнений:
ìSyt=na + bSt,
îSytt =aSt + bSt2
Определим все суммы, включенные в систему нормальных уравнений. Результаты
вычислений в таблице 5.
Таблица 5
|
Месяцы
|
Объем продаж (yt) тыс.руб.
|
ytt
|
Линейный тренд тыс.руб.
|
|
|
1
|
25
|
25
|
1
|
21,52
|
|
2
|
37
|
74
|
4
|
28,82
|
|
3
|
32
|
96
|
9
|
36,12
|
|
4
|
48
|
192
|
16
|
43,42
|
|
5
|
35
|
175
|
25
|
50,72
|
|
6
|
53
|
318
|
36
|
58,02
|
|
7
|
72
|
504
|
49
|
65,32
|
|
8
|
65
|
520
|
64
|
72,62
|
|
9
|
83
|
747
|
81
|
79,92
|
|
10
|
95
|
950
|
100
|
87,22
|
|
11
|
87
|
957
|
121
|
94,52
|
|
12
|
108
|
1296
|
144
|
101,82
|
|
Всего:
|
725,08
|
5854
|
650740,04
|
|
Решим полученную систему линейных уравнений:
î5854 =a78 + b650
b=7,3
a=14,22
Линейный тренд имеет вид:
= 14,22 + 7,3×t
Таким образом, искомые уравнения тренда:
Экспоненциальная функция:
= 8,4×e0.25×t
Линейная
функция:
= 14,22+7,3×t
3.2 Вычислим значение средней арифметической yср:
yср = 
= 740 :
12= 61,67
Рассчитаем статистические показатели, для чего промежуточные данные
вычислений (для суммарных значений) запишем в табличной форме:
Таблица 6
|
Месяцы
|
Объем продаж (yt)
|
Значения прогнозирующей функции
|
Значения ( yt- )2( yt- yср)2
|
|
|
|
Экспоненциальной, yt1
|
Линейной Экспоненциаль-ной, yt1Линейной
|
|
|
|
|
1
|
25
|
10,75
|
21,52
|
203,06
|
12,11
|
1344,69
|
|
2
|
37
|
13,86
|
28,82
|
535,46
|
66,91
|
608,61
|
|
3
|
32
|
18,23
|
36,12
|
189,61
|
16,97
|
880,31
|
|
4
|
48
|
22,83
|
43,42
|
633,53
|
20,98
|
186,87
|
|
5
|
35
|
29,32
|
50,72
|
32,26
|
247,12
|
711,29
|
|
6
|
53
|
37,63
|
58,02
|
236,24
|
25,2
|
75,17
|
|
7
|
72
|
48,3
|
65,32
|
561,69
|
44,62
|
106,71
|
|
8
|
65
|
62,08
|
72,62
|
8,53
|
58,06
|
11,09
|
|
9
|
83
|
79,72
|
79,92
|
10,76
|
9,49
|
454,97
|
|
10
|
95
|
102,31
|
87,22
|
53,44
|
60,53
|
1110,89
|
|
11
|
87
|
131,38
|
94,52
|
1969,58
|
56,55
|
641,61
|
|
12
|
108
|
168,67
|
101,82
|
3680,85
|
38,19
|
2146,47
|
|
Всего
|
740725,05740,04
|
8115,01
|
656,73
|
8278,68
|
|
|
.3 Вычислим
значения σ2ост ,σост ,V:
Для экспоненциальной функции:
σ2ост= 
=8115,01
: 12 = 676,25;
σост
= 
= 26;
V= (
)* 100%=
26/61,67*100% = 42,16
Для
линейной функции:
σ2ост
= 656,73:12 =54,73;
σост=
= 7,398;
V=7,398/61,67*100% =11,97
Сравнив
эти три показателя между собой мы видим, что для линейной функции они
значительно меньше, чем для экспоненциальной. Следовательно, линейная функция в
нашем случае лучше подходит для уравнения прогноза.
3.4 Чтобы вычислить индекс корреляцииRy/t, необходимо вычислить общую
дисперсию σ2общ по
формуле:
σ2общ= 
=8278,68
: 12 =689,89
Причем
она одинакова для любой прогнозирующей функции (в нашем случае - для
экспоненциальной и линейной).
Рассчитаем
значение индекса корреляции Ry/t:
Для
экспоненциальной функции:
Ry/t = 
= 0,14
Для линейной функции:
Ry/t
= 
=
= 0,96
Чем больше индекс корреляции, тем сильнее взаимодействие между
переменными tи yt .Как видно значение индекса
корреляции для линейной функции приближается к 1, т.е. очень высоко, что
указывает на значительную тесноту связи между переменными. Для экспоненциальной
функции оно достаточно низкое. Таким образом, и по критерию корреляции линейная
функция подходит больше, нежели экспоненциальная.
3.5 Для определения возможной ошибки прогноза доверительные интервалы для
индивидуальных значений объема продаж в 13,14 15 месяца рассчитываются по
формулам:
ytв(н)
= 
± ∆t ,
где ytв(н) - максимально (в) и минимально (н)
возможные значения объема продаж в момент времени t,
∆t=
tТDост
,
tТ - табличное значение t-критерия
Стьюдента.
Dост - остаточное среднее квадратическое отклонение:
Dост
=
,
N -
количество констант в уравнении прогноза.
a. Рассчитаем доверительный интервал для прогнозного значения объема
продаж на момент времени t = 13
Перейдем к переменной
t1=lnt= 2,565;
t1ср=
= 19,987/12 = 1,67
(t1ср)2 = 2,79
St12/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
Определим
верхние и нижние значения доверительного интервала:
y13в = 216,64
+ 3,17*28,487 √ (2,565-1,67)2/(12*(3,30-2,79)) + 1/12 +1 =
,64
+ 99,515 = 316,155
y13н = 216,64
- 3,17*28,487 √ (2,565-1,67)2/(12*(3,30-2,79)) + 1/12 +1 =
,64
- 99,515 = 117,124
Точно
также вычисляются значения доверительных интервалов для
t = 14 и t =
15
b. для t = 14
Перейдем к переменной
t1=lnt= 2,639;
y14в = 278,21 +100,418 = 378,628
y14н = 278,21 - 100,418 = 177,792
c. для t = 15
t1= lnt = 2,708
y15в = 357,17 + 189,957 = 547,127
y15н = 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
|
Месяцы
|
Объем продаж (тыс. руб.)
|
Скользящие средние
|
|
1
|
308
|
|
|
2
|
129
|
214,33
|
|
3
|
206
|
232,67
|
|
4
|
363
|
370,33
|
|
5
|
542
|
416,33
|
|
6
|
344
|
484,33
|
|
7
|
567
|
502,33
|
|
8
|
596
|
450,66
|
|
9
|
189
|
350
|
|
10
|
265
|
325
|
|
11
|
521
|
424,33
|
|
12
|
487
|
458,67
|
|
13
|
368
|
498,33
|
|
14
|
640
|
503,66
|
|
15
|
503
|
488,33
|
|
16
|
322
|
412
|
|
17
|
411
|
375,33
|
|
18
|
393
|
473
|
|
19
|
615
|
537
|
|
20
|
603
|
597,66
|
|
21
|
575
|
631,33
|
|
22
|
716
|
588
|
|
23
|
473
|
603,67
|
|
24
|
622
|
|
Используя ряды данных А, В и С можно построить График. Чтобы построить
Линию тренда, необходимо выделить ряд данных диаграммы, а затем выбрать команду
Вставка и Линия тренда. Для того, чтобы вывести на график уравнение тренда,
необходимо в меню Линии тренда в параметрах отметить пункт показывать уравнение
на диаграмме.
На рисунке 3 представлены графики исходной кривой, трехчленной скользящей
средней, линия тренда, указано уравнение этой функции.
Наибольшую достоверность аппроксимации даёт полиномиальная линия со
степенью шесть (R2 = 0,4346), которую и выбираем в
качестве линии тренда.
Рис. 3
2. Используя
функции ТЕНДЕНЦИЯ или РОСТ построить прогнозирующую функцию
В нашем случае линия, отражающая зависимость объема продаж от времени,
резко изгибается в одном из направлений, - это означает, что взаимосвязь
показателей носит нелинейный характер.
В случае нелинейной взаимосвязи функция Exel РОСТ поможет получить более точный прогноз.
Чтобы использовать функцию РОСТ, необходимо выделить ячейки D2:D25 и ввести следующую формулу, используя формулу массива:
= РОСТ (В2:В25;А2:А25)
Для ввода формулы массива надо нажать комбинацию клавиш <Ctrl+Shift+Enter>.
Рис. 4
3. Используя
функции программы Excel, посчитать
доверительные интервалы для 25-ого месяца
Вычислим
по формуле прогнозирующей функции для t =
25
y25 = 844,23
Доверительные интервалы для индивидуальных значений объема продаж
рассчитываются по формуле:
ytв(н)
= ± ∆t ,
то есть необходимо посчитать ∆t.
Для того, чтобы посчитать доверительные интервалы, воспользуемся функцией
ДОВЕРИТ из программы Exel.
Формат функции ДОВЕРИТ записывается следующим образом:
ДОВЕРИТ (альфа; стандартное отклонение; размер),
ü где (1- альфа) - значение вероятности, с которой значение yt+1 попадет в доверительный интервал,
для нашего примера P = 0,99
следовательно 1 - альфа =0,99; альфа = 0,01;
ü стандартное отклонение - это σобщ, где
σ2общ = 
общая
дисперсия, учитывающая отклонения исходных значений ytот
средней арифметической yср.
ü размер - это размер выборки.
Вычислим
yср = 
= 10758
: 24= 448,25;
σ2общ = =
661546,9 /24= 27564,45
σобщ = √27564,45
= 166,02
n = 24
В
ячейке вводим функцию ДОВЕРИТ (0,01; 166,02; 24);
В
результате ∆t =95,17
Тогда
ytв(н) = 844,23 ±95,17 ,
= 844,23
+ 95,17 = 939,4,
= 844,23
- 95,17 = 749,06.