И остатка долга

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100000 руб. на срок 5 лет при годовой ставке 2%.

Таблица с исходными данными имеет вид.

В ячейку В3 введена формула встроенной финансовой функции =ПЛТ(В1;В2;-В4), которая возвращает величину ежегодных выплат. В более ранних версиях Microsoft Excel эта функция обозначается как =ППЛАТ.

За первый год плата по процентам в ячейке В7 вычисляется по формуле =D6*$B$1. Основная плата в ячейке С7 вычисляется по формуле =$B$3-B7. Остаток долга в ячейке D7 вычисляется по формуле =D6-C7. Первоначальное значение долга равно размеру ссуды, поэтому в ячейку D6 введена формула =$B$4. Обратите внимание на символ $ в формулах, указывающий на абсолютную ссылку. В оставшиеся годы платы определяются с помощью протаскивания маркера заполнения диапазона B7:D7 вниз по столбцам.

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

Так же основную плату и плату по процентам можно было непосредственно найти с помощью встроенных финансовых функций =ОСПЛТ и =ПРПЛТ. В более ранних версиях Microsoft Excel эти функции обозначаются как =ОСНПЛАТ и =ПЛПРОЦ.

Планирование штатного расписания

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

Следовательно, прием на работу должен осуществляться на один месяц раньше. Кроме того, каждый рабочий должен в течение месяца, отведенного на его подготовку, пройти 100-часовую практику. Таким образом, за счет каждого обучаемого рабочего в течение месяца освобождается 100 человеко-часов.

Каждый полностью обученный рабочий в течение месяца может отработать до 150 часов. Предприятие в начале января уже имеет 60 опытных рабочих. При этом ни одного из них не снимают с работы. Установлено так же, что приблизительно 10% обучаемых рабочих по окончании обучения увольняются по каким-либо обстоятельствам.

Опытный рабочий обходится предприятию в $800, а обучаемый - $400 в месяц. Необходимо спланировать штат предприятия таким образом, чтобы минимизировать издержки за отчетные шесть месяцев.

Таблица с исходными данными приведена ниже.

Отведем диапазон ячеек В3:В8 под число новых рабочих, принимаемых на работу с января по июнь. В ячейку В2 введем число рабочих, работающих в декабре. В диапазоне D3:D8 вычислим число рабочих, постоянно работающих в текущем месяце, введя в ячейки D3 и D4 формулы =В2 и =D3+0,9*B3 соответственно и протаскивая последнюю формулу на диапазон D5:D8.

В диапазоне Е3:Е8 вычислим отработанные часы по месяцам, введя в ячейку Е3 формулу =D3*$G$12+B3*$F$12 и протаскивая ее на диапазон Е4:Е8. В ячейки F12 и G12 введены допускаемые величины отработки за месяц обучаемого и опытного рабочих.

В диапазоне F3:F8 вычислим затраты по месяцам, введя в ячейку F3 формулу =D3*$E$12+B3*$D$12 и протаскивая ее на диапазон F3:F8. В ячейки D12 и Е12 введены затраты на обучение и работу.

Суммарные затраты на планируемый период вычислим в ячейке F9 по формуле =СУММ(F3:F8).

Итоговые затраты составят $288000. Выполните команду /Сервис/Надстройки и активизируйте надстройку Поиск решения.

Затем выполним команду /Сервис/Поиск решения и заполним диалоговое окно Поиск решения. Зададим адрес целевой ячейки, укажем диапазон изменяющихся ячеек и введем ограничения, используя кнопку Добавить.

Ввод ограничений выполняется в диалоговом окне Изменение ограничения.

Кнопка Параметры активизирует диалоговое окно Параметры поиска решения, где активизируются режимы Линейная модель и Неотрицательные значения.

Кнопка Выполнить диалогового окна Поиск решения выводит окно Результаты поиска решения. Выбираем тип отчета Результаты.

Результаты расчета оптимального штата рабочих приведены на следующем рисунке. Согласно этим расчетам предприятие в последний месяц планового периода должна взять на обучение 17 новых рабочих.

Так же Excel генерирует лист отчета следующего вида.

Дата добавления: 2015-12-07