605
.pdf
МИНИСТЕРСТВО ПУТЕЙ СООБЩЕНИЯ РФ
СИБИРСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ПУТЕЙ СООБЩЕНИЯ (НИИЖТ)
Г.А. ЧЕРЕМНЫХ, О.И. НИКИФОРОВА
МОДЕЛИРОВАНИЕ, АНАЛИЗ
ИПОДГОТОВКА ДЕЛОВЫХ РЕШЕНИЙ
ВСРЕДЕ MS EXCEL
Учебное пособие
Новосибирск 2009
УДК 330:681.3 Ч–464
Ч е р е м н ы х Г.А., Н и к и ф о р о в а О.И. Моделирование, анализ и подготовка деловых решений в среде MS Excel: Учеб. пособие. — Новосибирск: Изд-во СГУПСа, 2009. — 110 с.
ISBN 5-93461-354-5
Рассмотрены необходимые теоретические сведения, методика и практика использования некоторых базовых и специальных информационных технологий моделирования, подготовки, оценки и анализа деловых решений в среде MS Excel.
Издание ориентировано на студентов специальности 080502 «Экономика и управление на предприятии (железнодорожный транспорт, строительство)», изучающих дисциплину «Компьютерное обеспечение экономической работы».
Рассматриваемые в учебном пособии информационные технологии могут использоваться в условиях реального бизнеса экономистами, аналитиками, менеджерами среднего и высшего звена, бухгалтерами с высшим образованием и другими специалистами как инструментальное средство автоматизированной обработки и анализа данных.
Утверждено редакционно-издательским советом университета в качестве учебного пособия.
О т в е т с т в е н н ы й р е д а к т о р ст. преп. Н.М. Ткачук
Р е ц е н з е н т ы:
кафедра экономической информатики Новосибирского государственного технического университета (и.о. завкафедрой канд. техн. наук, доц. А.В. Кравченко)
канд. экон. наук, проф. Российского государственного открытого технического университета путей сообщения Н.В. Федотова
ISBN 5-93461-354-5 |
© Черемных Г.А., Никифорова О.И., 2009 |
|
|
|
© Сибирский государственный |
|
университет путей сообщения, 2009 |
ПРЕДИСЛОВИЕ
Предлагаемое вашему вниманию учебное пособие ориентировано на специфику дисциплины «Компьютерное обеспечение экономической работы» (КОЭР) специальности «Эконо- мист-менеджер» дневного отделения ИЭФ.
Дисциплина КОЭР имеет региональный статус и включена в учебные планы специальности 8-го и 9-го семестров в объеме 119 ч, из них лабораторные работы — 68, самостоятельная работа — 51 ч; лекции учебными планами не предусмотрены.
Дисциплина КОЭР опирается на базовые знания профилирующих дисциплин выпускающих кафедр, лабораторные работы выполняются студентами в компьютерных учебных классах СГУПСа с использованием технического и программного обеспечения.
Изучение дисциплины КОЭР предполагает:
•ознакомление будущих экономистов-менеджеров с некоторыми типовыми экономическими задачами, для решения которых сегодня используются компьютерные технологии и информационные системы;
•обучение студентов выявлению количественных взаимосвязей между экономическими показателями исследуемого хозяйственного процесса и записи их на языке математики в виде формул, функций, неравенств, разработке экономико-математической модели процесса;
•освоение студентами некоторых базовых и специальных технологий моделирования, подготовки, оценки и анализа деловых решений в среде MS Excel, методики использования рассмотренных в работе информационных технологий при решении своих профессиональных задач.
Структура и содержание предлагаемого учебного пособия базируются на многолетнем опыте преподавания этой дисциплины авторами в СГУПСе и на изучении аналогичного зарубежного опыта.
Разделы пособия соответствуют классам типовых экономических задач и имеют идентичную структуру:
•внутри раздела сформулированы задачи, обсуждаемые на лабораторных работах;
•по каждой задаче приведены краткие теоретические и методические положения, включающие основные понятия, определения, формулы, в том числе сведения, полученные студентами ранее при изучении соответствующей профильной дисциплины;
•определена соответствующая информационная технология пакета прикладных программ MS Excel и показан порядок ее использования для решения тестового примера;
•по обсуждаемым темам приведены для тренинга контрольные задания, которые представляются к защите преподавателю.
3
1. ВВЕДЕНИЕ В МАТЕМАТИЧЕСКОЕ МОДЕЛИРОВАНИЕ ЭКОНОМИЧЕСКИХ ПРОЦЕССОВ В СРЕДЕ MS EXCEL.
ЭЛЕКТРОННАЯ ТАБЛИЦА КАК СРЕДСТВО ПОДДЕРЖКИ ДЕЛОВЫХ РЕШЕНИЙ
Среда моделирования MS Excel обеспечивает анализ данных и подготовку решений на основе математических моделей исследуемых экономических процессов (деловых ситуаций). Построена по типу систем поддержки и принятия решений. Теория принятия решений использует понятия [3]:
•альтернатива — возможные варианты решения задачи, множество конечных исходов задачи. Задачи выбора одной из известных альтернатив носят название задач оценки, в отличие от задач разработки, которые создают стратегии решения;
•решение — выбор одной из нескольких альтернатив, а также процесс выбора. Для принятия решения необходимы четко сформулированная цель, список альтернатив и правила их выбора. Принятие решений осуществляется с учетом всех факторов; решение основывается на моделировании ситуаций;
•выбор — правило (алгоритм) предпочтения альтернативы при принятии решения и система оценки (критериев) принимаемых решений;
•полезность — характеристика эффективности решения;
•оптимизация — повышение эффективности выбранного решения путем выбора альтернативы.
Математическое моделирование процесса — это выражение на математическом языке основных свойств, сторон процесса в их взаимной связи и обусловленности. Математический язык является универсальным языком, позволяющим проводить качественный и количественный анализ процесса в органическом единстве. Математическое моделирование экономического процесса позволяет глубже вскрыть его внутреннюю сущность, механизм развития, такие черты, которые были не известны до моделирования.
Наиболее часто математическая модель экономического процесса представляет собой систему уравнений и неравенств, состоящих из набора переменных величин и параметров. Переменные математической модели характеризуют определенные экономические величины (например, объем выпускаемой продукции, объем капитальных вложений), а параметры — количественные связи между отдельными экономическими величинами (например, нормы расхода сырья на единицу продукции определенного вида). Параметр модели — это относительно постоянная величина, включаемая в модель и рассматриваемая как свойство моделируемого процесса. При моделировании параметры выступают как выбираемые значения. Среди параметров выделяются такие, которые изменяют содержание модели, их называют управляющими параметрами. В свою очередь, значения параметров модели являются результатом обработки данных, полученных в процессе эксперимента или наблюдения с помощью различных статистических методов.
Переменные модели делятся на:
•экзогенные (внешние);
•эндогенные (внутренние).
Экзогенные по отношению к моделируемому процессу переменные рассматриваются как входные переменные, значения которых известны. Часто экзогенные переменные рассматриваются как ограничения в модели. Эндогенные по отношению к моделируемому процессу переменные считаются внутренними, их значения должны быть найдены в результате исследования, они непосредственно используются в управлении процессом и делятся на:
•независимые, изменяющие свое значение произвольным образом;
•зависимые, являющиеся результатом изменения значений других переменных.
Степень независимости переменных и взаимосвязи переменных друг с другом может быть различной.
Количественное решение математической модели экономического процесса предполагает нахождение значений неизвестных (эндогенных) переменных величин, которые должны быть выражены через значения параметров, и известных (экзогенных) переменных, или должен быть дан алгоритм их нахождения по этим данным [3].
4
Математическое моделирование изучаемого экономического процесса начинается с выделения его наиболее существенных свойств и сторон, описания их количественных взаимозависимостей. Математическая модель процесса должна отражать только главные, важнейшие его характеристики, закономерности развития, определяющие внутреннюю природу процесса,
впротивном случае математическая модель становится громоздкой и не поддается анализу. При этом в зависимости от цели научного анализа разрабатывают математическую модель процесса в целом или его части или моделируют лишь определенные его функции [14].
Усложнять модель необходимо только в том случае, если это может привести к получению новых существенных выводов о природе процесса или уточнению его характеристики. С другой стороны, построение излишне упрощенной модели процесса может привести к тому, что она не будет адекватной ему, и полученные на ее основе выводы потеряют практическую и познавательную ценность. Таким образом, границы сложности математической модели определяются, с одной стороны, необходимостью адекватности, а с другой — возможностями математического аппарата и компьютерной техники, а также информационного обеспечения.
Математическое моделирование определенного экономического процесса принципиально может выполнять две функции: отражать существующий экономический процесс; определять желаемое (планируемое) протекание экономического процесса. В первом случае математическая модель описывает реально существующий экономический процесс, взаимосвязь включенных в него экономических величин — переменных и параметров с целью выявления объективно существующих основных свойств процесса, его природы. Во втором — математическая модель играет активную роль; она должна показать, как будет или как должен протекать процесс, если в основу положить определенную систему конституирующих его природу предпосылок (показателей). Центр тяжести математического моделирования здесь переносится на то, чтобы была правильно сформулирована система предпосылок, которая должна определить желательную природу планируемого экономического процесса [14].
Вматематическом моделировании экономических процессов условно можно выделить два этапа: создание математической модели процесса, выраженной на языке математических символов; заполнение модели конкретными числовыми значениями переменных и параметров. Оба этапа одинаково важны с точки зрения того, чтобы математическая модель в необходимой степени адекватно отражала моделируемый экономический процесс. На первом этапе определяются важнейшие свойства и черты изучаемого процесса, а также принципиальные функциональные зависимости между входящими в него переменными величинами, на втором — числовые значения переменных и параметров.
Как бы точно математическая модель ни отражала изучаемый экономический процесс, полученные на ее основе выводы не смогут иметь практической ценности, если будут неправильно определены числовые значения параметров и экзогенных переменных. Необходимость адекватности модели изучаемому процессу, а также наличие соответствующей экономической информации о параметрах и переменных и возможности используемой компьютерной техники
врешающей степени определяют размерность математической модели, т.е. количество составляющих ее уравнений, переменных и параметров. Размерность модели оказывает существенное влияние на выбор методов количественного анализа, эффективность вычислительной обработки. Сокращение размерности осуществляется путем агрегирования параметров и уменьшения числа переменных модели.
Для исследования сложных экономических процессов создается иерархия взаимосвязанных моделей — система моделей. Система моделей включает наиболее агрегированные (обобщенные) и детализированные модели, определяет условия их взаимодействия: входные переменные агрегированных моделей рассматриваются как экзогенные переменные детализированных моделей.
По виду зависимости между входными и выходными переменными различают детерминированные и вероятностные модели. В детерминированных моделях выходные параметры однозначно определяются значениями входных переменных, при этом случайными, не предвиденными заранее воздействиями полностью пренебрегают. Для совокупности входных значений на выходе всегда получается единственно возможный результат. Вероятностные модели содержат случайные величины, благодаря которым для совокупности входных значений на выходе могут быть получены различные результаты. Вероятностные (или стохастические) модели учитывают фактор неопределенности информации, ее неточность или неполноту.
5
По назначению модели могут быть разделены на два класса:
•дескриптивные модели — предназначены для описания и объяснения наблюдаемых фактов или прогноза поведения процесса моделирования;
•нормативные модели — предназначены для нахождения желательного (оптимального) состояния процесса моделирования.
Изменения значений параметров и их взаимосвязи могут по-разному учитываться в моделях. В связи с этим различают статические и динамические модели. В статических моделях все зависимости отнесены к единому моменту времени, связи параметров и переменных фиксированы. В динамических моделях описывается развитие объекта во времени: учитывается изменение взаимосвязи параметров, переменных. Среди динамических моделей различают:
•оптимизационные модели — обеспечивают поиск наилучшего из возможных вариантов развития моделируемого процесса;
•модели равновесия — обеспечивают баланс различных показателей процесса. Динамические модели описывают начальное состояние процесса, изменение состояния и
используемые критерии оптимальности. Наиболее часто применяются временные ряды, для которых определяются тренд, сезонные колебания, случайная переменная (остаток). В динамических моделях учитывается разновременность значений переменных путем использования лагов, коэффициентов приведения, коэффициентов дисконтирования и т.п.
По виду функциональной зависимости между переменными, входящими в систему ограничений, модели делят на линейные и нелинейные. Большинство функциональных зависимостей между экономическими переменными имеют нелинейный характер. Однако при решении задач на основе нелинейных моделей сложно получить соответствующую экономическую информацию, и эти модели труднее поддаются математической обработке. Поэтому часто приходится аппроксимировать нелинейные связи между переменными их линейными приближениями.
Взависимости от длительности временного периода, на который определяется решение, математические модели могут быть краткосрочного (месяц, квартал, год), среднесрочного (до 5 лет) и долгосрочного (свыше 5 лет) планирования и прогноза поведения процесса. При этом могут меняться ограничения моделей, их размерность, методы формирования экономической информации о параметрах и экзогенных переменных и т.д.
Количественные методы анализа, разрабатываемые на стыке математики, кибернетики, экономики и информатики, позволяют реализовать большинство экономико-математических моделей. Выбор методов должен соответствовать сути модели и учитывать информационные технологии моделирования.
Информационные технологии моделирования определяют его информационное, программное и техническое обеспечение. Информационное обеспечение процесса моделирования основано на создании и ведении баз данных, содержащих значения параметров модели и исходные данные. Программные средства способствуют реализации вычислительных алгоритмов количественного анализа данных.
Среда моделирования MS Excel предоставляет пользователю широкие возможности для изучения различных экономических процессов через их математические модели [3].
Всреде MS Excel 2000, 2003, 2007 можно создавать комплексные информационные технологии для поддержки и принятия решений, основанные на компонентной архитектуре (COM — Component Object Model). В отдельном приложении интегрируются функции обработки различных программ в виде дополнительных пользовательских команд или специальных надстроек, между компонентами поддерживаются стандартные интерфейсы. Информационная технология OLE 2.0 (Object Linking and Embedding) позволяет включать в приложение Microsoft Excel объекты других приложений Microsoft Office.
Анализ данных требует применения эффективных информационных технологий подготовки исходных данных. Для больших, регулярно формируемых объёмов исходных данных разрабатываются технологии автоматизированного ввода данных в приложение Microsoft Excel путем конвертирования данных, создания запросов к внешним данным на базе Microsoft Query. Microsoft Excel позволяет работать с различными агрегированными представлениями в виде OLAPкубов (On-Line Analytical Processing) — многомерных данных, сформированных на основе баз данных серверов для целей анализа в Microsoft Excel.
К стандартным и специализированным технологиям анализа данных, реализуемым в Microsoft Excel, относятся:
6
•представление исходных данных в виде списков (базы данных) Microsoft Excel для целей анализа;
•фильтрация списков (базы данных) Microsoft Excel по различным условиям;
•агрегирование и своды исходных данных в виде списков (базы данных) Microsoft Excel, получение промежуточных итогов;
•использование встроенных функций Microsoft Excel;
•разработка и использование функций пользователя на языке Visual Basic;
•разработка и использование пользовательских шаблонов;
•графические методы решения экономических задач и представления результатов;
•Web-технологии Microsoft Excel;
•моделирование и оценка зависимостей между экономическими показателями;
•подбор параметров модели по заданному значению функционала;
•многовариантные расчеты и анализ чувствительности модели с помощью сценариев;
•быстрая генерация многовариантных решений и анализ чувствительности модели с помощью счетчиков;
•подстановка табличных значений в функционал модели;
•методы математического программирования для решения оптимизационных задач;
•разработка и оценка краткосрочных прогнозов экономических показателей на основе временных рядов;
•статистическая обработка экономической информации с помощью Пакета анализа
идр.
Знакомство с моделированием в среде MS Excel начнем с простой деловой ситуации, приведенной в нижеследующем задании.
Задание 1
Фирма К выпускает промышленное оборудование и продает эту продукцию клиентам. Своим постоянным клиентам фирма обычно предоставляет торговый кредит (отсрочку по оплате) сроком на один месяц под соответствующие проценты. В текущем месяце фирма отпустила свою продукцию корпорациям А, В, С. Данные о совершенных фирмой продажах в кредит
втекущем месяце представлены в табл. 1.1. Ежегодный рост инфляции в среднем 12 % (т.е. 1 %
вмесяц) [12].
|
|
Таблица 1.1 |
Объемы продаж оборудования фирмой К постоянным клиентам |
||
|
|
|
Постоянные клиенты |
Объем продаж, $ |
Процент кредитования |
|
|
|
Корпорация А |
10000 |
5 |
Корпорация В |
30000 |
2 |
Корпорация С |
15000 |
3,5 |
Определите сумму выручки фирмы от продаж продукции в кредит с учетом приведенных выше условий. Сделайте выводы.
Методика выполнения задания
1. Приступим к математическому моделированию описанного в задании экономического процесса. По условию исследуется лишь одна функция процесса — продажа промышленного оборудования фирмой К своим постоянным клиентам с отсрочкой по оплате на один месяц под соответствующие проценты кредитования.
Экономическая эффективность процесса определяется суммарной месячной выручкой от продаж оборудования в кредит с учетом инфляции. Эта выручка должна быть больше объема продаж постоянным клиентам без условий кредитования, причем чем больше разница между этими показателями, тем выше эффективность. Однако в задании речь идет о количественной оценке лишь одного (заданного) варианта развития процесса на один месяц.
Входными (экзогенными) переменными исследуемой деловой ситуации являются список постоянных клиентов и месячные объемы продаж им промышленного оборудования, а ее параметрами — проценты кредитования и средний уровень месячной инфляции. Последний являет-
7
ся управляющим параметром, поскольку его значение непосредственно влияет на экономическую эффективность процесса: чем выше средний уровень месячной инфляции, тем меньше суммарная месячная выручка от продаж оборудования в кредит с учетом инфляции. Чаще всего средний уровень месячной инфляции медленно ползет вверх. Однако в исходной постановке задачи его величина задана конкретным значением. Заметим также, что в процентах кредитования просматривается тенденция: чем больше объем продаж постоянному клиенту, тем ниже его процент кредитования.
Выходными (эндогенными) переменными исследуемой деловой ситуации являются:
•суммарная месячная выручка фирмы от продажи оборудования постоянным клиентам без условий кредитования;
•месячная выручка фирмы от продаж в кредит без учета инфляции по каждому постоянному клиенту;
•суммарная месячная выручка фирмы от продажи оборудования в кредит без учета инфляции по всему списку постоянных клиентов;
•месячная сумма инфляции;
•суммарная месячная выручка фирмы от продажи оборудования в кредит с учетом инфляции по всему списку постоянных клиентов;
•эффективность процесса кредитования фирмой постоянных клиентов, проц.
Опишем теперь на математическом языке количественные взаимосвязи между переменными и параметрами создаваемой математической модели.
1.Суммарная месячная выручка фирмы от продажи оборудования постоянным клиентам без условий кредитования = сумме месячных объемов продаж по всему списку постоянных клиентов.
2.Месячная выручка фирмы от продаж в кредит без учета инфляции по каждому посто-
янному клиенту = месячный объем продаж клиенту + месячный объем продаж клиенту × процент кредитования клиента.
3.Суммарная месячная выручка фирмы от продажи оборудования в кредит без учета инфляции по всему списку постоянных клиентов = сумме месячной выручки фирмы от продаж в кредит без учета инфляции по каждому постоянному клиенту.
4.Месячная сумма инфляции = суммарной месячной выручке фирмы от продажи обору-
дования в кредит без учета инфляции по всему списку постоянных клиентов × средний уровень месячной инфляции.
5.Суммарная месячная выручка фирмы от продажи оборудования в кредит с учетом инфляции по всему списку постоянных клиентов = суммарной месячной выручке фирмы от продажи оборудования в кредит без учета инфляции по всему списку постоянных клиентов – месячная сумма инфляции.
6.Эффективность процесса кредитования фирмой постоянных клиентов, проц., = [(суммарная месячная выручка фирмы от продажи оборудования в кредит с учетом инфляции по всему списку постоянных клиентов – суммарная месячная выручка фирмы от продажи оборудования постоянным клиентам без условий кредитования) / суммарная месячная выручка фир-
мы от продажи оборудования постоянным клиентам без условий кредитования] 100.
По виду зависимости между входными и выходными переменными и параметрами, входящими в систему вышеприведенных уравнений (см. пп. 1–6), математическая модель является линейной, детерминированной.
Для представления математической модели в среде моделирования MS Excel разработайте электронную табл. 1.2, в соответствующих клетках которой запишите в виде формул соотношения (см. пп. 1–6) между переменными модели. Создавая формулы, следует соблюдать правила их записи в MS Excel.
На основании полученной модели, используя значения ее входных переменных и параметров, определите суммарную выручку от продажи оборудования в кредит с учетом инфляции и рассчитайте эффективность процесса кредитования фирмой К постоянных клиентов (см. электронную табл. 1.3). Сделайте выводы.
2. По данным электронной табл. 1.3 постройте круговую диаграмму, определяющую долю каждой корпорации в общем объеме продаж оборудования фирмой К. Сравните полученные результаты с приведенными на рис. 1.1.
8
З а м е ч а н и е. Круговую диаграмму используют, если нужно отобразить долю каждой составляющей части показателя в общем значении показателя.
Таблица 1.2
Математическая модель продаж оборудования фирмой К постоянным клиентам в текущем месяце
Таблица 1.3
Продажи оборудования фирмой К постоянным клиентам в текущем месяце
9
Рис. 1.1. Доля каждой корпорации в общем объеме продаж оборудования фирмой К постоянным клиентам
3. По данным электронной табл. 1.3, используя информационную технологию «Подбор параметра», определите критический уровень инфляции, при котором фирме станет невыгодно продавать свою продукцию в кредит на прежних условиях.
Критерий оптимальности для принятия решения: если выручка фирмы от продажи продукции в кредит с учетом инфляции (уменьшаясь с ростом уровня среднемесячной инфляции) окажется равной или меньше суммарного объема продаж без кредитования, необходимо менять условия кредитования покупателей.
При такой постановке задачи принципиально меняется функция математического моделирования изучаемой нами деловой ситуации. Если ранее математическая модель лишь описывала реально существующую деловую ситуацию, то теперь модель играет активную роль и должна определить, как будет развиваться процесс при росте значения управляющего параметра — среднего уровня месячной инфляции. Более того, необходимо определить, какой уровень инфляции для фирмы окажется критическим, т.е. таким, при котором ей станет невыгодно продавать свою продукцию в кредит на прежних условиях. Сформулирован и критерий оптимальности для принятия решения, а прежние условия продажи оборудования в кредит определены существующими процентами кредитования постоянных клиентов, и при критическом среднем уровне месячной инфляции фирма должна принять решение эти проценты изменить.
Соотношения пп. 1–6 математической модели при этом не изменяются, но технология моделирования станет иной. Увеличивая средний уровень месячной инфляции в электронной табл. 1.3, надо отслеживать меняющееся значение суммарной месячной выручки фирмы от продажи оборудования в кредит с учетом инфляции, и, как только оно станет равным 55000, соответствующий уровень инфляции и будет критическим. Другими словами, необходимо подобрать такое значение управляющего параметра, при котором зависящая от него выходная переменная модели примет заданное значение (55000).
Для автоматического решения таких задач в среде MS Excel имеется информационная технология «Подбор параметра». При помощи данной технологии находят желаемое значение функции, зависящей только от одного параметра. Этот параметр в математической модели задачи первоначально должен быть задан какой-либо исходной числовой константой. Соответствующая параметру ячейка электронной таблицы называется ячейкой-параметром. В ходе решения задачи исходное значение ячейки-параметра будет автоматически изменяться. Ячейка электронной таблицы, соответствующая функции, зависящей от подбираемого параметра, называется целевой ячейкой. В ней должна быть записана формула с явной или неявной ссылкой на ячейку-параметр. Рассмотрим теперь порядок использования этой технологии на математической модели, представленной в электронной табл. 1.3.
1. В электронной табл. 1.3 установите табличный курсор в целевую ячейку B12 (здесь (см. табл. 1.2) записана формула = B10 – B11 с неявной ссылкой, через ячейку B11, на ячейку-
10