по состоянию на 25 января 2005 года

<<< Главная страница | < Назад

Регрессионный анализ. Оценка параметров экономической модели в программе MS Excel

 

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

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

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

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

Компьютерные эксперименты позволяют построить модель любого процесса и исследовать ее. При этом становятся очевидными скрытые взаимосвязи между разными экономическими показателями, а также появляется возможность прогнозировать поведение модели, задавая различные параметры.

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

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

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

 

Y = a₁ Ч X + a₂,

 

где Y ¯ объем продаж.

Эта переменная зависит от затрат на рекламу (Х), но зависит не напрямую, а через переменные a₁ и a₂, которые пока неизвестны. Их необходимо вычислить методом регрессионного анализа, т.е. специальным образом проанализировав статистику по объемам продаж и рекламным расходам за предыдущие месяцы.

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

Регрессионный анализ иначе называется эконометрией, а также приближением, сглаживанием и аппроксимацией, иногда эволюцией (от английского evaluation). Кроме того, здесь оцениваются параметры модели. Пожалуй, наиболее точно передают суть регрессионного анализа термины «сглаживание» и «приближение». Как видно из иллюстраций, именно использованием метода сглаживания достигается построение графика, который максимально приближен к исходным значениям (точкам на графике). Этот график выражает параметры a₁ и a₂, которые и необходимо найти. В терминологии легко запутаться, однако на практике все значительно проще, и выполнить необходимые манипуляции в программе MS Excel не представляет большой сложности.

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

Когда информация собрана и сведена в таблицу, нужно отобразить ее в графическом виде, т.е. расположить каждую из 18 пар «Рекламный бюджет – Объем продаж» в виде точки на плоскости. Для этого в книге Excel нужно выделить ячейки с данными и запустить «Мастер диаграмм» (меню «Вставка – Диаграмма»). В появившемся окне необходимо выбрать точечный тип диаграммы, который обычно рекомендуется для первичного анализа данных. Затем в диалоговом окне нужно несколько раз нажать кнопку «Далее» и кнопку «Готово». В результате на активном листе появится точечная диаграмма, которая отражает статистическую информацию в графическом виде. По горизонтальной оси расположены значения рекламного бюджета, а по вертикальной оси ¯ соответствующие значения объема продаж.

Точки на графике расположились довольно хаотически, хотя общая тенденция все же прослеживается. Какие же коэффициенты a₁ и a₂ в точности описывают эту функцию? Чтобы ответить на этот вопрос, необходимо построить данную функцию, т.е. построить линию тренда и уравнение с оценкой параметров.

Параметры линейной модели оцениваются в MS Excel с помощью той же статистической функции «ЛИНЕЙН» (в англоязычной версии MS Office ¯ «LINEST»), которая используется при построении линии тренда. Эта функция рассчитывает статистику для ряда с применением метода наименьших квадратов.

Проводя регрессионный анализ, MS Excel вычисляет для каждой точки квадрат разности между прогнозируемым и фактическим значением. Сумма этих квадратов разностей называется остаточной суммой квадратов. Затем программа подсчитывает сумму квадратов разностей между фактическими значениями и средним значением, которая называется общей суммой квадратов (регрессионая сумма квадратов плюс остаточная сумма квадратов). Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности (R²), который показывает, насколько полно уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными.

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

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

Чтобы построить линию тренда, нужно нажать правой кнопкой мыши на любой точке диаграммы, которая соответствует значению, и выбрать в появившемся контекстном меню команду «Добавить линию тренда». На экране возникает диалоговое окно с несколькими вариантами регрессионного анализа статистической информации. Линия тренда может быть линейная, логарифмическая, полиномиальная, степенная, экспоненциальная или «скользящее среднее».

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

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

Итак, выберем линейную линию тренда и перейдем на вкладку «Параметры», где можно выбрать название для графика и количество единиц для прогноза (т.е. выбрать, на сколько месяцев вперед выполнять аппроксимацию найденного тренда). Это несущественные параметры, но есть и важные для нас: нужно обязательно пометить галочкой опции, позволяющие отражать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R²).

После этого на экране появится линия тренда в виде линейной функции, а также уравнение с числовыми параметрами и коэффициент достоверности параметров. Это и есть искомая функция (Y = a₁ Ч X + a₂). Программа MS Excel автоматически вычислила все необходимые параметры, построила график и вывела уравнение на экран, так что его осталось только распечатать. Теперь мы знаем точные параметры зависимости объемов продаж от расходов на рекламу (с оговорками, конечно). Полученная модель полностью готова для применения в прогнозировании и планировании.

Теперь можно попробовать построить более сложную модель, в которой на объем продаж влияет не один фактор (затраты на рекламу), а сразу два – затраты на рекламу и цена товара. Такая модель называется двухфакторной регрессией, или двухфакторной линейной моделью. Чтобы подготовить данные для вычислений, нужно добавить в нашу таблицу информацию о цене товара по месяцам и построить еще одну однофакторную модель вышеописанным способом. На втором графике также присутствует формула, описывающая линейную функцию (Y = a₃ Ч X + a₄), в которой известны коэффициенты a₃, a₄.

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

Имея перед глазами обе формулы, нам достаточно всего лишь сложить их друг с другом и разделить на два. Исходные данные:

 

Y = 0,543x₁ + 5221,1

Y = –691,26x₂ + 84392

 

Результат вычислений:

 

Y = 0,2715x₁ – 345,63x₂ + 44806,55

 

Это уже двухфакторная модель зависимости объема продаж одновременно от цены товара и от расходов на рекламу. По этой формуле можно построить новый график (функция «ЛИНЕЙН») и аппроксимировать его, т.е. сделать прогноз на будущее. Данная модель помогает прогнозировать изменение прибыли фирмы при снижении или повышении цены товара, а также при изменении рекламных расходов. Интересно, что формула позволяет найти и зависимость цены товара от расходов на рекламу. Естественно, никакой зависимости в реальности, скорее всего, не существует, но Excel все-таки показывает результат вычислений – для этой программы нет ничего невозможного.

 

АНАТОЛИЙ АЛИЗАР

 

<<< Главная страница | < Назад

pravo.kulichki.ru ::: pravo.kulichki.com ::: pravo.kulichki.net

2004-2015 Республика Беларусь