ВУЗ:
Составители:
33
Г
ЛАВА 3. РЕШЕНИЕ ТИПОВЫХ ЗАДАЧ МОДЕЛИРОВАНИЯ СРЕД-
СТВАМИ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL
3.1. Типовые задачи моделирования «Что будет, если?», «Как
сделать, чтобы?»
Моделирование часто бывает связано с необходимостью рас-
считать результат какого-либо сложного выражения на основе из-
меняемых исходных данных или, наоборот, определить какими
должны быть исходные значения для получения заданного резуль-
тата. В первом случае речь идет о задаче «Что будет, если?», а во
втором – с задачей «Как сделать, чтобы?».
Продемонстрируем технологию решения задачи «Что будет,
если?» при начислении (сложных) процентов.
Пусть известен размер вклада, который будет помещен в банк
под определенный процент на определенный срок. Требуется опре-
делить сумму выплат, которую даст указанный вклад с учетом на-
численных процентов в конце периода (см. табл.3.1)
Таблица 3.1
AB
1
Размер вклада 300 000
2
Срок 5
3
Процентная ставка 0,05
4
5
Сумма выплат 382 884
В ячейке В5 находится формула для вычисления суммы выплат
=((1+В3)^B2)·B1.
При изменении размера вклада, срока вклада и процентной
ставки соответственно будет изменяться и сумма выплат. Пользу-
ясь этой моделью и многократно решая задачу «Что будет, если?»,
можно установить влияние исходных данных на конечный резуль-
тат.
Задача несколько усложняется, если требуется определить зна-
чения исходных данных, исходя из заданной суммы выплат, что со-
ответствует задаче «Как сделать, чтобы?» Вообще решение таких
задач осуществляется опытным путем (подбором). Решение данных
задач в Excel производится с помощью команды Сервис – Подбор
параметра.
В приведенном примере сумма выплат составила почти 400
000. Предположим, что через 5 лет необходимо получить ровно 500
000. В этом случае можно увеличить исходный размер вклада или
34
попытаться поискать другой банк, предлагающий более выгодные
проценты. Попробуем сначала увеличить размер вклада.
Выделите ячейку В5, в которой должен быть представлен же-
лаемый результат, откройте диалоговое окно Подбор параметра
командой Сервис – Подбор параметра. Адрес выделенной ячейки
будет автоматически вставлен в поле «Установить в ячейке». Ука-
жем в поле «Значение» целевое значение – 500 000. Поскольку в
нашем примере изменяется только размер вклада, а срок вклада и
процентная ставка остаются неизменными, поместим курсор ввода
в поле «Изменяя ячейку» и выделим ячейку В1. Адрес ячейки будет
автоматически вставлен в это поле. После нажатия кнопки «Ок»
результат вычислений будет представлен в следующем диалоговом
окне Состояние подбора параметра. После нажатия кнопки «Ок»
в этом окне найденные значения будут вставлены в таблицу. Если
же вы хотите повторить подбор параметра с использованием дру-
гих значений, следует нажать кнопку «Отмена». Значение в этом
случае не изменятся.
Аналогично могут быть подобраны значения других исходных
параметров срока вклада и процентной ставки.
3.2. Анализ чувствительности
Анализ чувствительности является развитием задачи типа «Что
будет, если?», дает возможность путем подстановки в формулу раз-
личных значений переменных, представить зависимость результа-
тов вычислений по формуле от значений входящих в нее перемен-
ных. Этот режим реализуется в Excel при помощи команды Дан-
ные – Таблица подстановки. Создавать таблицы можно на основе
одной или двух переменных (одномерный или двумерный анализ).
Значения переменных задаются в виде списков. При создании таб-
лицы данных с одной переменной список исходных значений зада-
ется либо в виде строки, либо в виде столбца. При использовании
двух переменных значений одной из них располагаются в столбце,
значения другой – в строке, а результаты вычислений – на пересе-
чении соответствующей строки и столбца. Для случая с одной пе-
ременной в формуле должна быть указана ссылка на одну ячейку, в
которой при создании таблицы будут подставляться значения из
списка, для таблицы с двумя переменными – ссылки на две ячейки.
Страницы
- « первая
- ‹ предыдущая
- …
- 15
- 16
- 17
- 18
- 19
- …
- следующая ›
- последняя »
