Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа № 1
Оптимальная ставка налога, имитационное моделирование
Цель работы: исследовать зависимость поступлений в бюджет от величины налоговой ставки; методами имитационного моделирования найти оптимальную ставку налогообложения прибыли
Теоретические сведения
Определение проблемы
Государство стремится увеличить налоги, чтобы наполнить бюджет для выполнения своих социально-экономических и оборонных функций.
Бизнес жалуется, что налоговое бремя велико, поэтому и налоговые ставки надо уменьшить.
Экономисты утверждают, что большие налоги сдерживают развитие экономики, а значит, и будущее наполнение бюджета.
Проблема. Теория и практика не знают величину приемлемой для всех налоговой ставки.
Задача. Обосновать величину налоговой ставки.
Рабочая гипотеза. Поступления в бюджет за определенный период вре мени будут наибольшими не при максимальной, а при оптимальной для бюджета ставке налога, т. е. с ростом налоговой ставки поступления в бюд жет будут сперва увеличиваться, а затем уменьшаться.
Уточнение и ограничение проблемы. Несмотря на массу налогов и терминов, источником развития бизнеса и источником налогового наполне ния бюджета в конечной инстанции является прибыль, т. е. превышение до ходов над расходами.
Лабораторная модель
Выделение проблемной системы (объекты и функции)
Законодатель объявляет ставку налога.
Бюджет получает налоговые отчисления от прибыли предприятий.
Предприятия по налоговой ставке на прибыль отчисляют средства и бюджет.
Описательная модель
Государство объявляет ставку налога на прибыль и получает от фирм средства в бюджет. Фирмы обладают собственным капиталом, производят прибыль, отчисляют по налоговой ставке средства в бюджет. Постналоговая прибыль как нераспределенная прибыль полностью включается в собствен ный капитал фирмы. Дивиденды не выплачиваются, никаких других отчис лений от прибыли не производится. Вся прибыль распределяется только на два потока: в бюджет, а остаток - в собственный капитал банка.
Математическая модель
Сумма налоговых поступлений в бюджет за моделируемый период представлена формулой
,
где BDt - сумма поступивших в бюджет средств от начала моделирования к концу года t, грн;
t - время, год. Для запасов этот момент - конец года, для потоков это интервал времени определенного года;
tf - последний (final) год моделирования;
tb - начальный (begin) интервал моделирования;
PRFt - доналоговая прибыль (profit), полученная предприятием за год t, грн./год;
TXRT - ставка налога на прибыль (tax rate).
Капитализируемый предприятием за период моделировния остаток прибыли:
Прибыль за t год:
PRFt = CPt * RN ,
где RN - рентабельность капитала предприятия. Задается как параметр предприятия, исходное данное.
Метод решения
Выполняется имитационное моделирование процесса развития пред приятия и накопления налоговых средств в бюджете во времени. Временной цикл организуется языком программирования Visual Basic for Application (VBA).
Исходные данные для параметров, переменных и показателей модели. В качестве исходных данных задаются числовые значения: налоговой ставки, рентабельности, начального капитала фирм и интервала моделирования.
Средства управления экспериментом включают программы автоматического ввода факторов и отображения таблиц и графиков от числений в бюджет и роста капитала предприятия.
Техника выполнения. Устанавливать для предприятий различные ставки налогов, измерять поступления в бюджет и характеристики развития предприятий и экономики. Полный экран окон лабораторной модели представлен на рис. 1.1.
Рис. 1.1. Экран эксперимента с табличными и графическими результатами
Анализ результатов
Чем выше рентабельность банка, тем ярче выражена оптимальная став ка налогообложения. С ростом рентабельности оптимальная ставка умень шается, стремясь к фиксированной величине, на нашем графике, примерно, к 23 %. Более отчетливо движение оптимальной ставки видно в таблице, где максимальные поступления в бюджет окружены рамками.
Возможные управленческие решения
Анализ результатов имитации будет неожиданным для стран с прогрес сивным налогообложением сверхприбылей корпораций: чем выше рента бельность, тем выгоднее бюджету уменьшить ставку налога. Фирмы с низ кой рентабельностью целесообразно облагать более высокими налогами. Выбраковывать, как это делает крестьянин с малопродуктивным скотом, а заводы - с неэффективным оборудованием. Разумеется, урожай не собира ют, пока он не созрел, и молодым фирмам необходим льготный период.
Контрольные вопросы
Индивидуальные задания
Задание 1. Однофакторный имитационный эксперимент
Исследовать зависимость налоговых поступлений в бюджет (BD) за фиксированный период времени от величины налоговой ставки на прибыль предприятий TXRT.
|
Вариант №1 |
|||
|
Начальный капитал |
250 |
Ставка налога |
40 |
|
Количество лет |
8 |
Рентабельность |
100 |
|
Вариант №2 |
|||
|
Начальный капитал |
200 |
Ставка налога |
50 |
|
Количество лет |
9 |
Рентабельность |
105 |
|
Вариант №3 |
|||
|
Начальный капитал |
150 |
Ставка налога |
60 |
|
Количество лет |
10 |
Рентабельность |
110 |
|
Вариант №4 |
|||
|
Начальный капитал |
350 |
Ставка налога |
70 |
|
Количество лет |
11 |
Рентабельность |
115 |
|
Вариант №5 |
|||
|
Начальный капитал |
300 |
Ставка налога |
65 |
|
Количество лет |
12 |
Рентабельность |
120 |
|
Вариант №6 |
|||
|
Начальный капитал |
400 |
Ставка налога |
55 |
|
Количество лет |
13 |
Рентабельность |
125 |
|
Вариант №7 |
|||
|
Начальный капитал |
450 |
Ставка налога |
45 |
|
Количество лет |
14 |
Рентабельность |
95 |
|
Вариант №8 |
|||
|
Начальный капитал |
400 |
Ставка налога |
35 |
|
Количество лет |
15 |
Рентабельность |
90 |
|
Вариант №9 |
|||
|
Начальный капитал |
350 |
Ставка налога |
30 |
|
Количество лет |
7 |
Рентабельность |
85 |
|
Вариант №10 |
|||
|
Начальный капитал |
300 |
Ставка налога |
75 |
|
Количество лет |
8 |
Рентабельность |
80 |
|
Вариант №11 |
|||
|
Начальный капитал |
250 |
Ставка налога |
45 |
|
Количество лет |
9 |
Рентабельность |
120 |
|
Вариант №12 |
|||
|
Начальный капитал |
200 |
Ставка налога |
55 |
|
Количество лет |
10 |
Рентабельность |
115 |
|
Вариант №13 |
|||
|
Начальный капитал |
150 |
Ставка налога |
65 |
|
Количество лет |
11 |
Рентабельность |
105 |
|
Вариант №14 |
|||
|
Начальный капитал |
200 |
Ставка налога |
75 |
|
Количество лет |
12 |
Рентабельность |
95 |
|
Вариант №15 |
|||
|
Начальный капитал |
300 |
Ставка налога |
70 |
|
Количество лет |
13 |
Рентабельность |
100 |
Задание 2. Двухфакторный имитационный эксперимент
Исследовать зависимость бюджетно-оптимальной ставки от эффектив ности работы фирмы. В качестве показателя эффективности выберем рентабельность, т. е. от ношение доналоговой прибыли к капиталу.
Изменяя ставку налога и рентабельность, заданную в задании 1 на -15%,
-10%, -5%, 0%, +5%, +10%, +15% рассчитать поступления в бюджет за указанный период. Построить графики зависимости поступлений в бюджет от ставки налога и рентабельности бизнеса.
Лабораторная работа № 2
Оптимальные бизнес-планы, план по продукции, технология оптимизации
Цель работы: научиться составлять наилучший (оптимальный) план производства про дукции с учетом ограниченного обеспечения материальными ресурсами; освоить методику и технологию оптимизации планов в табличном про цессоре Excel с помощью программы Поиск решения (Solver).
Теоретические сведения
Определение проблемы
На заводе электромедицинских приборов склад готовой продукции пуст. Все проходы забиты продукцией - незавершенной. Спрос есть, производственные мощ ности есть, но завод стоит. Нет реализации, прибыли, зарплаты, развития. Громадная кредиторская задолженность. В чем причина?
Составили прекрасный план производства продукции, но не учли объ емы запасов материалов и комплектующих на складах и ограниченные воз можности поставщиков узлов и деталей. На поточной линии запустили в производство серию кардиографа матери-плода. Не хватило самописцев. Сгрузили "незавершенку" в проходы цеха.
Перенастроили линию на серию индикаторов стадии наркоза. Выполни ли две трети плана - не хватило дисплеев. Сгрузили "незавершенку" в про ходы цеха.
На автомобильном заводе сборочный конвейер работает "с колес". Не учли возможностей поставщиков. На разные модели автомобилей не хвати ло то резины, то электрики, то двигателей. Конвейер стоит. Срочно достав ляют комплектующие самолетами, переплачивают, растет себестоимость продукции.
Проблема: менеджеры и плановики разрабатывали план производства продукции без учета ресурсов, т. е. запасов материалов и комплектующих на складах и возможностей поставщиков.
Выделение проблемной системы
План производства продукции обычно представляется в виде таблицы, исключающей перечень продукции и плановые объемы производства в нату ральном выражении (штуки, тонны, литры и т. д.).
При разработке плана уточняется цель производства: максимизация прибыли, максимизация реализации, снижение затрат и пр.
Возможные объемы производства зависят от обеспеченности тремя ви дами ресурсов: трудом, машинами и оборудованием, материалами и комплектующими.
Для небольшой лабораторной модели выбирается проблемная система, включающая номенклатуру продукции с искомыми плановыми объемами, критерием производства принимаем максимизацию прибыли, из ресурсов будем учитывать лишь ограничения по комплектующим узлам и деталям.
Постановка задачи:
Предприятие выпускает телевизоры, стереосистемы и акустические системы, используя общий склад комплектующих. Каждому типу изделий соответст вует своя норма прибыли. Запас комплектующих на складе ограничен. За дача сводится к определению количества каждого вида изделий для получе ния наибольшей прибыли, т. е. оптимальное соотношение объемов выпуска разных типов изделий в плане.
Следует учитывать уменьшение удельной прибыли при увеличении объемов производства в связи с дополнительными затратами на сбыт.
Лабораторная модель
Табличная модель
Обычно план по номенклатуре составляется в виде таблицы. Вначале структура документа вчерне составляется на бумаге или сразу в виде элек тронной таблицы. Пример приведен в табл. 2.1.
Таблица 2.1
Плановики, разрабатывая план производства продукции, располагают номенклатуру в первом столбце, как это принято в прайс-листах. Но, следуя фирменному примеру, имеющемуся в ваших компьютерах и составленному явно не экономистами, а математиками или программистами, примем рас положение плановой номенклатуры в строке.
Наименование продукции расположено в строке 8 листа Excel (телевизор, стереосистема, акустическая система). В строке 9 расположены ячейки искомого плана. Мы должны назначить количество изделий в плане производства.
Цель производства - максимально возможная прибыль вычисляется в ячейке D18.
Искомые величины: плановое количество продукции и прибыль - окру жены сплошной жирной рамкой. Необходимые для расчета плана исходные данные окружены пунктирными рамками.
В колонке А приводится наименование комплектующих изделий, необходимых для производства продукции. Рядом, в колонке В, задан как исходные данные запас комплектующих на складе. Это можно также представить как ежемесячная, квартальная или годовая мощность (возможность) поставщиков комплектующих.
Исходными данными для расчетов являются нормы расхода комплектующих на производство одного изделия. Они задаются в матрице диапазона, D11:F15 и готовятся технологами-нормировщиками.
Плановые затраты комплектующих на производство всех типов изделий не должны превышать запасов на складе. Они вычисляются в колонке С как сумма произведений планового количества продуктов на удельные нормы затрат комплектующих.
Прибыль по каждому типу изделий вычисляется в строке 17.
Исходным данным является коэффициент уменьшения отдачи. Он от дает убывающую эффективность роста продаж за счет роста затрат на рекламу и другие затраты в системе маркетинга и сбыта.
Математическая модель
Посмотрим на таблицу в представлении значений и формул и составим математическую модель.
Введем обозначения:
і - номер строки, ресурса;
j - номер столбца, продукта;
Xj - искомое плановое количество j-ro продукта;
Pj - прибыль (profit) на единицу j-ro продукта;
Bi - ограниченный (boundary = граница) запас і-го ресурса на складе;
Rij - норма расхода і-го ресурса на единицу j-ro продукта;
Сі - плановая сумма расхода і-го ресурса по всем продуктам;
В общем виде наша модель экономико-математической постановки за дачи будет выглядеть следующим образом:
Целевая функция (максимизировать прибыль)
при ограничениях Сі<=Bi и неотрицательных количествах продуктов Xj>=0.
Формулы табличной модели
После составления плановой таблицы необходимо связать показатели формулами для вычислений. Представление формул и чисел исходных дан ных дано в табл. 2.2.
Таблица 2.2
Переключение листа в режим представления формул или значений (ре зультатов вычисления) производится в меню Сервис > Параметры > Параметры окна > формулы.
В колонке С (Плановый расход комплектующих) введены формулы вы числения суммы произведений норм расхода ресурсов на плановое количе ство продукции. (Здесь можно воспользоваться функцией Excel СУММПРОИЗВ(), что значительно упростит ввод формул при решении больших экономических задач.)
В строке 17 (Прибыль по видам изделий) числа 75, 50 и 35 означают прибыль на единицу продукции, которая умножается на количество изделий по плану и корректируется возведением в степень коэффициентом умень шения прибыли из ячейки HI5.
В строке 18 суммируется прибыль по всей продукции из строки 17.
Программа оптимизации Поиск решения (Solver)
Для вызова программы оптимизатора выберите команду меню Сервис Поиск решения. Если команда Поиск решения отсутствует в меню Сервис, то надо установить эту надстройку.
Настройка экономико-математической модели
Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Поиск решения (рис. 2.1) и настроить экономико-математическую модель. Отличие экономико-математической постановки задачи оптимизации в табличном процессоре от традиционной экономико-математической постановки состоит в том, что в формулах задаются не символьные обозначения переменных и параметров, а координаты ячеек таблицы, в которых хранятся эти переменные. Excel позволяет писать в формулы символьные имена ячеек, но программа Поиск решения в 70 % случаев имена не воспринимает. Приходится использовать координатные ссылки на ячейки.
Диалоговое окно Поиск решения
Окно Поиск решения (рис. 2.1) вызывается командой меню Сервис > Поиск решения.
Рис. 2.1. Диалоговое окно Поиск решения
Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или уста новить равным заданному числу. Эта ячейка должна содержать формулу. В нашем примере это ячейка D18 (Прибыль).
Кнопка Равной служит для выбора варианта оптимизации значения це левой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить заданное число, введите его в поле. В нашем примере для максимизации прибыли мы нажимаем кнопку максимальному значению.
Поле Изменяя ячейки служит для указания ячеек, значения которых из меняются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указан ной в поле Установить целевую ячейку. В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек, разделяя их запятыми. В нашем при мере введен диапазон ячеек D9:F9, содержащий искомые величины плана производства продукции. Изменяемые ячейки должны быть прямо или кос венно связаны с целевой ячейкой. Допускается установка до 200 изменя емых ячеек.
Поле Предположить используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки.
Поле Ограничения служит для отображения списка граничных условий поставленной задачи. В нашем примере это величины диапазона расхода комплектующих С11:С15. Они не должны превышать запаса на складе В11:В15.
Команда Добавить служит для отображения диалогового окна Добавить ограничение.
Команда Изменить служит для отображения диалогового окна Измене ние ограничения.
Команда Удалить служит для снятия указанного курсором ограничения.
Команда Выполнить служит для запуска поиска решения поставленной задачи.
Команда Закрыть служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки, сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить.
Кнопка Параметры служит для отображения диалогового окна Пара метры поиска решения, в котором можно загрузить или сохранить оптими зируемую модель и указать предусмотренные варианты поиска решения.
Кнопка Восстановить служит для очистки полей окна диалога и восстанов ления значений параметров поиска решения, используемых по умолчанию.
Ввод и редактирование ограничений
Диалоговые окна изменения и добавления ограничений одинаковы, рис. 2.2.
Рис. 2.2. Диалоговое окно Изменение ограничения
В поле Ссылка на ячейку вводится адрес или имя ячейки или диапазона, на значения которых накладываются ограничения.
Выберите из раскрывающегося списка условный оператор, который не обходимо разместить между ссылкой и ее ограничением. Это знаки опера торов: не более, не менее, равно и т. д.
В поле Ограничения введите число, формулу или имя ячейки или диа пазона, содержащих или вычисляющих ограничивающие значения.
Чтобы приступить к набору нового условия, нажмите кнопку Добавить.
Чтобы вернуться в диалоговое окно Поиск решения, нажмите кнопку ОК.
Настройка параметров алгоритма и программы
Настройка параметров алгоритма и программы производится в диалого вом окне Параметры поиска решения, рис. 2.3.
Рис. 2.3. Диалоговое окно Параметры поиска решения
В окне устанавливаются ограничения на время решения задачи, выбира ются алгоритмы, задается точность решения, предоставляется возможность для сохранения вариантов модели и их последующей загрузки. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.
Поле Максимальное время служит для ограничения времени, отпускае мого на поиск решения задачи. В поле можно ввести время (в секундах), не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства лабораторных работ.
Поле Предельное число итераций служит для управления временем реше ния задачи, путем ограничения числа промежуточных вычислений. В поле можно ввести время (в секундах), не превышающее 32767; значение 100, ис пользуемое по умолчанию, подходит для решения большинства простых задач.
При достижении отведенного временного интервала или при выполне нии отведенного числа итераций на экране появляется диалоговое окно Те кущее состояние поиска решения.
Поле Относительная погрешность служит для задания точности (допустимой погрешности), с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Низкая точность соответствует введенному числу, содержащему меньшее количество десятичных знаков, чем число, используемое по умолчанию, например 0,0001. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации. Чем меньше введенное число, тем выше точность результатов.
Поле Допустимое отклонение служит для задания допуска на отклоне ние от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. При указании большего допуска по иск решения заканчивается быстрее.
Поле Сходимость результатов поиска решения применяется только к нели нейным задачам. Когда относительное изменение значения в целевой ячейке за последние 5 итераций становится меньше числа, указанного в поле Сходи мость, поиск прекращается. Условием сходимости служит дробь из интервала от 0 до 1. Лучшую сходимость характеризует большее количество десятичных знаков, например 0,0001 - это меньшее относительное изменение, чем 0,01. Чем меньше это значение, тем выше точность результатов. Лучшая сходимость требует больше времени на поиск оптимального решения.
Флажок Линейная модель служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи.
Флажок Неотрицательные значения позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле Ограничение диалогового окна Добавить ограничение.
Флажок Автоматическое масштабирование служит для включения ав томатической нормализации входных и выходных значений, качественно различающихся по величине, например максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах грн.лей.
Флажок Показывать результаты итераций служит для приостановки по иска решения для просмотра результатов отдельных итераций.
Кнопки Оценки служат для указания метода экстраполяции (линейная или квадратичная), используемого для получения исходных оценок значе ний переменных в каждом одномерном поиске.
Линейная служит для использования линейной экстраполяции вдоль ка сательного вектора.
Квадратичная служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.
Кнопки Разности (производные) служат для указания метода численно го дифференцирования (прямые или центральные производные), который используется для вычисления частных производных целевых и ограничи вающих функций.
Прямые используются для гладких непрерывных функций. Центральные используются для функций, имеющих разрывную произ водную. Несмотря на то что данный способ требует больше вычислений, он может помочь при получении итогового сообщения о том, что процедура поиска решения не может улучшить текущий набор влияющих ячеек.
Кнопки Метод поиска служат для выбора алгоритма оптимизации (ме тод Ньютона или сопряженных градиентов).
Кнопка Ньютона служит для реализации квазиньютоновского метода, в котором запрашивается больше памяти, но выполняется меньше итераций, чем в методе сопряженных градиентов. Здесь вычисляются частные произ водные второго порядка.
Кнопка Сопряженных градиентов служит для реализации метода со пряженных градиентов, в котором запрашивается меньше памяти, но вы полняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно велика и необходимо экономить па мять, а также если итерации дают слишком малое отличие в последователь ных приближениях.
Для решения линейных задач используются алгоритмы симплексного метода. Для решения целочисленных задач используется метод ветвей и границ.
Команда Сохранить модель служит для отображения на экране диалого вого окна Сохранить модель, в котором можно задать ссылку на область ячеек, предназначенную для хранения модели оптимизации. Данный вари ант предусмотрен для хранения на листе более одной модели оптимизации. Первая модель сохраняется автоматически.
Команда Загрузить модель служит для отображения на экране диалого вого окна Загрузить модель, в котором можно задать ссылку на область яче ек, содержащих загружаемую модель.
Вычисления и результаты решения задачи
Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения.
Программа начинает работать, в строке сообщений (слева внизу листа) появляется сообщение Постановка задачи... Ваша таблица с моделью и па раметрами алгоритма автоматически приводится к стандартам постановки задач математического программирования. Это преимущество Excel. В дру гих пакетах вам пришлось бы оторваться от экономической сути задачи и заниматься формальной математической постановкой задачи. После этапа постановки решается задача.
Чтобы прервать поиск решения, нажмите клавишу Esc. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.
По окончании счета появляется диалоговое окно Результаты поиска ре шения (рис. 2.4).
Нажав соответствующую кнопку, можно сохранить найденное решение во влияющих ячейках модели или восстановить исходные значения.
Поле Тип отчета служит для указания типа отчета, размещаемого на от дельном листе книги.
Отчет Результаты используется для создания отчета, состоящего из це левой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о на ложенных ограничениях.
Рис. 2.4. Диалоговое окно Результаты поиска решения
Отчет Устойчивость используется для создания отчета, содержащего сведения о чувствительности решения к малым изменениям в формуле мо дели или в формулах ограничений. Такой отчет не создается для моделей, значения в которых ограничены множеством целых чисел. В случае нели нейных моделей отчет содержит данные для градиентов и множителей Лагранжа. В отчет по нелинейным моделям включаются ограниченные затра ты, фиктивные цены, а также диапазоны ограничений.
Отчет Пределы используется для создания отчета, состоящего из целе вой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ. Такой отчет не создается для моделей, значения в кото рых ограничены множеством целых чисел. Нижним пределом является наименьшее значение, которое может содержать влияющая ячейка, в то время как значения остальных влияющих ячеек фиксированы и удовлетво ряют наложенным ограничениям. Соответственно верхним пределом назы вается наибольшее значение.
К сожалению, эти отчеты очень неудобны. Они перегружены плохо чи таемыми абсолютными ссылками со знаками доллара. Желает лучшего и перевод с английского на русский.
Кнопка Сохранить сценарий служит для отображения диалогового окна Сохранение сценария, в котором можно сохранить сценарий решения зада чи, чтобы использовать его в дальнейшем с помощью диспетчера сценариев Microsoft Excel. В поле Название сценария введите имя сценария. Чтобы создать сценарий, не сохраняя найденное решение и не отображая результа тов на листе, сохраните сценарий в диалоговом окне Результаты поиска ре шения, а затем выберите Восстановить исходные значения.
Просмотр промежуточных результатов поиска решения
Режим пошагового решения задач используется при отладке моделей. В диалоговом окне Поиск решения нажмите кнопку Параметры.
Чтобы получить возможность просмотра текущих значений влияющих ячеек каждой итерации, установите флажок Показывать результаты итера ций, нажмите кнопку ОК, а затем кнопку Выполнить.
На экране появится диалоговое окно Текущее состояние поиска реше ния (рис. 2.5), а влияющие ячейки листа изменят свои значения.
Рис. 2.5. Диалоговое окно Текущее состояние поиска решения
Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.
Чтобы выполнить следующую итерацию и просмотреть ее результаты, нажмите кнопку Продолжить.
Итоговые сообщения процедуры поиска решения
1. Если поиск решения успешно завершен, в диалоговом окне Результа ты поиска решения выводится одно из следующих сообщений:
Решение найдено. Все ограничения и условия оптимальности выполнены.
Все ограничения соблюдены с установленной точностью, и найдено за данное значение целевой ячейки.
Поиск свелся к текущему решению. Все ограничения выполнены.
Относительное изменение значения в целевой ячейке за последние 5 итераций стало меньше установленного значения параметра Сходимость в диалоговом окне Параметры поиска решения. Чтобы найти более точное решение, установите меньшее значение параметра Сходимость, но это зай мет больше времени.
2. Если поиск не может найти оптимальное решение, в диалоговом окне Результаты поиска решения выводится одно из следующих сообщений:
Поиск не может улучшить текущее решение. Все ограничения выполнены.
В процессе поиска решения нельзя найти такой набор значений влияю щих ячеек, который был бы лучше текущего решения. Приблизительное решение найдено, но либо дальнейшее уточнение невозможно, либо заданная погрешность слишком высока. Измените погрешность на меньшее чис ло и запустите процедуру поиска решения снова.
3. Поиск остановлен (истекло заданное на поиск время).
Время, отпущенное на решение задачи, исчерпано, но достичь удовле творительного решения не удалось. Чтобы при следующем запуске проце дуры поиска решения не повторять выполненные вычисления, установите переключатели Сохранить найденное решение или Сохранить сценарий.
4. Поиск остановлен (достигнуто максимальное число итераций).
Произведено разрешенное число итераций, но достичь удовлетвори тельного решения не удалось. Увеличение числа итераций может помочь, однако следует рассмотреть результаты, чтобы понять причины остановки. Чтобы при следующем запуске процедуры поиска решения не повторять выполненные вычисления, установите переключатель Сохранить найденное решение или нажмите кнопку Сохранить сценарий.
5. Значения целевой ячейки не сходятся.
Значение целевой ячейки неограниченно увеличивается (или уменьша ется), даже если все ограничения соблюдены. Возможно, следует в задаче снять одно ограничение или сразу несколько или наложить дополнительные ограничения. Изучите процесс расхождения решения, проверьте ограниче ния и запустите задачу снова. Например, в задаче об оптимальных портфе лях банков, если не наложить ограничение на портфель привлечения ресур сов, банк, как аферист, будет занимать деньги до бесконечности.
6. Поиск не может найти подходящего решения.
В процессе поиска решения нельзя сделать итерацию, которая удовле творяла бы всем ограничениям при заданной точности. Вероятно, ограниче ния противоречивы. Исследуйте лист на предмет возможных ошибок в формулах ограничений или в выборе ограничений.
7. Поиск остановлен по требованию пользователя. Нажата кнопка Стоп в диалоговом окне Текущее состояние поиска ре шения после прерывания поиска решения в процессе выполнения итераций.
8. Условия для линейной модели не удовлетворяются.
Установлен флажок Линейная модель, однако итоговый пересчет поро ждает такие значения, которые не согласуются с линейной моделью. Это означает, что решение недействительно для данных формул листа. Чтобы проверить линейность задачи, установите флажок Автоматическое масшта бирование и повторно запустите задачу. Если это сообщение опять появится на экране, снимите флажок Линейная модель и снова запустите задачу.
9. При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения.
При пересчете значений ячеек обнаружена ошибка в одной формуле или в нескольких сразу. Найдите целевую ячейку или ячейку ограничения, по рождающие ошибку, и измените их формулы так, чтобы они возвращали подходящее числовое значение.
Набрано неверное имя или формула в окнах Добавить ограничение или Изменить ограничение, или в поле Ограничение были заданы целое или двоичное ограничение. Чтобы ограничить значения ячейки множеством це лых чисел, выберите оператор целого ограничения в списке условных опе раторов. Чтобы установить двоичное ограничение, выберите оператор для двоичного ограничения.
10. Мало памяти для решения задачи.
Система не смогла выделить память, необходимую для поиска решения. Закройте некоторые файлы или приложения и попытайтесь снова выпол нить процедуру поиска решения.
Компьютерный поиск оптимального плана
Вызвать команду меню Сервис > Поиск решения.
Проверить настройку модели в диалоговом окне (рис. 2.1).
Цель - получение наибольшей прибыли, ячейка D18.
Изменяемые данные в диапазоне D9:F9. Это количество выпускаемых изделий каждого вида
Ограничения С11:С15<=В11:В15 введены, чтобы количество использо ванных комплектующих не превышало их запаса на складе.
Количество выпускаемых изделий не может быть отрицательным: D9:F9>=0.
Нажать кнопку Выполнить в окне Поиск решения. Через секунду полу чаем готовое решение. В формулу прибыли на изделие в ячейках D17:F17 входит показатель степени HI5, учитывающий уменьшение удельной прибыли с ростом объе ма производства.
Если значение H15 отлично от 1, задача нелинейная. В окне параметров надо снять флажок линейной модели.
Если изменить HI5 на 1,0 (прибыль не зависит от объема производства) и повторно запустить процесс поиска решения, найденное ранее оптималь ное решение будет другим. Данное изменение делает задачу линейной. Можно в окне параметров включить флажок линейной модели.
Анализ результатов и решения менеджера
В табл. 2.1 дано оптимальное решение, найденное программой Поиск решения. Достигнута максимальная прибыль при ограничениях ресурсов на складе.
Полностью израсходованы динамики в количестве 800 шт. Полностью израсходованы электронные платы в количестве 600 шт. Ограничения по этим ресурсам сдерживают дальнейшее увеличение прибыли плана по портфелю продукции. Менеджер решает увеличить запасы этих ресурсов на складе, заключить дополнительные договоры с поставщиками.
В то же время почти на 30 % выше плановой потребности запасы на складе по шасси, кинескопам, блокам питания. Заморожены оборотные средства, оборотный капитал, велики расходы по хранению ресурсов на складе, теряется прибыль, предприятию трудно погасить кредиторскую за долженность. Менеджер рекомендует снабженцам избавиться от лишних запасов на складе, улучшить финансовое положение фирмы.
Конечно, после оценки возможностей и вариантов снабженцев план не сколько раз надо пересчитать. Программа оптимизации позволяет сделать это за секунды.
Контрольные вопросы
1. Почему актуальна проблема оптимального планирования выпуска про дукции с учетом ограничений по ресурсам?
2. Сформулировать цель лабораторной работы.
3. Перечислить объекты проблемной системы.
4. Пояснить структуру плановой таблицы.
5. Перечислить исходные данные, переменные и результирующие показа тели модели.
6. Дать краткую характеристику программы оптимизации Поиск решения.
Индивидуальные задания
Предприятие электронной промышленности выпускает две модели радиоприемников, причем каждая модель производится на отдельной технологической линии. Суточный объем производства первой линии –60 изделий, второй линии –75 изделий. На радиоприемник первой модели расходуется 10 однотипных элементов электронных схем, на радиоприемник второй модели –8 таких же элементов. Максимальный суточный запас используемых элементов равен 800 единицам. Прибыль от реализации одного радиоприемника первой и второй моделей равна 30 и 20 долларов соответственно. Определить оптимальный суточный объем производства первой и второй моделей.
Процесс изготовления двух видов промышленных изделий состоит в последовательной обработке из них на трех станках. Время использования этих станков для производства данных изделий ограничено 10-ча-сами в сутки. Время обработки и прибыль от продажи одного изделия каждого вида приведены в табл. Найти оптимальный объем производства изделий каждого вида.
Изделие |
Время обработки одного изделия Мин |
Удельная прибыль, $ |
||
|
Станок 1 |
Станок 2 |
Станок 3 |
||
|
1 2 |
10 5 |
6 20 |
8 15 |
2 3 |
ВАРИАНТ 3
Фирма имеет возможность рекламировать свою продукцию, используя местные радио и телевизионную сеть. Затраты на рекламу в бюджете фирмы ограничены суммой $1000 в месяц. Каждая минута радиорекламы обходится в $5, а каждая минута телерекламы –в $100.Фирма хотела бы использовать радиосеть, по крайней мере, в два раза чаще, чем телевидение. Опыт прошлых лет показал, что объем сбыта, который обеспечивает каждая минута телерекламы, в 25 раз больше объема сбыта, обеспечиваемого одной минутой радиорекламы. Определить оптимальное распределение ежемесячно отпускаемых средств между радио- и телерекламой.
ВАРИАНТ 4
Фирма производит два вида продукции – А и В. Объем сбыта продукции А составляет не менее 60% общего объема реализации продукции обоих видов. Для изготовления продукции А и В используется одно и тоже сырье, суточный запас которого ограничен величиной 100 кг. Расход сырья на единицу продукции А составляет 2 кг, а на единицу продукции В – 4 кг. Цены продукции А и В равны 20 и 40 долларов соответственно. Определить оптимальное распределение сырья для изготовления продукции А и В.
ВАРИАНТ 5
Фирма выпускает ковбойские шляпы двух фасонов (А и В). Трудоемкость изготовления шляпы фасона А вдвое выше трудоемкости изготовления шляпы фасона А вдвое выше трудоемкости изготовления шляпы фасона В. Если бы фирма выпускала только шляпы фасона А, суточный объем производства мог бы составить 500 шляп. Суточный объем сбыта шляп обоих фасонов ограничен диапазоном от 150 до 200 штук. Прибыль от продажи шляпы фасона А равна $8, а фасона В -$5.Определить, какое количество шляп каждого фасона следует изготовить, чтобы максимизировать прибыль.
ВАРИАНТ 6
Изделия четырех типов проходят последовательную обработку на двух станках. Время обработки одного изделия каждого типа на каждом из станков приведено в табл.
|
Станок |
Время обработки одного изделия, ч |
|||
|
Тип 1 |
Тип 2 |
Тип 3 |
Тип 4 |
|
|
1 2 |
2 3 |
3 2 |
4 1 |
2 2 |
Затраты на производство одного изделия каждого типа определяются как величины, прямо пропорциональные времени использования станков (в машино-часах).Стоимость машино-часа составляет 10 и 15 долларов для станков 1 и 2 соответственно. Допустимое время использования станков для обработки изделий всех типов ограничено следующими значениями: 500 машино-часов для станка 1 и 380 машино-часов для станка 2. Цены изделий типов 1,2,3 и 4 равны 65,70,55 и 45 долларов соответственно. Составить план производства, максимизирующий чистую прибыль.
ВАРИАНТ 7
Завод выпускает изделия трех моделей (l, ll и lll). Для их изготовления используются два вида ресурсов (А и В), запасы которых составляют 4000 и 6000 единиц. Расходы ресурсов на одно изделие каждой модели приведены в табл.
|
Ресурс |
Расход ресурса на одно изделие данной модели |
||
|
I |
II |
III |
|
|
А В |
2 4 |
3 2 |
5 7 |
Трудоемкость изготовления изделия модели I вдвое больше, чем изделия модели II, и втрое больше, чем изделия модели III. Численность рабочих завода позволяет выпускать 1500 изделий модели I. Анализ условий сбыта показывает, что минимальный спрос на продукцию завода составляет 200, 200 и 150 изделий моделей I, II и III соответственно. Однако соотношение выпуска изделий моделей I, II и III должно быть равно 3:2:5. Удельная прибыль от реализации изделий моделей I, II и III составляет 30, 20 и 50 долларов соответственно. Определить выпуск изделий, максимизирующий прибыль.
ВАРИАНТ 8
Требуется распределить имеющиеся денежные средства по четырем альтернативным вариантам. Игра имеет три исхода. В табл. приведены размеры выигрыша (или проигрыша) на каждый доллар, вложенный в соответствующий альтернативный вариант, для каждого из трех исходов. У игрока имеется 500$, причем использовать их в игре можно только один раз. Точный исход игры заранее неизвестен. Учитывая эту неопределенность, распределить деньги так, чтобы максимизировать минимальную отдачу от этой суммы.
|
Исход |
Выигрыш или проигрыш на каждый доллар, вложенный в данный вариант |
|||
|
1 |
2 |
3 |
4 |
|
|
1 2 3 |
-3 5 3 |
4 -3 -9 |
-7 9 10 |
15 4 -10 |
Небольшая фирма выпускает два вида автомобильных деталей (А и В). Для этого она закупает литье, подвергаемое токарной обработке, сверловке и шлифовке. Данные, характеризующие производительность станочного парка фирмы, приведены в табл.
Станки Деталь А, шт./ч Деталь В, шт./ч |
|
Токарный 25 40 Сверильный 28 35 Шлифовальный 35 25 |
Каждая отливка, из которой изготовляют деталь А, стоит $2.Стоимость отливки для детали В -$3. Продажная цена деталей равна, соответственно, 5 и 6 долларов. Стоимость часа станочного времени составляет по трем типам используемых станков 20, 14 и 17,5 долларов соответственно. Предполагая, что можно выпускать для продажи любую комбинацию деталей А и В, нужно найти план выпуска продукции, максимизирующий прибыль.
ВАРИАНТ 10
Бролейное хозяйство птицеводческой фермы насчитывает 20000 цыплят, которые выращиваются до 8-недельного возраста и после соответствующей обработки поступают в продажу. Хотя недельный рацион цыплят зависит от их возраста, в дальнейшем будем считать, что в среднем (за 8 недель) он составляет 1 фунт. Для того чтобы цыплята достигли к восьмой неделе необходимого веса кормовой рацион должен удовлетворять определенным требованиям по питательности. Этим требованиям могут соответствовать смеси различных видов кормов или ингредиентов. Ограничим наше рассмотрение только тремя ингредиентами: известняком, зерном и соевыми бобами. В табл. приведены данные, характеризующие содержание (по весу) питательных веществ в каждом из ингредиентов и удельную стоимость каждого ингредиента.
Ингредиент |
Содержание питательных веществ, фунт/фунт ингредиент |
Стоимость $/фунт 0,04 0,15 0,4 |
||
|
Известняк Зерно Соевые бобы |
Кальций |
Белок |
Клетчатка |
|
|
0,38 0,001 0,002 |
- 0,09 0,5 |
- 0,02 0,08 |
Смесь должна содержать:
Необходимо определить количество каждого из трех ингредиентов, образующих смесь минимальной стоимости, при соблюдении требований к общему расходу кормовой смеси и ее питательности.
Лабораторная работа № 3
Выбор поставщиков, план перевозок, транспортная задача
Цель работы: научиться составлять наилучший (оптимальный) план перевозок от по ставщиков к потребителям с учетом ограниченных ресурсов поставщи ков и известной потребности потребителей; освоить методику и технологию оптимизации планов в Excel с помощью средства Поиск решения.
Теоретические сведения
Выделение проблемной системы
В проблемную систему включаем следующие объекты и показатели: перечень потребителей и объемы их потребностей, перечень возможных поставщиков и их возможности по объемам поставок, затраты на поставку единицы груза от каждого поставщика к каждому потребителю, критерий – общие плановые затраты на доставку всех грузов от всех поставщиков ко всем потребителям.
Постановка задачи
Требуется минимизировать затраты на перевозку товаров от предприятий-производителей на торговые склады. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей. В этой модели представлена задача доставки товаров с трех заводов на 5 региональных складов. Товары могут доставляться с любого завода на любой склад, однако очевидно, что стоимость доставки на большее расстояние будет большей.
Необходимо определить объемы перевозок между каждым заводом и складом в соответствии с потребностями складов и производственными мощностями заводов, при которых транспортные расходы минимальны.
Лабораторная модель. Табличная модель
Обычно план перевозок составляется в виде таблицы. Вначале структу ра документа вчерне составляется на бумаге или сразу в виде электронной таблицы. Пример приведен на рис. 3.1.
Рис. 3.1. Выбор поставщиков, план перевозок, транспортная задача
В верхней строке электронной таблицы Excel даны имена колонок А, В, С... В первой колонке - номера строк. В колонке А - имена заводов-поставщиков. В строке 7 - имена потребителей.
Искомые показатели окружены сплошной жирной рамкой. Общие пла новые затраты на перевозку в ячейке В20 надо минимизировать. Искомая плановая матрица объемов перевозки грузов от каждого поставщика к каж дому потребителю расположена в диапазоне C8:G10.
В диапазоне В8:В10 вычисляются планы поставок от каждого завода всем потребителям как суммы по строкам. Плановик во время расчетов наблюдает, чтобы эти суммы не превысили мощностей заводов-поставщиков. И строке 12 вычисляются планы поставок каждому потребителю от всех заводов как суммы по столбцам. Плановик наблюдает, чтобы эти суммы были равны или не меньше заказов потребителей.
В строках 13:18 представлены исходные данные для расчетов. Они ок ружены пунктирными рамками. В диапазон В16:В18 вводятся мощности заводов-поставщиков. В матрицу C16:G18 надо ввести стоимость перевозки единицы груза от каждого поставщика к каждому потребителю. В строку 14 надо ввести плановые потребности складов.
В строке 20 вычисляются стоимость перевозок для каждого склада и общие затраты по транспортировке.
Математическая модель
Введем обозначения:
n — количество поставщиков;
m - количество потребителей;
і - номер строки, поставщика, 1..n;
j - номер столбца, потребителя, 1 ..m;
Xij - искомое плановое количество перевозки от і-го поставщика к j-му потребителю;
Si - план поставок от і-го поставщика всем потребителям, сумма по строке;
;
Cj - план поставок j-му потребителю от всех поставщиков, сумма по столбцу
;
Pij - цена (price) франко-склад единицы груза от і-го поставщика к j-му потребителю;
Ві - ограниченная (boundary = граница) мощность і-го поставщика;
Dj - ограниченный спрос (demand) j-ro потребителя.
В общем виде наша модель экономико-математической постановки за дачи будет выглядеть следующим образом: минимизировать затраты на пе ревозку грузов (целевая функция)
при ограничениях Si <= Bi, Cj => Dj и неотрицательных объемах перевозок Xj >= 0 .
Для решения подобных задач математики разработали варианты сим плекс-метода, метод потенциалов, венгерский метод и др.
Формулы табличной модели
После составления плановой таблицы необходимо связать показатели формулами для вычислений. Представление формул и чисел исходных дан ных дано на рис. 3.2.
Рис. 3.2. Формулы табличной модели
Мы суммируем все поставки от каждого завода в диапазоне В8:В10, чтобы проконтролировать, что они не превысят мощность заводов в диапа зоне В16:В18. Также суммируем объемы поставок потребителям от всех заводов в строке 12, чтобы проконтролировать, что они не меньше заказов по требителей в строке 14.
В строке 20 мы умножаем матрицу плана объемов перевозок на матрицу стоимости перевозок и суммируем затраты в целевой ячейке В20.
Компьютерный поиск оптимального плана
Вызвать команду меню Сервис > Поиск решения. Появляется диалоговое окно оптимизатора (рис. 3.3).
Математическая постановка задачи для оптимизатора)
В диалоговое окно Поиск решения, в поле целевой ячейки, вводим ее адрес В20. В поле Изменяя ячейки вводим адреса матрицы искомого плана перевозок C8:G10. В поле Ограничения вводим 3 строки неравенств значе ний диапазонов: поставки от заводов не должны превышать мощности заво дов, поставки потребителям не должны быть меньше потребностей, значе ния плана не могут быть отрицательными.
Рис. 3.3. Диалоговое окно Поиск решения с моделью транспортной задачи
Свод параметров модели дан в табл. 3.1.
Таблица 3.1
|
Параметр задачи |
Ячейки |
Семантика |
|
Результат |
В20 |
Цель - уменьшение всех транспортных расходов |
|
Изменяемые данные |
C8:G10 |
Объемы перевозок от каждого из заводов к каждому складу |
|
Ограничения |
В8:В10<=В16:В18 |
Количество перевезенных грузов не может превышать произ водственных возможностей заводов |
|
C12:G12>=C14:G14 |
Количество доставляемых грузов не должно быть меньше по требностей складов |
|
|
C8:G10>=0 |
Число перевозок не может быть отрицательным |
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное реше ние готово.
Анализ результатов и решения менеджера
На рис. 3.1 дано оптимальное решение, найденное программой Поиск решения. Получен план перевозок с наименьшими затратами. Удовлетворе ны все ограничения.
По Уралу и Украине использованы мощности заводов полностью, по Белоруссии это не так. При долгосрочных связях плановик, менеджер могут предложить на будущее увеличить мощности заводов на Урале и Украине и уменьшить их в Белорусии. Затраты на транспортировку уменьшатся.
Контрольные вопросы
Индивидуальные задания
Транспортная задача. Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i-го пункта производства в j-й центр распределения cij приведена в таблице, где под строкой понимается пункт производства, а под столбцом – пункт распределения. Кроме того, в этой таблице в i-й строке указан объем производства в i-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.
Стоимость перевозки единицы продукции |
Объемы производства |
||||
1 |
3 |
4 |
5 |
20 |
|
5 |
2 |
10 |
3 |
30 |
|
3 |
2 |
1 |
4 |
50 |
|
6 |
4 |
2 |
6 |
20 |
|
Объемы потребления |
30 |
20 |
60 |
15 |
Стоимость перевозки единицы продукции |
Объемы производства |
||||
2 |
7 |
7 |
6 |
20 |
|
1 |
1 |
1 |
2 |
50 |
|
5 |
5 |
3 |
1 |
10 |
|
2 |
8 |
1 |
4 |
20 |
|
3 |
2 |
1 |
5 |
17 |
|
Объемы потребления |
40 |
30 |
20 |
20 |
ВАРИАНТ 3
Стоимость перевозки единицы продукции |
Объемы производства |
||||
6 |
3 |
4 |
5 |
20 |
|
5 |
2 |
3 |
3 |
70 |
|
3 |
4 |
2 |
4 |
50 |
|
5 |
6 |
2 |
7 |
30 |
|
Объемы потребления |
15 |
30 |
80 |
20 |
ВАРИАНТ 4
Стоимость перевозки единицы продукции |
Объемы производства |
||||
5 |
1 |
7 |
6 |
30 |
|
1 |
5 |
8 |
1 |
40 |
|
5 |
6 |
3 |
3 |
10 |
|
2 |
5 |
1 |
4 |
18 |
|
3 |
7 |
9 |
1 |
10 |
|
Объемы потребления |
20 |
40 |
30 |
20 |
ВАРИАНТ 5
Стоимость перевозки единицы продукции |
Объемы производства |
||||
3 |
9 |
4 |
5 |
40 |
|
1 |
8 |
5 |
3 |
10 |
|
7 |
2 |
1 |
4 |
30 |
|
2 |
4 |
10 |
6 |
20 |
|
Объемы потребления |
50 |
10 |
35 |
10 |
ВАРИАНТ 6
Стоимость перевозки единицы продукции |
Объемы производства |
||||
6 |
1 |
3 |
1 |
20 |
|
3 |
4 |
5 |
8 |
30 |
|
5 |
9 |
3 |
2 |
20 |
|
2 |
4 |
8 |
4 |
20 |
|
3 |
2 |
1 |
5 |
17 |
|
Объемы потребления |
50 |
30 |
20 |
20 |
ВАРИАНТ 7
Стоимость перевозки единицы продукции |
Объемы производства |
||||
5 |
9 |
4 |
5 |
30 |
|
1 |
5 |
5 |
6 |
20 |
|
2 |
2 |
10 |
4 |
30 |
|
3 |
7 |
2 |
6 |
40 |
|
Объемы потребления |
20 |
50 |
20 |
35 |
ВАРИАНТ 8
Стоимость перевозки единицы продукции |
Объемы производства |
||||
7 |
1 |
3 |
2 |
30 |
|
8 |
4 |
5 |
8 |
20 |
|
5 |
2 |
3 |
7 |
10 |
|
5 |
5 |
8 |
4 |
27 |
|
1 |
9 |
7 |
5 |
30 |
|
Объемы потребления |
30 |
40 |
50 |
10 |
Стоимость перевозки единицы продукции |
Объемы производства |
||||
7 |
9 |
1 |
5 |
20 |
|
2 |
7 |
5 |
6 |
30 |
|
3 |
5 |
10 |
8 |
40 |
|
3 |
7 |
4 |
5 |
30 |
|
Объемы потребления |
40 |
30 |
30 |
42 |
ВАРИАНТ 10
Стоимость перевозки единицы продукции |
Объемы производства |
||||
5 |
9 |
3 |
10 |
10 |
|
3 |
10 |
5 |
9 |
30 |
|
7 |
2 |
3 |
8 |
20 |
|
8 |
5 |
11 |
2 |
32 |
|
5 |
9 |
10 |
5 |
20 |
|
Объемы потребления |
50 |
10 |
30 |
10 |
Лабораторная работа № 4
Планирование численности персонала
Цель работы: научиться оптимально планировать постоянные штатные бригады для обслуживания неравномерного известного календарного спроса, обеспечивая каждому работнику два смежных выходных дня и минимизи руя численность всего персонала и затраты на заработную плату; освоить методику и технологию оптимизации планов в Excel с помо щью программы Поиск решения (Solver).
Теоретические сведения
Лабораторная модель
В проблемную систему включаем следующие объекты и показатели:
• персонал;
• бригады, включающие персонал;
• спрос на услуги или товары или конкретное количество сотрудников, необходимое для удовлетворения спроса;
• ограничения по условиям работы в виде потребности в двух последова тельных выходных днях;
• цель менеджмента - выполнение работ при минимальных расходах на заработную плату персонала.
Описательная модель
Зарплата всех сотрудников принята одинаковой. Необходимо опреде лить требуемое количество постоянных работников в каждой бригаде для удовлетворения спроса на работы при минимальных расходах на зарплату и минимальном количестве работников, если зарплата у всех одинакова.
Табличная модель
Обычно план численности персонала разрабатывается в виде таблицы. В начале структура документа вчерне составляется на бумаге или сразу в виде электронной таблицы. Пример приведен в рис. 4.1.
Рис. 4.1.
Исходные данные для расчетов в таблице окружены пунктирной рамкой. Искомые показатели окружены сплошной жирной рамкой. В графе А проставлены присвоенные бригадам номера. Для математической постановки задачи номер бригады задается индексной переменной i=1..n, где n- количество бригад.
В графе В названы смежные выходные дни бригад. В графу D вводятся вручную или компьютерной программой искомые планируемые количества человек в каждой бригаде Хі.
В ячейке D15 отображается количество работников во всех бригадах.
В ячейке D19 задана дневная зарплата работника. Она у всех одинакова.
В ячейке D20 вычисляется дневной фонд зарплаты всех работников. Это критерий качества плана (целевая функция), он подлежит минимизации. При равенстве зарплаты работников также будет минимизироваться и их численность.
В диапазон F7:L13 как исходные данные вводится календарь рабочих и выходных дней бригад. Это матрица Cij, где j - номер дня недели. Нуль элемента матрицы означает выходной день, единица - рабочий.
В диапазоне F15:L15 вычисляется общее количество работников, заня тых в конкретный день недели.
В диапазон F17:L17 вводится потребность в общем количестве работников в конкретный день недели. Это известный спрогнозированный спрос, заданный в переменной Bj.
Математическая модель
Введем обозначения:
п - количество бригад;
і - номер бригады;
Хі - искомое плановое количество работников в і-той бригаде;
m - количество дней в неделе;
j - порядковый номер дня недели: 1 = воскресенье, 2 = вторник ...
Cij - признак рабочего или выходного дня, календарная матрица;
Cij = 1 — рабочий день;
Cij = 0 — выходной день;
Bj - общая потребность в персонале (все бригады) по дням недели для выполнения работ;
Sj - плановое количество персонала (все бригады) по дням недели для выполнения работ:
Р - дневная ставка зарплаты одного работника, одинакова для всех; W - дневной фонд зарплаты всего персонала.
Критерий, цель оптимизации плана - минимизация дневного фонда зарплаты постоянного персонала бригад (целевая функция):
при ограничениях Sj >= Bj .
Для решения подобных задач математики разработали варианты алгоритмов целочисленного программирования, один из них реализован в программе Поиск решения Excel.
Формулы табличной модели
После составления плановой таблицы необходимо связать причинно-следственные показатели формулами для вычислений. Представление формул и чисел исходных данных дано в табл. 4.2.
Рис. 4.2.
В ячейке D15 функцией CYMM(D7:D13) вычисляется количество ра ботников во всех бригадах.
В ячейке D20 формулой D15*D19 вычисляется дневной фонд зарплаты всех работников. Это критерий качества плана (целевая функция), он под лежит минимизации. При равенстве зарплаты работников также будет ми нимизироваться и их численность.
В каждой ячейке диапазона F15:L15 вычисляется общее количество ра ботников, занятых в конкретный день недели. Формулой для каждого дня недели задается скалярное произведение вектора количества работников в бригадах Xi (колонка D) на вектор признака работы в конкретный день не дели Сі (колонки F:L). В таблице приведены сокращенные формулы только для воскресенья и субботы. Пример суммы произведений для воскресенья дан в ячейке F15 как =$D$7*F7+$D$8*F8+$D$9*F9+...
Управление экспериментами
Осуществляется с помощью программы оптимизации Поиск решения. Изменяемыми данными являются количество работников в бригадах. Для расширения экспериментов, где есть над чем подумать менеджеру по персоналу, можно менять дневную зарплату, динамику прогноза потребности в персонале, вводить в качестве ограничений дополнительные условия в рабочий календарь и др.
Компьютерный поиск оптимального плана
Вызвать команду меню Сервис > Поиск решения. Появляется диалоговое окно оптимизатора (рис. 4.3).
Рис. 4.3. Диалоговое окно Поиск решения с координатно-математической моделью задачи планирования персонала
Настройка модели (математическая постановка задачи для оптимизатора)
В диалоговое окно Поиск решения, в поле целевой ячейки, вводим адрес D20 Дневного фонда зарплаты. В поле Изменяя ячейки вводим адреса диапазона искомого плана количества работников в бригадах D7:D13. В поле Ограничения вводим 3 строки условий, ограничивающих область допустимых решений нашей задачи.
Мы гуманны и не будем рвать людей на части. Работники в бригадах могут быть не совсем здоровы, но обязательно целы. Это задается первым ограничивающим равенством D7:D13 = целое и является сигналом комплексу программ Поиск решения для применения алгоритма целочисленного программирования.
Второе ограничение выполняет хороший кадровый менеджер - он не принимает на работу плохих работников, а только хороших. Количество людей в бригадах не может быть отрицательным числом: D7:D13>=0.
Третье неравенство гарантирует 100%-ное обслуживание. Плановое количество работников не должно быть меньше потребности в работниках: F15:L15>=F17:L17.
Свод параметров модели дан в табл. 4.1.
Таблица 4.1
|
Параметр задачи |
Ячейки |
Пояснения |
|
Результат |
D20 |
Цель - минимизация расходов на оплату труда |
|
Изменяемые данные |
D7:D13 |
Число работников в группе |
|
Ограничения |
D7:D13 >= 0 |
Число работников в группе не может быть отрицатель ным |
|
D7:D13=цeлое |
Число работников должно быть целым |
|
|
F15:L15>=F17:L17 |
Число ежедневно занятых работников не должно быть меньше ежедневной потребности |
|
|
Вариант графика |
Строки 7-13 |
1 означает, что данная группа в этот день работает |
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное реше ние готово.
В рис. 4.1 дано оптимальное решение, найденное программой. Получен план комплектования бригад с наименьшим фондом зарплаты. Удовлетворены все ограничения. Выбор линейной модели в диалоговом окне параметров ускоряет получение результата.
Далее следует провести следующую серию экспериментов. Несколько раз выполнить оптимизацию, каждый раз сохраняя найденное решение. Общее минимальное количество работников в штате будет оста ваться неизменным, но план по составу бригад почти в каждом эксперименте будет различен. Это, вероятно, связано со спецификой ггрн.ости решения целочисленных задач, регулируемого параметром Допустимое отклонение, минимальное значение которого ограничено в программе Поиск решения одним процентом.
Расширить права, увеличив ограничения
На рис. 4.1 оптимального состава бригад количество персонала в седьмой бригаде равно нулю, т. е. бригады с выходными по субботам и воскресеньям отсутствуют. Это не устраивает персонал. Люди желают хотя бы периодически иметь общегосударственные выходные дни в субботу и воскресенье. Чтобы удержать персонал, фирма вынуждена пойти навстречу работникам, возможно даже за счет увеличения фонда зарплаты.
Чтобы расширить права работников на выходные, менеджер персонала по согласованию с профсоюзом должен ввести в модель дополнительные ограничения. Они согласились, что достаточно иметь группу с субботне- воскресным выходным днем из четырех человек. В этом случае людей периодически будут переводить из других бригад в воскресную бригаду для общегосударственных выходных. Введем в программу дополнительные ограничения по расширению прав персонала.
Вызвать команду меню Сервис>Поиск решения. Появляется диалоговое окно оптимизатора (рис. 4.5).
Рис. 4.5. Диалоговое окно Поиск решения с координатно-математической
моделью задачи планирования персонала с расширением прав
Нажмем кнопку Добавить ограничения и для седьмой бригады введем ограничение D13 >= 4, т. е. в субботне-воскресной бригаде по штату должно быть не менее четырех человек.
Нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное решение готово (табл. 4.4).
Плановое количество персонала выросло с 25 до 28 человек, дневной фонд зарплаты вырос 1 000 до 1 120 грн.
Рис. 4.6.
Анализ результатов
Составить оптимальный план персонала вручную даже на компьютере трудоемко, долго, практически невозможно. Программа оптимального целочисленного программирования составляет оптимальный план за секунды. Оптимальный план обеспечивает минимальный фонд зарплаты персона жа и выполнение календарного объема работ. При одинаковой зарплате сотрудников также обеспечивается минимальный штат персонала.
Предложение в отдельные дни всегда превышает спрос. Есть резервы снижения затрат на содержание персонала. Но уже надо использовать, другие средства менеджера персонала. Например, нанимать на пиковые дни студентов и среди недели предоставлять им выходные учебные дни и др.
Контрольные вопросы
Индивидуальные задания
1. Составить оптимальный план постоянных штатов бригад для обслуживания неравномерного известного календарного спроса, обеспечивая каждому работнику два смежных выходных дня и минимизи руя численность всего персонала и затраты на заработную плату.
2. Для расширения прав работников на выходные, ввести дополнительное ограничение на выходные дни: группа (бригада) с субботне-воскресным выходным днем должна состоять из N человек.
3. Добавить условие двукратного увеличения заработной платы для работающих в выходные дни.
Вариант №1
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
32 |
28 |
29 |
35 |
27 |
22 |
21 |
N=4
Вариант №2
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
31 |
33 |
28 |
30 |
24 |
32 |
33 |
N=5
Вариант №3
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
41 |
43 |
32 |
38 |
37 |
40 |
31 |
N=7
Вариант №4
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
28 |
29 |
34 |
31 |
36 |
31 |
34 |
N=6
Вариант №5
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
25 |
28 |
29 |
34 |
32 |
29 |
29 |
N=5
Вариант №6
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
36 |
34 |
38 |
31 |
32 |
28 |
19 |
N=4
Вариант №7
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
42 |
48 |
53 |
54 |
38 |
42 |
41 |
N=7
Вариант №8
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
42 |
36 |
46 |
39 |
42 |
32 |
30 |
N=4
Вариант №9
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
42 |
37 |
42 |
35 |
36 |
52 |
47 |
N=6
Вариант №10
|
Дни работы бригад |
Пн. |
Вт. |
Ср. |
Чт. |
Пт. |
Сб. |
Вс. |
|
Потребность в работниках |
54 |
58 |
52 |
61 |
55 |
44 |
38 |
N=5
Лабораторная работа № 5
Оптимальный план затрат на рекламу
Цель работы: научиться планировать оптимальные объемы затрат на рекламу для увеличения объема продаж и получения наибольшей прибыли; освоить методику и технологию оптимизации планов в Excel с помощью программы Поиск решения.
Теоретические сведения
Постановка задачи
При разработке годового финансового плана деятельности фирмы необходимо определить расходы на рекламу для получения наибольшей прибыли.
Определение проблемной системы. Предварительно проблемная систе ма должна включать:
Для начала упростим задачу годового планирования и будем определять расходы на рекламу для получения наибольшей прибыли в одном 1-ом квартале года. Предполагается, что увеличение рекламы вызывает увеличение продаж без «временного лага (запаздывания, последействия, отклоняющего аргумента), т.е. в этом же квартале.
Лабораторная модель. Табличная модель
Обычно план-прогноз движения средств разрабатывается в виде таблицы. Вариант разработки плана приведен в Excel табл. 5.1.
Искомые показатели таблицы окружены сплошной жирной рамкой. Это затраты на рекламу и производственная прибыль. Мы должны при разработке плана изменением объема затрат на рекламу добиться наилучшей прибыли. Исходные данные окружены пунктиром. Это коэффициент сезонного изменения объемов продаж, затраты на торговый персонал, цена и се бестоимость изделия.
Таблица 5.1
Формулы табличной модели
После составления плановой таблицы необходимо связать причинно-следственные показатели формулами для вычислений. Представление фор мул и чисел исходных данных дано в табл. 5.2.
Таблица 5.2
Если в ячейки введены числа, то это исходные (внешние, экзогенные) данные. Если в ячейке формула, то это вычисляемый (эндогенный) показатель. Свод формул с пояснениями дан в табл. 5.3.
Управление экспериментами
Осуществляется вручную или с помощью Таблицы подстановок и программы оптимизации Поиск решения. Управляющими данными являются суммы затрат на рекламу в ячейке В11. Управление осуществляется с целью максимизации прибыли в ячейке В15.
Для расширения экспериментов можно менять число периодов планирования, динамику прогноза фиксированных расходов, цены и себестои мость, вводить ограничения для показателей и др.
Таблица 5.3
Задание 1. Предварительный анализ элементов модели
Прогнозиста и плановика всегда преследует препротивное ощущение неуверенности. Будущее однозначно непредсказуемо. Но они его предска зывают, готовят варианты решений. Менеджеры принимают решения. Вы спроектировали плановую таблицу, но вы не уверены, достаточно ли факто ров включено в модель, правильно ли отражены причинно-следственные связи в модельных формулах элементов. Вы не математик. Вы экономист. Даже при наличии готовой формулы вы не чувствуете поведение показате лей. Будет ли показатель иметь экстремум? Встретим ли мы унимодальность или многоэкстремальность? Насколько сильно влияние факторов? Поэтому плановик, чтобы почувствовать модель, проверяет поведение ее отдельных элементов и показателей.
Зависимость продаж от затрат на рекламу
В нашем примере аналитики, статистики и экономометристы задали плановикам формулу зависимости объема продаж от затрат на рекламу в строке Число продаж как x=35*С*(РР+3000)^0.5.
где С – сезонная поправка;
РР – расходы на рекламу.
Чтобы понять и почувствовать силу влияния факторов, лучше воспользоваться графиком.
Задание: построить график зависимости числа продаж от затрат на рекламу.
Составить таблицу влияния фактора методом Excel Таблицы подстановки для одной функции с одним аргументом. Решение дано в нижней таблице на рис. 5.1.
Планируя эксперимент, установим начальное значение затрат на рекламу 6000 грн. и будем задавать следующие числа арифметической прогрессией с шагом 4000 грн. Введем в ячейку І23 число 6000, а в J23 - 10000. Отселектируем обе ячейки. Протянем прямоугольную рамку курсора за правый нижний угол с крестиком вдоль строки. Мы получили ряд чисел арифметической прогрессии. Это план однофакторного имитационного эксперимента.
Рис. 5.1. Зависимость количества продаж от затрат на рекламу
В ячейку Н24 введем формулу нашей функции. Она отображается в строке формул листа Excel вверху рисунка. Отселектируем диапазон с числами и формулой. Исполнить команды меню Данные Таблица подстановки. Появится диалоговое окно Таблица подстановки (рис. 5.2).
Рис. 5.2. Диалоговое окно таблицы подстановки
Введем в поле Подставлять значения по столбцам в ячейку В11, т. е. мы будем менять затраты на рекламу в нашей главной таблице. Щелкнем кнопку ОК.
Числами заполняется строка Число продаж. Известными способами построить над таблицей график. Наблюдаем и анализируем зависимость продаж от затрат на рекламу. Продажи растут. Экстремума нет. Значит, оптимальное решение в этой системе "реклама-продажи" невозможно. Крутизна зависимости с ростом затрат убывает, т. е. наблюдается убывающая эффективность фактора. Для количественной оцени убывающей эффективности фактора введем в строку 25 формулы оценки прироста продаж на каждом шаге эксперимента вычитанием из строки 24 предыдущего значения из последующего. На интервале исследо вания прирост продаж убывает от 600 до 300.
Зависимость прибыли от затрат на рекламу
Бесконечное увеличение затрат на рекламу будет бесконечно увеличи вать объем продаж. Но изменение прибыли, вероятно, будет иметь макси мум, поскольку увеличение затрат на рекламу в конце концов съест всю прибыль. Исследуем эту зависимость.
Методом построения прогрессии заполним строку затрат на рекламу в табл. 5.4. Каждое число копируем в ячейку рекламы табл. 5.1 и после пере счета таблицы копируем число производственной прибыли в соответствующую ячейку строки прибыли табл. 5.4. По данным этой таблицы построим график (рис. 5.3).
Таблица 5.4
|
Затраты на рекламу, грн. |
6000 |
10000 |
14000 |
18000 |
22000 |
26000 |
30000 |
|
Прибыль, грн. |
12895 |
14324 |
14964 |
15083 |
14825 |
14278 |
13500 |
Рис. 5.3. Зависимость прибыли от затрат на рекламу
Зависимость прибыли от затрат на рекламу имеет ярко выраженный максимум. Менеджер получит оптимальный план, если он определит затра ты на рекламу в 18 000 грн. При этом будет получена прибыль 15 000 грн.
Программный поиск оптимального плана на один период
Для поиска оптимального плана использовать программу Excel Поиск решения. В меню Сервис выполните команду Поиск решение. Появиться диалоговое окно (рис.5.4)
Рис. 5.4. Диалоговое окно Поиск решения однопериодной оптимизации без ограничений
Задайте В15 (прибыль за 1-й квартал) в поле Установить целевую ячейку. Выберите поиск наибольшего значения и укажите в качестве изменяе мой ячейки В11 (расходы на рекламу в 1-м квартале). Запустите процесс поиска решения.
В процессе решения задачи в строке состояния будут отображаться со общения. Через некоторое время появится сообщение о том, что решение найдено. В соответствии с найденным решением (табл. 5.1), затратив 17 093 грн. на рекламу в 1-м квартале, можно получить наибольшую при быль, которая составит 15 093 грн. Это решение точнее полученного ранее графического решения.
Задание 3. Разработка многоэтапного оптимального плана без ограничений
После того как вы освоили одноэтапную, статическую оптимиза цию плана, можно перейти к многоэтапной, динамической оптимизации и определить бюджет на рекламу в каждом квартале, соответствующий наибольшей сумме годовой прибыли. Форма поквартального годового плана представлена в табл. 5.5.
Таблица 5.5
Формулы таблицы поквартального годового плана представлены в табл.5.6.
В табл. 5.6 формулы по кварталам идентичны, поэтому для компактно сти таблицы не отображены 3-й и 4-й кварталы.
Таблица 5.6
Программный поиск оптимального плана
После того как студенты попытались составить оптимальный план вручную и убедились, что это почти невозможно, предлагается составить оптимальный план с помощью программы оптимизации Поиск решения.
Настройка модели в окне Поиск решения
В меню Сервис выполните команду Поиск решения. Появится диалоговое окно для настройки модели оптимизатора (рис. 5.5).
Задайте F15 (общая прибыль за год) в поле Установить целевую ячейку Выберите поиск максимального значения. Задайте в качестве изменяемых ячеек В11:Е11 (расходы на рекламу в каждом квартале). Поле Ограничения пусто.
Рис. 5.5. Окно настройки модели оптимизации
Задача является нелинейной задачей оптимизации средней степени сложности. Нелинейность уравнения связана с операцией возведения в степень в формуле строки числа продаж. Поэтому, нажав кнопку Параметры, задаем в окне настройки алгоритмов нелинейную задачу и выбираем методы Ньютона или градиентный.
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное решение готово. Студенты получили урок полезности математических методов оптимизации.
В табл. 5.7 дано оптимальное решение, найденное программой. Получен план с максимальной годовой прибылью от поквартальных годовых инве стиций в рекламу. Годовая прибыль увеличена до 79 706 грн. при годовых затратах на рекламу 89 706 грн.
Таблица 5.7
Задание 4. Оптимальный план с ограничением бюджета рекламы
Наиболее близкие к жизни модели учитывают также ограничения, на кладываемые на те или иные величины. Эти ограничения могут относиться к ячейкам результата, ячейкам изменяемых данных или другим величинам, используемым в формулах для этих ячеек. Итак, бюджет покрывает расхо ды на рекламу и обеспечивает получение прибыли, однако наблюдается тенденция к уменьшению эффективности вложений. Поскольку нет гаран тии, что данная модель зависимости прибыли от затрат на рекламу будет работать в следующем году (учитывая существенное увеличение затрат), целесообразно ввести ограничение расходов, связанных с рекламой. Предпо ложим, что расходы на рекламу за 4 квартала не должны превышать 40 000 грн. Добавим в рассмотренную задачу соответствующее ограничение.
Настройка модели в окне Поиск решения
В меню Сервис выполните команду Поиск решения и в диалоговом окне (рис. 5.6) нажмите кнопку Добавить. Введите в поле Ссылка на ячейку ссылку F11 (общие расходы на рекламу) листа Excel. Содержимое этой ячейки не должно превышать 40 000 грн. Выберите устанавливаемое по умолчанию от ношение <= (меньше или равно). В поле Ограничение, расположенном справа, введите число 40 000. Нажмите кнопку ОК и затем Выполнить.
Рис. 5.6. Окно настройки модели оптимизации с ограничением
В табл. 5.8 дано оптимальное решение, найденное программой. Годовой бюджет на рекламу в 40 000 грн. не превышен, ограничение соблюдено. В соответствии с найденным решением на рекламу будет выделено 5117 грн. в 3-м квартале и 15 263 грн. - в 4-м квартале. Прибыль увеличится с 69 662 грн. (при равномерных вложениях по 10 000 грн. в квартал, табл. 5. до 71 447 грн. при одинаковом годовом бюджете на рекламу.
Таблица 5.8
Изменение ограничений
Поиск решения позволяет экспериментировать с различными парамет рами задачи для определения наилучшего варианта решения. Например, из менив ограничения, можно оценить изменение результата. Попробуйте из менить ограничение на рекламный бюджет с 40 000 до 50 000 грн. и по смотрите, как изменится при этом общая прибыль.
В меню Сервис выберите пункт Поиск решения. В списке Ограничения уже задано ограничение $F$11<=40 000. Нажмите кнопку Изменить. Изме ните в поле значение с 40 000 на 50 000. Нажмите кнопку ОК, а затем - Вы полнить.
Найденное решение представлено в табл. 5.9. Прибыль равна 74 817 грн., что на 3 370 грн. больше прежнего значения 71 447 грн. при ограничении 40 000 грн. Для большинства предприятий увеличение капиталовложений на 10 000 грн., приносящее 3 370 грн. (т. е. 33,7 % рентабельности вложе ний), является оправданным. Прибыль при таком решении будет на 4 889 грн. меньше по сравнению с задачей без ограничений, однако при этом требуется и на 39 706 грн. капиталовложений меньше.
Таблица 5.9
Анализ результатов
Составить оптимальный план инвестиций в рекламу вручную даже на компьютере трудоемко, долго, практически невозможно. Компьютерная программа оптимального математического программирования составляет план за секунды.
Анализ быстро получаемых вариантов плана позволяет менеджеру ос мыслить и прочувствовать влияние ряда факторов на показатели плана, по лучить дополнительную прибыль за счет разработки научно обоснованного оптимального варианта плана.
Контрольные вопросы
Индивидуальные задания
Вариант №1
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,8 |
1,2 |
1,3 |
0,9 |
|
Расходы на торговый персонал |
8000 |
10000 |
11000 |
8000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
5000 |
|||
|
Цена изделия, грн. |
50 |
Вариант №2
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,7 |
1,0 |
0,9 |
1,1 |
|
Расходы на торговый персонал |
7000 |
9000 |
8000 |
10000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
10000 |
|||
|
Цена изделия, грн. |
40 |
Вариант №3
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,9 |
1,1 |
0,9 |
1,2 |
|
Расходы на торговый персонал |
5000 |
6000 |
5500 |
7000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
5000000 |
|||
|
Цена изделия, грн. |
70 |
Вариант №4
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,8 |
1,2 |
1,3 |
0,9 |
|
Расходы на торговый персонал |
8000 |
10000 |
11000 |
8000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
250000 |
|||
|
Цена изделия, грн. |
75 |
Вариант №5
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,7 |
1,0 |
0,9 |
1,1 |
|
Расходы на торговый персонал |
7000 |
9000 |
8000 |
10000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
5000 |
|||
|
Цена изделия, грн. |
60 |
Вариант №6
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,75 |
1,1 |
1,1 |
0,8 |
|
Расходы на торговый персонал |
11000 |
15000 |
14000 |
12000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
100000 |
|||
|
Цена изделия, грн. |
35 |
Вариант №7
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
1,25 |
0,9 |
0,9 |
1,4 |
|
Расходы на торговый персонал |
14000 |
11000 |
11500 |
16000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
500000 |
|||
|
Цена изделия, грн. |
110 |
Вариант №8
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
1,1 |
0,7 |
0,6 |
1,2 |
|
Расходы на торговый персонал |
6000 |
3000 |
2500 |
7000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
25000 |
|||
|
Цена изделия, грн. |
80 |
Вариант №9
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,8 |
1,1 |
1,3 |
0,8 |
|
Расходы на торговый персонал |
2500 |
5000 |
6000 |
3000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
250000 |
|||
|
Цена изделия, грн. |
55 |
Вариант №10
|
I квартал |
II квартал |
III квартал |
IV квартал |
|
|
Сезонность |
0,9 |
1,1 |
1,2 |
0,8 |
|
Расходы на торговый персонал |
3500 |
5000 |
6000 |
3000 |
|
Объем продаж x |
|
|||
|
Рекламный бюджет, N, грн. |
50000 |
|||
|
Цена изделия, грн. |
50 |
Лабораторная работа № 6
Инвестиции свободных средств, динамическая оптимизация
Цель работы: научиться планировать инвестиции временно свободных денежных средств в ценные бумаги с наибольшей доходностью и наименьшим риском потери ликвидности; освоить методику и технологию оптимизации планов в Excel с помощью программы Поиск решения.
Теоретические сведения
Постановка задачи
Фирмы с небольшим собственным капиталом для обеспечения непре рывности производства покрывают потребность в оборотных средствах займами. Растет кредиторская задолженность и процентные затраты на оп лату ссуд. Снижается эффективность и ценовая конкурентоспособность предприятия.
Фирмы с достаточным собственным капиталом и значительным произ водственным циклом работают от собственного капитала и имеют на рас четных и фондовых счетах временно свободные денежные средства. Фирмы инвестируют (продают) на время деньги на денежных рынках и рынках ценных бумаг. Получают процентную или дисконтную прибыль, выплачи вают дивиденды, увеличивают капитал, улучшают ценовую конкурентоспо собность.
Финансовый менеджер стремится разместить временно свободные де нежные средства с наибольшей прибылью. Одновременно он должен обес печить ликвидность активов, необходимую для удовлетворения текущих потребностей фирмы в денежных средствах. Но на кредитном и бумажных рынках высоколиквидные активы малоприбыльны. Более прибыльны дол госрочные активы, но они малоликвидны. Финансовый менеджер решает проблему оптимального выбора между долями ликвидных и прибыльных активов в инвестиционном портфеле.
Определение проблемной системы
Предварительно проблемная система должна включать:
1. Оценку объемов свободных денежных средств.
2. Прогноз потребности в текущих расходах и поступлениях.
3. Перечень инструментов кредитного и бумажного рынка с их стоимост ными характеристиками и прогнозом доходности.
4. Предполагаемые средства решения проблемы: инструментарий эконо мико-математического, алгоритмического, программного и компьютер ного моделирования.
Лабораторная модель
Банк использует свободные средства на расчетном счете предприятия, но они до востребования и в любой момент могут быть сняты. Банк рискует. Предприятие тоже рискует. Оно может оказаться неплатежеспособным. Для погашения обязательств не будет средств на расчетном счете.
Банк стремится закрепить средства на срок и предлагает предприятию перевести часть средств с расчетного счета на депозит. Предприятие это устраивает, но оно предпочитает не депозит, а депозитный сертификат, так как его всегда можно продать при необходимости повысить ликвидность. Обслуживающий банк предлагает 1-, 3- и 6-месячные депозитные сертификаты.
Задача финансового менеджера - на полугодовом интервале планирова ния с наибольшей доходностью разместить временно свободные денежные средства на расчетном счете в 1-, 3- и 6-месячные депозитные сертификаты фиксированной доходности. При этом менеджер должен обеспечить собст венные потребности в средствах и страховой резерв.
Фиксированный неснижаемый остаток на расчетном счете необходим для обслуживания платежей по текущим операциям и обязательствам, нем заинтересованы и банк и предприятие: снижается риск неплатежей, повышается надежность и доверие предприятия, банка, партнеров.
Необходимо определить 9 сумм: ежемесячные суммы для 1-месячных депозитов; суммы депозитов 1-го и 4-го месяцев для квартальных депозитов; сумму 6-месячного депозита в 1-м месяце. Предполагается, что суммы депозитов и проценты возвращаются (погашаются, поступают) постнумерандо (в конце месяца), а инвестируются пренумерандо (в начале месяца).
Табличная модель
Обычно план-прогноз движения средств разрабатывается в виде табли цы. Это прогнозная или плановая оборотно-сальдовая ведомость для не скольких периодов планового горизонта. В ней отражаются сальдо будущих бухгалтерских счетов на начало периодов, дебетовые и кредитовые обороты (приход и расход средств, поступления и выплаты) и конечные сальдо пе риодов. Вначале структура документа составляется вчерне на бумаге или сразу в виде электронной таблицы. Пример приведен на рис. 6.1.
Рис. 6.1.
Исходные данные для расчетов в таблице окружены пунктирной рам кой. Искомые показатели окружены сплошной жирной рамкой.
В графе А даны наименования показателей и операций по движению средств на плановом расчетном счете. В диапазоне В6:С8 как исходные данные введены сроки депозитов и их доходность. В колонках В и G пред ставлены сокращенные оборотно-сальдовые ведомости на каждый месяц полугодового планового периода. В колонке Н - ведомость за 7-й месяц, но уже без инвестиций в депозитные сертификаты. Эта колонка позволяет учесть поступления от предшествующих полугодовых инвестиций. В ячей ке Н8 вычисляется процентная прибыль по сертификатам за полгода инве стиций. Это цель нашего плана, главный критерий его качества.
В строке 11 дается начальное сальдо для каждого месяца. Оно выполня ется копированием конечного сальдо предыдущего месяца из строки 18. Только начальная сумма 1 -го месяца не вычисляется, а задается конкретным числом, как начальное условие.
В проекте таблицы поступление средств (дебет счета, входной поток, приход) вычисляется в строках 12 (погашение основных сумм депозитов) и 13 (поступление процентных платежей за пользование депозитом).
В строках с 14-й по 16-ю отводится место для кредитования счета (рас ходов, выплат) по еще неизвестным при разработке плана инвестициям в 1-месячные, 3-месячные и 6-месячные депозитные сертификаты.
В строке 17 (Расходы) как исходное данное задан прогноз расходов и поступлений на счет по всем другим операциям, кроме операций по депо зитным сертификатам. Поступления даны со знаком минус.
В строке 18 вычисляется конечное сальдо каждого месяца как сумма начального сальдо с возвратом основных сумм депозитов и процентов по ним за вычетом инвестиций в депозиты и прогнозируемых расходов.
Формулы таблицы
После составления плановой таблицы необходимо связать причинно-следственные показатели формулами для вычислений. Представление фор мул и чисел исходных данных дано на рис. 6.2.
Рис. 6.2.
В таблице формулы по месяцам идентичны, поэтому для компактности таблицы мы скрываем 3, 4 и 5-й месяцы.
Управление экспериментами
Осуществляется с помощью программы оптимизации По иск решения. Управляющими данными являются суммы инвестиций на по купку депозитных сертификатов в строках 14:16. Управление осуществля ется с целью максимизации процентной прибыли в ячейке Н8.
Для расширения экспериментов, где есть над чем подумать финансово му менеджеру, можно менять доходность депозитов, динамику прогноза расходов, вводить ограничения для показателей и др.
Практическая работа
Обычно банк при заключении договора на расчетно-кассовое обслужи вание требует установить суммы неснижаемых остатков на счетах клиентов. Это повышает надежность и банка и клиента по обслуживанию обяза тельств. Определим границу сальдо на конец каждого месяца в 100 000 руб. Теперь при планировании инвестиций в сертификаты менеджер должен контролировать, чтобы остатки счета не были меньше 100 000 руб.
Увеличить инвестиции - возрастут доходы, но выйдут за границы обяза тельных остатков на счете. Уменьшить инвестиции - упадут доходы, оста нется недоиспользованной часть средств.
Компьютерный поиск оптимального плана
Вызвать команду меню Сервис>Поиск решения. Появляется диалоговое окно оптимизатора (рис. 6.3).
Рис. 6.3. Диалоговое окно Поиск решения с координатно-математической моделью задачи планирования инвестиций в депозитные сертификаты
Настройка модели (математическая постановка задачи для оптимизатора).
В диалоговое окно Поиск решения, в поле целевой ячейки, вводим адрес Н8 Доход по процентам. В поле Изменяя ячейки вводим адреса ячеек иско мого плана инвестиций в депозиты. В поле Ограничения вводим 4 строки условий, ограничивающих область допустимых решений нашей задачи.
Первое, второе и четвертое ограничение требуют, чтобы инвестиции в депозиты были больше нуля, т. е. неотрицательными. В движении средств по счету они будут вычитаться, пойдут по кредиту.
Третье неравенство требует неснижаемого остатка на расчетном счете (сальдо на конец месяца) в сумме 100 000 руб. Это обеспечивает надеж ность (ликвидность) банка и фирмы.
Свод параметров модели дан в табл. 6.1.
Таблица 6.1
|
Результат |
H8 |
Цель - получение наибольшего дохода по процентам |
|
Изменяемые данные |
B14:G14 |
Сумма по каждому типу депозита. |
|
B15, E15, B16 |
||
|
Ограничения |
B14:G14>=0 |
Сумма каждого депозита не может быть меньше |
|
B15:B16>=0 |
нуля. |
|
|
E15>=0 |
||
|
B18:H18>=100000 |
Конечная сумма не должна быть меньше 100000 р. |
После настройки модели и установки параметров алгоритма нажимаем кнопку Выполнить окна Поиск решения. Через секунду оптимальное реше ние готово. Студенты получили урок полезности математических методов оптимизации.
На рис. 6.4 дано оптимальное решение, найденное программой. Получен план максимальной доходности инвестиций свободных денежных средств в депозитные сертификаты. Удовлетворены все ограничения.
Рис. 6.4.
Найденное оптимальное решение предполагает получение дохода по процентам в размере 16 531 руб. при вложении максимально возможных сумм в 6- и 3-месячные депозиты с последующим возвратом к 1-месячным. Данное решение удовлетворяет всем поставленным ограничениям.
Анализ результатов
Составить оптимальный план инвестиций свободных оборотных средств вручную даже на компьютере трудоемко, долго, практически не возможно. Программа оптимального программирования составляет план за секунды.
Депозитные сертификаты являются эффективным средством вложения временно свободных средств предприятия при управлении оборотными фондами.
Контрольные вопросы
Индивидуальные задания
Вариант №1
|
Расходы по месяцам |
65000 |
20000 |
-15000 |
-20000 |
40000 |
-10000 |
20000 |
|
Нач. сумма |
300000 |
Неснижаемый остаток |
75000 |
Доходность |
1,5 |
2,5 |
5 |
Вариант №2
|
Расходы по месяцам |
-15000 |
10000 |
-15000 |
20000 |
30000 |
-15000 |
25000 |
|
Нач. сумма |
400000 |
Неснижаемый остаток |
75000 |
Доходность |
1,5 |
2,5 |
7 |
Вариант №3
|
Расходы по месяцам |
25000 |
25000 |
-10000 |
20000 |
-35000 |
-20000 |
40000 |
|
Нач. сумма |
500000 |
Неснижаемый остаток |
100000 |
Доходность |
1,5 |
2,5 |
6 |
Вариант №4
|
Расходы по месяцам |
50000 |
-30000 |
25000 |
-40000 |
50000 |
-18000 |
25000 |
|
Нач. сумма |
600000 |
Неснижаемый остаток |
100000 |
Доходность |
1,5 |
3 |
8 |
Вариант №5
|
Расходы по месяцам |
-45000 |
60000 |
-35000 |
50000 |
20000 |
-15000 |
30000 |
|
Нач. сумма |
600000 |
Неснижаемый остаток |
90000 |
Доходность |
1 |
2 |
6 |
Вариант №6
|
Расходы по месяцам |
-25000 |
40000 |
-55000 |
30000 |
40000 |
10000 |
10000 |
|
Нач. сумма |
500000 |
Неснижаемый остаток |
100000 |
Доходность |
1 |
2 |
8 |
Вариант №7
|
Расходы по месяцам |
40000 |
-20000 |
25000 |
-30000 |
-10000 |
50000 |
25000 |
|
Нач. сумма |
250000 |
Неснижаемый остаток |
50000 |
Доходность |
2 |
3 |
6 |
Вариант №8
|
Расходы по месяцам |
15000 |
10000 |
-10000 |
-15000 |
30000 |
-15000 |
25000 |
|
Нач. сумма |
200000 |
Неснижаемый остаток |
50000 |
Доходность |
2 |
4 |
9 |
Вариант №9
|
Расходы по месяцам |
-10000 |
15000 |
-10000 |
20000 |
25000 |
-16000 |
20000 |
|
Нач. сумма |
200000 |
Неснижаемый остаток |
60000 |
Доходность |
2 |
3 |
8 |
Вариант №10
|
Расходы по месяцам |
15000 |
10000 |
-5000 |
-10000 |
-15000 |
30000 |
25000 |
|
Нач. сумма |
250000 |
Неснижаемый остаток |
60000 |
Доходность |
1,5 |
2,5 |
6 |