- •Экономико-математические методы и модели
- •Содержание
- •Предисловие
- •1 Цели и задачи изучения дисциплины
- •2 Программа теоретического курса
- •3 Общие положения, рекомендации и требования к выполнению контрольной работы
- •4 Задания контрольной работы
- •4.1 Теоретическая часть Задание 1. Составление структурно-логических схем и тестов
- •4.2 Практическая часть
- •Задание 2. Система экономико-математических моделей оптимального планирования и управления
- •Задание 3. Экономико-статистическое моделирование и прогнозирование
- •5 Методическое пособие к решению практических заданий
- •5.1 Методика решения задания 2
- •2. Решение задачи с помощью инструмента Excel Поиск решения.
- •3. Анализ оптимального решения.
- •1. Экономико-математическая модель задачи.
- •2. Решение задачи с помощью инструмента Excel Поиск решения.
- •5.2 Методика решения задания 3
- •1. Использование инструмента Описательная статистика
- •2. Проведение корреляционного анализа
- •3. Прогнозирование развития показателей с помощью линии тренда Excel
- •4. Прогнозирование с применением функции экспоненциального сглаживания
- •5. Прогнозирование с применением метода скользящего среднего
- •6. Использование функции линейн для создания модели тренда
- •7. Использование функции тенденция для построения прогнозов
- •8. Использование функции предсказ для построения прогнозов
- •9. Анализ нелинейных процессов с помощью функции лгрфприбл.
- •10. Составление нелинейных прогнозов с помощью функции рост
- •11. Прогнозирование с использованием парной регрессии
- •12. Расчет и оценка уравнения множественной регрессии средствами Excel
- •Список рекомендуемой литературы
- •Приложение а Критические значения f-критерия (распределение Фишера)
- •Приложение б Распределение Стьюдента (t-распределение)
3. Прогнозирование развития показателей с помощью линии тренда Excel
Составить прогноз товарооборота торгового предприятия на 17-й месяц (табл. 6) с помощью команды «Добавить линию тренда».
Таблица 6 - Сведения о динамике товарооборота торгового предприятия
|
А |
В |
С |
D |
E |
F |
G |
1 |
|
|
|
|
|
|
|
2 |
Порядковый номер месяца |
Объем товарооборота, тыс. руб. |
|
|
|
|
|
3 |
1 |
28415 |
|
|
|
|
|
4 |
2 |
28231 |
|
|
|
|
|
5 |
3 |
29783 |
|
|
|
|
|
6 |
4 |
30969 |
|
|
|
|
|
7 |
5 |
30494 |
|
|
|
|
|
8 |
6 |
29757 |
|
|
|
|
|
9 |
7 |
30850 |
|
|
|
|
|
10 |
8 |
31325 |
|
|
|
|
|
11 |
9 |
31359 |
|
|
|
|
|
12 |
10 |
31610 |
|
|
|
|
|
13 |
11 |
32366 |
|
|
|
|
|
14 |
12 |
33313 |
|
|
|
|
|
15 |
13 |
33508 |
|
|
|
|
|
16 |
14 |
33374 |
|
|
|
|
|
17 |
15 |
34811 |
|
|
|
|
|
18 |
16 |
36046 |
|
|
|
|
|
19 |
Итого |
|
|
|
|
|
|
Выполнение:
Чтобы составить прогноз развития исследуемого показателя, используя линии тренда Excel, сначала необходимо с помощью Мастера диаграмм построить диаграмму (График) его динамики на основе базовых данных (ячейки В3:В19 таблицы 6).
Когда диаграмма построена, необходимо щелкнуть правой клавишей мыши на любой точке графика, чтобы открылось контекстное меню, в котором содержится команда «Добавить линию тренда». После ее выбора Excel выведет окно диалога Линии тренда, содержащее две основные вкладки: Тип и Параметры.
Вкладка Тип помогает пользователю выбрать тип линии тренда, которая будет аппроксимировать исходные данные. В диалоговом окне предлагается пять типов линий тренда. Для их построения Excel использует модели следующего вида:
- линейную (у = mх + b);
- полиномиальную (у = b + m1x + m2x2 +...+ m6х6);
- логарифмическую (у = m · ln x + b);
- экспоненциальную (у = m · еb·x);
- степенную (у = m · хb).
После задания типа линии тренда выделяют вкладку Параметры. Откроется ее окно диалога, в котором пользователь определяет следующие важные моменты:
1) количество прогнозируемых периодов и направление прогноза: вперед или назад;
2) когда выбрана линейная, полиномиальная или экспоненциальная кривая роста, то в поле Пересечение кривой с осью у в точке 0 задается ее у-пересечение: если данное поле обозначить флажком, то Excel будет искать лучшее уравнение кривой, которая на координатной плоскости обязательно должна пройти через начало координат;
3) через установку флажка в соответствующих полях окна диалога пользователь решает, отражать ли на выходной диаграмме уравнение, на основе которого была построена линия тренда, и размер квадрата коэффициента корреляции r2, характеризующий качество аппроксимации.
C помощью команды «Добавить линию тренда» составим сразу пять различных вариантов прогноза товарооборота торгового предприятия на 17-й месяц и при этом по r2 оценить общее качество моделей, на основе которых они были получены.
Используя возможности Excel по созданию в ячейках рабочего листа формул, с помощью приведенных на графиках уравнений кривых роста рассчитаем значения прогноза товарооборота на 17-й месяц (табл. 7).
Таблица 7 - Прогноз товарооборота на 17-й месяц
Тип модели тренда |
Формула расчета прогноза |
Прогноз объема товарооборота на 17-й месяц, тыс. руб. |
Линейная |
=437,43*17+27920 |
35356,3 |
Логарифмическая |
=2429,4*ln(17)+26981 |
33864,0 |
Полиномиальная |
=3,9737*17^3-88,245*17^2+ +925,09*17+27432 |
37178,5 |
Степенная |
=27215*17^0,0774 |
33887,9 |
Экспоненциальная |
=28081*е^(0,0138*17) |
35490,0 |
Рисунок 16 - График развития товарооборота торгового предприятия |
Рисунок 17 - Оценка прогноза товарооборота торгового предприятия на основе линейной кривой роста |
Рисунок 18 - Оценка прогноза товарооборота на основе логарифмической кривой роста |
Рисунок 19 - Оценка прогноза товарооборота на основе полиномиальной кривой роста (степень 3)
|
Рисунок 20 - Оценка прогноза товарооборота на основе степенной кривой роста |
Рисунок 21 - Оценка прогноза товарооборота на основе экспоненциальной кривой роста |
Вывод: Приведенные на рис. 16-21 графики динамики товарооборота свидетельствуют, что наибольшая степень приближения линии тренда к базовым данным достигнута в случае полиномиальной кривой роста 3-й степени (см. рис. 4, r2 = 0,9519), наименьшая — в случае логарифмической кривой (см. рис. 3, r2 = 0,7779).