Экономико-статистическое моделирование и прогнозирование средствами 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	Н
	№ п/п	Уровень рентабель­ности, %
		0, 94
		1, 22
		0, 8
		1, 67
		1, 56
		0, 94
		1, 23
		0, 87
		1, 22
		1, 43
		0, 16
		0, 44
		0, 8
		1, 03
		0, 55
		1, 22
		1, 17
		0, 02
		0, 28
		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	Е
	№ п/п	Уровень рентабельности, %		Результат
		0, 94
		1, 22		Среднее	0, 9385
		0, 8		Стандартная ошибка	0, 10199
		1, 67		Медиана	0, 985
		1, 56		Мода	1, 22
		0, 94		Стандартное отклонение	0, 45611

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

 

	А	В	С	D	Е
	№ п/п	Уровень рентабельности, %		Результат
		1, 23		Дисперсия выборки	0, 20803
		0, 87		Эксцесс	-0, 3986
		1, 22		Асимметричность	-0, 5196
		1, 43		Интервал	1, 65
		0, 16		Минимум	0, 02
		0, 44		Максимум	1, 67
		0, 8		Сумма	18, 77
		1, 03		Счет
		0, 55		Наибольший(5)	1, 22
		1, 22		Наименьший(3)	0, 28
		1, 17		Уровень надежности(95, 0%)	0, 21347
		0, 02
		0, 28
		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
	Поряд­ковый номер месяца	Объем товаро­оборота, ден. ед.	Обора­чивае­мость товаров, дни	Удельный вес торговой площади в общей, %	Удельный вес торгово-оперативного персонала в общей численности работников, %	Удельный вес товаров с высокими торговыми надбавками, %
			43, 5	44, 0	67, 7	22, 5
			43, 0	44, 0	67, 7	18, 0
			43, 0	44, 0	70, 2	24, 9
			43, 5	47, 8	70, 0	24, 4
			43, 0	47, 8	68, 0	20, 6
			42, 5	47, 8	68, 0	19, 0
			43, 0	49, 0	70, 2	22, 2
			41, 5	49, 0	70, 0	21, 6
			42, 0	50, 3	70, 0	19, 8
			41, 5	50, 3	70, 0	19, 7
			40, 5	50, 3	70, 0	23, 1
			40, 0	50, 3	70, 0	23, 9
			40, 0	50, 3	68, 0	21, 2
			39, 0	50, 3	68, 0	20, 4
			39, 5	50, 3	70, 0	24, 2
			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
		Объем товарооборота, ден. ед.	Оборачиваемость товаров, дни	Удельный вес торговой площади в общей, %	Удельный вес торгово-оперативного персонала в общей численности работников, %	Удельный вес товаров с высокими торговыми надбавками, %
	Объем товарооборота, ден. ед.
	Оборачиваемость товаров, дни	-0, 90538094
	Удельный вес торговой площади в общей, %	0, 75014268	-0, 690531
	Удельный вес торгово-оперативного персонала в общей численности работников, %	0, 38663194	-0, 153964	0, 3726409
	Удельный вес товаров с высокими торговыми надбавками, %	0, 50878765	-0, 276455	0, 0374572	0, 580400123

 

Вывод: На основе приведенной матрицы можно содержательно оценить связь значений объема товарооборота с каждым из отобранных факторов и выбрать наиболее значимые из них для включения в модель. Так, полученные коэффициенты корреляции, характеризующие тесноту связи объема товаро­оборота с отобранными факторами (см. столбец В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
	Порядковый номер месяца	Объем товарооборота, ден. ед.
	Итого

Выполнение:

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

 

⇐ Предыдущая12345678910Следующая ⇒

Поделиться: