Материал: 1263

y = 1,5384 Ln(x) + 8,867; R2 = 0,9724.

Здесь также возможен количественный прогноз (R2 > 97%). Далее в ячейку G2 запишем формулу из предположения №2

(рис. 20).

Рис. 20. Прогноз по второму предположению

Ожидаемая цена на следующий год по логарифмической зависимости = 11,62 тыс. руб.

Делаем третье предположение, считая, что искомая зависимость квадратичная, и выбираем: Полиномиальная степень 2

параметры поместить уравнение на диаграмме поместить на

30

y = 0,0571х2 +0,06929х + 8,08; R2 = 0,9892.

Здесь также возможен количественный прогноз (R2 = 98,9 %). Несмотря на очень высокий коэффициент детерминации,

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

Вывод: остается два варианта это прогноз по минимуму (логарифмический тренд) или по максимуму (линейный тренд). Какой выбрать? Минимум выбирается в том случае, если этот продукт производится и продается, а максимум выбирается тогда, когда этот продукт покупается.

Пример 2.

Пусть необходимо выявить основную тенденцию (тренд) фактического объема выпускаемой продукции за период с 2000 по 2004 гг. Данные примера (поквартальные) показаны на рис. 22. Наша задача спрогнозировать объем выпускаемой продукции на 2005 г.

Применяя алгоритм нахождения трендовой составляющей, можно найти уравнение регрессии и коэффициента детерминации это R2 на рис. 22, где показан наилучший, на наш взгляд, тренд

31

(полином третьей степени). В этом примере R2 = 26,7%, что очень мало для количественного прогноза. Тем не менее из этого анализа можно сделать качественный прогноз:

1)производство явно нестабильно;

2)наблюдается дальнейший спад производства.

Рис. 22. Таблица и тренд динамики выпуска продукции

В этом случае для количественной оценки прогноза применяется описательная статистика. Для вызова этой функции необходимо на панели меню выбрать команду: Сервис Анализ данных Описательная статистика. Затем заполняется диалоговое окно

Описательная статистика, как показано на рис. 23. Флажок Метки в первой строке устанавливается в том случае, если первая строка (столбец) во входном диапазоне содержит заголовок.

На рис. 24 показаны основные показатели описательной статистики. С уровнем надежности 95% можно утверждать, что средний объем выпуска продукции лежит в пределах математического ожидания М = 19,3 0,96.

32

Рис. 23. Заполнение диалогового окна Описательная статистика

Далее команда ОК, тогда получим рис. 24.

Рис. 24. Описательная статистика

33

Коэффициент вариации

свидетельствует о колебаниях объемов выпускаемой продукции выше допустимых. В строительном производстве коэффициент вариации не должен превышать 5%. Это еще раз подтверждает, что производство нестабильное и не следует ожидать в будущем показателя объема выпуска продукции более чем среднее значение (М 20 тыс. руб.).

4.2. Множественная регрессия

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

Уравнение линейной регрессии имеет вид

n

yb mixi.

i1

Для получения уравнения регрессии необходимо:

определить значения b, mi;

оценить достоверность полученных уравнений.

Ответы на эти вопросы можно получить с помощью функции Excel ЛИНЕЙН( ), имеющей формат

= ЛИНЕЙН(интервал значений У; блок значений хi; константа; статистика).

Здесь константа и статистика логические параметры, имеющие значения либо истина, либо ложь. Если константа = 1 (истина), то b 0, иначе b = 0. Если статистика = 1 (истина), то происходит оценка достоверности, иначе оценки нет.

Важно, что минимальное количество исходных данных для линейной зависимости определяется по формуле k = n + 2, где n количество искомых переменных.

Пример 3.

Определить зависимость между ценой, качеством и