Практика 1.
Пример 1. Решение полностью выполняется за счет возможностей Excel или Calc:
• Данные должны быть выставлены по возрастанию х или t;
• х или t должны быть от1 … с шагом 1;
| Год, t | Темпы инфляции y, % |
• Строится график y(x) или y(t), тип графика – с маркерами;
• По любой точке графика щелкаем левой кнопкой мыши, затем – правой;
• Выбираем «Добавить линию тренда»;
• В меню «Формат линии тренда» выбираем: Линейная; Прогноз на 1 период вперед; Показывать уравнение на диаграмме; Поместить на диаграмму величину достоверности аппроксимации
• Определено уравнение линейной регрессии 1,216* t + 1,138
• Уравнение подходит к имеющимся статистическим данным: на графике почти прямая линия; точность полученного линейного уравнения регрессии 0,991 (стремится к 1);
• Прогноз по полученному уравнению имеет практическую ценность.
| 13,3 |
Т.е., на 10 год уровень инфляции (с достоверностью 99,1%) будет 13,3%.
Пример 2.
Использование функций Excel для поэтапного определения параметров линейного уравнения регрессии.
1. Пусть даны результаты статистических исследований
| Уровень зарплат в регионе, х, в тыс. руб | Темпы инфляции y, % |
| 12,7 | 1,6 |
| 14,9 | 1,7 |
| 1,9 | |
| 2,1 | |
| 25,4 | 2,3 |
| 2,5 | |
| 2,7 | |
| 3,4 | |
| 3,5 |
2. Сортировать всю заданную таблицу по возрастанию х.
3. Построить график y (убедиться, что график - почти прямая линия, только тогда правильно использовать ЛИНЕЙНУЮ эконометрическую модель y=a+b*x)
4. Определить степень зависимости у от х (оценить тесноту линейной связи) с помощью коэффициента корреляции r y,x.
Коэффициент парной линейной корреляции показывает силу связи между у и х. Для нахождения этого коэффициента используем функцию MS Excel =КОРРЕЛ (все у; все х).
| r | 0,998991503 |
Модель определять целесообразно при I r y,x I >0,5.
| tнабл | 58,86648217 |
Т.к. tнабл существенно больше табличного, то между переменными х и y наблюдается тесная взаимосвязь.
• 5. Приступаем к нахождению неизвестных коэффициентов эконометрической модели:
• Сначала найдем b. Используем функцию =ЛИНЕЙН(все у; все х).
• Теперь найдем а. Сначала найдем суммы у и х.
• Найдем среднее х (хср) и среднее у (уср)
• а = y cp- x cp* b
| а | 0,979697893 |
| b | 0,05 |
• 6. Вычислим значения в столбце у расч по формуле модели a+b*x
• Найдем сумму урасч
| Уровень зарплат в регионе, х, в тыс. руб | Темпы инфляции y, % | урасч=а+b*x | Дробь | |
| 12,7 | 1,6 | 1,6 | 1,64874 | |
| 14,9 | 1,7 | 1,7 | 2,259028 | |
| 1,9 | 1,9 | 0,197107 | ||
| 2,1 | 2,1 | 0,003127 | ||
| 25,4 | 2,3 | 2,3 | 1,171227 | |
| 2,5 | 2,5 | 1,745072 | ||
| 2,7 | 2,7 | 1,479536 | ||
| 3,4 | 3,4 | 0,701474 | ||
| 3,5 | 3,5 | 0,733994 | ||
| 3,0 | 1,104367 | <= Еотн |
7. Построим на одном поле графики у и у расч.
Проведем первичную проверку качества модели:
1) сумма у должна быть равна или очень близка к значению суммы у расч
2) график у и график у расч должны или совпадать (идеальная модель), или быть близки друг у к другу (чем больше совпадений/пересечений графиков – тем лучше модель)
Так как Еотн состоит из среднего от суммы дробей, эти дроби необходимо вычислить
Средняя ошибка аппроксимации в данном примере 1,1%; что существенно меньше допустимого значения в 7%.
Делаем вывод о хорошем качестве модели.
Выполняем по модели прогноз, что в регионе с доходность в 40 тыс. руб. инфляция 3%, прогноз имеет погрешность в 1,1%.
Практика 2
Тема: Нелинейные парные модели регрессии
Дано:
| День t | Продажи Y |
Задание: Выполнить прогноз продаж на 15 день.
Этап 1. Убеждаемся, что переменная t по возрастанию, начиная с 1, с шагом 1.
Этап 2. Строим график.
На графике представлена нелинейная зависимость Y от t.
Этап 3. Воспользуемся возможностями Excel для определения коэффициентов эконометрической модели.
Этап 4. Вычислим расчетные значения Yрасч по модели.
Этап 5. Сделаем прогноз.
| День t | Продажи Y | Yрасч |
Результат работы: На 15 день планируется уровень продаж в размере 528, с вероятностью 100%
Практика 3
Тема: Нелинейные парные модели регрессии (парабола и гипербола)
Дано:
| День t | Продажи Y |
Задание: Выполнить прогноз на 27 день.