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

Формируемые навыки и умения:

- освоение методики анализа статистических данных; - освоение методики прогнозирования значений экономических показателей с помощью функций и пакета анализа MS Excel; - изучение и освоение методики проведения корреляционного и регрессионного анализа.

Теоретическая поддержка

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

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

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

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

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

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

Таблица 3.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. Для этого используют ко­манду Анализ данных из меню Сервис. В открывшемся окне Инструменты анализа вызывают инструмент Корреляция.

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

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

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

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

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

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

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

Функция ТЕНДЕНЦИЯ рассчитывает прогнозные значе­ния исследуемого показателя в соответствии с линейным трендом.

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

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

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

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

Вызвать функции ЛИНЕЙН, ТЕНДЕНЦИЯ, ПРЕДСКАЗ, ЛГРФПРИБЛ и РОСТ можно в диалоговом окне Мастера функций (категория «Статистические»), располо­женном на панели инструментов Стандартная.

Задание 1. Использование инструмента Описательная статистика

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

Таблица 3.2 - Данные об уровне рентабельности по магазинам (гастрономам) области за отчетный период

	А	В	С	D	Е	F	G	Н
1	№ п/п	Уровень рентабель­ности, %
2	1	0,94
3	2	1,22
4	3	0,8
5	4	1,67
6	5	1,56
7	6	0,94
8	7	1,23
9	8	0,87
10	9	1,22
11	10	1,43
12	11	0,16
13	12	0,44
14	13	0,8
15	14	1,03
16	15	0,55
17	16	1,22
18	17	1,17
19	18	0,02
20	19	0,28
21	20	1,22

Выполнение:

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

1) Входной диапазон (интервал) — предполагает ввод ссылки на ячейки рабочего листа, которые содержат анали­зируемые данные. Тогда входной диапазон объединяет ячейки В1:В21;

2) Группирование — требует установления переключате­ля в положение «По столбцам» или «По строкам» в зависи­мости от расположения данных во входном диапазоне. Пос­кольку данные об уровне рентабельности расположены в таблице 3.2 в виде столбца, то переключатель следует устано­вить в положение «По столбцам»;

3) Метки в первой строке/Метки в первом столбце — позволяет определить название каждого столбца (или стро­ки) выходной таблицы. Переключатель устанавливается в положение «Метки в первой строке», если первая строка во входном диапазоне содержит названия столбцов. Когда в первом столбце входного диапазона находятся названия строк, переключатель устанавливается в положение «Метки в первом столбце». Если входной диапазон не содержит ме­ток, то необходимые заголовки в выходном диапазоне созда­ются на основе программы автоматически. Учитывая, что в таблице 3.2 первая строка содержит названия столбцов, пере­ключатель следует установить в положение «Метки в первой строке»;

4) Уровень надежности — используется, если в выход­ную таблицу необходимо включить строку для уровня на­дежности. Тогда в соответствующее поле диалогового окна вводится требуемое значение. В экономических расчетах, как правило, значения уровня надежности задают в размере 95 или 99 %. Например, значение 95 % вычисляет уровень надежности среднего со значимостью 0,05;

5) К-й наибольший — применяется, если в выходную таб­лицу необходимо включить строку для k-го наибольшего значения входного диапазона данных. В соответствующем окне вводится число k. Если k равно 1, эта строка будет со­держать максимум из набора данных. Например, при оценке конкурентоспособности нашего гастронома (пусть в таблице 3.2 он имеет порядковый номер 7) для нас важно проследить, по­пал ли уровень его рентабельности в первую тройку наиболее высокорентабельных предприятий, а также, каков диапазон изменения уровня рентабельности у трех самых высокорен­табельных магазинов. Тогда для k-го наибольшего в диалого­вом окне надо ввести цифру 3 (т.е. k = 3). Это значит, что в выходной таблице, кроме максимального значения уровня рентабельности, будет отражен третий за ним по убывающей размер уровня рентабельности из всей исследуемой совокуп­ности данных;

6) К-й наименьший — применяется, если в выходную таб­лицу необходимо включить строку для k-го наименьшего значения входного диапазона данных. В соответствующем окне вводится число k. Если k равно 1, эта строка будет со­держать минимум из набора данных. Например, для нас важно убедиться, что уровень рентабельности исследуемого гастронома не относится к пяти самым низким показателям. Тогда в диалоговом окне для k-го наименьшего вводится цифра 5. Это значит, что в выходной таблице, кроме мини­мального значения уровня рентабельности, будет отражен пя­тый за ним по возрастающей размер уровня рентабельности;

7) Выходной диапазон — предполагает введение ссылки на левую верхнюю ячейку выходного диапазона. Инстру­мент Описательная статистика выводит два столбца сведе­ний для каждого набора данных. Левый столбец содержит наименования рассчитанных статистических величин, а пра­вый — их значения. В нашем случае выходная таблица статистических характеристик должна быть расположена, например, на том же рабочем листе, что и входная, на одном с ней уровне, но правее. Тогда можно за­дать следующий выходной диапазон — D1;

8) Новый лист — применяют, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки А1. При необходимости в поле диалогового окна, располо­женном напротив соответствующего положения переключа­теля, вводится имя нового листа;

9) Новая книга — используется, когда необходимо от­крыть новую книгу и вставить результаты анализа в ячейку А1 на первом листе этой книги;

10) Итоговая статистика — требует установления флажка, который означает, что в выходном диапазоне необ­ходимо получить полный список статистических характе­ристик: Среднее, Стандартная ошибка (среднего), Медиана, Мода, Стандартное отклонение, Дисперсия выборки, Эк­сцесс, Асимметричность, Интервал, Минимум, Максимум, Сумма, Счет, Наибольшее (k-e), Наименьшее (k-e), Уровень надежности.

Рисунок 3.1 - Окно диалога Описательная статистика

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

Таблица 3.3 - Статистическая оценка данных об уровне рентабельности по магазинам (гастрономам) области за отчетный год

	А	В	С	D	Е
1	№ п/п	Уровень рентабельности, %		Результат
2	1	0,94
3	2	1,22		Среднее	0,9385
4	3	0,8		Стандартная ошибка	0,10199
5	4	1,67		Медиана	0,985
6	5	1,56		Мода	1,22
7	6	0,94		Стандартное отклонение	0,45611

Продолжение таблицы 3.3

	А	В	С	D	Е
1	№ п/п	Уровень рентабельности, %		Результат
8	7	1,23		Дисперсия выборки	0,20803
9	8	0,87		Эксцесс	-0,3986
10	9	1,22		Асимметричность	-0,5196
11	10	1,43		Интервал	1,65
12	11	0,16		Минимум	0,02
13	12	0,44		Максимум	1,67
14	13	0,8		Сумма	18,77
15	14	1,03		Счет	20
16	15	0,55		Наибольший(5)	1,22
17	16	1,22		Наименьший(3)	0,28
18	17	1,17		Уровень надежности(95,0%)	0,21347
19	18	0,02
20	19	0,28
21	20	1,22

Вывод: Приведенные в таблице 3.3 данные позволяют оперативно проследить, что уровень рентабельности по двадцати иссле­дуемым предприятиям за анализируемый период сложился в среднем 0,94+0,46 % и колебался в пределах 0,02-1,67 %.

С известной долей условности можно предположить, что приблизительно 68 % магазинов имели уровень рента­бельности между 0,48% (0,94 - 0,46) и 1,4% (0,94 + 0,46).

Стандартное отклонение (± 0,456) свидетельствует о доста­точно сильном разбросе размеров уровня рентабельности предприятий относительно его среднего значения, т.е. отоб­ранные магазины далеко не в равной степени могут рассмат­риваться в качестве конкурентов нашего предприятия.

Ис­следуемый гастроном № 7, имея уровень рентабельности 1,23 %, не относится к тройке самых высокорентабельных предприятий (1,43-1,67 %), но, судя по медиане, принадле­жит к той половине предприятий, которая обладает большей рентабельностью. Чаще всего в выборке присутствует уро­вень рентабельности 1,22 % (что тоже ниже показателя на­шего гастронома), а отрицательное значение коэффициента асимметрии свидетельствует о более высокой плотности рас­пределения значений уровня рентабельности, больших вели­чины 1,22 % (левосторонней асимметрии). Следовательно, по показателю рентабельности у гастронома № 7 гораздо меньше реальных конкурентов, чем общее количество чле­нов выборки. При этом рассчитанные коэффициенты асим­метрии и эксцесса указывают на неоднородность исследуе­мого массива данных и необходимость пересмотра его соста­ва. В этой связи для проведения углубленного анализа реаль­ных конкурентов магазина № 7 по избранному признаку це­лесообразно пересмотреть и урезать выборку.

Задание 2. Проведение корреляционного анализа

Провести корреляционный анализ това­рооборота на основе информации, подготовленной с помощью электронных таблиц Excel (таблица 3.4).

Для проведения корреляционного анализа объема товарообо­рота (исследуемый показатель) могут быть отобраны следую­щие факторы:

- товарооборачиваемость в днях;

- удельный вес торговой площади в общей площади пред­приятия;

- удельный вес торгово-оперативных работников в их об­щей численности;

- удельный вес товаров с высоким (в рамках установленно­го действующим законодательством предельного уровня) уровнем торговых надбавок.

Таблица 3.4 - Исходные данные для проведения корреляционного анализа

	А	В	С	D	Е	F
1
2	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни	Удельный вес торговой площади в общей, %	Удельный вес торгово-оперативного персонала в общей численности работников, %	Удельный вес товаров с высокими торговыми надбавками, %
3	1	28415	43,5	44,0	67,7	22,5
4	2	28231	43,0	44,0	67,7	18,0
5	3	29783	43,0	44,0	70,2	24,9
6	4	30969	43,5	47,8	70,0	24,4
7	5	30494	43,0	47,8	68,0	20,6
8	6	29757	42,5	47,8	68,0	19,0
9	7	30850	43,0	49,0	70,2	22,2
10	8	31325	41,5	49,0	70,0	21,6
11	9	31359	42,0	50,3	70,0	19,8
12	10	31610	41,5	50,3	70,0	19,7
13	11	32366	40,5	50,3	70,0	23,1
14	12	33313	40,0	50,3	70,0	23,9
15	13	33508	40,0	50,3	68,0	21,2
16	14	33374	39,0	50,3	68,0	20,4
17	15	34811	39,5	50,3	70,0	24,2
18	16	36046	39,0	49,0	70,0	26,5

Выполнение:

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

1) Входной диапазон (интервал) — предполагает ввод ссылки на ячейки рабочего листа, которые содержат анали­зируемые данные. Для данных таблицы 3.4 вход­ной диапазон имеет вид - B2:F18;

2) Группирование — требует установления переключа­теля в положение «По столбцам» или «По строкам» в зави­симости от расположения данных во входном диапазоне. Поскольку анализируемые наборы данных расположены в таблице 3.4 в виде столбцов, то переключатель следует устано­вить в положение «По столбцам»;

3) Метки в первой строке/Метки в первом столбце — позволяет определить название каждого столбца (или стро­ки) выходной таблицы. Переключатель устанавливается в положение «Метки в первой строке», если первая строка во входном диапазоне содержит названия столбцов. Когда в первом столбце входного диапазона находятся названия строк, переключатель устанавливается в положение «Метки в первом столбце». Если входной диапазон не содержит ме­ток, то необходимые заголовки в выходном диапазоне созда­ются на основе программы автоматически. Учитывая, что в таблице 3.4 первая строка содержит названия столбцов, пере­ключатель следует установить в положение «Метки в первой строке»;

4) Выходной диапазон — предполагает введение ссылки на левую верхнюю ячейку выходного диапазона. Например, если необходимо, чтобы выходная таблица располагалась на том же рабочем листе, что и входная, и непосредственно под ней, то можно задать выходной диапазон ячейкой А21;

5) Новый лист — применяют, чтобы открыть новый лист в книге и вставить результаты анализа, начиная с ячейки А1. При необходимости в поле диалогового окна, располо­женном напротив соответствующего положения переключа­теля, вводится имя нового листа;

6) Новая книга — используется, когда необходимо от­крыть новую книгу и вставить результаты анализа в ячейку А1 на первом листе этой книги.

Рисунок 3.2 - Окно диалога Корреляция

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

Таблица 3.5 - Матрица парных коэффициентов корреляции

	А	В	С	D	E	F
21		Объем товарооборота, ден. ед.	Оборачиваемость товаров, дни	Удельный вес торговой площади в общей, %	Удельный вес торгово-оперативного персонала в общей численности работников, %	Удельный вес товаров с высокими торговыми надбавками, %
22	Объем товарооборота, ден. ед.	1
23	Оборачиваемость товаров, дни	-0,90538094	1
24	Удельный вес торговой площади в общей, %	0,75014268	-0,690531	1
25	Удельный вес торгово-оперативного персонала в общей численности работников, %	0,38663194	-0,153964	0,3726409	1
26	Удельный вес товаров с высокими торговыми надбавками, %	0,50878765	-0,276455	0,0374572	0,580400123	1

Вывод: На основе приведенной матрицы можно содержательно оценить связь значений объема товарооборота с каждым из отобранных факторов и выбрать наиболее значимые из них для включения в модель. Так, полученные коэффициенты корреляции, характеризующие тесноту связи объема товаро­оборота с отобранными факторами (см. столбец В21:В26 таблицы 3.5), составляют соответственно: -0,905 для фактора «оборачиваемость товаров»; 0,750 для фактора «удельный вес торговой площади в общей»; 0,509 для фактора «удельный вес товаров с высокими торговыми надбавками»; 0,387 для фактора «удельный вес торгово-оперативного персонала в общей численности работников». Согласно шкале Чеддока (таблица 3.1), для данного торгового предприятия показатель объема товарооборота имеет весьма высокую тесноту связи с фактором «оборачиваемость това­ров» и высокую — с фактором «удельный вес торговой пло­щади в общей». Значение коэффициента корреляции, рассчитанное для товарооборота и фактора «удельный вес торгово-оперативно­го персонала», свидетельствует о слабо выраженной линей­ной связи между этими показателями.

Знак «-» перед коэффициентом корреляции в ячейке В23 означает, что между объемом товарооборота и размером товарооборачиваемости в днях имеет место обратная связь, т.е. при росте количества дней одного оборота товарного запаса предприятия в днях (иными словами — замедлении товарооборачиваемости) объем его реализации при прочих равных условиях будет падать. С остальными факторами объем то­варооборота находится в прямой зависимости.

Задание 3. Прогнозирование развития показателей с помощью линии тренда Excel

Составить прогноз товарооборота торгового пред­приятия на 17-й месяц (см. данные таблицы 3.6) с помощью ко­манды Добавить линию тренда.

Таблица 3.6 - Сведения о динамике товарооборота торгового предприятия

	А	В	С	D	E	F
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.
3	1	28415
4	2	28231
5	3	29783
6	4	30969
7	5	30494
8	6	29757
9	7	30850
10	8	31325
11	9	31359
12	10	31610
13	11	32366
14	12	33313
15	13	33508
16	14	33374
17	15	34811
18	16	36046
19	Итого

Выполнение:

Чтобы составить прогноз развития исследуемого показа­теля, используя линии тренда Excel, сначала необходимо с помощью Мастера диаграмм построить диаграмму (График) его дина­мики на основе базовых данных (ячейки В3:В19 таблицы 3.6).

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

Вкладка Тип помогает пользователю выбрать тип линии тренда, которая будет аппроксимировать исходные данные. В диалоговом окне предлагается пять типов линий тренда. Для их построения Excel использует модели следующего вида:

- линейную (у = mх + b);

- полиномиальную (у = b + m₁x + m₂x² +...+ m₆х⁶);

- логарифмическую (у = m · ln x + b);

- экспоненциальную (у = m · е^b·x);

- степенную (у = m · х^b).

После задания типа линии тренда выделяют вкладку Па­раметры. Откроется ее окно диалога, в котором пользова­тель определяет следующие важные моменты:

1) количество прогнозируемых периодов и направление прогноза: вперед или назад;

2) когда выбрана линейная, полиномиальная или экспо­ненциальная кривая роста, то в поле Пересечение кривой с осью у в точке 0 задается ее у-пересечение: если данное поле обозначить флажком, то Excel будет искать лучшее уравне­ние кривой, которая на координатной плоскости обязатель­но должна пройти через начало координат;

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

C помощью ко­манды Добавить линию тренда составим сразу пять различных вариантов прогноза товарооборота торгового пред­приятия на 17-й месяц и при этом по r² оценить общее качество моделей, на основе которых они были полу­чены.

Используя возмож­ности Excel по созданию в ячейках рабочего листа формул, с помощью приведенных на графиках уравнений кривых рос­та рассчитаем значения прогноза товарооборота на 17-й месяц (таблица 3.7).

Таблица 3.7 - Прогноз товарооборота на 17-й месяц

Тип модели тренда	Формула расчета прогноза	Прогноз объема товарооборота на 17-й месяц, ден. ед.
Линейная	=437,43*17+27920	35356,3
Логарифмиче­ская	=2429,4*ln(17)+26981	33864,0
Полиноми­альная	=3,9737*17^3-88,245*17^2+925,09*17+27432	1 37178,5
Степенная	=27215*17^0,0774	33887,9
Экспоненци­альная	=28081*е^(0,0138*17)	35490,0

Рисунок 3.3 - График развития товарооборота торгового предприятия	Рисунок 3.4 - Оценка прогноза товарооборота торгового предприятия на основе линейной кривой роста
Рисунок 3.5 - Оценка прогноза товарооборота на основе логарифмической кривой роста	Рисунок 3.6 - Оценка прогноза товарооборота на основе полиномиальной кривой роста (степень 3)
Рисунок 3.7 - Оценка прогноза товарооборота на основе степенной кривой роста	Рисунок 3.8 - Оценка прогноза товарооборота на основе экспоненциальной кривой роста

Вывод: Приведенные на рисунках 3.3–3.8 графики динамики то­варооборота свидетельствуют, что наибольшая степень при­ближения линии тренда к базовым данным достигнута в слу­чае полиномиальной кривой роста 3-й степени (см. рисунок 3.6, r² = 0,9519), наименьшая — в случае логарифмической кри­вой (см. рисунок 3.5, r² = 0,7779).

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

Составить прогноз товарооборота торгового предприятия на основе данных таблицы 3.6 с помощью инструмента Экспоненциальное сгла­живание.

Выполнение:

Активизировать инстру­мент Экспоненциальное сглаживание можно из меню Сер­вис после загрузки надстройки Пакет анализа посредством команды Анализ данных. Открывшееся окно диалога Экспоненциальное сглаживание предлагает пользователю оп­ределиться со следующими параметрами (рисунок 3.9):

1) Входной диапазон — требует ссылки на ячейки, содер­жащие данные об исследуемом показателе. Этот диапазон должен состоять из одного столбца или одной строки и содер­жать данные как минимум в четырех ячейках. В нашем примере параметр Входной диапазон включает ячейки В2:В19 (таблица 3.8);

2) Фактор затухания — это корректировочный фактор, минимизирующий нестабильность совокупности данных. Он может иметь значения в пределах от 0 до 1. По умолчанию пользователя Excel самостоятельно принимает значение фак­тора затухания равным 0,3. В нашем примере для параметра Фактор затухания введем значение 0,1;

3) Метки — требует установки флажка, если первая строка или первый столбец входного диапазона содержит за­головки (название столбца или строки). В нашем примере установим флажок для параметра Метки;

4) Выходной диапазон — предполагает ссылку на вер­хнюю ячейку выходного диапазона. В нашем примере установим верхнюю ячейку Выходного диапазона – С3;

5) Вывод графика — требует установки флажка, если пользователю, кроме значения прогноза, необходимо полу­чить встроенную диаграмму динамики фактических и рас­четных значений;

6) Стандартные погрешности — требует установки флаж­ка, если пользователю для проведения оценки качества про­гнозов необходимо включить в выходной диапазон столбец со стандартными погрешностями e(t).

Рисунок 3.9 - Окно диалога «Экспоненциальное сглаживание»

В результате работы инструмента Экспоненциальное сглаживание получаем следующие сглаженные показатели товарооборота (см. столбец С таблицы 3.8), в том числе прогноз на 17-й месяц — 35 908 ден. ед.

Таблица 3.8 - Расчет прогноза товарооборота с помощью инструмента Экспоненциальное сглаживание

	А	В	С
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.	Экспоненциальное сглаживание
3	1	28415	#Н/Д
4	2	28231	28415
5	3	29783	28249,4
6	4	30969	29629,6
7	5	30494	30835,1
8	6	29757	30528,1
9	7	30850	29834,1
10	8	31325	30748,4
11	9	31359	31267,3
12	10	31610	31349,8
13	11	32366	31584,0
14	12	33313	32287,8
15	13	33508	33210,5
16	14	33374	33478,2
17	15	34811	33384,4
18	16	36046	34668,3
19	17		35908,2

Задание 5. Прогнозирование с применением метода скользящего среднего

5.1 При исследовании спроса на реализуемый то­вар-новинку в фирменном магазине в течение 20 дней было зафиксировано определенное количество поступивших на не­го жалоб со стороны покупателей (таблица 3.9, ячейки В2:В21). Выяснить, существует ли какая-либо тенденция поступления жалоб с помощью инструмента Скользящее сред­нее.

Выполнение:

Инструмент Скользящее среднее можно вызвать в диалоговом окне команды Анализ данных из меню Сервис. Открывшееся окно диалога предлагает пользователю за­дать следующие основные параметры (рисунок 3.10):

1) Входной диапазон (интервал) - это диапазон ячеек В2:В21 (см. таблицу 3.9);

2) Интервал усреднения примем равным 3, т.е. бу­дем определять трехдневное скользящее среднее;

3) Выходной диапазон (интервал) за­даем именем ячейки С2. При этом следует иметь в виду, что первые несколько ячеек выходного диапазона будут всегда содержать значение #Н/Д. Количество таких ячеек равно значению выбранного интервала усреднения минус один. Эта ситуация связана с недостаточным количеством базовых данных для вычисления среднего значения первых результа­тов наблюдений;

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

Рисунок 3.10 - Окно диалога Скользящее среднее

Таблица 3.9 - Оценка тенденции поведения показателей исследуемого динамического ряда методом скользящего среднего

	А	В	С
1	День месяца	Количество жалоб
2	1	10	#Н/Д
3	2	11	#Н/Д
4	3	10	10,333
5	4	12	11
6	5	13	11,667
7	6	13	12,667
8	7	13	13
9	8	10	12
10	9	16	13
11	10	9	11,667
12	11	15	13,333
13	12	10	11,333
14	13	15	13,333
15	14	17	14
16	15	12	14,667
17	16	15	14,667
18	17	14	13,667
19	18	18	15,667
20	19	19	17
21	20	15	17,333

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

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

Выполнение:

- на отдельном рабочем листе Excel составим разработочную таблицу следующего вида (таблица 3.10);

Таблица 3.10 - Методика составления прогноза товарооборота на основе скользящего среднего средствами Excel (разработочная таблица)

	А	В	С	D	Е
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.	Цепные темпы прироста, %	Показатели выравненного ряда	Среднее изменение темпов прироста
3	1	28415
4	2	28231
5	3	29783
6	4	30969
7	5	30494
8	6	29757
9	7	30850
10	8	31325
11	9	31359
12	10	31610
13	11	32366
14	12	33313
15	13	33508
16	14	33374
17	15	34811
18	16	36046

- учитывая, что объем товарооборота представляет собой абсолютный стои­мостный показатель, для расчета скользящего среднего соз­дадим в разработочной таблице колонку относительных ве­личин, характеризующих динамику товарооборота предпри­ятия — цепных темпов прироста (вводим в ячейку С4 формулу =В4/В3*100-100 и копируем ее в ячейки С5:С18) (столбец С таблицы 3.10);

- вы­равнивание динамического ряда, состоящего из 15 цепных темпов прироста товарооборота, проведем, используя интер­вал усреднения, равный, например, 5. Для расчета показате­лей выровненного ряда (столбец D таблицы 3.10) воспользуемся другим способом создания скользящего среднего в Excel, т.е. прямым введением формулы в соответствующую ячей­ку рабочего листа. Так, чтобы получить пятимесячное сколь­зящее среднее цепных темпов прироста, в ячейку D8 таблицы 10 вводится формула =СРЗНАЧ(С4:С8). Затем эта формула с помощью средства Автозаполнение копируется и вставляет­ся в ячейки D9:D18;

- для составления прогноза товарооборота на 17-й месяц в таблице 3.10 необходимо занести еще три формулы: расчета среднего изменения темпов его прироста: =(D18-D8)/10 (ячейка Е18), расче­та прогнозируемого цепного темпа прироста на 17-й месяц: =D18+2*E18 (ячейка С20), расчета прогнозируемого объема товарооборо­та: B18*(C20+100)/100 (ячейка В20). Результатом всех проведенных операций становится итоговая таблица 3.11, содержащая все промежуточ­ные элементы и значения единой цепи составления прогноза товарооборота.

Таблица 3.11 - Прогноз товарооборота на основе скользящего среднего средствами Excel (итоговая таблица)

	А	В	С	D	Е
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.	Цепные темпы прироста, %	Показатели выравненного ряда	Среднее изменение темпов прироста
3	1	28415
4	2	28231	-0,648	#Н/Д
5	3	29783	5,498	#Н/Д
6	4	30969	3,982	#Н/Д
7	5	30494	-1,534	#Н/Д
8	6	29757	-2,417	0,976
9	7	30850	3,673	1,840
10	8	31325	1,540	1,049
11	9	31359	0,109	0,274
12	10	31610	0,800	0,741
13	11	32366	2,392	1,703
14	12	33313	2,926	1,553
15	13	33508	0,585	1,362
16	14	33374	-0,400	1,261
17	15	34811	4,306	1,962
18	16	36046	3,548	2,193	0,122
19	Прогноз
20	17	36924	2,436

Задание 6. Использование функции ЛИНЕЙН для создания модели тренда

Составить прогноз товарооборота торгового пред­приятия по данным таблицы 3.6 с помощью функции ЛИНЕЙН.

Выполнение:

Функция рабочего листа ЛИНЕЙН помогает определить характер линейной связи между результатами наблюдений и временем их фиксации и дать ей математическое описание, наилучшим образом аппроксимирующее исходные данные. Для построения трендовой модели она использует уравнение вида у = mх + b, где у — исследуемый показатель; х = t — временной тренд; b, m — параметры уравнения, характери­зующие соответственно у-пересечение и наклон линии трен­да.

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

Используя метод наименьших квадратов, функция ЛИНЕЙН создает массив значений, который описывает ис­комую модель тренда. Учитывая, что создается массив зна­чений, функция должна задаваться пользователем в виде формулы массива. Поэтому перед началом работы с ЛИ­НЕЙН необходимо на рабочем листе выделить диапазон яче­ек, достаточный для размещения создаваемого ею массива значений. Так, для прогнозирования товарооборота по дан­ным таблицы 3.6 обозначим ячейками E10:F14 диапазон для формирования выходного массива (таблица 3.12). После того, как выделен выходной диапазон и пользователь определился с аргументами функции посредством диалогового окна ЛИ­НЕЙН, следует нажать на клавиатуре кнопки Ctrl+Shift+Enter.

Таблица 3.12 - Расчет и оценка линейной модели тренда с помощью функции ЛИНЕЙН

	A	B	C	D	E	F
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.
3	1	28415
4	2	28231
5	3	29783
6	4	30969
7	5	30494
8	6	29757
9	7	30850
10	8	31325	Линейная оценка		437,425	27920,1
11	9	31359	Статистика		34,958505	338,033
12	10	31610			0,917921	644,603
13	11	32366			156,56746	14
14	12	33313			65055814	5817182
15	13	33508
16	14	33374
17	15	34811
18	16	36046

Функция ЛИНЕЙН имеет четыре аргумента (рисунок 3.11):

1) Известные значения у — это множество уже извес­тных значений исследуемого показателя, на основе которых будет производиться оценка параметров уравнения тренда. Так, при составлении прогноза товарооборота торгового пред­приятия по данным таблицы 3.6 известные значения у представ­лены в виде столбца и находятся в ячейках В3:В18;

2) Известные значения х — при построении трендовой модели представляют собой временной ряд, соответствую­щий по размерам первому аргументу. В нашем примере он находится в ячейках А3:А18 таблицы 3.6 и отражает порядко­вые номера месяца;

3) Конст — логическое значение, которое указывает на необходимость расчета параметра b (свободного члена) при построении модели тренда. Если Конст имеет значение ИСТИНА, то параметр b вычисляется. Если Конст имеет значение ЛОЖЬ, то параметр b принимается равным нулю;

4) Статистика — логическое значение, которое указы­вает на необходимость отражения на рабочем листе дополни­тельной статистической информации, позволяющей судить о качестве построенной модели. Если этот аргумент имеет зна­чение ЛОЖЬ или ссылка на него отсутствует, то функция ЛИНЕЙН не рассчитывает статистические характеристики. Если Статистика задана значением ИСТИНА, то мас­сив, создаваемый функцией, содержит значения следующих статистических величин (таблица 3.13).

Таблица 3.13 - Значения статистических величин функции ЛИНЕЙН

Стандартная ошибка для параметра m (COm)	Стандартная ошибка для свободного члена b (СОb)
Квадрат коэффициента корреляции (r^2)	Стандартная ошибки для у (СОу)
F-критерий (F)	Степень свободы (df)
Сумма квадратов регрессии (SSp)	Остаточная сумма квадратов (SS0)

Рисунок 3.11 - Окно диалога функции ЛИНЕЙН

Вывод: Число в ячейке Е10 представляет собой наклон линии тренда (m = 437,425), а число в ячейке F10 — это у-пересечение прямой линии (b = 27920,1). Можно составить линей­ную модель, описывающую динамику товарооборота торго­вого предприятия, которая принимает следующий вид:

Y = 27920,1 + 437,425x,

где х = t — порядковый номер месяца.

В нашем примере коэффициент корреляции (см. таблицу 3.12, ячейка Е12) r² = 0,9179, что указывает на высокое качество линейной модели.

В нашем примере F_крит находится по таблице F-распреде­ления на пересечении столбца 1 (так как в модели только од­на переменная х — временной тренд) и строки 14 (см. ячейку F13 таблицы 3.12). В приложении А находим для рас­пределения Фишера с (1;14) степенями свободы, что при 5%-м уровне значимости (доверительная вероятность 95 %) табличное значение F_кp = 4,6. Поскольку F = 156,567 > 4,6 (см. ячейку Е13 таблицы 3.12), то полученная модель трен­да полезна для использования в прогнозировании.

Рассчитаем значения t-статистики для оценки параметров m и b построенной нами модели на основе данных таблицы 3.12:

Табличное значение t_крит для уровня значимости 0,05 (до­верительная вероятность 0,95) с df = 14 степенями свободы равно 2,145 (см. приложение Б). Поскольку |t_m| > 2,145, |t_f| > 2,145, статистическая значимость параметров построенной модели признается весьма высокой.

Задание 7. Использование функции ТЕНДЕНЦИЯ для построения прогнозов

Составить прогноз товарооборота торгового пред­приятия по данным таблицы 3.6 с помощью функции ТЕНДЕНЦИЯ.

Выполнение:

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

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

1) Известные значения у;

2) Известные значения х;

3) Новые значения х;

4) Конст.

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

Третий аргумент — это определяемые самим пользовате­лем значения х (в модели тренда — времени t), для которых ТЕНДЕНЦИЯ рассчитывает по найденной модели соответст­вующие значения у. Так, при прогнозировании товарооборо­та торгового предприятия с помощью функции ТЕНДЕН­ЦИЯ на рабочем листе выделим диапазон ячеек С3:С21, где ячейки С3:С18 - для отражения значений каждой точки базового интервала вре­мени на линии тренда (таблица 3.14), а ячейки С19:С21 - для перспективной оценки товарооборота на три ближайших месяца. Аргумент Новые значения х в этом случае будет за­дан ячейками А3:А21, и формируется следующая формула массива: =ТЕНДЕНЦИЯ(В3:В18;А3:А18;А3:А21).

По окончании работы с окном диалога ТЕНДЕНЦИЯ необходимо нажать на клавиатуре кнопки Ctrl + Shift + Enter. В выходном диапазоне (см. столбец С таблицы 3.14) фун­кция ТЕНДЕНЦИЯ моментально отразит все рассчитанные на основе линейной модели тренда значения у, соответствую­щие заданному аргументу Новые значения х (т.е. ячейкам А3:А21). Из них значения, находящиеся в ячейках С3:С18, будут относиться к базовому временному интервалу, а в ячейках С19:С21 — содержать прогноз товарооборота на 17-й, 18-й и 19-й месяцы.

Рисунок 3.12 - Окно диалога функции ТЕНДЕНЦИЯ

Таблица 3.14 - Расчет прогноза товарооборота с помощью функции ТЕНДЕНЦИЯ

	А	В	С
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.	Тенденция
3	1	28415	28358
4	2	28231	28795
5	3	29783	29232
6	4	30969	29670
7	5	30494	30107
8	6	29757	30545
9	7	30850	30982
10	8	31325	31419
11	9	31359	31857
12	10	31610	32294
13	11	32366	32732
14	12	33313	33169
15	13	33508	33607
16	14	33374	34044
17	15	34811	34481
18	16	36046	34919
19	17		35356
20	18		35794
21	19		36231

Задание 8. Использование функции ПРЕДСКАЗ для построения прогнозов

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

Выполнение:

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

1) X — это точка данных (числовое значение), для кото­рой необходимо рассчитать прогноз исследуемого показате­ля. Так, при прогнозировании товарооборота на 17-й, 18-й и 19-й месяцы (таблица 3.15) в качестве аргумента X определим ячейки А19:А21;

2) Известные значения у (прогнозируемого показателя). В нашем примере их можно задать в виде диапазона ячеек В3:В18 (см. таблицу 3.15);

3) Известные значения х (времени t). Этот аргумент дол­жен совпадать по размерам с массивом Известные значения у. Этот аргумент в нашем примере охватывает ячейки А3:А18.

Соответственно обозначенным в окне диалога ПРЕДСКАЗ аргументам формируется формула следующего вида: =ПРЕДСКАЗ(А19:А21;В3:В18;А3:А18).

По окончании работы с окном диалога ПРЕДСКАЗ необходимо нажать на клавиатуре кнопки Ctrl + Shift + Enter.

Рисунок 3.13 - Окно диалога функции ПРЕДСКАЗ

Таблица 3.15 - Расчет прогноза товарооборота с помощью функции ПРЕДСКАЗ

	А	В	С
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.
3	1	28415
4	2	28231
5	3	29783
6	4	30969
7	5	30494
8	6	29757

Продолжение таблицы 3.15

	А	В	С
	Порядковый номер месяца	Объем товарооборота, ден. ед.
9	7	30850
10	8	31325
11	9	31359
12	10	31610
13	11	32366
14	12	33313
15	13	33508
16	14	33374
17	15	34811
18	16	36046
19	17	35356	ПРЕДСКАЗ
20	18	35794
21	19	36231

Задание 9. Анализ нелинейных процессов с помощью функции ЛГРФПРИБЛ

Рассчитать параметры модели тренда и дать ей качественную оценку с помощью функции ЛГРФПРИБЛ для базовых данных о динамике товарооборота предприя­тия, приведенных в таблице 3.6.

Выполнение:

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

Функция ЛГРФПРИБЛ имеет те же четыре аргумента, что и функция ЛИНЕЙН и формирует аналогичный массив результатов (рисунок 3.14). Поэтому она вводится как формула для работы с массивами, т.е. требует: во-первых, выделения диапазона ячеек, в которых будет формироваться массив результатов; во-вторых, после работы с окном диалога ЛГРФПРИБЛ — нажатия клавиш Ctrl + Shift + Enter.

Однако следует учиты­вать, что дополнительная статистика, которую отражает функция ЛГРФПРИБЛ, основана на линейной модели вида

In у = х  ln т + ln b.

Поэтому при оценке значений стандартных ошибок пара­метров модели (СО_т и CO_b) их необходимо сравнивать не с са­мими значениями параметров, а с их натуральными лога­рифмами — ln m и ln b. Последние можно рассчитать с по­мощью функции LN (категория «Математические»), вызы­вая ее в окне диалога Мастера функций.

С этой целью на рабочем листе Excel выделим диапазон ячеек D11:E15 для формирования выходного массива (таблица 3.16). Работая с окном диалога ЛГРФПРИБЛ, формируем следующую формулу массива: =ЛГРФПРИБЛ(В3:В18;А3:А18;ИСТИНА; ИСТИНА). Затем нажимаем клавиши Ctrl + Shift + Enter.

Рисунок 3.14 - Окно диалога функции ЛГРФПРИБЛ

Таблица 3.16 - Расчет и оценка модели тренда с помощью функции ЛГРФПРИБЛ

	A	B	C	D	E
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.
3	1	28415
4	2	28231
5	3	29783
6	4	30969
7	5	30494
8	6	29757
9	7	30850
10	8	31325
11	9	31359	Экспоненциальная оценка	1,014	28080,897
12	10	31610	Статистика	0,001	0,010
13	11	32366		0,922	0,020
14	12	33313		165,579	14
15	13	33508		0,065	0,005
16	14	33374
17	15	34811
28	16	36046

Вывод: Приведенный в таблице 3.16 массив результатов работы фун­кции позволяет построить следующую модель тренда, в осно­ве которой лежит уравнение экспоненциальной кривой роста:

у = 28080,897 · 1,01^х,

где х = t — порядковый номер месяца.

Оценка статистических характеристик приведенной мо­дели показывает, что качество ее подгонки к фактическим значениям у выше, чем модели, построенной с помощью функции ЛИНЕЙН. Коэффициент r² в данном случае имеет значение 0,922 (ячейка D13), что несколько больше соответ­ствующего показателя в случае линейной модели, равного 0,9179. В этой связи (и учитывая однофакторный характер модели) можно ожидать улучшение оценок F- и t-статистики. Следо­вательно, качество построенной модели позволяет использо­вать ее при составлении прогнозов развития товарооборота на ближайшую перспективу.

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

Учитывая оценку статистических характеристик фун­кции ЛГРФПРИБЛ, рассчитать прогноз товарооборота по торговому предприятию с помощью функции РОСТ. При этом необходимо получить теорети­ческие значения товарооборота (оцененные на основе най­денной модели у = 28080,897·1,01^х) для базового диапазо­на времени (т.е. 16 прошедших месяцев), а также спрогно­зировать динамику товарооборота на ближайшие 3 месяца.

Выполнение:

Функция РОСТ определяет точки, лежащие на экспоненциальной кривой роста. Она работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ, и имеет те же четыре аргумента (рисунок 3.15).

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

Для формирования выходного массива результатов выде­лим на рабочем листе Excel, соответственно, ячейки С3:С21 (таблица 3.17). Затем вызовем функцию РОСТ из окна диалога Мастера функций. При работе с диалоговым окном РОСТ формула массива принимает вид: =РОСТ(В3:В18;А3:А18;А3:А21). Последнее действие — нажать клавиши Ctrl + Shift + + Enter.

Рисунок 3.15 - Окно диалога функции РОСТ

Таблица 3.17 - Расчет прогноза товарооборота с помощью функции РОСТ

	А	В	С
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.	РОСТ
3	1	28415	28470,4
4	2	28231	28865,2
5	3	29783	29265,6
6	4	30969	29671,5
7	5	30494	30083,0
8	6	29757	30500,3
9	7	30850	30923,3
10	8	31325	31352,2
11	9	31359	31787,0
12	10	31610	32227,9
13	11	32366	32674,9
14	12	33313	33128,1
15	13	33508	33587,5
16	14	33374	34053,4
17	15	34811	34525,7
18	16	36046	35004,5
19	17		35490,0
20	18		35982,3
21	19		36481,3

Задание 11. Прогнозирование с использованием парной регрессии

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

Таблица 3.18 - Исходные данные для поиска уравнения связи переменных

	А	В	С
1
2	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни
3	1	28415	43,5
4	2	28231	43,0
5	3	29783	43,0
6	4	30969	43,5
7	5	30494	43,0
8	6	29757	42,5
9	7	30850	43,0
10	8	31325	41,5
11	9	31359	42,0
12	10	31610	41,5
13	11	32366	40,5

Продолжение таблицы 3.18

	А	В	С
	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни
14	12	33313	40,0
15	13	33508	40,0
16	14	33374	39,0
17	15	34811	39,5
18	16	36046	39,0
19

Выполнение:

Чтобы найти уравнение парной регрессии, которое наилучшим образом опишет изучаемую зависимость, обратимся к графическому методу. С помощью Мастера диаг­рамм построим точечную диаграмму зависимости товарооборота от оборачиваемости товаров (см. рисунок 3.16).

Рисунок 3.16 - График зависимости товарооборота от

товарооборачиваемости товаров

После того как диаграмма построена, необходимо обратиться к команде Excel «Добавить линию тренда» из контекстного ме­ню панели Диаграмма. Учитывая возможности Excel, оце­ним качество аппроксимации базовых данных каждым из пяти предлагаемых окном диалога Линии тренда типом ли­ний: для линейного уравнения R² = 0,8197; для уравнения логарифмической кривой R² = 0,8216; для уравнений полинома 4-й степени R² = 0,8287; для уравнения степенной кривой R² = 0,8155; для уравнения экспоненциальной кривой R² = 0,8142.

Полученные результаты свидетельствуют, что наиболее адекватно (судя по величине R²) отражают зависимость това­рооборота от изменения товарооборачиваемости кривые, пос­троенные на основе уравнений полиномов 4-й степе­ни. На рисунке 3.17 приведена кривая роста, которую описывает уравнение полинома 4-й степени. Рассчитанный Excel коэффициент R² (0,8287) указывает на достаточно высокое качес­тво приближения базовых данных.

Рисунок 3.17 - Аппроксимация базовых данных полиномиальной кривой роста

Проведем оценку статистической значимости параметров уравнения полинома 4-й степени, построенного на основе соответствующего массива базовых данных (таблица 3.19, ячей­ки A2:F18) с помощью функции ЛИНЕЙН. Для формирования выходного массива значе­ний параметров уравнения и статистических характерис­тик обозначим диапазон ячеек В20:F24.

Таблица 3.19 - Оценка статистической значимости модели регрессии с помощью функции ЛИНЕЙН (уравнение полинома 4-й степени)

	А	В	С	D	E	F
1
2	Порядковый номер месяца	Объем товарооборота, ден. ед.	Оборачиваемость товаров, дни (х)	х^2	х^3	х^4
3	1	28415	43,5	1892,3	82312,9	3580610,1
4	2	28231	43	1849,0	79507,0	3418801,0
5	3	29783	43	1849,0	79507,0	3418801,0
6	4	30969	43,5	1892,3	82312,9	3580610,1
7	5	30494	43	1849,0	79507,0	3418801,0
8	6	29757	42,5	1806,3	76765,6	3262539,1
9	7	30850	43	1849,0	79507,0	3418801,0
10	8	31325	41,5	1722,3	71473,4	2966145,1
11	9	31359	42	1764,0	74088,0	3111696,0
12	10	31610	41,5	1722,3	71473,4	2966145,1
13	11	32366	40,5	1640,3	66430,1	2690420,1
14	12	33313	40	1600,0	64000,0	2560000,0
15	13	33508	40	1600,0	64000,0	2560000,0
16	14	33374	39	1521,0	59319,0	2313441,0
17	15	34811	39,5	1560,3	61629,9	2434380,1
18	16	36046	39	1521,0	59319,0	2313441,0
19	17 (прогноз)	36667	37,5
20		2,269166117	-330,0698403	17765,93	-419248	3693351,7
21	Статистика	100,6502388	16606,9862	1027052	28216610	290563825
22		0,828678371	1050,630858	#Н/Д	#Н/Д	#Н/Д
23		13,30168019	11	#Н/Д	#Н/Д	#Н/Д
24		58730919,23	12142077,21	#Н/Д	#Н/Д	#Н/Д

При определении в диалоговом окне ЛИНЕЙН аргумен­тов функции формируется следующая формула массива: =ЛИНЕЙН(В3:В18;С3:F18;ИСТИНА;ИСТИНА).

В первой строке массива результатов, отображенного функцией ЛИНЕЙН после нажатия клавиш Ctrl + Shift + + Enter (ячейки В20:F24 таблицы 3.19), находим уточненные в ходе математических расчетов значения параметров уравне­ния.

Вывод: Модель связи товарооборота (у) и оборачиваемости това­ров (х), построенная на основе уравнения полинома 4-й сте­пени, имеет вид:

у = 3693352 - 419248х + 17765,93х² - 330,07х³ + 2,269х⁴.

Заметьте, что рассчитанный в массиве Статистика ко­эффициент R², равный 0,8287 (ячейка В22), соответствует значению R², приведенному на рисунке 3.17.

В нашем примере (см. таблицу 3.19) значения всех рассчитан­ных параметров уравнения (ячейки В20:F20) меньше по мо­дулю значений их стандартных ошибок (ячейки В21:F21). Следовательно, надежность оценок параметров регрессии не может быть признана удовлетворительной и составленную модель не следует применять для прогнозирования исследуе­мого показателя.

Используя линии тренда Excel, найдем уравнение другой кривой, для которой значение R₂ будет наибольшим. Таким об­разом, наилучшее качество аппроксимации исходных дан­ных достигается в случае уравнения логарифмической кри­вой (рисунок 3.18).

Рисунок 3.18 - Аппроксимация базовых данных логарифмической кривой роста

Статистическую надежность сделанной оценки можно проверить с помощью F-критерия, расчетное значение которого в случае парной регрессии определяют на основе следующей формулы

.

Итак, для уравнения парной регрессии с коэффициентом R², равным 0,8216, F = 64,5.

По таблице F-распределения (см. приложение А) находим, что при 5%-м уровне значимости для распределения Фишера с (1;14) степенями свободы F_крит = 4,60. Поскольку 64,5 > 4,60, можно сделать вывод об адекватности и достаточной точности модели. Следовательно, при условии сохранения существовавшей ранее взаимосвязи переменных на период упреждения модель вида у = -49619ln(х) + 216503 мо­жет быть использована для прогнозирования.

Чтобы составить прогноз объема товарооборота на 17-й месяц, осталось определить значение переменной х (т.е. оборачиваемости товаров) для данного месяца. Это зна­чение может быть получено (в зависимости от характера по­казателя) на основе экстраполяционных методов, методов экспертных оценок или непосредственно задано составите­лем прогноза. Так, будем полагать, что рассматриваемое тор­говое предприятие на 17-й месяц планирует проведение оп­ределенных рекламных мероприятий и выставки-продажи, что по оценкам специалистов позволит ускорить оборачивае­мость товаров в среднем на 1,5 дня. В этом случае товарооборачиваемость по предприятию в прогнозируемом месяце сос­тавит 37,5 дня.

Прогноз объема товарооборота можно получить, создав соответствующую модели формулу в любой (предваритель­но выделенной) ячейке рабочего листа. Выделим, к приме­ру, ячейку В19 (см. таблицу 3.19) и внесем в нее следующую формулу: =-49619*ln(37,5)+216503. После нажатия клавиши Enter в ячейке В19 отразится прогноз объема товарооборота на 17-й месяц, равный 36 667 ден. ед.

Задание 12. Расчет и оценка уравнения множественной регрессии средствами Excel

Построить модель множествен­ной линейной регрессии, которая позволит оценить объем товарооборота на ближайшую перспективу при заданных па­раметрах независимых переменных: «обо­рачиваемость товаров» и «удельный вес товаров с высо­кими торговыми надбавками». Исходные данные представлены в таблице 3.20.

Таблица 3.20 - Исходные данные

	А	В	С	D
1
2	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни	Удельный вес товаров с высокими торговыми надбавками, %
3	1	28415	43,5	22,5
4	2	28231	43,0	18,0
5	3	29783	43,0	24,9
6	4	30969	43,5	24,4
7	5	30494	43,0	20,6
8	6	29757	42,5	19,0
9	7	30850	43,0	22,2
10	8	31325	41,5	21,6
11	9	31359	42,0	19,8
12	10	31610	41,5	19,7
13	11	32366	40,5	23,1
14	12	33313	40,0	23,9
15	13	33508	40,0	21,2
16	14	33374	39,0	20,4
17	15	34811	39,5	24,2
18	16	36046	39,0	26,5

Выполнение:

При построении модели множественной регрессии целесооб­разнее обратиться к инструменту Excel Регрессия, который предлагает исчерпывающую статистическую информацию о ее параметрах и качестве. Порядок работы с инструментом Регрессия определяется соответствующим окном диалога. Его можно вызвать через команду Анализ данных из контекстного меню панели Сер­вис. Диалоговое окно Регрессия предлагает пользователю определиться с набором следующих параметров (рисунок 3.19):

1) Входной интервал У — предлагает ввод ссылки на ячейки рабочего листа, которые содержат диапазон базовых данных зависимой переменной у (исследуемого показателя). В нашем примере Входной интервал Y – В2:В18;

2) Входной интервал X — предполагает ввод ссылки на ячейки рабочего листа, которые содержат диапазон базовых данных независимых переменных х₁, х₂, ..., x_k. В нашем примере Входной интервал Х – С2:D18;

3) Метки — требует установления флажка, если первая строка входного интервала содержит заголовки (названия столбцов). Если заголовки отсутствуют, то флажок устанав­ливать не нужно — Excel автоматически создаст соответству­ющие названия для данных выходного диапазона. В нашем примере – устанавливаем флажок;

4) Уровень надежности — позволяет пользователю опре­делить необходимый уровень надежности оценки выходного диапазона значений. По умолчанию Excel применяет уро­вень 95 %. Если его нужно изменить, то для данного параметра следует установить флажок и в специально открывше­еся поле ввести нужный уровень надежности. В нашем - примере флажок не устанавливаем;

5) Константа-ноль — требует установления флажка, ес­ли для уравнения регрессии не нужно рассчитывать пара­метр b (свободный член). В этом случае Excel принимает b, равным нулю. В нашем примере – флажок не устанавливаем;

6) Выходной диапазон — предполагает ввод ссылки на верхнюю левую ячейку выходного диапазона. Выходной массив значений будет зани­мать не менее семи столбцов и содержать три основных раз­дела: 1. Регрессионная статистика; 2. Дисперсионный ана­лиз; 3. Параметры (коэффициенты) регрессии и характерис­тики их статистической значимости;

7) Новый лист — применяют, если результаты анализа следует разместить на новом листе книги, начиная с ячейки А1;

8) Новая книга — используется, если результаты анализа необходимо разместить на первом листе специально откры­той для этого новой книги;

9) Остатки — требует установления флажка, если в це­лях проведения углубленного статистического анализа ка­чества модели в выходной диапазон, кроме трех основных разделов, необходимо включить значения отклонений фак­тических данных исследуемого показателя от соответствую­щих им точек регрессионной прямой (У фактическое - У рас­четное);

10) Стандартизированные остатки — применяют с той же целью для включения в выходной диапазон значений стандартных остатков;

11) График остатков — используется, если для статис­тического анализа необходимо построить диаграммы остат­ков для каждой независимой переменной х;

12) График подбора — предполагает формирование на ра­бочем листе диаграмм, позволяющих отследить характер связи и степень разброса наблюдаемых и предсказанных значений исследуемого показателя у с каждой независимой пе­ременной х;

13) График нормальной вероятности — требует установ­ления флажка, если пользователю необходимо получить гра­фик нормального распределения вероятности для исследуе­мого показателя.

Рисунок 3.19 - Окно диалога Регрессия

Так, в первом разделе выходного массива «Регрессионная статистика» (см. ячейки А4:В8 таблицы 3.21) приведены основные статистические характерис­тики общего качества уравнения: коэффициент множествен­ной корреляции R, коэффициент детерминации R², стандартная ошибка оценки. Значе­ние R², равное 0,892, свидетельствует о том, что на основе полученного уравнения регрессии можно объяснить 89,2 % вариации объема товарооборота.

Статистические характеристики второго раздела выход­ного массива «Дисперсионный анализ» (ячейки A10:F14) по­зволяют оценить меру разброса (дисперсию) зависимой пере­менной у и остаточной вариации (дисперсии) отклонений во­круг линии регрессии. Так, значение SS_р (ячейка С12) ха­рактеризует часть дисперсии, объясненную регрессией, а SS_о (ячейка С13) — часть дисперсии, не объясненной регрес­сией из-за наличия ошибок ε. При проведении регрессионно­го анализа особый интерес представляет изменение этих зна­чений по мере введения каждого регрессора. Качество моде­ли улучшится, если после введения в нее нового фактора зна­чение объясненной части дисперсии возрастет, а не объяс­ненной — снизится.

В ячейках D12:D13 отражены соответственно дисперсия исходного ряда (МS_p = SS_p / df, где df = k — см. ячейку В12) и несмещенная дисперсия остаточной компоненты (MS₀ = SS₀ / df, где df = п - k - 1 — см. ячейку В13).

В ячейке F12 второго раздела выходного массива приведен уровень значимости для оцененного F. Значения F-статистики (53,72) выглядит вполне допус­тимым, поскольку уровень значимости для нее (5,210⁷) ос­тается гораздо ниже 5%-го предела, принятого для табличных F-статистик. Следова­тельно, есть основания ожидать, что F-наблюдаемое будет больше F_крит.

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

у = 71650,26 – 1098,94х₁ + 255,838х₂,

где х₁ — оборачиваемость товаров, дни; х₂ - удельный вес товаров с высокими торговыми надбавками, %.

Оценить значимость каждого параметра позволяют зна­чения t-статистики (см. ячейки D17:D19). Можно использо­вать приведенный в выходном массиве уровень значимости (см. ячейки Е17:Е19): если он не превышает 0,05 (т.е. 5%-го уровня), то рассчитанные характеристики t-статистики будут больше табличного значения. Следовательно, статис­тическая значимость рассчитанных параметров уравнения весьма высока.

И, наконец, наряду с точечными значениями коэффици­ентов регрессии третий раздел выходного массива позволяет получить их интервальные оценки с доверительной вероят­ностью 95 % (см. ячейки F17:G19 таблицы 3.21):

58598,85 < b < 84701,68; -1370,81 < m₁ < -827,08; 68,597 < m₂ < 443,079.

На основании изложенного можно с 95%-й увереннос­тью утверждать, что параметры уравнения содержат информацию, значимую для расчета исследуемого показателя.

Таблица 3.21 - Регрессионный анализ

	А	В	С	D	E	F	G
1	ВЫВОД ИТОГОВ
2
3	Регрессионная статистика
4	Множественный R	0,94449
5	R-квадрат	0,892061
6	Нормированный R-квадрат	0,875455
7	Стандартная ошибка	767,1098
8	Наблюдения	16
9
10	Дисперсионный анализ
11		df	SS	MS	F	Значимость F
12	Регрессия	2	63223048,8	31611524,42	53,7193	5,2E-07
13	Остаток	13	7649947,59	588457,5077
14	Итого	15	70872996,4
15
16		Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
17	Y-пересечение	71650,26	6041,29087	11,86009163	2,41E-08	58598,85	84701,68
18	Оборачиваемость товаров, дни	-1098,94	125,841366	-8,732770177	8,46E-07	-1370,81	-827,08
19	Удельный вес товаров с высокими торговыми надбавками, %	255,8378	86,6708225	2,951832613	0,011232	68,59687	443,0787