eng Product-Costing

Строим регрессию

Уравнения регрессии могут быть найдены при помощи MS Excel, IBM SPSS Statistics и других программ.

Здесь будет приведен пример нахождения линейного тренда в MS Excel.

В Таблице 1 приведены имеющиеся данные о фактических продажах за 24 месяца: t - номер месяца (условный, начиная с первого), Q - количество проданных товаров. Требуется спрогнозировать объем продаж в 25-ом месяце.

Таблица 1. Пример данных о продажах за два года по месяцам

Эти же данные на графике выглядят так:

Рис. 2.4. Пример графика фактических продаж

Чтобы найти усредненную линию, в Excel используем функцию «Анализ данных», где из возможных вариантов анализа выбираем «Регрессия».

В качестве входного интервала Y указывается интервал ячеек, где перечислены значения объемов продаж; интервала X – интервал ячеек с номерами периодов; уровень надежности по умолчанию рекомендуется установить на уровне 0,95; в качестве выходного интервала указывается любое удобное место документа Excel, куда программа должна будет вывести результаты расчетов (рис. 2.5).

Рис. 2.5. Пример установления условий регрессионного анализа в Excel

Полученные результаты выглядят так:

Рис. 2.6. Пример результатов регрессионного анализа в Excel

Уравнение линейного тренда всегда следующее:

Q = a0 + a1 * t.

Результаты решения задачи всегда заключаются в получении значениях константы (a0), которая обозначена в Excel как «Y-пересечение» (ячейка E17) и коэффициента a1 («Переменная X1» – ячейка E18) для конкретных реальных данных.

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

Q = 25,200 + 44,900 * t.

Далее в столбце C находим значения Q, соответствующие найденному тренду (для этого следует в ячейку С2 внести формулу, как показано на рис. 2.7, и продлить ее по столбцу до ячейки C25).

Рис. 2.7. Пример нахождения значений объема продаж, соответствующих тренду

Далее можно наложить график тренда на график фактического объема продаж (рис. 2.8).

Рис. 2.8. Наложение графиков фактических объемов продаж (сплошная линия) и линии тренда (пунктирная линия)

Найденное прогнозное значение объема продаж на 25-ый период, находящееся на линии тренда, также показано на рис. 2.9.

Рис. 2.9. Результат прогнозирования объема продаж на основе линейного тренда в MS Excel

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