Моделирование экономических процессов на базе EXCEL

      3. Моделирование в EXCEL

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

      Анализ чувствительности заключается в оценке влияния изменения исходных параметров проекта на его конечные характеристики, в качестве которых, обычно, используется внутренняя норма прибыли или NPV. Техника проведения анализа чувствительности состоит в изменении выбранных параметров в определенных пределах, при условии, что остальные параметры остаются неизменными. Чем больше диапазон вариации параметров, при котором NPV или норма прибыли остается положительной величиной, тем устойчивее проект.

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

      В качестве варьируемых исходных переменных принимают:

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

      В качестве результирующих показателей  реализации проекта могут выступать:

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

      Результаты  анализа чувствительности приводятся в табличной или графической  формах.

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

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

      Термин  «линейное программирование» не означает написание компьютерной программы, а характеризует определение  плана работы конкретного экономического объекта на основе выявления линейных связей между его элементами.

      Для решения ЗЛП разработано много  методов, как графических, так и  численных. В MS Excel для решения линейных и целочисленных задач с ограничениями  используется метод ветви и границы, разработанный Джоном Уотсоном и Деном Филстра. Практически этот метод реализуется встроенной процедурой ПОИСК РЕШЕНИЯ, являющейся одной из надстроек Excel.

      Так же, как и ТАБЛИЦЫ ПОДСТАНОВКИ, и ПОДБОР ПАРАМЕТРА, ПОИСК РЕШЕНИЯ  является частью блока задач, который  иногда называют анализом «что-если». Использование процедуры ПОИСК РЕШЕНИЯ предоставляет возможности:

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

      ПОИСК РЕШЕНИЯ - это мощное средство Excel, позволяющее  найти и целевую функцию, и  параметры, при которых достигается минимум, максимум или определенное значение целевой функции.

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

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

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

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

- Двухфакторный дисперсионный анализ с повторениями - этот вид анализа применяется, если данные можно систематизировать по двум параметрам.

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

      Корреляционный анализ. Функции КОРРЕЛ и ПИРСОН вычисляют коэффициент корреляции между двумя переменными измерений, когда для каждой переменной измерение наблюдается для каждого субъекта N (пропуск наблюдения для субъекта приводит к игнорированию субъекта в анализе). Корреляционный анализ иногда применяется, если имеется более двух переменных измерений для каждого субъекта N. В результате выдается таблица, корреляционная матрица, показывающая значение функции КОРРЕЛ (или ПИРСОН) для каждой возможной пары переменных измерений.

Коэффициент корреляции, как ковариационный анализ, характеризует область, в которой  два измерения "изменяются вместе". В отличие от ковариационного анализа коэффициент масштабируется таким образом, что его значение не зависит от единиц, в которых выражены переменные двух измерений (например, если вес и высота являются двумя измерениями, значение коэффициента корреляции не изменится после перевода веса из фунтов в килограммы). Любое значение коэффициента корреляции должно находится в диапазоне от -1 до +1 включительно.

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

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

Ковариационный  анализ вычисляет значение функции  КОВАР для каждой пары переменных измерений (напрямую использовать функцию КОВАР вместо ковариационного анализа имеет смысл при наличии только двух переменных измерений, то есть при N=2). Элемент по диагонали таблицы, возвращаемой после проведения ковариационного анализа в строке i, столбец i, является ковариационным анализом i-ой переменной измерения с самой собой; это всего лишь дисперсия генеральной совокупности для данной переменной, вычисляемая функцией ДИСПР.

      Анализ Фурье. Предназначается для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Эта процедура поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.

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

      Ранг и персентиль. Используется для вывода таблицы, содержащей порядковый и процентный ранги для каждого значения в наборе данных. Данная процедура может быть применена для анализа относительного взаиморасположения данных в наборе. Она использует функции РАНГ и ПРОЦЕНТРАНГ. РАНГ не работает со связанными значениями. Если требуется учитывать связанные значения, можно воспользоваться функцией РАНГ вместе с коэффициентом изменения, описанным в файле справки для функции РАНГ.

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

      Выборка. Создает выборку из генеральной совокупности, рассматривая входной диапазон как генеральную совокупность. Если совокупность слишком велика для обработки или построения диаграммы, можно использовать представительную выборку. Кроме того, если предполагается периодичность входных данных, то можно создать выборку, содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж, создание выборки с периодом 4 разместит в выходном диапазоне значения продаж из одного и того же квартала.

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

Заключение

      Модель  представляет собой абстрактное описание системы (объекта, процесса, проблемы, понятия) в некоторой форме, отличной от формы их реального существования.

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

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