Определенного финансового проекта (стюардессы)

Лабораторная работа № 3

Тема: решение задач оптимизации личного состава фирмы в процессе выполнения

определенного финансового проекта (стюардессы).

Программное обеспечение: OS Windows, MS Excel, MS Word

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

Задача оптимизации решается по принятому алгоритму:

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

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

в) составленный по результатам оптимизации отчет позволяет проанализировать оптимальность решения.

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

Порядок выполнения задания:

1-й этап: создадим таблицу с условиями задачи в электронном виде (MS Excel), для чего: - в колонку А вносим названия месяцев периода рассмотрения;

- в колонку В число новых стюардесс;

- в диапазоне ячеек С3:С8 вводим количество человеко-часов налета;

- в D12 и E12 вносятся затраты на обучение и работу имеющегося персонала;

- в F12 и G12 указываем допустимый месячный налет для обучаемой и штатной сотрудницы;

Полученная в результате этих действий картинка приведена на рис.1.

 

2-й этап: в колонку D вводим формулу для расчета полного количества стюардесс в данном месяце, для чего в D3 помещаем =B2, а в D4 вводим = D3 + B3 (протаскиваем последнюю для задания формул в ячейках D4 - D8) (см. рис.2).

 

3-й этап: в колонке ячеек E определяется оптимальный налет по месяцам, для чего в первую из них (E3) вводится соответствующая формула (=D3*$G$12+B3*$F$12) и протаскивается до ячейки, соответствующей последнему месяцу – E8. Полученный результат представлен на рис.3.

 

4-й этап: для расчета затрат по месяцам в ячейки F3:F8 вводится формула =D3*$E$12+B3*$D$12 с протаскиванием до ячейки F8 (она учитывает как оплату штатных сотрудниц, так и возможные дополнительные расходы на принимаемых вновь). Результат представлен на рис.4.

 

Рис.1.

 

 

Рис.2.

 

Рис.3.

 

Рис.4.

5 -й этап: для расчета за планируемый период суммарных затрат необходимо ввести в ячейку F9 формулу суммирования, для чего вызвать формулу “СУММ” и применить ее для соответствующих ячеек (F3:F8) (см. рис. 5).

Рис.6.

 

Рис.7.

 

 

6 -й этап: осуществляем поиск оптимального решения, для чего через “Сервис” вызываем “Поиск решения” и устанавливаем в появившемся окне целевую ячейку($F$9), выбираем изменяемые ячейки ($B$3:$B$8) и фиксируем ограничения ($B$3:$B$8=целое; $B$3:$B$8>=0; $E$3:$E$8>= $C$3:$C$8); после этого нажимаем “Выполнить” (полученные результаты представлены на рис. 8, рис.9, рис.10).

Примечание: формат всех ячеек должен быть числовым; для целевой ячейки нужно выбрать минимальное значение.

 

Рис. 8.

Рис.9.

Рис.10.

Рис.11.

Рис.12.

Рис.13.

 

 

На 11 и 12 рисунках представлены результаты применения оптимизации численности личного состава стюардесс. После выбора в окне “Результаты поиска решения” транспаранта “Результаты” и нажатия “ОК” создается отчет, пример которого приведен на рис.13.