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

4.  Степенная                                       y = c x^b

5.  Экспоненциальная                          y = c e^{b x}

Линию тренда в Excel можно добавить к любому ряду данных на графике, при изменении значений любых точек ряда данных линия тренда автоматически пересчитывается и обновляется на диаграмме. Кроме того, на диаграмме можно отобразить уравнение регрессии и величину достоверности аппроксимации в виде квадрата коэффициента корреляции R². Чем ближе величина R² к 1, тем точнее достоверность.

Применение того или иного типа аппроксимации определяется из априорных знаний о законах поведения экономических систем. Если эти законы неизвестны, то можно выбрать тот тип аппроксимации, для которого величина R² максимальна.

Построение линии тренда целесообразно выполнять в следующей последовательности:

1.  Вначале с помощью Мастера диаграмм построить и отформатировать точечный график (этот вид диаграмм наиболее точно отражает зависимость одной величины от другой).

2.  Выделить диаграмму и выполнить команду Диаграмма-Добавить линию тренда. На вкладке «Тип» диалогового окна «Линия тренда» выбрать необходимый тип аппроксимации, а на вкладке «Параметры» можно установить параметры тренда: название линии тренда в легенде, показывать уравнение линии тренда на диаграмме, добавить величину достоверности R² и др.

3. 

Подтвердить выбранные параметры (кнопка «ОК») – на диаграмме помимо исходного графика появится график линии тренда, уравнение линии и величина достоверности аппроксимации (рис. 3).

Рис. 3. Построение линий тренда

На рис.3 для сравнения представлены две линии тренда – одна с линейной аппроксимацией, другая – со степенной. Для решения задачи прогнозирования целесообразно выбрать тот тренд, у которого величина достоверности R² больше, в данном случае тренд со степенной аппроксимацией .

Для решения задачи прогнозирования нужно выполнить следующие действия:

1. На рабочем листе Excel определить ячейку (например, K2) с изменяемым параметром Q и ввести в нее любое положительное число (в данном случае это требование степенной функции).

2. Определить ячейку (например, K3), в которую записать формулу (=16,738·K2^ – 0,5154), определяющую зависимость цены спроса D от количества выпускаемых товаров Q.

3. Выделить ячейку с формулой и выполнить команду Сервис-Подбор параметра… В окне «Подбор параметра» в поле «Значение» установить требуемое значение цены D, равное 1, в поле «Изменяя значение ячейки» ввести адрес ячейки со значением Q и подтвердить выбор кнопкой «ОК». В ячейке K2 появится

прогнозируемое значение спроса, равное 236 при цене 1 грн. (рис. 4).

Рис. 4. Решение задачи прогнозирования

3. Определение равновесной точки спроса и предложения

Пусть в ходе анализа на одном из рынков города получены данные спроса D и предложения S на некоторый товар.

Необходимо определить равновесную точку Е спроса и предложения на рынке товара, при которой цена спроса D и цена предложения S равны.

Решение поставленной задачи может быть осуществлено с использованием рассмотренных выше методик.

1. Вначале необходимо определить аналитические зависимости для спроса ТрендD(Q) и предложения ТрендS(Q) (аппроксимировать экспериментальные данные линями тренда). Это можно сделать, применив рассмотренную ранее методику построения линий тренда.

2. Далее целесообразно ввести новую функцию F(Q)=ТрендS(Q) – ТрендD(Q). В точке E, где функции ТрендS(Q) и ТрендD(Q) пересекаются, функция F(Q) равна 0. Следовательно, для нахождения равновесной точки достаточно найти корень Q_E  уравнения F(Q) = 0.

3. На рабочем листе Excel выделить несколько ячеек, в одну из них, например С2, ввести изменяемый параметр Q, в другую (С3) ввести формулу для ТрендS(Q), в третью (С4) – формулу для ТрендD(Q), и наконец, в четвертую (С5) –формулу для функции F(Q) = ТрендS(Q) – ТрендD(Q).

4. Выполнить команду Сервис-Подбор параметра… и установить соответствующие значения в окне диалога «Подбор параметра» в поле «Значение» установить 0 для ячейки С5, в поле «Изменяя значение ячейки» ввести адрес ячейки с изменяемым параметром (С2) и подтвердить выбор кнопкой «ОК», получить характеристики равновесной точки: равновесный объем товара Q_E, и равновесная цена P_E (ячейки С3, С4).