УДК 681.3

Прогнозирование в MS Excel на основе разных математических моделей

Антипова Татьяна Николаевна – доцент кафедры Высшей математики МИРЭА – Российского технологического университета.

Аннотация: Рассматриваются вопросы статистического прогнозирования исследуемого показателя, на основе разных математических моделей, в табличном процессоре Excel. Для оценки основной компоненты используются линейная и адаптивная модели. В MS Excel для каждой модели проектируются электронные таблицы, которые используются для расчета параметров модели, анализа ее качества и нахождения точечных и интервальных оценок прогнозируемого показателя. На основании информации, полученной для каждой из математических моделей, строятся сводные таблицы, c помощью которых выбирается наиболее подходящая в данных условиях модель. Изложенный материал поясняется на конкретном примере. Предложенная работа позволяет студентам лучше освоить практическое применение методов математической статистики и математического моделирования, а также способствует успешному освоению табличного процессора MS Excel, который широко используется современными аналитиками. 

Ключевые слова: статистическое прогнозирование, линейная и адаптивная модели, электронные таблицы в Excel, точечные и интервальные оценки показателя.

Введение

В современном обществе большое внимание уделяется задачам прогнозирования. Решение таких задач можно выполнять методами статистического прогнозирования [1-5]. Процесс прогнозирования, опирающийся на такие методы, распадается на два основных этапа. Это обобщение данных, наблюдаемых за достаточно продолжительный период, и представление статистических закономерностей в виде модели, которая, как правило, выражается аналитической функцией. На практическую значимость и точность статистического прогноза сильно влияет выбор модели, используемой для оценки основной компоненты уровня. Нужная модель выбирается после оценки ее качества с статистической точки зрения, т.е. после исследования модели на адекватность и точность.

В статистическом прогнозировании информация о динамике показателей задается временными рядами (ВР) наблюдений, состоящими из N уровней Y_ф(t), t = 1,…,N. Формально задача прогнозирования сводится к получению оценок Y_р(t) уровней ВР на некотором будущем временном интервале, т.е. в моменты времени t = N+1,…,N+k. Такой процесс связан с многочисленными расчетами и пересчетами, которые можно реализовать с помощью пакета MatLab, а также специальных статистических пакетов, таких как Statistica, Statgraphics и других [6]. Однако это можно сделать без финансовых и временных затрат на приобретение и освоение специального программного обеспечения с помощью программы MS Excel (ТП Excel), которая широко используется для работы с электронными таблицами.

В данной работе рассматривается статистическое прогнозирование исследуемого показателя в MS Excel, с использованием, при необходимости, автоматизации расчетов и текстовых выводов. Для прогнозирования используются линейная и адаптивная модели. После нахождения уравнения модели осуществляется оценка ее качества и построение точечных и интервальных прогнозов. Это делается путем проектирования для каждой модели основных электронных таблиц (ЭТ) и ЭТ дополнительных имен. Основные таблицы нужны для вычисления параметров модели и нахождения ее уравнения; для анализа качества модели; для точечного и интервального прогнозирования с помощью построенной модели. В этих таблицах широко используется автоматизация расчетов по формулам и автоматизация получения текстовых выводов об уравнении модели и ее качестве. Автоматизация расчетов и текстовых выводов, а также связь между основными таблицами модели осуществляется с помощью ее таблицы дополнительных имен. Работа логических формул, используемых для анализа качества моделей и получения соответствующих выводов, реализуется с помощью таблицы дополнительных имен общей для всех моделей.

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

Далее описанный процесс поясняется на примере электронной книги, которая предназначена для прогнозирования на пять шагов вперед (k=5) показателей, заданных ВР, состоящими из девяти уровней, (N=9), и поясняется на примере ВР, в котором отражена динамика сбыта продукции предприятия за девять месяцев (таблица 1).

Таблица 1. Динамика сбыта продукции.

Прогнозирование на основе линейной модели

Прогнозирование на основе линейной модели (ЛМ) предполагает, что оценка прогнозируемого показателя Y_р(t) осуществляется по формуле (1):

Y_р(t) = a + b·t      (1)

В формуле (1) a и b – параметры модели. Они оцениваются по методу наименьших квадратов (МНК) [7-9], согласно которому  a =  Y_фср  – b·t_ср. Такая оценка параметров выполняется с помощью таблицы “Оценка параметров ЛМ” (таблица 2). После указания в столбце Y_ф(t) информации о прогнозируемом показателе вычисляются параметры модели b = 118/60 = 1,9667 и a = 79,1111 – 1,9667·5 = 69,2778. Затем находится уравнение ЛМ (2).

Таблица 2. Оценка параметров ЛМ.

Y_р(t) = 69,2778 + 1,9667·t      (2)

Качество модели определяется ее адекватностью и достаточной точностью. Модель считается адекватной, если ее ряд остатков E(t) обладает свойствами случайности и независимости последовательных уровней, а также, с надежностью близкой к единице, подтверждается гипотеза о нормальности распределения последовательных уровней этого ряда.

Оценка качества модели выполняется с помощью ЭТ “Проверка случайности и независимости” и “Проверка нормальности и точности” [3]. При проектировании этих таблиц для проверки случайности уровней ряда остатков используется критерий поворотных точек. Проверка независимости (отсутствия автокорреляции) осуществляется путем проверки отсутствия в ряде остатков систематической составляющей. Это делается с помощью d-критерия Дарбина-Уотсона. Соответствие ряда остатков нормальному закону распределения определяется при помощи R/S-критерия. Ряд остатков ЛМ (2) указан в столбце E(t) таблицы 2. Применение к нему перечисленных критериев, с надежностью p близкой к единице, указывает на адекватность модели.

Точность модели определяется средним квадратичным отклонением s (точнее его наилучшей статистической оценкой) и средней относительной ошибкой E_отн. Для ЛМ (2) E_отн = 1,5573% (менее 5%), что свидетельствует об удовлетворительной точности этой модели.

Таблица 3. Точечное и интервальное прогнозирование по ЛМ.

В таблице 3 указаны данные о точечном и интервальном прогнозировании (p = 0,7) на пять шагов вперед на основе ЛМ (3). Так как эта модель обладает свойствами адекватности и точности, то утверждение о том, что, при сохранении сложившихся закономерностей развития, результат прогноза попадет в интервал, образованный нижней и верхней границами, правомерно.

Прогнозирование на основе адаптивной модели

В прогнозировании, для лучшего учета особенностей изменения исследуемого показателя в конце интервала наблюдения, используются адаптивные модели. В таких моделях наибольшее значение при оценке параметров придается последним наблюдениям. Рассмотрим одну из таких моделей – модель Брауна (3), в которой k – число шагов прогнозирования,a(t – 1), b(t – 1) – параметры модели.

Y_р(t) = a(t – 1) + b(t – 1)·k      (3)

Корректировка a(t) и b(t), t = 1,2,…,N, осуществляется по формулам (4). 

a(t) = a(t – 1) + b(t – 1) + E(t)·(1-B²), b(t) = b(t – 1) + E(t)·(1-B²).      (4)

В этих формулах В – коэффициент дисконтирования данных, отражающий большую степень доверия к более поздним показаниям, E(t) – отклонение расчетного показателя от фактического, a(0), b(0) – начальные значения параметров модели.

Начальные значения параметров a(0) и b(0) являются базой для последующих уточнений. Они оцениваются по первым пяти известным уровням Y_ф(t), t = 1,2,…,5, при помощи МНК. Такая оценка выполняется в таблице 4. В результате получается b(0) = 15/10 = 1,5 и a(0) = 75,2 – 1,5·3 = 70,7. Затем эти оценки уточняются.

Таблица 4. Нахождение начальных значений параметров.

Предположим, что k = 1. Тогда значения прогнозируемого показателя вычисляются по формуле (5).

Y_р(t) = a(t – 1) + b(t – 1)      (5)

Пусть в формулах (4) B = 0,6, тогда с учетом формулы (5), получаются формулы (6), которые используются для корректировки значений параметров.

a(t) = Y_р(t) + E(t)·0,64, b(t) = b(t–1) + E(t)·0,16      (6)

Для уточнения текущих значений величин Y_р(t), a(t), b(t), t = 1,2,…,9, по формулам (5) и (6), используется таблица 5.

После уточнения значений параметров на последнем шаге, c учетом того, что a(9) = 88,0443, b(9) = 2,3171, находится соответствующее уравнение модели Брауна (7), в котором N = 9.

Таблица 5 . Уточнение параметров АМ.

Y_р(N + k) = 88,0443 + 2,3171·k (7)

Проверка качества модели осуществляется по критериям, указанным для ЛМ (2), на основании ряда остатков E(t) таблицы 5. Его исследование с надежностью близкой к единице указывает на адекватность полученной АМ Брауна. Эта модель имеет удовлетворительную точность, т.к. имеет следующие характеристики точности: S = 1,5649, E_отн = 1,4234%.

Данные о прогнозирование на основе АМ (7) приводятся в таблице 6.

Таблица 6. Точечное и интервальное прогнозирование по АМ.

Сравнительный анализ моделей

Результаты исследования качества ЛМ(2) и АМ(7) приводятся в таблице 7.

Таблица 7. Таблица качества моделей.

Результаты точечного и интервального прогнозирования (p = 0,7) приводятся в таблице 8.

Таблица 8. Таблица результатов прогнозирования.

В выводах, полученных на основании таблиц 7 и 8, указывается на то, что обе модели имеют практическую значимость, так как удовлетворяют свойствам точности и адекватности. Точечные прогнозные оценки моделей имеют явную близость. Характеристики точности лучше у линейной модели, поэтому для прогнозирования исследуемого показателя лучше использовать линейную модель. Дальнейший прогноз, на основании новых данных таблицы 1, может привести к другому результату.

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

Список литературы

1. Четыркин Е.М. Статистические методы прогнозирования. –М.: Статистика , 1977. – 200с.
2. Орлова И.В., Половников В.А., Федосеев В.В. Лекции по экономико-математическим моделированию. –М.: ВЗФЭИ, 2005.
3. Антипова Т.Н. Автоматизация экономического прогнозирования в ТП Excel: Учебное пособие. - М.: Изд.-во МГУПИ, 2008. –86 с.
4. Дуброва Т.А. Статистические методы прогнозирования в экономике: Учебное пособие / Дуброва Т.А., Архипова М.Ю. Московский государственный университет экономики, статистики и информатики. –М.,2004. –136с.
5. Федосеев В.В. Экономико-математические модели и прогнозирование рынка труда: Вузовский учебник. – Инфра-М, 2013.– 144с.
6. Боровиков В.П., Ивченко Г.И. Прогнозирование в системе STATISTICA в среде Windows . Основы теории и интенсивная практика на компьютере: Учебное пособие. –М.: Финансы и статистика, 2006. – 368с.
7. Г.Корн, Т.Корн. Справочник по математике. Для научных сотрудников и инженеров. – М.: Наука, 1977. –.832с.
8. Карасев А.И. Теория вероятностей и математическая статистика: Учебник для экономических специальностей вузов. – М.: Статистика, 1994.- 279с.
9. Гмурман В.Е.Теория вероятностей и математическая статистика: Учебное пособие. – М.: Издательство Юрайт, 2010. – 479с.