Статистическое моделирование в excel

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

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

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

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

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

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

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

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

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

Таблица 3.1 — Шкала
значений коэффициентов корреляции

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выполнение:

Порядок
обработки ряда данных с помощью
инструмента Описательная статистика
установлен в диалоговом окне Описательная
статистика,
которое можно вызвать из меню Сервис
через команду Анализ
данных.
Открывшееся окно диалога предлагает
пользователю определиться с набором
следующих параметров (рисунок 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;

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

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

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

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

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

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

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

Вывод:
Приведенные в таблице 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 — Исходные данные для проведения
корреляционного анализа

Выполнение:

Для
количественной оценки взаимосвязи
объема данных обра­щаются к инструменту
Корреляция,
содержащемуся в паке­те «Статистический
анализ» 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 — Матрица парных коэффициентов
корреляции

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

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

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

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

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

Выполнение:

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

Вывод:
Приведенные на рисунках 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 — Расчет прогноза товарооборота с
помощью инструмента Экспоненциальное
сглаживание

Задание
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 — Оценка тенденции поведения показателей
исследуемого динамического ряда методом
скользящего среднего

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

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

Выполнение:

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

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

—
учитывая, что объем товарооборота
представляет собой абсолютный стои­мостный
показатель, для расчета скользящего
среднего соз­дадим в разработочной
таблице колонку относительных ве­личин,
характеризующих динамику товарооборота
предпри­ятия — цепных темпов прироста
(вводим в ячейку С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
(итоговая таблица)

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

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

Выполнение:

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

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

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

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

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

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

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

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

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

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

Рисунок 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 — Расчет прогноза товарооборота с
помощью функции ТЕНДЕНЦИЯ

Задание
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 — Расчет и оценка модели тренда с
помощью функции ЛГРФПРИБЛ

Вывод:
Приведенный в таблице 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-й степени)

При
определении в диалоговом окне ЛИНЕЙН
аргумен­тов функции формируется
следующая формула массива:
=ЛИНЕЙН(В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 — Исходные данные

Выполнение:

При
построении модели множественной
регрессии целесооб­разнее обратиться
к инструменту 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;

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

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 — Регрессионный анализ

Cтатистический анализ данных, моделирование и прогноз

Электронный процессор Excel позволяет строить математические модели
по
имеющимся табличным данным. Математическая модель дает возможность
прогнозировать состояние моделируемого объекта и выбирать на этой основе
оптимальное управление объектом. Для этих целей Excel содержит пакет
анализа данных, в который входят: регрессионный анализ, корреляция,
дисперсионный анализ и другие средства.

Регрессионный анализ

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

Пример простой линейной регрессии:

y=m₁x+b.

Пример множественной линейной регрессии:

Для оценки степени связи между величинами используется коэффициент
множественной корреляции R Пирсона (корреляционное отношение), который
может принимать значения от 0 до 1. R=0 если между величинами нет
никакой связи и R=1, если между величинами имеется функциональная
(детерминированная) связь. В большинстве случаев R принимает
промежуточные значения от 0 до 1. Величина
R² называется коэффициентом детерминации.

Задачей построения регрессионной зависимости является нахождение вектора
коэффициентов M модели (1) при котором коэффициент R принимает максимальное значение.

Для оценки значимости R применяется F-критерий Фишера, вычисляемый по
формуле

где n — размер выборки (количество экспериментов); k — число
коэффициентов модели.
Если F превышает некоторое критическое значение для данных n и k и
принятой
доверительной вероятности, то величина R считается существенной. Таблицы
критических значений F приводятся в справочниках по математической
статистике.

Таким образом, значимость R определяется не только его величиной, но и
соотношением между количеством экспериментов и количеством коэффициентов
(параметров) модели. Действительно, корреляционное отношение для n=2
для простой линейной модели равно 1 (через 2 точки на плоскости можно
всегда провести единственную прямую). Однако, если экспериментальные
данные являются случайными величинами, доверять такому значению R
следует с большой осторожностью. Обычно для получения значимого R и
достоверной регрессии стремятся к тому, чтобы количество экспериментов
существенно превышало количество коэффициентов модели (n>>k).

Для построения линейной регрессионной модели необходимо:

1) подготовить список из n строк и m столбцов, содержащий
экспериментальные данные (столбец, содержащий выходную величину y должен
быть либо первым, либо последним в списке);

2) обратиться к меню Сервис/Анализ данных/Регрессия

Если пункт «Анализ данных» в меню «Сервис» отсутствует, то следует обратиться к пункту «Надстройки» того же меню и установить флажок «Пакет анализа».

3) в диалоговом окне «Регрессия» задать:

• входной интервал Y;
• входной интервал X;
• выходной интервал — верхняя левая ячейка интервала, в который будут
  помещаться результаты вычислений (рекомендуется разместить на новом
  рабочем листе);

4) нажать «Ok» и проанализировать результаты.

Пример использования множественной линейной регрессии

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

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

В этом примере предполагается, что существует линейная зависимость между
каждой независимой переменной (x₁, x₂,
x₃ и x₄) и зависимой переменной (y), то есть ценой здания под офис в данном районе. Исходные данные показаны на рисунке.

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

В итоге мы получили следующую математическую модель:

y = 52318 + 27,64*x₁ + 12530*x₂ + 2553*x₃ —
234,24*x₄.

Теперь застройщик может определить оценочную стоимость здания под
офис в том же районе. Если это здание имеет площадь 2500 квадратных
метров, три офиса, два входа и время эксплуатации — 25 лет, можно
оценить его стоимость, используя следующую формулу:

y = 27,64*2500 + 12530*3 + 2553*2 — 234,24*25 + 52318 = 158 261 у.е.

В регрессионном анализе наиболее важными результатами являются:

• коэффициенты при переменных и Y-пересечение, являющиеся искомыми
  параметрами модели;
• множественный R, характеризующий точность модели для имеющихся
  исходных данных;
• F-критерий Фишера (в рассмотренном примере он значительно превосходит критическое значение, равное 4,06);
• t-статистика – величины, характеризующие степень значимости отдельных
  коэффициентов модели.

На t-статистике следует остановиться особо. Очень часто при
построении
регрессионной модели неизвестно, влияет тот или иной фактор x на y.
Включение в модель факторов, которые не влияют на выходную величину,
ухудшает качество модели.
Вычисление t-статистики помогает обнаружить такие факторы. Приближенную
оценку можно сделать так: если при n>>k величина t-статистики по
абсолютному значению существенно больше трех, соответствующий
коэффициент следует считать значимым, а фактор включить в модель, иначе
исключить из модели. Таким образом, можно предложить технологию
построения регрессионной модели, состоящую из двух этапов:

1) обработать пакетом «Регрессия» все имеющиеся данные, проанализировать
значения t-статистики;
2) удалить из таблицы исходных данных столбцы с теми факторами, для
которых коэффициенты незначимы и обработать пакетом «Регрессия» новую
таблицу.

Для примера рассмотрим переменную x4. В справочнике по математической
статистике t-критическое с (n-k-1)=6 степенями свободы и доверительной
вероятностью 0,95 равно 1,94. Поскольку абсолютная величина t, равная
17,7 больше, чем 1,94, срок эксплуатации — это важная переменная для
оценки стоимости здания под офис. Аналогичным образом можно
протестировать все другие переменные на статистическую значимость.
Ниже приводятся наблюдаемые t-значения для каждой из независимых
переменных:

Все эти значения имеют абсолютную величину большую, чем 1,94;
следовательно, все переменные, использованные в уравнении регрессии,
полезны для предсказания оценочной стоимости здания под офис в данном
районе.