Для 7 летних площадок предприятия общественного питания известны средние за день значения объема выручки (Y, руб.) и количества посетителей (Х, человек).
у 133000 150000 156000 195000 345000 365000 433000
х 95 145 165 233 333 350 456
При решении задачи каждый студент выполняет свой вариант. Номер варианта соответствует номеру в журнале группы. Данные следует подготовить следующим образом:
Y= у –1000*№ в журнале группы
X= х+ № в журнале группы
Пример. Вариант 27.
Y27 =133000-1000*27 =150000-1000*27 =156000-1000*27 =195000-1000*27 =345000-1000*27 =365000-1000*27 =433000-1000*27
X27 =95+27 =145+27 =165+27 =233+27 =333+27 =350+27 =456+27
Требуется решить в Excel
На основании данных, приведенных в таблице, постройте линейную регрессионную объема выручки (Y, руб.). Оцените параметры модели с помощью:
надстройки Excel Анализ данных, инструмент Регрессия;
надстройки Excel Поиск решения;
функции ЛИНЕЙН
матричных функций Excel по формуле;
по формулам:,.
Дайте экономическую интерпретацию параметрам модели. Отобразите на графике исходные данные и результаты моделирования.
Оценить качество модели регрессии. Оценить точность модели с помощью средней относительной ошибки аппроксимации.
С вероятностью 0,9 (α=0,1) дать прогноз относительно выручки новой площадки, если число посетителей составит 80% от максимального значения. Результаты моделирования прогнозирования отобразить на графике.
Решение
1. На основании данных, приведенных в таблице, постройте линейную регрессионную объема выручки (Y, руб.). Оцените параметры модели с помощью:
а) надстройки Excel Анализ данных, инструмент Регрессия
Оценить параметры модели с помощью:
надстройки Excel Анализ данных, используя инструмент Регрессия:
Данные записать в таблицу Excel.
Выбрать команду на вкладке Данные команда Анализ данных.
В диалоговом окне Анализ данных выбрать инструмент Регрессия.
В диалоговом окне Регрессия в поле Входной интервал Y ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал Х ввести адрес диапазона, который содержит значения независимой переменной (рис. 6).
Установить флажок Метки в первой строке для отображения заголовков столбцов.
Выбрать параметры вывода. В данном примере Выходной интервал $E$6.
В поле Остатки и График подбора поставить флажки.
ОК.
Результаты выполнения инструмента Регрессия получим в виде протокола (рис.1.). Протокол состоит из четырех таблиц: первая - Регрессионная статистика, вторая - Дисперсионный анализ, третья таблица без названия, в которой содержится информация о коэффициентах регрессии и четвертая, в которой содержатся предсказанные значения и остатки.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,981312659
R-квадрат 0,962974534
Нормированный R-квадрат 0,955569441
Стандартная ошибка 25985,23356
Наблюдения 7
Дисперсионный анализ
df SS MS F Значимость F
Регрессия 1 8,78E+10 8,7809E+10 130,04219 9,0774E-05
Остаток 5 3,38E+09 675232363
Итого 6 9,12E+10
Коэффициенты Стандартная ошиб t-статистика P-Значение Нижние 95% Верхние 95%
Y-пересечение -34005,95654 24894,78 -1,3659874 0,2301762 -98000,026 29988,1125
Индекс промышленного производства (%) 928,8106286 81,44887 11,4036043 9,0774E-05 719,439655 1138,1816
ВЫВОД ОСТАТКА
Наблюдение Предсказанное Остатки
1 79308,94015 26691,06
2 125749,4716 -2749,47
3 144325,6841 -15325,7
4 207484,8069 -39484,8
5 300365,8697 17634,13
6 316155,6504 21844,35
7 414609,5771 -8609,58
Рис.1. Фрагмент протокола выполнения регрессионного анализа.
Во втором столбце третьей таблицы отчета (Рис.1.) содержатся значения параметров уравнения регрессии
Модель .
Интерпретация параметров модели: 928.811 показывает, что при увеличении количества посетителей на 1 человека объем выручки в среднем увеличится на 928,81 руб. В скобках указаны стандартные ошибки коэффициентов регрессии.
надстройки Excel Поиск решения:
Согласно принципу метода наименьших квадратов оценки и находятся путем минимизации суммы квадратов суммы квадратов отклонений RSS по всем возможным значениям при заданных (наблюдаемых) Xи Y.Задача сводится к математической задаче поиска точки минимума функции двух переменных. Задача может быть решена с использованием надстройки Excel Поиск решения.
Поиск решения – это надстройка Excel, которая позволяет решать оптимизационные задачи.
В диалоговом окне Поиск решения есть три основных параметра:
• Оптимизировать целевую функцию.
• Изменяя ячейки переменных.
• В соответствии с ограничениями.
Рассмотрим технологию оценки параметров модели линейной регрессии зависимости стоимости ущерба, нанесенного пожаром, от расстояния до ближайшей пожарной станции на основании данных с использованием надстройки Excel Поиск решения
.
Изменяя ячейки переменных. Здесь указываются ячейки, значения в которых будут изменяться для того, чтобы оптимизировать результат в целевой ячейке. В нашем примере это – ячейки $F$5:$G$5 (рис.2).
Рис. 2. Введены формулы для вычисления значения целевой функции
Поле Оптимизировать целевую функцию. Целевая ячейка связана с другими ячейками этого рабочего листа с помощью формул. В нашем примере это ячейка I9, в которой в результате введенных формул получим сумму квадратов отклонений расчетных данных от фактических RSS. Для запуска Поиска решений на вкладке Данные выбрать команду Поиск решения и указать в появившемся меню адреса целевой функции, изменяемых ячеек и выбрать поиск наименьшего значения.
Количество посетителей (человек) Объем выручки (руб.)
59055-241935 259715-259080
122 106 000
79308,94 712412676,1
172 123 000
125749,47 7559593,94
192 129 000
144325,68 234876594,6
260 168 000 -34005,957 928,811 207484,81 1559049975
360 318 000
300365,87 310962549,8
377 338 000
316155,65 477175608
483 406 000
414609,58 74124817,2
3 376 161 814,70
Рис.3.В ячейках F5:G5 будут находиться параметры модели линейной регрессии и
Уравнения регрессии:
Модель .
функции ЛИНЕЙН:
Рассмотрим технологию оценки параметров модели линейной регрессии зависимости на основании данных с использованием функции ЛИНЕЙН.
Вначале выделяем диапазон для размещения результатов выполнения функции ЛИНЕЙН. На листе выделяется область высотой 5 строк и шириной, равной количеству столбцов с данными. В нашем примере их два (D2:E6). Затем вызываем функцию ЛИНЕЙН.
При применении функции ЛИНЕЙН табличного процессора Excel на экран дисплея выдается диалоговое окно для задания значений (Y) и (X).
В данном окне в строке Известные значения_y внести 7 значений из столбца В, а в строке Известные значения_х– соответственно 7 значений из столбца А. Если необходимо выполнить оценку двух параметров – постоянного и регрессионного, то в строке Конст указать значение 1. Так как при оценке параметров модели, в первую очередь, интересуют статистические сведения, то в окне строки Статистика указать значение 1.
После ввода всех значений, как показано на рис.4, нажать клавиши CTRL+SHIFT+ENTER.
После этого будет выдан результат (рис.4).
928,810629 -34005,957
81,4488654 24894,7799
R2 0,96297453 25985,2336
F 130,04219 5 ν
ESS 8,7809E+10 3376161815 RSS
Рис. 4.Результат функции ЛИНЕЙН
Слева и справа от полученных значений указаны их обозначения (наименования):
и – оценки параметров полученной парной регрессии (1-я строка);
и – стандартные ошибки оцененных параметров (2-я строка);
R2 – коэффициент детерминации (3-я строка, 1-й столбец);
– стандартная ошибка полученной регрессии (3-я строка, 2-й столбец);
F– статистика Фишера полученной парной регрессии (4-я строка, 1-й столбец);
ν – степень свободы (3-я строка, 2-й столбец);
ESS– (5-я строка, 1-й столбец);
RSS– квадрат остатков для полученной парной регрессии(5-я строка, 2-й столбец).
Итак, в первой строке выдаются значения параметров и , а во второй – соответственно их стандартные ошибки и .
В ячейках E2 и F2 будут находиться параметры модели линейной регрессии = – 34005,957 и = 928,811.
Выпишем полученное уравнение регрессии
Задать вопрос
на новый заказ в Автор24.
