Иформационные технологии анализа данных в MS Office

Задача «Оптимальное размещение денежных средств на депозите»

Условие задачи:

Осуществляется размещение свободных денежных средств на депозитных счетах. Все свободные денежные средства могут использоваться для вкладов. Срок размещения денежных средств – не более 6 месяцев; длительность действия депозитов 1, 3 и 6 месяцев.

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

Требуется:

Найти оптимальную схему размещения свободных средств на депозитных счетах.

Информационная технология:

Вставить новый лист – команда меню ПравкаЛист.
Переименовать лист в лист Поиск решения 2 – команда меню ФорматЛистПереименовать.
Разместить исходные данные для решения задачи согласно рис. 27:

В ячейку С1 ввести норматив страховой суммы.
В ячейку F1 ввести начальную сумму свободных денежных средств.
В ячейки B4–B6 ввести процентные ставки по депозитам.
В ячейки B8–G8 ввести номера учетных периодов.

Создать именованные блоки для ячеек:

В1 – блок с именем Доход.
D1 – блок с именем Страховая_сумма.
В4–В6 – блок с именем Проценты.
B12:G12 – блок с именем Депозит1.
B14:Е14 – блок с именем Депозит3.
B16 – блок с именем Депозит6.
B18:G18 – блок с именем Расходы_и_доходы_за_период.
B19:H19 – блок с именем Сумма_на_конец_периода.

	A	B	C	D	E	F		G	H
1	Доход		Страховая сумма		Начальная сумма
2
3	Срок, мес.	Доход, %	Начало периода			Конец периода
4	1	8%	1, 2, 3, 4, 5, 6			1, 2, 3, 4, 5, 6
5	3	12%	1,2,3, 4			3, 4, 5, 6
6	6	18%	1			6
7
8	Номер периода	1	2	3	4	5	6		ИТОГО
9	Сумма на начало периода
10	Погашение депозитов в конце периода
11	Проценты к получению в конце периода
12	Выдача 1-мес. депозита
13	Выдача 3-мес. депозита
14	Выдача 6-мес. депозита
15	Погашение 1 мес. депозита
16	Погашение 3-мес. депозита
17	Погашение 6 мес. депозита
18	Расходы и доходы за период
19	Сумма на конец периода

Рисунок 27

Ввести формулы для 1–го месяца:

В ячейке B9: =F1 (ссылка на начальную сумму для размещения на депозитах).
В ячейки B15–B17 записать 0 (сумма погашения депозитов за предыдущие периоды).
В ячейку B10 записать формулу = СУММ(B15:B17) – всего сумма к погашению в конце 1–го месяца.
Скопировать формулу ячейки B10 в ячейки C10:H10.
В ячейку B11 записать 0формулу: =СУММПРОИЗВ(Проценты;B15:B17) – всего проценты к получению в конце 1–го месяца.
Скопировать формулу ячейки B11 в ячейки C11:H11.
В ячейку B19 записать формулу: =B9+B10+B11-(B12+B13+В14)+B18-Страховая_сумма – свободная сумма в конце 1–го месяца для размещения на депозите в следующем периоде.

 В формуле вместо ссылки В14 при наличии блока Депозит6 будет произведена автоматическая замена на имя блока.

Ввести формулы для 2-го месяца:

В ячейку С9: =В19 (ссылка на свободную сумму для размещения на депозитах на конец 1–го месяца).
Скопировать формулу ячейки С9 в ячейки D9:H9.
В ячейку С15: = B12 (ссылка на сумму 1–месячного депозита предшествующего периода).
Скопировать формулу ячейки С15 в ячейки D15:H15.
В ячейку С19: = C9+C10+C11-(C12+C13)+C18-Страховая_сумма (ссылка на сумму 1–месячного депозита предшествующего периода).
Скопировать формулу ячейки С19 в ячейки D19:H19.

 Формулы для 3–го месяца вводить не нужно, так как они уже существуют.

Ввести формулы для 4-го месяца:

В ячейку Е16: = B13 (ссылка на сумму 3–месячного депозита предшествующего периода).
Скопировать формулу ячейки Е16 в ячейки F16:H16.

 Формулы для 5–го и 6-го месяцев вводить не нужно, так как они уже существуют.

Ввести формулу в ячейку H17: = В14

 В формуле вместо ссылки на В14 при наличии блока Депозит6 будет произведена автоматическая замена на имя блока.

Ввести формулу в ячейку В1: =H19-F1 (сумма дохода).

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

Выделить ячейку F1, снять защиту ячейки – команда ФорматЯчейки, вкладка Защита.
Выделить блок ячеек Страховая_сумма, снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.
Выделить блок ячеек Проценты, снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.
Выделить блок ячеек Расходы_и_доходы_за_период, снять защиту ячеек – команда ФорматЯчейки, вкладка Защита.
Установить защиту на лист – команда меню СервисЗащитаЗащитить лист.

 Установка защиты обеспечивает сохранение всех введенных формул (блоки ПОТРЕБНОСТЬ, ЦЕЛЬ).

Команда меню СервисПоиск решения, создать модель задачи вида – рис. 28.

Рисунок 28

Настроить параметры решения задачи (см. предыдущую задачу).
Решить задачу, сформировать все отчеты.
Построить сценарий для изменяемых ячеек:

В4:В6 – уровень доходности по депозитам;

F1 – начальная сумма;

B18:G18 – сумма дополнительных расходов/доходов.

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

Содержание