Файл: Учебное пособие для студентов специальностей 125 01 10 Коммерческая деятельность.doc

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) для нас важно проследить, по­пал ли уровень его рентабельности в первую тройку наиболее высокорентабельных предприятий, а также, каков диапазон изменения уровня рентабельности у трех самых высокорен­табельных магазинов. Тогда для

---