Работа с процессорами Word и Excel при создании технических текстов. Однофакторная модель прогноза уровня спроса на выпускаемую продукцию: Методические указания к лабораторным работам, страница 5

Для получения значений коэффициентов модели при известных базисных функциях jj(t) применяют обычные методы математического анализа. Условием минимума критерия F является

.

В экономико-математических исследованиях наибольшее применение получили следующие виды однофакторных моделей:

линейная     ym(t) = b0 + b1t;                                                                         (2)

полиномиальная   ym(t) = b0 + b1t + b2t2 +…+ bktk,  где jj(t) = tj;                (3)

экспоненциальная      .                                                        (4)

Модель считается известной, если определены значения bj. Полученную модель можно использовать для вычисления значений ym(t), выходящих за пределы наблюдаемого периода Tн. При этом выдвигается гипотеза о том, что будущее является продолжением настоящего при сохранении стабильности существующих тенденций. Погрешность при составлении прогноза на будущее обычно оценивают величиной стандартного отклонения Sy в наблюдаемом периоде, определяемого по формуле

.                                                                          (5)           

Считая, что ошибка прогнозирования подчиняется нормальному закону распределения, можно утверждать, что с вероятностью P=0,95 ожидаемое значение ym(t) будет находиться в интервале (ym(t)+2*Sy, ym(t)-2*Sy). Величину относительной ошибки аппроксимации оценивают по формуле

%,                                                                (6)

где e - средняя относительная ошибка аппроксимации опытных данных сглаживающей кривой ym(t). Обычно задаются допустимой ошибкой аппроксимации, не превышающей 10 %. Интервал прогноза выбирают порядка 20-30 % от длительности наблюдаемого периода Тн. Прогнозируемые значения ym(t) необходимо по истечении времени прогноза сравнивать с фактическими данными и, если возникает необходимость, производить корректировку прогнозирующей функции.

Выбор конкретного типа модели обусловлен характером экспериментальных данных за период Тн либо последовательным увеличением степени полинома модели (3) до тех пор, пока относительная ошибка аппроксимацииe  станет меньше 5-10 %.

Пример применения табличного процессора Excel

Пусть предприятие выпускает некоторую продукцию. Объем продаж продукции за 12 предшествующих месяцев (наблюдаемый период Тн) в тоннах показан в табл. 1.

                                                                                                    Таблица 1

I

1

2

3

4

5

6

7

8

9

10

11

12

ti

1

2

3

4

5

6

7

8

9

10

11

12

y(ti)

10

10,2

10,4

10,8

11,4

12

12,8

13,6

14,3

15,1

15,8

16,4

Для изучения выберем полиномиальную и экспоненциальную модели. Полиномиальная модель третьего порядка имеет вид

ym(t) = b0 + b1t + b2t2 + b3t3 .                                                                 (7)

Требуется найти значения коэффициентов b0, b1, b2, b3, доставляющих минимум функционалу

.

Воспользовавшись методом наименьших квадратов, получим систему уравнений:

 ,

,              (8)

,        

 .

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

 ,                                                           (9)

где Т1,Т2,…,Т6,Y,YT1,YT2,YT3 – значения сумм, фигурирующих в системе уравнений (8). Для решения данной системы уравнений выбран метод Гаусса, рассмотренный в лабораторной работе № 3.

Для получения прогнозирующей однофакторной модели требуются следующие операции.

1.  Привести систему уравнений (8) к матричному виду (9).

2.  Решить систему уравнений (9).

3.  По формуле (7) вычислить значения ym(ti) для ti=1,2,…,15 и поместить их в таблицу.

4.  Вычислить величину стандартного отклонения Sy и оценку точности аппроксимации соответственно по формулам (5) и (6).

5.  Поместить график y(ti) и график ym(ti) на одной диаграмме.

Выполнение этих операций хорошо вписывается в возможности табличного процессора Exсel. Отметим, что выполнение п. 2 требует применения макроса и приложения Visual Basic.

Для вычисления значений элементов временных рядов и соответствующих им сумм, входящих в систему уравнений (8), воспользуемся столбцами A, B, C ,D, E, F и H, I, J, K (см. табл. 2 и 3).         В ячейки второй строки введем их имена t, t^2, … , t^6 и y(t),                   y(t)*t,y(t )*t^2, y(t)*t^3. Так как n=12, то в строки с 3 по 14 столбцов А и Н занесем значения t1, t2, …, t12 и y1, y2, …, y12 из таблицы. Значение элементов остальных столбцов системы уравнений (8) являются производными от значений элементов столбцов А и Н. Для их вычисления в ячейку В3 введем формулу =А3*А3, в ячейку С3 - =В3*А3, в ячейку I3 - =Н3*А3, в ячейку J3 - =Н3*В3 и т.п. Распространим формулы строки 3 на строки 4,5,…,14. Для этого необходимы следующие операции: выделить  ячейки с А3 по F3, подвести курсор к правому нижнему углу ячейки F3, нажать левую клавишу мыши (курсор из белого “жирного” крестика превращается в “тощий” черный) и, не отпуская ее, тянуть за этот уголок до строки 14. Те же операции проделать и со столбцами H, I, J, K. В результате в соответствующих столбцах появятся значения элементов временных рядов. Значение суммы любого из указанных столбцов можно получить, нажав кнопку å на стандартной панели, и записать ее в соответствующий столбец строки 15 (табл. 2 и 3).

В ячейки А18:D18 введем текст “Исходная матрица уравнений”. Для удобства выполнения операции 1 сгруппируем полученные значения сумм столбцов в одном месте, а именно в ячейках А19:D22 и F19:F22. В ячейку Е22 введем число 3, соответствующее четырем уравнениям системы (9)  (табл. 4).

                                                                                                            Таблица 2