Закрыть книгу Расчет заработной платы и выйти из Microsoft Excel.

Отладка модели

Результат выполнения этого задания – документ Microsoft Word Зарплата и отпускные – представлен на Рис. 2 – ведомость начисления отпускных сумм сотрудников отдела продаж за II квартал 2005 г. и Рис. 3– ведомости начисления заработной платы за апрель, май и июнь.

Рис. 2. Ведомость начисления отпускных сумм

Рис. 3. Ведомости начисления заработной платы за апрель, май и июнь

Практическое занятие №3. Ипотечное кредитование Краткая справка

Ипотека (от греческого «hypotheke» - залог) – вид залога, при котором заложенное имущество, как правило, земля и другая недвижимость, остается во владении залогодателя до наступления срока платежа.

Суть ипотеки (ипотечного кредита) заключается в следующем: вы заключаете с банком кредитный договор, согласно которому вам выделяются деньги на покупку недвижимости (квартиры). За пользование предоставленным кредитом вы платите банку проценты, а также ежемесячно производите возврат заемных средств. Недвижимость, приобретенная за счет кредита, будет находиться в залоге (ипотеке) у банка до полного погашения ипотечного кредита заемщиком (амортизации кредита). То есть, если у вас изменится финансовое положение, и вы не сможете выполнять кредитные обязательства перед банком, то он продаст квартиру и вернет свои деньги. В качестве ипотеки может выступать недвижимость, уже находящаяся у вас в собственности на момент заключения договора. Ипотечный кредит выдается на длительный срок (долгосрочные кредиты в России – более 10 лет). Некоторые банки допускают возможность досрочного погашения кредита. С помощью заемных средств вы можете приобрести жилье как на начальном этапе строительства, включая стадию котлована, так и на рынке вторичного жилья. Преимущества ипотечного кредита заключается в том, что, приобретая жилье в кредит, вы получаете возможность жить в своей собственной новой квартире уже сегодня, а расплачиваться за нее постепенно в течение срока кредита. Надо лишь помнить, что согласно заключенному ранее договору квартира находится в залоге у банка. Это значит, что условия договора будут ограничивать действия, которые вы сможете производить с данной недвижимостью. То есть продать ее до погашения суммы кредита вы не сможете.

В зависимости от способа амортизации ипотечные кредиты подразделяют на:

1. Постоянный ипотечный кредит.

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

2. Ипотеку с переменными выплатами

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

Постановка задачи

Предположим, Вы решили купить однокомнатную квартиру в строящемся доме в Санкт-Петербурге стоимостью $44500³. Вы решили взять ипотечный кредит, например, в Сбербанке, в размере $40000 (размер кредита не может превышать 90% от реальной стоимости объекта) на 3 года под 11% годовых⁴.

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

Разработка моделей решения Модель 1. Амортизация стандартного (аннуитетного) ипотечного кредита.

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

Каждый месяц на протяжении всего срока возврата кредита заемщик выплачивает:

ЕВ=ОЧП+ППП,

где ЕВ – ежемесячная постоянная выплата, ОЧП – основная часть платежа, ППП – платежи по процентам за кредит.

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

Для вычисления ОЧП используется функция ОСПЛТ, которая возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки и имеет следующий синтаксис:

Осплт(ставка;период;кпер;пс;бс;тип),

где ставка – процентная ставка за период (в данной задаче период равен одному месяцу, так как выплаты по процентам производятся ежемесячно; значит месячная процентная ставка вычисляется как годовая, деленная на 12 месяцев); период – задает период, значение должно быть в интервале от 1 до кпер; кпер – общее число периодов выплат (так как выплаты по кредиту производятся ежемесячно, то кпер вычисляется как количество лет кредита, умноженное на 12 месяцев) ; пс – приведенная стоимость, т. е. общая сумма, которая равноценна ряду будущих платежей; бс – требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0, т. е. для займа, например, значение бс равно 0; тип – число 0 или 1, обозначающее, когда должна производиться выплата.

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

ПРПЛТ(ставка;период;кпер;пс;бс;тип),

где аргументы имеют то же значение, что и для функции ОСПЛТ.

Аргументы ставка, период, кпер и пс в этих двух функциях являются обязательными, аргументы бс и тип могут отсутствовать. Для данной задачи аргумент бс равен нулю, так как после последней выплаты кредит полностью погашается. Аргумент тип также равен нулю (предполагается, что выплаты производятся в конце каждого периода).

Замечания.

1. Вычислить величину основной части платежа и ежемесячные выплаты по процентам можно следующим образом:

,

,

где - баланс долга на конец предыдущего месяца (т. е. на момент i-ой выплаты); - процентная ставка за месяц; - общее число выплат (количество месяцев кредита); - номер выплаты (месяца).

2. Для вычисления общей суммы постоянного ежемесячного платежа можно использовать функцию ПЛТ, которая имеет синтаксис:

ПЛТ(ставка;кпер;пс;бс;тип),

где ставка – процентная ставка по ссуде; кпер – общее число выплат по ссуде; пс – приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой; бс – требуемое значение будущей стоимости, или остатка средств после последней выплаты. (Если аргумент бс опущен, то он полагается равным 0, т. е. для займа, например, значение бс равно 0.); тип – число 0 или 1, обозначающее, когда должна производиться выплата.

Модель 2. Амортизация кредита с фиксированными выплатами основной суммы долга.

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

Каждый месяц на протяжении всего срока возврата кредита заемщик выплачивает:

ЕВ=ОЧП+ППП,

где ЕВ – ежемесячная выплата, ОЧП – основная часть платежа (постоянная), ППП – платежи по процентам за кредит.

Для вычисления ОЧП используется следующая формула:

,

где СК – сумма кредита, ЧВ – число выплат (месяцев полного возврата ссуды).

Для вычисления ППП используется следующая формула:

,

где - баланс долга на конец предыдущего месяца (т. е. на момент i-ой выплаты); - процентная ставка за месяц.

Реализация Модель 1. Амортизация стандартного (аннуитетного) ипотечного кредита.

1. Открыть новую книгу Microsoft Excel и сохранить ее в личной папке под именем Ипотека.

2. На первом листе рабочей книги (назвать этот лист Постоянные платежи) сформировать блок исходных данных как показано ниже:

Замечание. Денежные средства, которые должны быть выплачены, представляются отрицательными числами; денежные средства, которые должны быть получены, представляются положительными числами.

3. Для ячеек В2 и В3 установить процентный формат, для ячейки В6 установить денежный формат в долларах США без десятичных знаков.

4. В ячейку В3 ввести формулу для вычисления месячной процентной ставки (годовая ставка, деленная на 12 месяцев).

5. В ячейку В5 ввести формулу для вычисления общего числа выплат (количество лет кредита, умноженное на 12 месяцев).

6. Заполнить ячейки как показано ниже:

   	C	D	E	F	G
   8	Месяц	Платеж в счет основной суммы	Баланс долга	Платежи по % за месяц	Всего платежи за месяц

7. В ячейках C9:C44 с использованием функции Автозаполнение сформировать список номеров выплат по месяцам (от 1 до 36).

8. В ячейке D9 с использованием функции ОСПЛТ ввести формулу для вычисления основной части платежа (параметр ОЧП в модели 1). Автозаполнить диапазон D10:D44.

9. В ячейке E9 ввести формулу для вычисления баланса долга на конец месяца (представляет собой разницу между оставшейся суммой долга и суммарный платежей в счет основной суммы). Баланс долга на конец последнего месяца должен быть равен нулю.

10. В ячейке F9 ввести формулу для вычисления платежей по процентам за первый месяц с использованием финансовой функции ПРПЛТ (параметр ППП в модели 1). Автозаполнить диапазон F10:F44.

11. В ячейке G9 ввести формулу для вычисления общего платежа за первый месяц (параметр ЕВ в модели 1). Автозаполнить диапазон G10:G44.

12. В строке 46 вычислить сумму по столбцам D, F и G.

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

14. Сохранить изменения.

Модель 2. Амортизация кредита с фиксированными выплатами основной суммы долга.

1. Второй лист рабочей книги Ипотека назвать Переменные платежи.

2. В ячейке А1 ввести название План амортизации кредита с фиксированными выплатами основной суммы долга.

3. Ячейки А2:В6 заполнить так же, как и на первом листе.

4. Заполнить ячейки как показано ниже:

   	C	D	E	F	G
   8	Месяц	Платеж в счет основной суммы	Баланс долга	Платежи по % за месяц	Всего платежи за месяц

5. В ячейках C9:C44 с использованием функции Автозаполнение сформировать список номеров выплат по месяцам (от 1 до 36).

6. В ячейке D9 ввести формулу для вычисления основной части платежа (параметр ОЧП в модели 2). Автозаполнить диапазон D10:D44.

7. В ячейке E9 ввести формулу для вычисления баланса долга на конец месяца (представляет собой разницу между оставшейся суммой долга и суммарный платежей в счет основной суммы). Баланс долга на конец последнего месяца должен быть равен нулю.

8. В ячейке F9 ввести формулу для вычисления платежей по процентам за первый месяц (параметр ППП в модели 2). Автозаполнить диапазон F10:F44.

9. В ячейке G9 ввести формулу для вычисления общего платежа за первый месяц (параметр ЕВ в модели 2). Автозаполнить диапазон G10:G44.

10. В строке 46 вычислить сумму по столбцам D, F и G.

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

12. Сохранить изменения, закрыть рабочую книгу и выйти из Microsoft Excel.

Отладка моделей

Для модели 1 результаты представлены на Рис. 4 – график погашения кредита и на Рис. 6 – таблица расчета выплат.

Рис. 4. График погашения кредита с постоянными выплатами

Для модели 2 результаты представлены на Рис. 5 – график погашения кредита и на Рис. 7 – таблица расчета выплат.

Рис. 5. График погашения кредита с переменными выплатами

Рис. 6. Таблица расчета выплат по модели 1

Рис. 7. Таблица расчета выплат по модели 2

Практическое занятие №4. Оптимизация доставки товаров Краткая справка

Задача оптимизации доставки товаров потребителям (скажем с нескольких складов в несколько магазинов) относится к классу задач оптимального планирования.

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

Здесь коэффициенты - действительные числа. Использование матричных обозначений позволяет записать задачу линейного программирования в виде:

где:

,

- матрица размера ,

- вектор размера ,

- вектор размера ,

означает поиск минимума целевой функции.

означает поиск максимума целевой функции.

Очевидно соотношение:

.

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

Если после отбрасывания одного условия допустимая область не изменяется, то это условие считается лишним.

В случае недостающего условия или для преобразования неравенства в равенство вводится дополнительная переменная.

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

В Microsoft Excel задачи подобной оптимизации решаются программой Поиск решения, которая относится к группе средств, выполняющих так называемый анализ «что-если». Суть этой методики состоит в том, что можно автоматически изменять исходные переменные задачи и сразу же видеть результаты этих изменений. Автоматическое обновление вычислений обеспечивает интерактивную обратную связь с экспериментами «что-если». Если для модели установлен автоматический пересчёт, то можно изменять (небольшими порциями - инкрементами) значение ячейки (или нескольких ячеек) независимой переменной (или нескольких переменных) и тут же увидеть результаты пересчёта во всех результирующих ячейках, которые зависят от изменённых значений. Программа, запускаемая командой Поиск решения, может применяться для решения задач, которые включают много изменяемых ячеек (независимых переменных), и помогают найти такие их комбинации, которые минимизируют или максимизируют значения в целевых ячейках (зависимых переменных). Такую методику можно применить и к решению задач линейного программирования.

Постановка задачи

Рассмотрим задачу нахождения такого плана перевозок продукции с М складов к N потребителям, который требовал бы минимальных затрат. Обозначим - количество продукции, поставляемое со склада i потребителю j. Пусть - издержки доставки единицы продукции со склада i потребителю j. Предполагается, что транспортные расходы пропорциональны количеству перевозимой продукции, т. е. .

Обозначим

1) для ;

2) для .

где:

- количество продукции, находящееся на складе i;

- количество продукции, необходимой потребителю j.

Для решения задачи необходимо соблюдение равенства

.

Таким образом, потребность в продукции должна быть обеспечена.

Целевая функция определяется равенством

.

Исходными данными при решении данной задачи являются:

• издержки транспортировки либо прибыль от реализации товара (массив Р);

• количество товара на каждом складе (массив С);

• количество товара, нужного каждому потребителю (массив В).

Реализация

Решение задачи рассмотрим на примере доставки товара с 4 складов для 5 потребителей.

1. Открыть новую книгу Microsoft Excel и сохранить ее в личной папке.

2. Первый рабочий лист назвать Издержки и опт доставка

3. Заполнить диапазон А1:F6 как показано ниже.

   	A	B	C	D	E	F
   1	Издержки доставки товаров со складов в магазины (массив Р)
   2	Потребители Склады	Магазин 1	Магазин 2	Магазин 3	Магазин 4	Магазин 5
   3	Склад 1	3,2	2,7	2,9	2,5	2,8
   4	Склад 2	2,9	2,9	3,1	2,7	2,9
   5	Склад 3	2,7	2,6	2,8	2,4	2,7
   6	Склад 4	3,1	2,8	3,2	2,8	2,9

4. Присвоить имя диапазону B3:F6.

   1. Для этого:

      • Выделить диапазон B3:F6

      • В меню Вставка выбрать Имя – Присвоить

      • В появившемся диалоговом окне Присвоение имени в поле Имя: набрать Р

      • Щелкнуть Добавить и ОК. Теперь, если диапазон B3:F6 выделен, то слева в строке формул в качестве его имени появилась надпись Р

5. Заполнить диапазон А8:Н17 как показано ниже.

   	A	B	C	D		E		F		G	H
   8	Таблица оптимальной доставки товаров
   9	Количество перевозимых товаров (массив Х)
   10	Потребители Cклады	М1	М2	М3		М4		М5		Вывоз со склада	Наличие на складе
   11	Склад 1										250
   12	Склад 2										220
   13	Склад 3										280
   14	Склад 4										250
   15	Сумма доставки
   16	Требуемая сумма	190	210	220		230		150
   17	Минимум целевой функции

6. Диапазону B11:F14 присвоить имя X (выполнить аналогично п. 4).

7. В строке Сумма доставки ввести формулы расчета товара фактически доставленного каждому потребителю.

   1. Для этого:

      • В ячейку В15 ввести формулу =СУММ(В11:В14). Также можно воспользоваться кнопкой Автосумма на панели инструментов Стандартная

      • Автозаполнить диапазон С15:F15

8. В ячейке G16 вычислить общее количество товара, необходимого потребителям.

   1. Для этого:

      • В ячейку G16 ввести формулу =СУММ(В16:F16)

9. В колонке Вывоз со склада ввести формулы расчета фактического вывоза товара с каждого склада/

   1. Для этого:

      • В ячейку G11 ввести формулу =СУММ(В11:F11)

      • Автозаполнить диапазон G12:G14

10. В ячейке H15 ввести формулу для вычисления общего количества товара, находящегося на складе.

    1. Для этого:

       • В ячейку Н15 ввести формулу =СУММ(Н11:Н14)

11. В ячейку Н16 ввести формулу контроля общих сумм в ячейках G16 и HI5:

=ЕСЛИ(G16=Н15; «совпадают»; «НЕ совпадают»)

12. Диапазону G11:G14 присвоить имя Вывоз_со_склада.

13. Диапазону Н11:Н14 присвоить имя Наличие_на_складе.

14. Диапазону В15:F15 присвоить имя Сумма_доставки.

15. Диапазону В16:F16 присвоить имя Требуемая_сумма.

16. В ячейку В17 ввести формулу для вычисления минимума целевой функции:

=СУММПРОИЗВ(Р;Х)

17. Объединить ячейки В17 и С17.

    1. Для этого:

       • Выделить ячейки В17 и С17

       • Щелкнуть правой кнопкой мыши на выделенных ячейках и в появившемся контекстном меню выбрать Формат ячеек

       • В открывшемся диалоговом окне Формат ячеек на вкладке Выравнивание установите флажок объединение ячеек

       • Щелкнуть ОК

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

18. В меню Сервис выбрать Поиск решения. Откроется диалоговое окно Поиск решения, в котором:

    • В поле Установить целевую ячейку указать адрес целевой ячейки В17

    • В переключателе Равной: установить положение минимальному значению

    • В поле ввести имя диапазон целевых ячеек Bl1:F14 (где должен быть размещён результат решения) – X. Для этого:

      • Щелкнуть в поле Изменяя ячейки:

      • На клавиатуре нажать клавишу F3. Появится диалоговое окно Вставка имени, в котором выбрать Х и щелкнуть ОК (также можно воспользоваться меню Вставка – Имя – Вставить)

    • Для добавления ограничений системы линейных уравнений нажать кнопку Добавить. Откроется диалоговое окно Добавление ограничения. Здесь:

      • В поле Ссылка на ячейку ввести имя массива решений X (с помощью клавиши F3), в соседнем (справа) списковом поле без имени выбрать строку со знаком неравенства (>=), а в поле Ограничение: - ввести число 0. Нажать кнопку Добавить

      • Таким же способом в окне Добавление ограничения ввести равенство Х=целое (в поле Ссылка на ячейку опять ввести X через клавишу F3, а в правом от неё списковом поле выбрать значение цел и нажать кнопку Добавить)

      • Далее ввести (в те же три поля) равенство Вывоз_со_склада=Наличие_на_складе и нажать кнопку Добавить

      • Наконец, ввести там же равенство Сумма_доставка=Требуемая_сумма нажать ОК

      • После этого опять откроется диалоговое окно Поиск решения, в котором в поле Ограничения будут находиться введенные только что четыре условия

    • Щелкнуть на кнопке Выполнить