10. Сценарный анализ: переключатель «пессимист — база — оптимист»

Будущее неизвестно, поэтому финансовую модель считают в нескольких сценариях. Удобный приём — таблица сценариев и ячейка-переключатель: в таблице для каждого допущения записаны три значения, а в ячейке G1 — номер активного сценария (1, 2 или 3). Вся модель берёт допущения через переключатель.

• =ВЫБОР($G$1;B2;C2;D2) — вернёт B2 при 1, C2 при 2, D2 при 3. Просто и наглядно, когда вариантов мало;
• =ИНДЕКС(B2:D2;$G$1) — то же через позицию в диапазоне. Удобно, когда сценариев много;
• =ИНДЕКС(B2:D2;ПОИСКПОЗ("База";B1:D1;0)) — сценарий выбирается по названию, а не по номеру.

Переключили G1 с 2 на 1 — и вся модель мгновенно показала пессимистичный результат. Никаких копий листа «Модель (пессимист)».

В настольном Excel есть ещё два встроенных инструмента (на вкладке Данные → Анализ «что если»):

• Диспетчер сценариев сохраняет наборы значений для изменяемых ячеек и строит сводный отчёт по сценариям;
• Таблица данных пересчитывает формулу для целого ряда значений одного или двух параметров (например, прибыль при 80…250 чашках в день).

В этом тренажёре этих двух инструментов нет, поэтому здесь мы строим переключатель формулами — этот способ работает в любой версии Excel и в Google Таблицах, и его легче проверять. Ставка налога 20% — пример, сверяйте с действующим законодательством.