Тема 6. Анализ «что–если»

Анализ «что–если» обеспечивает проверку различных значений для формул на листе. Существует три инструмента для проведения такого анализа.

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

2. Диспетчер сценариев. Позволяет создать и сохранить различные группы значений (сценарии) и переключаться между ними.

3. Подбор параметров. Выполняет поиск значения, которое надо ввести в формулу для получения известного результата.

Таблицы данных

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

Таблицы для одной переменной. Если вы хотите вычислить месячные выплаты по ссуде 200 000 р. на 30 лет для нескольких процентных ставок, это можно сделать с помощью таблицы данных для одной переменной.

1. Создайте следующую таблицу: в ячейке С1 – 200 000, процентные ставки – в ячейках В3:В8 (рис. 2.14).

2. Введите формулу в ячейку С2 = ПЛТ(A2/12;30·12;C1).

Здесь: A2/12 – месячная процентная ставка; 30 – срок ссуды в годах, 30·12 – срок ссуды в месяцах; C1 – ссуда.

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

С1 – это пустая вспомогательная ячейка, куда впоследствии будет подставлена ссуда, т.е. та переменная, которая стоит в строке. В данном примере ссуда не является переменной, а имеет фиксированное значение, в следующем примере ссуда является переменной.

3. В результате в ячейке C2 появится сумма –555,56. Знак минус возникает из-за того, что это ваши ежемесячные выплаты по процентам, вы их отдаете.

4. Выделите диапазон таблицы данных В2:С8.

5. В меню Данные выберите команду Таблица данных. Выберите – Подставлять значения по строкам в, щелкните на ячейку A2. Результат –
на рис. 2.15.

Таблица с несколькими формулами. Если вы хотите взять ссуду 180 000 р. и сравнить с выплатами для ссуды в 200 000 р., то необходимо использовать несколько формул.

1. Введите в D1 – 180 000 р.

2. В ячейку D2 введите формулу = ПЛТ (A2/12;30·12;D1).

3. Выделите диапазон В2:D8.

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

Таблицы данных для двух переменных. Требуется узнать месячные выплаты для разных процентных ставок при сроках 5, 10, 15 и 20 лет.

1. Создайте следующую таблицу (рис. 2.17).

2. Величину ссуды введите в ячейку вне области таблицы, например, I2 – 1000000 р.

3. В ячейку В2 введите = ПЛТ (A2/12;B1·12;I2) (получится значение #ДЕЛ/0!).

4. Выделите диапазон В2:F8.

Рис. 2.17

5. В меню Данные выберите команду Таблица данных. Выберите – Подставлять значения по столбцам в, щелкните на ячейку В1, Подставлять значения по строкам в, щелкните на ячейку A2.

6. Изменив сумму ссуды в ячейке I2, вы можете посмотреть месячные выплаты по другой ссуде.

Диспетчер сценариев

Таблица данных – хорошее средство в относительно простых ситуациях, когда используются только одна или две переменные, но реальные задачи обычно имеют значительно больше неизвестных величин. При моделировании более сложных задач, в которых бывает до
32 переменных, можно обратиться к команде Сценарии.

Сценарии – это именованная комбинация значений, заданных для одной или нескольких изменяемых ячеек в модели «что–если». Изменяемые ячейки – это ячейки, содержащие значения, которые вы ходите использовать в качестве переменной.

Сценарием в Microsoft Excel называется набор значений подстановки, используемый для прогнозирования поведения модели. Существует возможность создания и сохранения в листе различных сценариев и переключения на любой из этих сценариев для просмотра результатов. Так, если требуется сформировать бюджет, но годовой доход точно не известен, то для дохода определяются различные значения, а затем для каждого сценария выполняется анализ «что–если».

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