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

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

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

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

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

Таблица 3.1

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

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

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

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

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

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

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

3.2. Анализ чувствительности

Анализ чувствительности является развитием задачи типа «Что будет, если?», дает возможность путем подстановки в формулу различных значений переменных, представить зависимость результатов вычислений по формуле от значений входящих в нее переменных. Этот режим реализуется в Excel при помощи команды Данные – Таблица подстановки. Создавать таблицы можно на основе одной или двух переменных (одномерный или двумерный анализ). Значения переменных задаются в виде списков. При создании таблицы данных с одной переменной список исходных значений задается либо в виде строки, либо в виде столбца. При использовании двух переменных значений одной из них располагаются в столбце, значения другой – в строке, а результаты вычислений – на пересечении соответствующей строки и столбца. Для случая с одной переменной в формуле должна быть указана ссылка на одну ячейку, в которой при создании таблицы будут подставляться значения из списка, для таблицы с двумя переменными – ссылки на две ячейки.