А. Прогнозирование с помощью программы EXCEL по уравнениям трендов

1. Открыть файл, ввести необходимые данные

2. Для выявления взаимосвязей и зависимостей необходимо:

а) построить график, для чего выделить соответствующие столбцы в таблице, вызвать “ мастер диаграмм ”, с помощью открывшихся диалогов выбрать тип диаграммы, ввести название диаграммы, наименование показателей по осям X и Y, щелкнуть мышью по кнопке “ закончить”; изучить появившуюся на экране диаграмму и сделать вывод;

б) рассчитать коэффициенты корреляции между прогнозируемым показателем (например, среднесписочная численность за 2003 год) и временем, для чего вызвать “мастер функций”, найти статистическую функцию “ коррел”, ввести в открывшиеся окна значения Y среднесписочная численность работников производства; X- это значения времени, например, месяцы года - 1, 2, 3 и т.д.); для ввода значений Y и X необходимо выделить соответствующий столбец в таблице; завершить работу, щелкнув мышью по кнопке “ закончить”; аналогично рассчитывают коэффициенты корреляции между другими столбцами таблицы и временем, например между объемом строительного либо промышленного производства за 2003 год.

3. Определить параметры тренда, то есть найти значения коэффициентов a - отрезок и b - наклон, для чего поставить курсор на свободную ячейку, вызвать “мастер функций”, найти статистическую функцию “ отрезок ”, в открывшиеся окна ввести значения Y и X (напомним еще раз, что Y - это значения прогнозируемого показателя, например, среднесписочная численность работников основного производства; X - это значения времени, например, месяцы года - 1, 2, 3 и т.д.; если ошибочно ввести в качестве Y - значения времени, а в качестве X - значения объемов производства, то по данной модели можно будет прогнозировать изменения времени в зависимости от среднесписочная численность работников основного производства;, что является абсурдом); для ввода значений Y и X необходимо выделить соответствующий столбец в таблице; завершить работу, щелкнув мышью по кнопке “ закончить”. Выполнить аналогичные действия по определению значения “наклон”, для чего в “мастере функций” использовать функцию “наклон”. Напомним, что отрезок означает начало отсчета и показывает пересечение линией тренда оси ординат, а наклон измеряет, на сколько единиц изменяется прогнозируемый показатель за единицу времени (например, за 1 месяц).

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

=a0 + а1*t,

где a0 - значение отрезка, которое можно записать в виде числа (например, 6,17) или ввести его абсолютный адрес: например, $c$15;

а1 - значение наклона, которое также можно ввести или в виде числа (например, 19,5), или указать его абсолютный адрес: $c$16;

t - время, то есть месяц (год), на который прогнозируется данный показатель; напомним, что значение t на первый месяц, для которого прогнозируется данный показатель (например, объем промышленного производства), равно значению месяца, следующего за периодом, по которому определялись параметры модели: если отрезок и наклон определялись на основе предшествующих 12 месяцев 2003 г., то при прогнозировании на январь 2004 г. t = 13, на февраль t= 14, на март t = 15 и т.д.

После ввода формулы нажать клавишу Enter – так будет получен результат прогнозирования. Затем переместить курсор в следующую ячейку, соответствующую февралю 2004 г., и ввести формулу:

=адрес ячейки января + значение наклона.

Значение наклона можно ввести в виде числа или с использованием абсолютного адреса.

Адрес ячейки января должен вводиться в виде относительного адреса, то есть без символа $. Это необходимо для того, чтобы можно было скопировать значения данной ячейки на все последующие месяцы прогнозируемого года. Смысл этой формулы в следующем: в феврале ожидаемый объем промышленного производства будет больше (или меньше, если наклон имеет отрицательное значение) на величину наклона.

Следующим этапом является прогнозирование данного показателя на все другие месяцы текущего года, для чего пользуются процедурой заполнения смежных ячеек: установить указатель мыши на маленький черный квадрат в правом нижнем углу ячейки, в которой расположено значение прогноза на февраль, при этом указатель мыши примет форму крестика; теперь следует держать нажатой левую клавишу мыши и скопировать значения ячейки (протянуть) на все последующие месяцы. В результате этой операции при прогнозировании на март к прогнозному значению показателя в феврале прибавится значение наклона, при прогнозировании на апрель - к прогнозному значению марта прибавится значение наклона и т.д. Напомним, что наклон показывает, на сколько единиц изменится прогнозируемый показатель за месяц.

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

Расчеты по прогнозированию можно также выполнить, используя статистическую функцию “ предсказ”. Техника расчетов такова:

а) создать электронную таблицу данных, в которую введены: 1-й столбец - месяцы любого прошедшего года (например, 2002 г.), 2-й столбец - показатели, характеризующие прогнозируемый процесс в этом же году (например, среднесписочная численность работников основного производства;);

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

в) вызвать “ мастер функций”, затем статистическую функцию “ предсказ”, ввести в открывшиеся окна требуемые сведения: в первой строке - относительный адрес X - месяц, на который будет предсказываться объем производства (в данном случае - 13), во второй строке - столбец значений Y - прогнозируемый показатель, в 3-й строке - столбец значений X - месяцы прошедшего года (от 1 до 12). Завершить работу, щелкнув мышью по кнопке “ закончить”. Затем указать абсолютные адреса значений Y и X, используя символ $, и скопировать результаты прогнозирования в данной ячейке на все последующие. Автоматически будут выполнены расчеты на все последующие месяцы.

Кроме указанных выше, для определения параметров моделей можно использовать следующие функции: “ лгрфприбл” - для расчета параметров экспоненциального тренда, “ линейн” - для расчета параметров линейного тренда, “ рост” - для прогнозирования по экспоненциальному тренду. Техника прогнозных расчетов с использованием функции “ рост” полностью совпадает с расчетами по функции “ предсказ”.

Если используются ретроспективные расчеты для испытания моделей (например, на 2004 г.), то полученные прогнозные расчеты можно сопоставить с фактическими значениями и определить точность прогноза, вычисляя ошибку прогноза по формуле ошибки аппроксимации.

Аналогично могут быть рассчитаны средние ошибки прогноза (по модулю).

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

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