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