Задание 3. Экономико-статистическое моделирование и прогнозирование

Наиболее широкое распространение при построении прогнозов развития в практике коммерческой деятельности получили экономико-статистические модели, которые описывают зависимость исследуемого экономического показателя от одного или нескольких факторов, оказывающих на него существенное влияние.

MS Excel предлагает широкий диапазон средств для изу­чения экономической информации. Множество встроенных статистических функций (СРЗНАЧ, МЕДИАНА, МОДА и др.) используют для проведения несложного анализа дан­ных.

Если возможностей встроенных функций недостаточ­но, то обращаются к инструменту Описательная статистика, имеющийся в па­кете «Статистический анализ» MS Excel. Выходной диапазон инструмента Описательная статистика содержит следующие статистические характеристики для каждой переменной из входного диапазона: среднее, стандартная ошибка, медиана, мода, стандартное отклоне­ние, дисперсия и др. (см. методику решения задания 3 п. 1).

Корреляционный анализ – это раздел математической статистики, посвященный изучению взаимосвязей между случайными величинами. Основной целью корреляционного анализа является уста­новление характера влияния факторной переменной на ис­следуемый показатель и определение тесноты их связи с тем, чтобы с достаточной степенью надежности строить модель развития исследуемого показателя.

С технической точки зрения проведение корреляционно­го анализа сводится к расчету коэффициентов парной корре­ляции, значения которых помогут судить о характере и тес­ноте связи между исследуемым показателем и каждой отоб­ранной факторной переменной.

Коэффициент парной корреляции используется в качес­тве меры, характеризующей степень линейной связи двух переменных. Значение коэффициента корреляции лежит в интервале от -1 (в случае строгой линейной отрицательной связи) до +1 (в случае строгой линейной положительной связи). Соответ­ственно, положительное значение коэффициента корреля­ции свидетельствует о прямой связи между исследуемым и факторным показателем, а отрицательное — об обратной. Чем ближе значение коэффициента корреляции к 1, тем тес­нее связь. Качественно оценить тесноту связи позволяет спе­циальная шкала значений коэффициентов корреляции, раз­работанная профессором Колумбийского университета США Чеддоком.

Размер коэффициента корреляции	0,1-0,3	0,3-0,5	0,5-0,7	0,7-0,9	0,9-0,99
Теснота связи	слабая	умеренная	заметная	высокая	весьма высокая

Для количественной оценки взаимосвязи двух наборов данных можно обратиться к статистической функции КОРРЕЛ, вызывая ее в диалоговом окне Мастера функций.

Од­нако чаще всего в экономических расчетах приходится иметь дело сразу с несколькими (более двух) наборами данных, вза­имосвязи которых требуется изучить. В этом случае рассчитывают коэффициент множественной корреляции, который при­нимает значения от 0 до 1, но несет в себе более универсаль­ный смысл: чем ближе его значение к 1, тем в большей степе­ни учтены факторы, влияющие на зависимую переменную, тем более точной выглядит построенная на основе отобран­ных факторов модель.

В таких случаях обра­щаются к инструменту Корреляция, содержащемуся в паке­те «Статистический анализ» Excel. Для этого используют ко­манду Анализ данных из меню Сервис. В открывшемся окне Инструменты анализа вызывают инструмент Корреляция (см. методику решения задания 3 п. 2).

Регрессионный анализ имеет своей целью вывод, определение (идентификацию) уравнения регрессии, включая статистическую оценку его параметров.

В основе любой регресси­онной модели лежит уравнение (или система уравнений) рег­рессии, которое показывает, каким будет в среднем измене­ние зависимой переменной у, если независимые переменные х примут конкретные значения. Это обстоятельство позволя­ет применять модель регрессии не только для анализа, но и для прогнозирования (см. методику решения задания 3 п. 11 и 12).

Методика построения и виды моделей тренда

Если имеется некоторая совокупность данных, характе­ризующих динамику исследуемого показателя, то всегда можно попытаться найти на графике наилучшую линию, ко­торая будет «ближайшей» к точкам наблюдений в рамках всей их совокупности. Чтобы составить прогноз развития исследуемого показа­теля, используя линии тренда Excel, сначала необходимо с помощью Мастера диаграмм построить диаграмму его дина­мики на основе базовых данных. Когда диаграмма построена, откры­вается контекстное меню, в котором содержится команда «Добавить линию тренда». После ее выбора Excel выведет ок­но диалога Линии тренда, содержащее две основные вкладки: Тип и Параметры (см. методику решения задания 3 п. 3).

Прогнозирование с применением функции экспоненциального сглаживания

Для составления прогнозов методом экспоненциального сглаживания в Excel предусмотрен инструмент Экспоненци­альное сглаживание. Активизировать инстру­мент Экспоненциальное сглаживание можно из меню Сер­вис после загрузки надстройки Пакет анализа посредством команды Анализ данных. Инструмент Экспо­ненциальное сглаживание целесообразно применять для со­ставления прогнозов только на период, непосредственно сле­дующий за интервалом базовых наблюдений (см. методику решения задания 3 п. 4).

Вычисление скользящего среднего средствами Excel

Инструмент Скользящее среднее можно вызвать в диалоговом окне команды Анализ данных из меню Сервис. Как правило, прогноз с применением скользящего сред­него составляется на период, непосредственно следующий за интервалом наблюдения (см. методику решения задания 3 п. 5).

Составление линейных прогнозов средствами Excel

Функция рабочего листа ЛИНЕЙН помогает определить характер линейной связи между результатами наблюдений и временем их фиксации и дать ей математическое описание, наилучшим образом аппроксимирующее исходные данные. Вызвать функцию ЛИНЕЙН можно в диалоговом окне Мастера функций (категория «Статистические»), располо­женном на панели инструментов Стандартная (см. методику решения задания 3 п. 6).

Функция ТЕНДЕНЦИЯ рассчитывает прогнозные значе­ния исследуемого показателя в соответствии с линейным трендом. Вызвать функцию ТЕНДЕНЦИЯ можно из окна диалога Мастера функций, расположенного на панели Стандартная (см. методику решения задания 3 п. 7).

Функция ПРЕДСКАЗ аналогична функции ТЕНДЕН­ЦИЯ за исключением того, что она определяет лишь одну точку на линии тренда и не может рассчитать массив, кото­рый формирует эту линию. Поэтому ее удобно использовать для оперативного вычисления единичных прогнозов. Как и ранее названные функции, ПРЕДСКАЗ можно вызвать в ди­алоговом окне Мастера функций (см. методику решения задания 3 п. 8).

Использование возможностей Excel при построении нелинейных прогнозов

Функция ЛГРФПРИБЛ работает подобно функции ЛИНЕЙН. Различия между ними состоят лишь в том, что ЛИНЕЙН определяет параметры прямой линии, наилучшим об­разом аппроксимирующей исходные данные, а функция ЛГРФПРИБЛ — экспоненциальной кривой. Эта функция, как и ЛИНЕЙН, относится к категории «Статистические» и может быть вызвана с помощью окна диалога Мастера фун­кций (см. методику решения задания 3 п. 9).

В то время как функция ЛГРФПРИБЛ рассчитывает па­раметры уравнения экспоненциальной кривой роста, кото­рая аппроксимирует наилучшим образом множество базо­вых данных, функция РОСТ определяет точки, лежащие на этой кривой (см. методику решения задания 3 п. 10).

Задача 3.1

В рамках оценки конкурентоспособности продовольственных товаров, реализуемых в магазинах ОАО «Заднепровье», исследовать центральную тенденцию и изменчивость товарооборачиваемости двенадцати товарных групп на основе следующих собранных по ним за отчетный период данных, приведенных в таблице.

№	Товарные группы	Товарооборачиваемость, дней
1	Мясо и птица	10,6
2	Колбасные изделия и копчености	6,0
3	Рыба и морепродукты	16,8
4	Молоко и молочные продукты	7,2
5	Хлеб, хлебобулочные изделия	3,3
6	Макаронные изделия	35,6
7	Яйца и яйцепродукты	6,2
8	Картофель, овощи	16,6
9	Плоды, ягоды	4,9
10	Кондитерские изделия	41,4
11	Водка, ликероводочные изделия	12,5
12	Безалкогольные напитки	31,8

Анализ провести с помощью инструмента Excel Описательная статистика. Описать алгоритм анализа. Построить диаграмму для сравнения товарооборачиваемости по всем товарным группам. Сделать выводы. Результаты исследования распечатать.

Задача 3.2

В рамках оценки эффективности деятельности торгового предприятия ОАО «Арма» исследовать центральную тенденцию и изменчивость производительности труда в двенадцати магазинах ОАО «Арма» на основе следующих собранных по ним за отчетный период данных, приведенных в таблице.

№ магазина	Производительность труда одного работника, млн руб.
4	25906
17	29047
24	34486
42	28153
70	38104
78	25462
84	28199
86	21550
88	38000
95	36704
98	22102
102	35297

Анализ провести с помощью инструмента Excel Описательная статистика. Описать алгоритм анализа. Построить диаграмму для сравнения производительности труда по всем магазинам. Сделать выводы. Результаты исследования распечатать.

Задача 3.3

Провести корреляционный анализ прибыли на основе информации, представленной в таблице.

№ месяца	Прибыль от реализации товаров, тыс. руб.	Розничный товарооборот, тыс. руб.	Издержки обращения, тыс. руб.	Обора­чивае­мость товаров, дни
1	375,556	2605,3	370,307	43,5
2	343,068	2423	380,833	43,0
3	394,745	2180,3	377,563	43,0
4	376,533	2421,7	364,826	43,5
5	435,719	2880,7	386,856	43,0
6	405,424	2201,1	330,248	42,5
7	412,705	2819,5	377,901	43,0
8	410,563	2755,7	359,231	41,5
9	406,955	2715	356,825	42,0
10	365,915	2683,9	348,120	41,5
11	356,637	2962,2	322,387	40,5
12	452,757	3347,7	393,116	40,0

Количественную оценку взаимосвязи объема данных провести с помощью инструмента Excel Корреляция. Построить диаграмму для анализа динамики прибыли от реализации товаров за 12 месяцев. Сделать выводы. Результаты анализа распечатать.

Задача 3.4

Составить прогноз валового дохода торгового предприятия на 13-й, 14-й и 15-й месяцы на основании данных отчетного периода, представленных в таблице.

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Валовый доход, ден. ед.	437	407	483	503	552	527	528	621	579	639	609	808

Составить прогноз развития валового дохода с помощью построения диаграммы его динамики, используя линии тренда Excel. Подобрать линию тренда, имеющую наибольшую степень приближения к базовым данным. Показать уравнения для расчета прогноза на графиках. Результаты прогноза распечатать.

Задача 3.5

Составить прогноз балансовой прибыли торгового предприятия на 9-й, 10-й и 11-й годы на основании данных предыдущих периодов, представленных в таблице.

Год	1	2	3	4	5	6	7	8
Балансовая прибыль, ден. ед.	175	438	532	110	108	236	-262	181

Составить прогноз развития балансовой прибыли с помощью построения диаграммы ее динамики, используя линии тренда Excel. Подобрать линию тренда, имеющую наибольшую степень приближения к базовым данным. Показать уравнения для расчета прогноза на графиках. Результаты прогноза распечатать.

Задача 3.6

Спрос на товар имеет резко выраженный сезонный характер. Зная колебания спроса, составить прогноз на 13-й месяц.

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Объем спроса, ден. ед.	145	198	472	564	1027	1982	3441	1550	840	920	642	311

Прогноз выполнить с помощью инструмента Excel Экспоненциальное сглаживание. Проиллюстрировать результаты на графике Экспоненциального сглаживания. Построить диаграмму динамики спроса, создать линию тренда, имеющую наибольшую степень приближения к базовым данным, показать уравнение модели. Сравнить графики. Сделать выводы. Результаты анализа распечатать.

Задача 3.7

Составить прогнозы объема продаж продукции предприятием на шесть месяцев в условиях, когда в течение последнего года происходило значительное сокращение штата торговых работников.

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Объем продаж, ден. ед.	593	570	486	854	797	362	594	271	254	433	529	994

Прогноз выполнить с помощью инструмента Excel Скользящее среднее. Проиллюстрировать результаты на графике Скользящее среднее. Построить диаграмму динамики объема продаж, создать линию тренда, имеющую наибольшую степень приближения к базовым данным, показать уравнение модели. Сравнить графики. Сделать выводы. Результаты анализа распечатать.

Задача 3.8

По предлагаемым ниже данным объемов продаж про­дукции создать линейную регрессию с использованием функции ЛИНЕЙН. Построить диаграмму динамики объема продаж, создать линию тренда, имеющую наибольшую степень приближения к базовым данным, показать уравнение модели. Рассчитать прогнозные показатели объемов продаж на следующие три месяца по полученному уравнению регрессии. Оценить качество линейной модели. Сделать выводы. Результаты анализа распечатать.

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Объемы продаж, ден. ед.	165	163	180	306	277	295	341	444	478	546	492	583

Задача 3.9

Составить на следующие пять дней прогнозы жалоб клиентов фирмы, поступающих на ее продукцию, по данным за последние 10 дней. Использовать для этого функцию ТЕНДЕНЦИЯ для составления линейного прогноза. Проиллюстрировать результаты на диаграмме и показать уравнение линейной модели, оценить ее качество. Сделать выводы.

Номера дней	1	2	3	4	5	6	7	8	9	10
Количество звонков	10	9	11	12	13	10	8	14	12	17

Задача 3.10

По предлагаемым ниже данным объемов продаж про­дукции создать линейную регрессию с использованием функции ПРЕДСКАЗ. Построить диаграмму динамики объема продаж, создать линию тренда, имеющую наибольшую степень приближения к базовым данным, показать уравнение модели. Рассчитать прогнозные показатели объемов продаж на следующие три месяца по полученному уравнению регрессии. Оценить качество линейной модели. Сделать выводы. Результаты анализа распечатать.

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Объемы продаж, ден. ед.	165	163	180	306	277	295	341	444	478	546	492	583

Задача 3.11

По предлагаемым ниже данным объема спроса на телевизоры LG создать нелинейную регрессию с использованием функции ЛГРФПРИБЛ.

Месяц	1	2	3	4	5	6	7	8	9
Объем спроса, ден. ед.	53	220	334	458	526	627	789	821	934

Построить модель тренда. Оценить качество построенной модели. Рассчитать прогнозные показатели объемов спроса на следующие три месяца по полученному уравнению регрессии. Построить диаграмму динамики объема спроса, создать линию тренда, имеющую наибольшую степень приближения к базовым данным, показать уравнение модели. Сделать выводы. Результаты анализа распечатать.

Задача 3.12

Составить на следующие пять дней прогноз на продажу товара, спрос на который резко возрос за последние 10 дней после удачной рекламной кампании. Спрос носит нелинейный характер, поэтому для прогнозирования использовать функцию РОСТ. Проиллюстрировать результаты на диаграмме и показать уравнение нелинейной модели, оценить ее качество. Какая из линий тренда лучше описывает фактические данные? Сделать выводы. Результаты анализа распечатать.

Номера дней	1	2	3	4	5	6	7	8	9	10	11	12
Объем спроса, ден. ед.	100	40	59	78	94	109	128	143	179	210	242	286

Задача 3.13

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

Месяц	1	2	3	4	5	6
Характеристика качества	450	960	145	212	265	312
Цена аппаратуры	4500	8000	3000	5500	5400	6500

Уравнение парной регрессии определить с помощью графического метода. Построить линии тренда и оценить качество аппроксимации базовых данных каждым из пяти типом линий. Сделать выводы. Результаты анализа распечатать.

Задача 3.14

По данным характеристики качества (время наработки на отказ в днях) и зависящей от нее цены аппаратуры (ден. ед.) проанализировать тесноту связи между ценой и качеством и найти уравнение парной регрессии.

Месяц	1	2	3	4	5	6
Характеристика качества	450	960	145	212	265	312
Цена аппаратуры	4500	8000	3000	5500	5400	6500

Уравнение парной регрессии определить с помощью функции ЛИНЕЙН. Оценить качество статистической значимости модели регрессии. Предположив, что характеристика качества (время наработки на отказ в днях) в седьмом месяце составит 500 дней, составить прогноз цены аппаратуры в седьмом месяце. Результаты анализа распечатать.

Задача 3.15

По предлагаемым ниже данным объемов продаж про­дукции, уже рекламировавшейся фирмой ранее, проанализировать возможность описания взаимосвязи данных линейной регрессией, проиллюстрировав зависимость объема продаж от величины сметы на рекламу на диаграмме. Построить линию тренда, показать уравнение модели и оценить ее качество и достоверность. Предположив, что смета на рекламу в 11-м месяце повысится до 1000 ден. ед., спрогнозировать объем продаж в 11-м месяце. Сделать выводы. Результаты анализа распечатать.

Месяц	1	2	3	4	5	6	7	8	9	10
Смета на рекламу, ден. ед.	35	100	118	336	372	554	556	665	710	821
Объемы продаж в единицах продукции	165	63	18	306	77	95	541	544	478	746

Задача 3.16

По предлагаемым ниже данным объемов продаж про­дукции, уже рекламировавшейся фирмой ранее, выполнить линейный рег­рессионный анализ с использованием пакета анализа Excel. Сделать выводы. Результаты распечатать.

Месяц	1	2	3	4	5	6	7	8	9	10
Смета на рекламу, ден. ед.	35	100	118	336	372	554	556	665	710	821
Объемы продаж в единицах продукции	165	63	18	306	77	95	541	544	478	746

Задача 3.17

Имеются следующие фактиче­ские данные по объемам продаж за прошедшие месяцы: 593, 570, 486, 854, 797, 362, 594, 271, 45, 254, 433, 529, 994, 319, 610, 748, 832, 193, 720, 415, 536, 850, 201, 833. Составить прогнозы объема продаж продукции на следующие три месяца в условиях, когда в течение последних двух лет происходи­ло значительное сокращение штата торговых работников предприятия. Прогнозы выполнить несколькими способами. Прогнозные значения сравнить с фактическими данными. Сделать выводы. Результат прогноза распечатать.

Задача 3.18

Для исследования процентного прироста прибыли (у) в розничной торговле собраны данные о средних показателях рознич­ной цены (х₁), количестве товара (х₂), его ассортименте (х₃), затра­тах на поставку товара (х₄) и производственных издержках (х₅) за предыдущие периоды.

х1	х2	х3	х4	х5	у
1,2	1,2	13	35	2,1	0,99
1,4	1,5	10	80	1,1	1,21
1,9	1,3	10	23	2,3	2,07
2,5	1,4	16	80	1,4	2,14
3,4	2,6	12	80	1,7	3,05
1,9	1,6	13	69	1,5	3,87
2,7	1,7	13	80	0,5	4,87

Требуется составить зависимость между всеми факторами X (i=l,2,3,4,5) и приростом прибыли У по этим данным с помощью инструмента анализа Регрессия. Сделать выводы. Результаты анализа распечатать.

Задача 3.19

Исследовать зависимость объема продаж продукции от изменения расходов на рекламу и колебания цены на единицу этой продукции, выполнив линейный рег­рессионный анализ с использованием пакета анализа Excel. Построить уравнение регрессии, проанализировать его достоверность. Представить прогноз на будущее при сумме расходов на рекламу, равной 1000 ден. ед., и продажной цены, равной 25 ден. ед. Сделать выводы. Результаты распечатать.

Смета на рекламу	35	100	118	336	372	554	556	665	710	821
Цена на единицу продукции	88	110	85	28	101	71	7	82	62	24
Объемы продаж в единицах продукции	165	63	18	306	77	95	541	544	478	746

Задача 3.20

Для исследования процентного прироста прибыли (у) в розничной торговле собраны данные о средних показателях рознич­ной цены (х₁), количестве товара (х₂), его ассортименте (х₃), затра­тах на поставку товара (х₄) и производственных издержках (х₅) за предыдущие периоды.

х1	х2	х3	х4	х5	у
1,2	1,2	13	35	2,1	0,99
1,4	1,5	10	80	1,1	1,21
1,9	1,3	10	23	2,3	2,07
2,5	1,4	16	80	1,4	2,14
3,4	2,6	12	80	1,7	3,05
1,9	1,6	13	69	1,5	3,87
2,7	1,7	13	80	0,5	4,87

Требуется создать нелинейную регрессию с помощью функции РОСТ. Сделать выводы. Результаты анализа распечатать.