Скорее всего вы сталкивались с ситуацией, когда строишь в Excel расчетную модель, делаешь все логично и вроде по методике. И тут – раз – и выпадает сообщение о циклической ссылке. Основных причин может быть две. Первая причина – просто ошибка, например, если не туда сослались. В таком случае ничего особо не посоветуешь. Нужно исправлять формулу. Вторая причина циклической ссылки — это когда методика расчетов не совпадает с логикой формул Excel. А вот это уже интересно. И как раз об этом статья.
Итак, обсудим, что делать, если логика вычислений требует добавлять в формулы взаимные ссылки. Спойлер: в Excel есть галочка, которая все исправит.
Пример. Рассмотрим ситуацию на простом примере с плановой калькуляцией доходов и расходов.
Дано:
- Сумма расходов по статьям 25 150.
- Плановая рентабельность продаж 20%.
Требуется:
- на основе расходов и рентабельности рассчитать прибыль;
- получившуюся прибыль прибавить к расходам и найти сумму выручки.
Да, формулировка «рассчитать прибыль на основе расходов и рентабельности» может показаться странной. Мы привыкли, что прибыль — это разница между доходами и расходами. На самом деле все верно. Это при расчете фактической прибыли мы из выручки вычитаем расходы. А при построении плана (особенно затратным методом) часто за основу берутся именно расходы и оценивается, какая должна быть выручка, чтобы обеспечить заданный уровень прибыли и рентабельности. Поэтому в примере сначала считаем прибыль, затем прибавляем её к расходам и получаем выручку.
Первая мысль, как найти прибыль, показана на рисунке 1. Нужно расходы умножить на рентабельность:
Прибыль = 25 150 х 20% = 5 030
Получившуюся прибыль прибавим к расходам, получим выручку, и задача вроде выполнена.
Выручка = 25 150 + 5 030 = 30 180
Рисунок 1. Расходы на Рентабельность продаж умножать нельзя.
Вообще-то нет, не выполнена… Если проверить, какая получится рентабельность продаж, увидим, что это не 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.
Рисунок 2. Пишите формулу, как требует методология, даже если появится сообщение о циклической ссылке.
Не проблема! Включите итерации в вычислениях в меню Excel: Файл → Параметры → Формулы → Включить итеративные вычисления.
Рисунок 3. Включите итеративные вычисления.
После этого добрый Excel подберет правильные суммы и сам подставит необходимые значения, чтобы всё сходилось и считалось правильно.
Рисунок 4. Результат итеративных вычислений.
Плановая калькуляция доходов и расходов – это самый простой для понимания пример, где можно использовать итеративные вычисления. Их можно также подключать для оценки запасов и переходящих остатков незавершенного производства. Или в финансовом моделировании.
Однако, используя эту галочку, помните, что итерации могут нагружать компьютер и отнимать ресурсы. И еще помните, что циклическая ссылка часто появляется из-за того, что просто не туда сослался. В таком случае нужно исправлять именно ошибки, а не включать итерации в формулах.
