Анализ и прогноз рядов наблюдений

Цель работы

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

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

Трудно найти область знаний или хозяйственной деятельности, г де не приходилось бы принимать решения, основанные на знании поведения объекта в пространстве признаков или времени. В большинстве случаев это решение принимается на основании модели[1], базирующейся на знании предыдущих состояний объекта, на умении прогнозировать его поведение в будущем (или прошлом).

Excel предоставляет пользователю широкие возможности построения таких моделей и прогнозирования поведения объекта. Это прежде всего методы построения линий трендов для известных значений временных рядов[2], методы статистического анализа данных наблюдений, методы линейного и динамического программирования («Поиск решения») и др.

Рассмотрим применение этих методов на нескольких примерах, имеющих отношение к деятельности торговых предприятий.

Практическая часть

Постановка задачи

Составить прогноз затрат на питание населения в зависимости от дохода.

1. Построение линии тренда расходов на продукты питания в зависимости от уровня дохода семьи

2. Использование процедуры «Поиск Решения» для подбора коэффициентов функции аппроксимации данных наблюдений.

Алгоритм решения задачи. В таблице 6 приведены статистические данные опроса (январь 2001 года) населения по их затратам на приобретение продуктов питания.

Таблица 6 – Исходные данные

Доход на члена семьи	Затраты на питание

1. Для анализа приведенных данных постройте диаграмму рассеяния в корреляционном поле - Затраты семьи на приобретение продуктов - доходы семьи.

Выделите таблицу данных; выполните команду ВСТАВКАЮДИАГРАММА. В открывшемся диалоговом окне выберите пиктограмму «точечная».

Выполните указания «мастера диаграмм» и построить диаграмму, иллюстрирующую затраты семьи на продукты питания в зависимости от дохода.

2. Для построения линии тренда:

- выделите кривую (после щелчка мышью на кривой на ней должны появиться маркеры выделения);

- выполните команду ДИАГРАММА ðДОБАВИТЬ ЛИНИЮ ТРЕНДА

При построении линии тренда на вкладке «линия тренда» выполните следующие действия (рисунок 29):

- выберите наиболее подходящий тип кривой аппроксимации ( в нашем примере - это аппроксимация – «логарифмическая»)

- Установите флажки «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации R²»

При необходимости в группе списков «Прогноз» установить необходимую величину шага прогноза (необходимая величина шага прогноза вводится либо с клавиатуры, либо использованием стрелок).

После того как построены линии тренда и соответствующие им уравнения, не составляет труда вычислить новое значение 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

Примечание. Самостоятельно выберите тип линии тренда (по наилучшему значению критерия R²)

Задача 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