Финансовые расчеты и оптимизационные задачи в MS EXCEL

РАЗДЕЛ 2: «ФИНАНСОВЫЕ РАСЧЕТЫ И ОПТИМИЗАЦИОННЫЕ ЗАДАЧИ В MSEXCEL»

2.1 РАСЧЕТЫ С ИСПОЛЬЗОВАНИЕМ ФИНАНСОВЫХ ФУНКЦИЙ В MSEXCEL

Постановка задачи и ее математическая формулировка

Вычислить сумму, которую необходимо положить на депозит под 20% годовых, чтобы через три года получить 90 млн.руб. при ежеквартальном начислении процентов.

Описание входной и выходной информации

Результаты расчетов, графики и диаграммы

2.3 ОПТИМИЗАЦИОННЫЕ ЗАДАЧИ В MS EXCEL

Постановка задачи и ее математическая формулировка

Определить оптимальный план перевозки грузов с четырех складов в четыре пункта реализации, если известно, что на первом складе хранится 20 единиц, на втором – 30 единиц, на третьем – 50 единиц, на четвертом – 20 единиц продукции; потребность в продукции в пунктах реализации составляет соответственно 30 ед., 20 ед., 60 ед., и 15 ед.; стоимость перевозки груза со i-го пункта производства в j-ый пункт распределения составляет: 1,          3, 4, 5; 5, 2, 10, 3; 3, 2, 1, 4 и 6, 4, 2, 6.

Описание входной и выходной информации

В первую очередь составим таблицу с исходными данными (в соответствии с рисунком 2.3). Поскольку данная задача относится к типу задач с неправильным балансом(125>120), то необходимо ввести фиктивный склад. Стоимость перевозки груза из фиктивного склада в магазин принимается равной нулю. Для того чтобы решить данную задачу её следует формализовать, т.е. записать в виде уравнений (формул). Пусть X – количество перевозимого груза, следовательно  Х₁₁, Х₁₂, Х₁₃, Х_14, X₂₁, X₂₂, X₂₃, X₂₄, X₃₁, X_32, X_33, X₃₄, X₄₁, X₄₂, X₄₃, X₄₄, X₅₁, X₅₂,X₅₃,X₅₄ –количество перевозимого груза из соответствующего склада в соответствующий магазин. Тогда ограничения на потребности магазинов будут следующими: Х₁₁+X₂₁+X₃₁+X₄₁=30, Х₁₂+X₂₂+X₃₂+X₄₂=20, Х₁₃+X₂₃+X₃₃+X₄₃=60, Х₁₄+X₂₄+X₃₄+X₄₄=15 на количество груза на складах: Х₁₁+Х₁₂+Х₁₃+Х₁₄<=20, X₂₁+X₂₂+X₂₃+X₂₄<=30, X₃₁+X₃₂+X₃₃+X₃₄<=50,  X₄₁+X₄₂+X₄₃+X₄₄<=20,  X₅₁+X₅₂+X₅₃+X₅₄<=5. Целевая ячейка в этом случае будет равна: 1*Х₁₁+3*Х₁₂+4*Х₁₃+5*Х₁₄+5*X₂₁+2*X₂₂+10*X₂₃+3*X₂₄+3*X₃₁+2*X₃₂+1*X₃₃+4*X₃₄+6*X₄₁+4*X₄₂+2*X₄₃+6*X₄₄+0*X₅₁+0*X₅₂+0*X₅₃+0*X₅₄ (в соответствии с рисунком 2.4). По смыслу ясно, что количество перевозимого товара должно быть равно целому числу, также оно должно быть либо больше 0, либо равным ему. Исходя из всего этого, с помощью диалогового окна Поиск решения следует подобрать правильное решение задачи, предварительно введя вышесказанные ограничения (в соответствии с рисунком 2.5).

Результаты расчетов, графики и диаграммы

Рисунок 2.3 – Исходные данные

Рисунок 2.4 – Зависимости формул

Рисунок 2.5 – Параметры поиска решения

Рисунок 2.6 – Поиск решения

2.4 ИСПОЛЬЗОВАНИЕ ТАБЛИЦЫ ПОДСТАНОВКИ В MSEXCEL

Постановка задачи и ее математическая формулировка

Облигации приобретены (дата соглашения) 1.05.02. Предполагаемая дата погашения (дата вступления в силу) 1.03.03 по курсу 100. Купонный доход (ставка) – 8% с выплатой 4 раза в год, ставка помещения (доход) – 29%. Дата выплаты первого купона (последний доход) – 1.03.02, временной базис расчета – 1. Проанализировать изменение цены (курса) покупки облигаций в зависимости от ставки и дохода ценной бумаги с помощью Таблицы подстановки. Для решения задачи воспользоваться функцией ЦЕНАПОСЛНЕРЕГ().

Описание входной и выходной информации

Для решения этой задачи следует использовать функцию ЦЕНАПОСЛНЕРЕГ() (в соответствии с рисунком 2.7 – 2.8).

Для того, чтобы создать таблицу подстановки в ячейку листа А1 введем формулу, которая ссылается на две ячейки ввода и аналогична формуле в ячейке I2, которая применялась для расчета курса покупки облигаций. Ниже формулы введем значения первой переменной (годовая ставка дохода), правее формулы в строку введем значения второй переменной (купонный доход) (в соответствии с рисунком 2.9). Затем выделяем диапазон ячеек, содержащий формулу и оба набора данных подстановки, выбираем команду вкладка Данные – Анализ “что если”- Таблица данных. В поле Подставлять значения по столбцам ввести ссылку для значений подстановки в столбце (D2), в поле Подставлять значения по строкам, соответственно, ссылку для значений подстановки в строке (F2). Получившаяся Таблица подстановки представлена ниже (в соответствии с рисунком 2.10).

Результаты расчетов, графики и диаграммы

Рисунок 2.7 – Окно диалога Аргументы функции

Рисунок 2.8 – Расчет курса покупки облигаций

Рисунок 2.9 – Создание таблицы подстановки

Рисунок 2.10 – Заполненная таблица подстановки

2.5 АНАЛИЗ БИЗНЕС-СИТУАЦИЙ С ПОМОЩЬЮ ДИСПЕТЧЕРА СЦЕНАРИЕВ В MSEXCEL

Постановка задачи и ее математическая формулировка

Облигация приобретена (дата соглашения) 3.09.01 и будет погашена (дата вступления в силу) 11.09.03. Размер купонной ставки (ставка) – 8% с выплатой раз в полугодие. Ожидаемая годовая ставка помещения (доход) – 14%, номинал облигации (погашение) – 500. Временной базис расчета – 1. Определить курс (цену) покупки облигации с использованием функции ЦЕНА(). Построить сценарии для функции ЦЕНА(), выбрав в качестве изменяемых ячеек значения: ставка, погашение, частота выплат. Оформить результаты в виде отчета по сценариям.

Описание входной и выходной информации

Для решения этой задачи следует использовать функцию ЦЕНА() (в соответствии с рисунком 2.11 - 2.12).

Затем, с помощью диспетчера сценариев создаём итоговый отчет по сценариям, в котором отображаются результаты подстановки различных комбинаций входных параметров. Диспетчер сценариев вызывается с помощью команды вкладка Данные – Анализ “что если”– Диспетчер сценариев. В появившемся окне с помощью соответствующих кнопок добавляем новые сценарии, объединяем их и получаем итоговый отчет для существующих сценариев (в соответствии с рисунком 2.13).

Результаты расчетов, графики и диаграммы

Рисунок 2.11 – Окно диалога Аргументы функции

Рисунок 2.12 – Расчет курса покупки облигации

Рисунок 2.13 – Итоговый отчет по сценариям