2015-04-08
614
Наиболее широкое распространение при построении прогнозов развития в практике коммерческой деятельности получили экономико-статистические модели, которые описывают зависимость исследуемого экономического показателя от одного или нескольких факторов, оказывающих на него существенное влияние.
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 по избранному признаку целесообразно пересмотреть и урезать выборку.
