Анализ «что если» и подбор параметра

Анализ «что если» отвечает на вопрос «а что будет, если...» и решает обратную задачу: какой вход даёт нужный результат.

Анализ «что если» (what-if analysis) — набор инструментов, которые прогоняют модель при разных входных значениях, чтобы увидеть, как меняется итог, или подобрать вход под заданную цель.

Зачем это нужно

Электронная таблица — это живая модель: меняете цену, и пересчитывается выручка, прибыль, налог. Анализ «что если» позволяет не подбирать числа вслепую, а задавать вопросы вроде: «Что с прибылью при цене 800 и при 1000?», «Какую цену поставить, чтобы выручка стала ровно 500 000?». Это ежедневный инструмент экономиста и популярный сюжет экзаменационных задач на проценты и пропорции.

В таблицах есть три уровня таких инструментов: сценарии (сравнить несколько готовых наборов входов), таблицы данных (прогнать один-два параметра по диапазону) и подбор параметра (обратный расчёт под цель).

Сценарии: сравниваем варианты

Сценарий — это именованный набор значений для входных ячеек. Вы создаёте «Пессимистичный», «Базовый», «Оптимистичный» сценарии, в каждом своя цена и спрос, а программа показывает, как меняется прибыль. Это удобно, когда входов несколько и хочется переключаться между готовыми комбинациями одним кликом.

Модель прибыли простая. Пусть $p$ — цена, $q$ — количество проданного, $c$ — затраты на единицу. Тогда прибыль:

$$ \text{Прибыль} = (p - c)\cdot q $$

При $c = 300$ сценарии могут выглядеть так:

Проверим базовый: $(800 - 300)\cdot 500 = 500 \cdot 500 = 250\,000$. Сходится.

Таблица данных: один параметр — много значений

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

Подбор параметра: обратный расчёт

Самое мощное — подбор параметра. Обычно вы задаёте вход и смотрите выход. Подбор параметра идёт наоборот: вы говорите «хочу, чтобы вот эта ячейка стала равна 500 000, меняя вот эту входную ячейку», и программа сама находит нужный вход.

Подбор параметра — обратный расчёт: задаёшь желаемый результат в целевой ячейке и ячейку-вход, а инструмент подбирает значение входа, при котором цель достигается.

Пример: нужная выручка → цена

Выручка — это $\text{Выручка} = p \cdot q$. Пусть мы продаём $q = 625$ штук, а нужна выручка ровно 500 000 рублей. Какой должна быть цена $p$? Аналитически:

$$ p = \frac{\text{Выручка}}{q} = \frac{500\,000}{625} = 800 $$

Подбор параметра придёт к тому же результату численно: он будет пробовать цены и сужать диапазон, пока выручка не станет 500 000. Когда зависимость нелинейная (например, спрос сам падает с ростом цены), вручную обратную формулу не вывести — а подбор параметра справится. Покажем его логику методом половинного деления на Python:

q = 625                 # продаём столько штук
cel = 500_000           # нужная выручка

def vyruchka(price):
    return price * q     # модель: выручка от цены

lo, hi = 0.0, 5000.0    # ищем цену в этом диапазоне
for _ in range(60):     # 60 уточнений — заведомо хватит
    mid = (lo + hi) / 2
    if vyruchka(mid) < cel:
        lo = mid        # выручка мала — цену вверх
    else:
        hi = mid        # выручка велика — цену вниз

print("Подобранная цена:", round((lo + hi) / 2, 2))
print("Проверка выручки:", round(vyruchka((lo + hi) / 2)))

Вывод:

Подобранная цена: 800.0
Проверка выручки: 500000

Программа нашла цену 800 — ту же, что и формула. Именно так работает встроенный «Подбор параметра»: подставляет вход, смотрит на отклонение от цели и корректирует, пока не попадёт.

Как это работает

Подбор параметра — это численное решение уравнения $f(x) = \text{цель}$, где $f$ — ваша цепочка формул. Инструмент не знает алгебры вашей модели; он работает как чёрный ящик: меняет вход на маленький шаг, смотрит, в какую сторону и насколько сдвинулся результат, и итеративно приближается к цели (метод, близкий к делению пополам и методу Ньютона). Поэтому он одинаково хорошо берёт и линейные, и хитрые нелинейные модели — лишь бы зависимость была непрерывной.

Частые ошибки

• Целевая ячейка без формулы. Подбор меняет вход и ждёт, что цель пересчитается. Если в целевой ячейке записано число, а не формула, зависящая от входа, инструмент ничего не подберёт.
• Вход и выход не связаны. Меняемая ячейка должна реально влиять на целевую через формулы. Если связи нет, подбор выдаст «решение не найдено».
• У задачи нет решения. Нельзя получить выручку 500 000 при $q = 100$ и максимально разумной цене — подбор честно сообщит, что цель недостижима. Это не ошибка программы, а свойство модели.
• Несколько решений. У нелинейного уравнения может быть два корня; подбор найдёт лишь один, зависящий от стартового значения. Проверяйте, что найденный вход осмыслен.
• Сценарии не обновили. После изменения формул модели старые сценарии хранят прежние выходы — пересоздайте их.

Итоги

• Анализ «что если» прогоняет модель при разных входах и решает обратную задачу.
• Сценарии сравнивают несколько готовых наборов значений; таблица данных гоняет один параметр по диапазону.
• Подбор параметра находит вход, при котором целевая ячейка примет нужное значение.
• Целевая ячейка обязана быть формулой, зависящей от меняемой ячейки.
• Под капотом подбор — численное решение уравнения итерациями, как деление пополам в примере.