Экономическая модель.Задана готовая модель деятельности предприятия. Все параметры и связи между экономическими показателями считаем постоянными, кроме инвестиций в рекламу по кварталам. Требуется определить оптимальный бюджет на рекламу в каждом квартале так, чтобы годовая прибыль оказалась бы максимальной. Математическая модель. Выберем в качестве математической модели данной задачи задачу нелинейного программирования. При этом в качестве целевой функции рассмотрим функцию прибыли предприятия за год как функцию четырёхмерного вектора Х, где компоненты представляют инвестиции в рекламу по кварталам. Задачу оптимизации решим 4 раза: без ограничений на суммарные инвестиции в рекламу за год, и с ограничениями на суммарные инвестиции в рекламу за год в размерах 40000, 50000 и 30000 ден.ед. Из найденных решений выберем то, в котором окажется наибольшим коэффициент прибыльности за год. Программное обеспечение. Задачу решим методом нелинейной оптимизации пакета Excel с помощью надстройки "Поиск решения";. Модель постройте следующим образом. 1. В первой строке записываем: A1 - Квартал, B1 - 1-ый, C1 - 2-й, D1 - 3-й, E1 - 4-й, F1 - Всего. 2. Во второй строке записываем: A2 - Сезонный фактор; для учёта сезонного изменения спроса зададим фиксированные значения сезонного фактора по кварталам: B2 - 0,9, C2 - 1,1, D2 - 0,8, E2 - 1,2 3. Третью строчку пропускаем для удобства восприятия нформации. В четвертой строке пишем: A4 - Объем сбыта, в ячейке B4 зададим для объема сбыта формулу: =(35+0.5*N)*B2*(B10 + 3000 + N*0.35)^0.5, и распространим ее на все кварталы направо. В эту формулу вместо N следует подставить номер варианта, например, номер, под которым фамилия студента стоит в списке группы. Замечание. Обратите внимание на то, что связь между ячейками B2 и B10 нелинейная! 3. В пятой строке набираем: A5 - Доход от оборота, B5 - набираем формулу: =B4*$B$17, распространяем ее на все кварталы вправо. 4. В шестой строке набираем: A6 - Себестоимость реализованной продукции, B6 - набираем формулу: =B4*$B$18, распространяем ее на все кварталы направо. 5. В седьмой строке набираем: A7 - Валовая прибыль, B7 - набираем формулу: =B5 - B6, распространяем ее на все кварталы направо. 6. Восьмую строку пропускаем. В девятой строке набираем: A9 - Затраты на зарплату, зададим величины этих затрат следующим образом: B9 - 8000, C9 -8000, D9 - 9000, E9 - 9000. 7. В десятой строке набираем: A10 - Затраты на рекламу, пусть это будут следующие величины: B10 - 10000, C10 -10000, D10 - 10000, E10 - 10000. 8. В одиннадцатой строке набираем: A11 - Накладные расходы, пусть это будет 15% от дохода от оборота: B11 - набираем формулу: =0,15* B5, распространяем ее на все кварталы направо. 10. В двенадцатой строке набираем: A12 - Валовые издержки, B12 - набираем формулу: =СУММ(B9:B11), распространяем ее на все кварталы направо 11. Тринадцатую строку пропускаем. В четырнадцатой строке набираем: A14 - Прибыль от продукции, B14 - набираем формулу: =B7 - B12, распространяем ее на все кварталы направо. 12. В пятнадцатой строке набираем: A15 - Коэффициент прибыльности, B15 - набираем формулу: = B14/B5, распространяем ее на все кварталы направо. 13. Шестнадцатую строку пропускаем. В семнадцатой набираем: A17 - Цена изделия, B17 - 40. 14. В восемнадцатой строке набираем: A18 - Затраты на изделие, B18 - 25. 15. Заполним последний столбец в соответствии со смыслом заполняемых строк. 16. Сохраним файл под оригинальными именами в папке "Мои документы" 5 раз. Рекомендации к решению задачи. Пусть нас интересует, как следует распределить инвестиции в рекламу по кварталам, чтобы прибыль за год была наибольшей без ограничений на суммарные годовые затраты на рекламу. Открываем один из сохранённых файлов. Нажимаем кнопку главного меню - Сервис. В подменю выбираем - Поиск решения. Заполняем в диалоговом окне ячейки: целевая - $F$14, устанавливаем флажок - Максимальное значение, набираем в окошке - Изменяемые ячейки - $B$10:$E$10, нажимаем кнопку - Параметры -убеждаемся, что установленный по умолчанию режим применения методов нелинейной оптимизации не отменен (то есть проверяем, не установлен ли флажок в окошке "Линейная модель" и если установлен, то снимаем его), далее нажимаем - Ok, Выполнить, выбираем " Сохранить найденное решение ";. Анализируем результат, он нас не устраивает (почему?). Ограничим суммарные затраты на рекламу. Посмотрим, как следует перераспределить затраты на рекламу по кварталам при фиксированной сумме за год, чтобы годовая прибыль была максимальной. Открываем следующий из сохранённых файлов. Вновь нажимаем - Сервис, Поиск решения, Добавить, - добавляем ограничение - F10 ≤ 40000, Ok, Выполнить, выбираем " Сохранить найденное решение ";. Анализируем результат. Загружаем ещё один файл с исходными данными. Вновь нажимаем - Сервис, Поиск решения, ограничение F10 ≤ 50000, Ok, Выполнить, выбираем " Сохранить найденное решение ";. Анализируем результат. Аналогично решаем задачу при ограничении суммарных годовых расходов на рекламу величиной 30 000р Сравниваем между собой все найденные решения и выбираем наилучшую инвестиционную политику из числа исследованных для рекомендации ЛПР.
Задача 2. Решите задачу линейного программирования по подбору оптимального графика работы для семи бригад работников с пятидневной рабочей неделей и двумя выходными подряд, обеспечивающий требуемый уровень обслуживания при наименьших затратах на оплату труда, методом линейной оптимизации пакета EXCEL с использованием надстройки "Поиск решения". Выберите следующую модель. В первом столбце набираем: A6 - График, A7 - А, A7 - Б, A9 - В, A10 - Г, A11- Д, A12 - Е, A13 - Ж, A14:A18 пропускаем; A19, B19, C19 объединяем и набираем: - Дневная оплата работника; A20, B20, C20 объединяем и набираем: - Общая недельная зарплата. Объединяем ячейки от B6 до B18 с ячейками от C6 до C18 В объединенных ячейках нового второго столбца набираем: B6 - Выходные дни, B7 - Воскрес., понедельник, B8 - Понедельник, вторник, B9 - Вторник, среда, B10 - Среда, четверг, B11 - Четверг, пятница, B12 - Пятница, суббота, B13 - Суббота, воскресенье, B14 пропускаем, B15 - Всего:, B16 пропускаем, B17 объединяем с C17 - Всего требуется, B18 пропускаем. В третьем столбце таблицы набираем: D6 - Работники, D7 - 4, D8 - 4, D9 - 4, D10 - 6, D11 - 6, D12 - 4, D13 - 4, D14 пропускаем, D15 - набираем формулу: = СУММ(D7:D13), D16: D18 пропускаем, D19 - 40р., D20 - набираем формулу: =D15*D19. Столбец E пропускаем. Далее набираем: F6 - Вс.,F7 - 0,F8 - 1,F9 - 1, F10 - 1, F11 - 1, F12 - 1,F13 - 0, F14 пропускаем, F15 - набираем формулу: = $D$7*F7 + $D$8*F8 + $D$9*F9+ $D$10*F10 + $D$11*F11 + $D$12*F12 + $D$13*F713 и распространяем эту формулу направо до столбца L включительно, F16 пропускаем, F17 - 22, F18:F20 пропускаем. Замечание. Цифра 1 означает, что данная группа в этот день работает, 0 - не работает. Далее набираем: G6 - Пн.,G7 - 0,G8 - 0,G9 - 1, G10 - 1, G11 - 1, G12 - 1,G13 - 1, G14 пропускаем, G15 - 17, G16 пропускаем, G17 - 17, G18:G20 пропускаем. Дальнейшее заполнение ячеек H6:L13 производим аналогично в соответствии с выходными и рабочими днями недели. Шестнадцатая строка вся пропускается. В семнадцатой строке набираем далее: H17 - 13, I17 - 14, J17 - 15, K17 - 18, L17 - 24. Таблица полностью заполнена. Проверка. В ячейке D20 должно появиться число 1280р.
|
