Лабораторная работа 7
.doc
Практическая работа 7
аНАЛИЗ И ПРОГНОЗ РЯДОВ НАБЛЮДЕНИЙ
Цель работы
Научиться использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели.
Теоретическое введение
Трудно найти область знаний или хозяйственной деятельности, г де не приходилось бы принимать решения, основанные на знании поведения объекта в пространстве признаков или времени. В большинстве случаев это решение принимается на основании модели1, базирующейся на знании предыдущих состояний объекта, на умении прогнозировать его поведение в будущем (или прошлом).
Excel предоставляет пользователю широкие возможности построения таких моделей и прогнозирования поведения объекта. Это прежде всего методы построения линий трендов для известных значений временных рядов2, методы статистического анализа данных наблюдений, методы линейного и динамического программирования («Поиск решения») и др.
Рассмотрим применение этих методов на нескольких примерах, имеющих отношение к деятельности торговых предприятий.
Практическая часть
Постановка задачи
Составить прогноз затрат на питание населения в зависимости от дохода.
1. Построение линии тренда расходов на продукты питания в зависимости от уровня дохода семьи
2. Использование процедуры «Поиск Решения» для подбора коэффициентов функции аппроксимации данных наблюдений.
Алгоритм решения задачи. В таблице 6 приведены статистические данные опроса (январь 2001 года) населения по их затратам на приобретение продуктов питания.
Таблица 6 – Исходные данные
-
Доход на члена семьи
Затраты на питание
500
330
750
540
1000
700
1250
800
1500
890
1750
980
2000
1050
2250
1100
2500
1140
2750
1180
3000
1210
1. Для анализа приведенных данных постройте диаграмму рассеяния в корреляционном поле - Затраты семьи на приобретение продуктов - доходы семьи.
Выделите таблицу данных; выполните команду ВСТАВКАÞДИАГРАММА. В открывшемся диалоговом окне выберите пиктограмму «точечная».
Выполните указания «мастера диаграмм» и построить диаграмму, иллюстрирующую затраты семьи на продукты питания в зависимости от дохода.
2. Для построения линии тренда:
- выделите кривую (после щелчка мышью на кривой на ней должны появиться маркеры выделения);
- выполните команду ДИАГРАММА ДОБАВИТЬ ЛИНИЮ ТРЕНДА
При построении линии тренда на вкладке «линия тренда» выполните следующие действия (рисунок 29):
- выберите наиболее подходящий тип кривой аппроксимации ( в нашем примере - это аппроксимация – «логарифмическая»)
- Установите флажки «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации R2»
П
ри
необходимости в группе списков «Прогноз»
установить необходимую величину шага
прогноза (необходимая величина шага
прогноза вводится либо с клавиатуры,
либо использованием стрелок).
После того как построены линии тренда и соответствующие им уравнения, не составляет труда вычислить новое значение Y (зависимой переменной) для нового значения Х (независимой переменной).
3. Вычислите теоретическую численность по формуле
( )
(смотри мастер функций), отклонение - модуль разности теоретических и фактических значений функции и погрешность - максимальное отклонение. В результате будет получена таблица 7.
4. Подберите значения коэффициентов а и b более точно, используя сервисную функцию Excel Поиск решения. В отличие от Подбора параметра - Поиск решения может для достижения нужного результата изменять или подбирать подходящие значения во многих ячейках (смотри процедуру Сервис - Поиск решения). После выполнения задачи произойдет изменение значений ячеек в соответствии с найденным решением.
Обратите внимание, что коэффициенты а и b изменились, а погрешность уменьшилась.
Таблица 7 – Расчетные данные
|
a |
b |
№ пп |
Доход на члена семьи |
Затраты на питание |
Теоретические затраты на питание |
Откло-нение |
|
496,4005 |
2743,728 |
1 |
500р. |
330р. |
341р. |
11,20623 |
|
|
|
2 |
750р. |
540р. |
542р. |
2,479298 |
|
|
|
3 |
1 000р. |
700р. |
685р. |
14,71519 |
|
|
|
4 |
1 250р. |
800р. |
796р. |
3,946629 |
|
|
|
5 |
1 500р. |
890р. |
887р. |
3,442125 |
|
|
|
6 |
1 750р. |
980р. |
963р. |
16,92166 |
|
|
|
7 |
2 000р. |
1050р. |
1029р. |
20,63661 |
|
|
|
8 |
2 250р. |
1100р. |
1088р. |
12,16906 |
|
|
|
9 |
2 500р. |
1 140р. |
1140р. |
0,131949 |
|
|
|
10 |
2 750р. |
1180р. |
1187р. |
7,443966 |
|
|
|
11 |
3 000р. |
1210р. |
1231р. |
20,63645 |
|
|
|
|
Погрешность |
20,63661 |
||
5. Определите затраты на питания в зависимости от доходов:
а
) Постройте
на одной диаграмме совмещенные графики
роста численности населения на основе
статистических и теоретических данных
(рисунок 30).
б) Проанализировав данные таблицы и графики, сделайте вывод об адекватности предложенной математической модели реальному процессу (т.е. вывод о правильности описания роста населения формулой (1)).
Варианты заданий для самостоятельной работы
Задача 1 «Кафе». На основании экспериментальных данных таблицы: определить при какой входной плате выручка владельца кафе будет оптимальной (таблица 7).
Таблица 7 – Экспериментальные данные для задачи «Кафе»
|
Входная плата X, $ |
Среднее число посетителей |
||||
|
a |
b |
c |
d |
e |
|
|
1 |
20 |
23 |
35 |
25 |
28 |
|
1,5 |
17,5 |
19,7 |
28 |
24,2 |
26 |
|
2 |
16 |
17 |
21 |
22 |
24,5 |
|
2,5 |
14 |
13 |
19 |
21,4 |
20 |
|
3 |
12,4 |
11,6 |
18,8 |
19 |
17,5 |
|
3,5 |
11 |
10 |
16 |
18 |
16 |
|
4 |
9,2 |
8,9 |
14 |
16 |
14 |
|
5 |
7 |
6,5 |
14 |
14,5 |
12,4 |
Примечание. Самостоятельно выберите тип линии тренда (по наилучшему значению критерия R2)
Задача 2 «Продажа жалюзи». В зависимости от солнечных дней и географического положения региона стремление людей приобрести жалюзи меняется, но не менее важным фактором является цена за установку. Возьмем несколько городов, находящихся в разных климатических условиях. Выясните оптимальную цену на установку для каждого из городов (таблица 8).
Таблица 8 – Экспериментальные данные для задачи «Продажа жалюзи»
|
Цена за установку, $ |
Количество проданных жалюзи размером 1,5*2,0 в среднем за день, шт. |
|||
|
Красноярск |
Норильск |
Краснодар |
Минусинск |
|
|
10,5 |
7 |
1 |
11,2 |
9 |
|
7 |
10 |
2,4 |
15 |
12,3 |
|
9 |
5 |
1,6 |
9 |
6,5 |
|
11,5 |
3 |
0,7 |
8,1 |
5 |
|
8,5 |
8 |
2 |
13,8 |
10,5 |
|
7,5 |
14 |
3,4 |
18 |
16 |
1 Модель не обязательно должна быть формализована в виде каких-то математических уравнений. Житейский опыт - это тоже одна из форм модели, построенной на знании и понимании поведения объекта в аналогичных ситуациях.
2 Понятие временного ряда не ограничивается набором значений изменения состояния объекта во времени, это может быть изменение в пространстве, где роль оси времени выполняет любой другой параметр.