Расчет и оценка уравнения множественной регрессии средствами Excel

Построить модель множествен­ной линейной регрессии, которая позволит оценить объем товарооборота на ближайшую перспективу при заданных па­раметрах независимых переменных: «обо­рачиваемость товаров» и «удельный вес товаров с высо­кими торговыми надбавками». Исходные данные представлены в таблице 18.

Таблица 18 - Исходные данные

Выполнение:

При построении модели множественной регрессии целесооб­разнее обратиться к инструменту Excel Регрессия, который предлагает исчерпывающую статистическую информацию о ее параметрах и качестве. Порядок работы с инструментом Регрессия определяется соответствующим окном диалога. Его можно вызвать через команду Анализ данных из контекстного меню панели Сер­вис. Диалоговое окно Регрессия предлагает пользователю определиться с набором следующих параметров (рис. 32).

Рисунок 32 - Окно диалога «Регрессия»

Так, в первом разделе выходного массива «Регрессионная статистика» (см. ячейки А4:В8 табл. 19) приведены основные статистические характерис­тики общего качества уравнения: коэффициент множествен­ной корреляции R, коэффициент детерминации R², стандартная ошибка оценки. Значе­ние R², равное 0,892, свидетельствует о том, что на основе полученного уравнения регрессии можно объяснить 89,2 % вариации объема товарооборота.

Статистические характеристики второго раздела выход­ного массива «Дисперсионный анализ» (ячейки A10:F14) по­зволяют оценить меру разброса (дисперсию) зависимой пере­менной у и остаточной вариации (дисперсии) отклонений во­круг линии регрессии. Так, значение SS_р (ячейка С12) ха­рактеризует часть дисперсии, объясненную регрессией, а SS_о (ячейка С13) — часть дисперсии, не объясненной регрес­сией из-за наличия ошибок ε. При проведении регрессионно­го анализа особый интерес представляет изменение этих зна­чений по мере введения каждого регрессора. Качество моде­ли улучшится, если после введения в нее нового фактора зна­чение объясненной части дисперсии возрастет, а не объяс­ненной — снизится.

В ячейках D12:D13 отражены соответственно дисперсия исходного ряда (МS_p = SS_p / df, где df = k — см. ячейку В12) и несмещенная дисперсия остаточной компоненты (MS₀ = SS₀ / df, где df = п - k - 1 — см. ячейку В13).

В ячейке F12 второго раздела выходного массива приведен уровень значимости для оцененного F.Значения F-статистики (53,72) выглядит вполне допус­тимым, поскольку уровень значимости для нее (5,2•10⁷) ос­тается гораздо ниже 5%-ного предела, принятого для табличных F-статистик. Следова­тельно, есть основания ожидать, что F-наблюдаемое будет больше F_крит.

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

у = 71650,26 – 1098,94х₁ + 255,838х₂,

где х₁ — оборачиваемость товаров, дни; х₂ - удельный вес товаров с высокими торговыми надбавками, %.

Оценить значимость каждого параметра позволяют зна­чения t-статистики (см. ячейки D17:D19). Можно использо­вать приведенный в выходном массиве уровень значимости (см. ячейки Е17:Е19): если он не превышает 0,05 (т.е. 5%-ного уровня), то рассчитанные характеристики t-статистики будут больше табличного значения. Следовательно, статис­тическая значимость рассчитанных параметров уравнения весьма высока.

И, наконец, наряду с точечными значениями коэффици­ентов регрессии третий раздел выходного массива позволяет получить их интервальные оценки с доверительной вероят­ностью 95 % (см. ячейки F17:G19 табл. 19):

58598,85 < b < 84701,68; -1370,81 < m₁ < -827,08; 68,597 < m₂ < 443,079.

На основании изложенного можно с 95%-ной увереннос­тью утверждать, что параметры уравнения содержат информацию, значимую для расчета исследуемого показателя.

Таблица 19 - Регрессионный анализ

Список рекомендуемой литературы

Основная учебная литература

1. Курс лекций по экономико-математическим методам и моделям. – Могилев, МГУП, 2004.

2. Спирин А.А., Фомин Г.П. Экономико-математические методы и модели в торговле: Учебное пособие для экономических и товароведных факультетов торговых вузов. – М.: Экономика, 1988. – 149 с.

3. Экономико-математические методы и модели: Учебное пособие / Н.И. Холод, А.В. Кузнецов, Я.Н. Жихар и др. // Под общ. ред. А.В. Кузнецова. - Мн.: БГЭУ,1999. – 413 с.

Дополнительная литература

4. Багриновский К.А., Матюшонок В.М. Экономико-математические методы и модели (микроэкономика): Учебное пособие для вузов. – М.: Изд-во РУДН, 1999. – 183 с.: ил.

5. Балашевич В.А., Андронов A.M. Экономико-математическое моделирование производственных систем: Учебное пособие для вузов. – Мн.: Унiверсiтэцкае, 1995. – 240 с.

6. Бережная Е.В., Бережной В.И. Математические методы моделиро­вания экономических систем: Учеб. пособие. – М.: Финансы и статистика, 2001. – 368 с.: ил.

7. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. – СПб.: БХВ – Санкт-Петербург, 1999. – 336 с.

8. Кузнецов А.В. и др. Высшая математика: Математическое программирование: Учебник / Кузнецов А.В., Сакович В.А., Холод Н.И. // Под общ. ред. А.В. Кузнецова. – Мн.: Выш. шк., 1994. – 286 с.

9. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. – СПб.: ВНV–Санкт-Петербург, 1997. – 384 с., ил.

10. Лабскер Л.Г., Бабешко Л.О. Теория массового обслуживания в экономической сфере: Учебное пособие для вузов. – М.: Банки и биржи, ЮНИТИ, 1998. – 319 с.

11. Федосеев В.В., Эриашвили Н.Д. Экономико-математические методы и модели в маркетинге: Учебное пособие для вузов / Под редакцией В.В. Федосеева. – М.: ЮНИТИ-ДАНА, 2001. – 159 с.

12. Черняк А.А., Новиков В.А., Мельников О.И., Кузнецов А.В. Математика для экономистов на базе Mathcad. – СПб.: БХВ-Петербург, 2003. – 496 с.

13. Четыркин Е.М. Статистические методы прогнозирования. – М.: Статистика, 1977. – 184 с.

14. Шелобаев С.И. Математические методы и модели в экономике, финансах, бизнесе: Учеб. пособие для вузов. – М.: ЮНИТИ-ДАНА, 2000. – 367 с.

15. Экономико-математические методы и модели. Компьютерные технологии решения: Учебное пособие / И.Л. Акулич, Е.И. Велесько и др. – Мн.: БГЭУ, 2003. – 348 с.

16. Экономико-математические методы и прикладные модели: Учебно-методическое пособие для студентов II и III курса всех специальностей / Всероссийский заочный финансово-экономический институт: В.А. Половников, И.В. Орлова, А.Н. Гармаш, В.В. Федосеев. – М.: Финстатинформ, 1997. – 104 с.

Приложение А

Критические значения F-критерия (распределение Фишера)

В таблице приведены значения F-критерия при 5%-ном и 1%-ном уровнях значимости в зависимости от числа степеней свободы v₁ = k для столбца и v₂ = (n - k - 1) для строки.

Таблица А.1

Продолжение таблицы А.1

Приложение Б