Глава 3. Решение типовых задач моделирования средствами электронной таблицы excel

3.1. Типовые задачи моделирования «Что будет, если?», «Как сделать, чтобы?»

Моделирование часто бывает связано с необходимостью рассчитать результат какого-либо сложного выражения на основе изменяемых исходных данных или, наоборот, определить какими должны быть исходные значения для получения заданного результата. В первом случае речь идет о задаче «Что будет, если?», а во втором – с задачей «Как сделать, чтобы?».

Продемонстрируем технологию решения задачи «Что будет, если?» при начислении (сложных) процентов.

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

Таблица 3.1

В ячейке В5 находится формула для вычисления суммы выплат

=((1+В3)^B2)∙ B1.

При изменении размера вклада, срока вклада и процентной ставки соответственно будет изменяться и сумма выплат. Пользуясь этой моделью и многократно решая задачу «Что будет, если?», можно установить влияние исходных данных на конечный результат.

Задача несколько усложняется, если требуется определить значения исходных данных, исходя из заданной суммы выплат, что соответствует задаче «Как сделать, чтобы?» Вообще решение таких задач осуществляется опытным путем (подбором). Решение данных задач в Excel производится с помощью команды Сервис – Подбор параметра.

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

Выделите ячейку В5, в которой должен быть представлен желаемый результат, откройте диалоговое окно Подбор параметра командой Сервис – Подбор параметра. Адрес выделенной ячейки будет автоматически вставлен в поле «Установить в ячейке». Укажем в поле «Значение» целевое значение – 500 000. Поскольку в нашем примере изменяется только размер вклада, а срок вклада и процентная ставка остаются неизменными, поместим курсор ввода в поле «Изменяя ячейку» и выделим ячейку В1. Адрес ячейки будет автоматически вставлен в это поле. После нажатия кнопки «Ок» результат вычислений будет представлен в следующем диалоговом окне Состояние подбора параметра. После нажатия кнопки «Ок» в этом окне найденные значения будут вставлены в таблицу. Если же вы хотите повторить подбор параметра с использованием других значений, следует нажать кнопку «Отмена». Значение в этом случае не изменятся.

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