АНАЛИЗ И ПРОГНОЗ РЯДОВ НАБЛЮДЕНИЙ

Цель работы

Научиться использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели.

Теоретическое введение

Трудно найти область знаний или хозяйственной деятельности, г де не приходилось бы принимать решения, основанные на знании поведения объекта в пространстве признаков или времени. В большинстве случаев это решение принимается на основании модели[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