4. Оценим значимость уравнения регрессии.

Проверку значимости проведем на основе вычисления F-критерия Фишера:

Значение F-критерия Фишера можно найти в таблице «Дисперсионный анализ» протокола Excel (см. рис. 4).

Табличное значение F-критерия можно найти с помощью функции FPACПOБP(). При доверительной вероятности 0,95 при v₁=k = 2 и v₂ = п-к- 1 = 16-2- 1 = 13 оно составляет 3,81. Определите табличное значение F-критерия Фишера на листе протокола регрессионного анализа, напротив критерия Фишера, и сравните значения. Поскольку F_paсч > F_Ta6л, уравнение регрессии следует признать адекватным.

5. Оценим с помощью t-критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.

Значения t_aj-критерия для коэффициентов уравнения регрессии а₀, a₁ a₂ можно вычислить по формулам

Однако эти расчетные значения t-критерия Стьюдента для коэффициентов уравнения регрессии а₁, а₂ уже найдены при использовании надстройки Анализ данных и приведены в столбце D на рис. 4. Табличное значение t-критерия Стьюдента, необходимое для сравнения, найдите с помощью функции СТЮДРАСПОБР() при 5%-ном уровне значимости и 13 степенях свободы (16 – 2 - 1 = 13). Сравните его с абсолютной величиной рассчитанных программой значений.

Если то коэффициенты a₁, а₂ существенны (значимы). Сделайте вывод и запишите полученные значения в конспект лабораторной работы.

6. Оценим влияние факторов на зависимую переменную по модели.

Для каждого коэффициента регрессии вычислим коэффициент эластичности и бета-коэффициент соответственно по формулам, указанным ниже:

Для определения коэффициентов эластичности найдите необходимые средние значения у и х_j. Произведите эти расчеты, используя Excel, используя не цифры указанные выше, а ссылки на ячейки, где эти значения вычислены (коэффициенты с листа регрессионного анализа, а средние значения и среднеквадратические отклонения, получите сами на листе 2 с использованием функций СРЗНАЧ() и СТАНДОТКЛОН() для соответствующих столбцов у, х₁ и х₂.

Коэффициент эластичности Э₁ показывает, что при увеличении затрат на рекламу на 1% объем реализации увеличится на 0,29%, а коэффициент показывает на какую часть величины среднеквадратического отклонения S_у изменится зависимая переминая У, если соответствующая независимая переменная X, изменится на величину своего среднеквадратического отклонения при фиксированном значении остальных независимых переменных. Например, при увеличении затрат на рекламу на 4,913 тыс. руб. объем реализации увеличится. (0,457 ), что составляет 0,457 • 102,865=47 тыс. руб. Напомним, что коэффициент характеризует изменчивость результатов.

8. Определим точечные и интервальные прогнозные оценки объема реализации на два месяца вперед

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

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

Рис.5 Прогноз показателя «Затраты на рекламу»

Если для фактора Х₁ «Затраты на рекламу» выбрать модель которая получается по умолчанию, то прогноз будет неверен вследствие ошибок округления при коэффициентах с высокими степенями. Поэтому щелкните уравнение тренда и в формате подписей данных выберите формат числовой с 8-мью знаками после запятой.

На листе первом добавьте в ячейки B18, B19 номера прогнозируемых месяцев (17 и 18). Затем присвойте диапазону B2:B19 имя х. Затем в ячейку G2 скопируйте уравнение линии тренда с диаграммы и с корректируйте его

= -0,0005516*x^5 + 0,0291503*x^4 - 0,5514574*x^3 + 4,3189733*x^2 - 11,615648*x + 12,8307692

Численные значения, соответствующие последующим месяцам и прогнозируемому периоду получите, скопировав формулу в диапазон G3:G19.

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

Для получения экстраполяции индекса потребительских расходов постройте на первом листе точечный график и в качестве линии тренда выберите полином второго порядка (параболу). Затем в столбце F в диапазоне F2:F19 найдите теоретические значения индекса потребительских свойств и результаты прогноза на 17 и 18 месяцы.

Рис.6 Прогноз показателя «Индекс потребительских расходов, %»

по которой построен прогноз на два шага вперед.

Рис. 7 после добавления теоретических значений параметров

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

подставим в нее найденные прогнозные значения факторов X₁ и Х₂:

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

232

где (значе-

ние получено с помощью функции СТЬЮДРАСПРОБР (0,1; 13) для заданной вероятности 90% с числом степеней свободы, равным 13).

На второй шаг (l = 2):

На первый шаг (l = 1):

, Результаты прогнозных оценок модели регрессии при а = 0,1 представим в табл. 3.4.7.

Таблица 3.4.7

Упреждение	Прогноз	Нижняя граница	Верхняя граница
1 ' 2	355,399 344,179	273,94 261,71	436,85 426,65

Ответ. С вероятностью 90% объем реализации на следующие два месяца будет лежать в пределах, указанных в табл. 3.4.7.