Модели для прогнозирования экономических показателей с помощью статистических функций MS Excel

Функции «ПРЕДСКАЗ», «РОСТ» и «ТЕНДЕНЦИИ» - встроенные статистические функции MS Excel, используемые для прогнозирования на основе заданных временных рядов и рядов динамики.

Функция «ПРЕДСКАЗ» - это встроенное уравнение регрессии линейного вида y = a + bx. Ее синтаксис имеет вид:

ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x)

Функция «Рост» представляет собой уравнение регрессии вида y = b*m^x. Ее синтаксис имеет вид:

РОСТ(известные_значения_y; известные_значения_x; новые_значения_x; конст)

Функция «Тенденция» представляет собой линейный тренд вида y = b+mx. Синтаксис этой функции имеет вид:

ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x; новые_значения_x; конст),

где x - точка данных, для которой предсказывается значение;

известные_значения_y - зависимый массив или интервал данных.

известные_значения_x - независимый массив или интервал данных.

новые_значения_x -новые значения x, для которых рассчитываются соответствующие прогнозные значения y.

конст - логическое значение, которое указывает, должна ли константа b в уравнениях регрессии равняться 1.

Методика применения всех трех функций одинакова. Для этого:

а) создается таблица с исходными данными (таблица 1);

б) определяется период времени, на который предполагается провести прогноз;

в) создается таблица для величин прогнозируемых показателей (табл. 2);

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

Математическая и компьютерная модели для прогнозирования….

Для прогнозирования с помощью моделей временных рядов и рядов динамики необходимо строить математическую модель, поскольку ее алгоритмы уже встроены в MS Excel в виде статистических функций. В MS Excel имеются три статистических функции, которые могут быть использованы для прогнозирования. Однако две из них («ПРЕДСКАЗ» и «ТЕНДЕНЦИЯ») в принципе идентичны и выполняют расчеты по одному и тому же алгоритму линейной парной регрессии (методом наименьших квадратов).

Поэтому в компьютерную модель достаточно включить две функции («ПРЕДСКАЗ» и «РОСТ»).

Методика создания компьютерной модели состоит в следующем:

- создается таблица с исходными данными (табл.1);

- создается таблица-шаблон для расчетных показателей (табл.2);

- в ячейку таблицы 2 для первой пары зависимостей используя мастер функций и адреса массивов ячеек 1-й таблицы функции «ПРЕДСКАЗ» и «РОСТ»;

- эти функции копируются в ячейки для других пар зависимостей.

Рассмотрим методику разработки компьютерной модели для прогнозирования с помощью временных рядов для регионов Российской Федерации на примере Республики Дагестан.

В качестве зависимых показателей выбраны валовой региональный продукт (зависимая переменная y, млрд. руб.), объем инвестиций (x1, млрд. руб.) численность занятых в экономике (x2, тыс. чел.) и стоимость основных фондов (x3, млрд. руб.) за 2002-2009гг.

Независимым показателем (показателем-фактором) является время (t, годы), который принимает значения от 1 до 8 (t = 1,2,…,8 соответственно 2002, 2003,…, 2009гг.).

Чтобы провести прогноз с помощью временных рядов должен быть задан интервал времени, на который предполагается провести прогноз. При этом прогнозный период не может быть больше одной трети от числа уровней статистической совокупности. В нашем случае число уровней равно 8 (количество лет в статистической совокупности).

Следовательно, число лет прогнозируемого периода равен 2-3 (8:3).

Примем численные значения t на прогнозируемый период равными 9, 10 и 11 (соответственно 2010, 2011, 2012гг). Исходные данные, необходимые для прогнозирования, приведены в таблице 10.

Таблица 10

Исходная таблица

Рис.3. Динамика роста ВРП, инвестиций, стоимости основных фондов и численности занятых в экономике Республики Дагестан за 2002-2009 гг.

Создаем пустую таблицу 11 для рассчитываемых прогнозируемых значений зависимых показателей.

Таблица 11

Таблица-шаблон для прогнозируемых значений показателей

Таблица 11 заполняется следующим образом:

- выделяем массив для расчета прогнозируемых значений для валового регионального продукта;

- запускаем инструментарий «Мастер функций» и из группы статистических функций выбираем функцию «ПРЕДСКАЗ»;

- в появившемся окне «ПРЕДСКАЗ» в качестве известные значения выбираем данные по ВРП за 2002-2009гг., известных значений x – данные t = 1, 2,…, 8, а в качестве новых значений x данные t = 9,10, 11 + ok;

- нажимаем клавишу F2 (режим редактирования), а затем комбинацию клавиш Shift + Contr + Enter. В результате в выделенном массиве для прогнозируемых значений буду выведены прогнозируемые значения ВРП.

Установив функции «ПРЕДСКАЗ», введенный в ячейки для ВРП за 2010-2012гг. абсолютные ссылки для заданных значений t и новых значений t, можно эту функцию копировать в ячейки за 2010-2012 гг. для инвестиций, численности занятых и стоимости фондов.

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

Компьютерная модель готова.

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

Таблица 12

Отчетная таблица с прогнозными значениями показателей для Республики Дагестан

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

Эту модель можно использовать для прогнозирования по федеральным округам и по стране в целом.

Более обоснованные прогнозные значения для ВРП можно рассчитать с помощью рядов динамики «ВРП – инвестиции», «ВРП – численность занятых в экономике». «ВРП – стоимость основных фондов». Наличие зависимости ВРП от каждого из трех указанных показателей иллюстрируют графики точек рассеивания, которые являются частью компьютерной модели.

Рис.4. Графики точек рассеивания для рядов динамики: «ВРП от инвест» (а),

«ВРП от числ» (б), «ВРП от инвес» (в)

Методика аналогично методике прогнозирования с помощью временных рядов. Отличие состоит в том, что в качестве показателя-фактора выступает не время t = 1, 2,…, 8, а объем инвестиций (x1), численность занятых в экономике (x2) и стоимость основных фондов (x3).

Отличия эти наглядно видны из таблицы 13, создаваемые для расчетных показателей.

Таблица 13

Таблица-шаблон

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

Функции «ПРЕДСКАЗ» и «РОСТ» вводятся в ячейки за 2010-2012гг. для зависимости «ВРП от инвестиций». Затем эти функции копируются для двух других зависимостей. В результате формируется таблица 14.

Таблица 14

Отчетная таблица

до

Первый способ

Второй способ