Материал: Методические указания по выполнению лабораторных и самостоятельных работ. Морозов В.П., Свиридова Т.А

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

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

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

Время проведения: 2 часа

Программное обеспечение: 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.

Анализ полученного отчета по заданию. Выводы.

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

Контрольные вопросы

1. Каков должен быть формат ячеек от B3 до F9?

2. Каким образом задаются параметры в столбце C, D, E?

3. Как рассчитывается значение затрат в целевой ячейке?

4. Объясните последовательность действий при поиске оптимального решения.

5. Каким образом устанавливаются ограничения, изменяемые ячейки?

Задания для самостоятельной работы

1. Рассмотрите, как повлияет изменение количества сотрудниц в начальном штате на оптимизацию при сохранении распределения часов по месяцам.

2. Измените значения “затрат на стюардессу” и “разрешенный налет” и проанализи-руйте результаты оптимизации.

3. Придумайте самостоятельно задачу на оптимизацию (минимизацию) штата сотрудников учреждения (в условиях расширения или сокращения).

4. Рассмотрите возможный набор неточностей в разобранной лабораторной работе, который не позволит правильно провести оптимизацию.

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

Тема: Финансовый и статистический анализ. Применение в MS Excel встроенных функций.

Время проведения: 4 часа

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

Постановка целей занятия: для вычисления величины постоянной периодической выплаты ренты (например, регулярных платежей по займу) при неизменной величине процентной ставки используется функция ППЛАТ; эта функция содержит пять основных аргументов (ставка; кпер; нз; бз; тип);

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

второй – это общее число периодов выплат;

третий является текущим значением, равным общей сумме, которую составят в будущем платежи;

четвертый – баланс наличности, достижимый после последней выплаты (если этот аргумент не задан, он считается равным 0);

пятый – обозначает фазу периода, когда производится выплата (если “тип” равен 0 или отсутствует, то оплата происходит в конце периода, если 1, то в начале периода).

В том случае, когда последние два аргумента являются нулями, функция ППЛАТ задается формулой:

, где I , n, P – первые три из аргументов ППЛАТ.

Следует помнить, что при выборе единиц измерения первых двух аргументов нужно быть последовательным, т.е. при выплатах, например, по четырехгодичному займу из расчета 12% годовых “ставка” задается как 12%/12, а аргумент “кпер” – 4*12. Если же платежи являются ежегодными, то и обозначения изменятся на 12% и 4 соответственно.

Если необходимо найти общую сумму, выплачиваемую на протяжении интервала выплат, то возвращаемое функцией ППЛАТ значение следует умножить на величину “кпер”. И, наконец, следует помнить, что в функциях, связанных с интервалами выплат, деньги, выплачиваемые в качестве депозита на накопление представляются отрицательным числом, а получаемые в качестве дивидендов – положительным.

Итак, в ходе данного лабораторного занятия нужно применить встроенную функцию ППЛАТ (PMT) для вычисления 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе20% и ежемесячной (ежегодной) выплате.

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

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

Необходимо проверить, чтобы форматы В3, В4, В5 были соответственно “денежным” и “процентным” (рис.2).

Рис.1.

Рис.2.

2-й этап: для вычисления значения размера ссуды в ячейку В7 вводится формула =B4*(1-B5) (рис.3); после нажатия “ENTER” в ячейке получается искомое значение (рис.4).

Рис.3.

Рис.4.

3-й этап: выделяем ячейку В9 для расчета срока погашения ежемесячной ссуды, для чего вводим формулу: =D9*12. Нажимаем и получаем 360 месяцев (рис.5). В В11 вводим формулу ППЛАТ (рис.6 и рис.7).

Рис.5.

Рис.6.

Рис.7.

Рис.8.

Аналогично же поступаем и с ячейкой D11, вводя в нее функцию ППЛАТ (рис.8 и рис.9).

Рис.9.

Рис.10.

4-й этап: для нахождения общей суммы выплат (за весь интервал) выделяется ячейка В12 и вводится формула =B9*B11. Аналогично поступают и для D12 (=D9*D11) (см. рис.10 и рис.11).

Рис.11.

Рис.12.

5-й этап: и, наконец, вычисляем общую сумму комиссионных, для чего выделяем ячейки B13 и D13, в которые помещаем формулы (=B12-$B$7) и (=D12-$B$7) соответственно (рис.12 и рис.13).

Рис.13.

Анализ полученного отчета по заданию. Выводы.

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

Контрольные вопросы

1. Какие стандартные функции Вам известны? Продемонстрируйте умение их вызвать.

2. Аргументы и их назначение при использовании функции ППЛАТ.

3. Что такое “интервал выплат”, “ставка”, “кпер”? их определения.

4. Каким образом и почему задаются расчеты в ячейках В7, В9, В11, В12, В13?

5. Каким образом и почему задаются расчеты в ячейках D11, D12, D13?

6. Каковы правила использования формул в Excel?

7. Какие возможны характерные ошибки при выполнение данной лабораторной работы?

8. Расскажите последовательность применения ППЛАТ в работе.