Скорее всего вы сталкивались с ситуацией, когда строишь в Excel расчетную модель, делаешь все логично и вроде по методике. И тут – раз – и выпадает сообщение о циклической ссылке. Основных причин может быть две. Первая причина – просто ошибка (например, не туда сослались). В таком случае ничего особо не посоветуешь. Нужно исправлять формулу. Вторая причина циклической ссылки — это когда методика расчетов не совпадает с логикой формул Excel. А это интересно. И как раз об этом статья.
Итак, обсудим, что делать, если логика вычислений требует добавлять в формулы взаимные ссылки. Спойлер: в Excel есть галочка, которая все исправит.
Рассмотрим ситуацию на простом примере с плановой калькуляцией доходов и расходов. Дано:
- Сумма расходов по статьям 25 150 ₽
- Плановая рентабельность продаж 20%.
Требуется:
- на основе расходов и рентабельности рассчитать прибыль;
- получившуюся прибыль прибавить к расходам и найти сумму выручки.
Да, формулировка «рассчитать прибыль на основе расходов и рентабельности» может показаться странной. Мы привыкли, что прибыль — это разница между доходами и расходами. На самом деле все ok.
Это при расчете фактической прибыли мы из выручки вычитаем расходы. А при построении плана (особенно, затратным методом) достаточно часто мы берем за основу именно расходы и оцениваем, какая должна быть выручка, чтобы обеспечить заданный уровень прибыли и рентабельности. Поэтому в нашем примере сначала считаем прибыль, затем прибавляем ее к расходам и получаем выручку.
Первая мысль, как найти прибыль показана на рисунке 1. Нужно расходы умножить на Рентабельность:
Прибыль = 25 150 ₽ • 20% = 5 030 ₽
Получившуюся прибыль прибавим к расходам, получим выручку, и задача вроде выполнена.
Выручка = 25 150 ₽ + 5 030 ₽ = 30 180 ₽
Вообще-то нет, не выполнена… Если проверить, какая получится Рентабельность продаж, увидим, что это не 20%.
Проверка рентабельности = 5 030 ₽ / 30 180 ₽ = 17%
Потому что нельзя просто так взять Рентабельность продаж и умножить на расходы. Так делают с наценкой. Чтобы расчет получился верным, нужно изменить формулу расчета прибыли. От 100% отнять Рентабельность 20%, взять обратную величину, так мы получим коэффициент Выручки относительно расходов. А чтобы получить коэффициент прибыли придется отнять еще 100%.
Доля прибыли относительно расходов = 1 / ( 100% — 20% ) — 100% = 25%
Умножаем на расходы:
Прибыль = 25 150 ₽ • 25% = 6 287 ₽
Получаем выручку:
Выручка = 25 150 ₽ + 6 287 ₽ = 31 437 ₽
Проверяем:
Проверка рентабельности = 6 287 ₽ / 31 437 ₽ = 20% – Результат правильный.
Но! Согласитесь, весь предыдущий абзац в целом непонятен и такие «упражнения» с коэффициентами на большой расчетной модели реализовать сложно, а иногда невозможно. Или они получатся такими, что из-за сложности потом их не поменяешь. Что делать?
Все просто — напишите формулу, как есть, как этого требует методика расчета. То есть формулу Прибыли запишите, как произведение Выручки на Рентабельность. А формулу Выручки — как сумму Прибыли и Расходов. Вроде все логично и правильно, только у вас присутствуют взаимные ссылки на ячейки. И после таких действий появится сообщение о циклической ссылке, см. рисунок 2.
Не проблема! Переходим Файл → Параметры → Формулы → Включить итеративные вычисления.
После этого добрый Excel подберет правильные суммы и сам подставит необходимые значения, чтобы всё сходилось и считалось правильно.
Плановая калькуляция доходов и расходов – это самый простой для понимания пример, где можно использовать итеративные вычисления. Их можно также подключать для оценки запасов и переходящих остатков незавершенного производства. В финансовом моделировании.
Однако, используя эту галочку, помните, что итерации могут нагружать компьютер и отнимать ресурсы. И еще помните, что циклическая ссылка часто появляется из-за того, что просто не туда сослался. В таком случае нужно исправлять именно ошибки, а не включать итерации в формулах ?