Stamp

Почему финансовая модель в Excel подводит собственника каждый квартал

Представьте: в июле вы наняли двух менеджеров по продажам и сняли склад побольше, договор на три года. Решали как обычно. Открыли модель в Excel, нашли строку с деньгами на конец сентября и увидели там запас. Новые зарплаты и аренда прибавили к расходам 370 тыс. ₽ в месяц, и таблица их спокойно выдерживала.

К середине сентября на счёте оказалось заметно меньше, чем обещал файл. Менеджеры уже работают, аренда подписана. А в модели никто не может показать строку, где потерялась разница.

Формулы в такой таблице обычно в порядке, да и собирали её не дилетанты. Подводит она иначе. Модель замирает в день сборки, а бизнес за квартал сдвигается сразу в нескольких местах. Каждый сдвиг похож на шум. Вместе они дают разрыв. Повторяются эти сдвиги из квартала в квартал в тех же строках модели. Зная эти строки, промах можно заметить задолго до конца квартала, пока решения ещё можно отыграть.

План в Excel не совпал с фактом: где искать причину

Финансовая модель состоит из догадок о будущем, связанных формулами. Сколько продадим, почём купим, когда заплатят клиенты, что добавится в расходы. Догадки ставят один раз, когда собирают файл. Следующие месяцы файл живёт отдельно от бизнеса.

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

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

Значит, чинить надо не формулы, а сами догадки. Осталось понять, какие из них сдают первыми.

Какие допущения в финансовой модели устаревают первыми

Модель собственник собирает сам или вместе с бухгалтером. В ней почти всегда есть пять строк-догадок, и стареют они с разной скоростью.

Неоплаченный счёт под календарём: срок оплаты прошёл, деньги ещё не поступили.
Допущение Как его записывают в файле Что с ним происходит за квартал
Рост продаж ровный процент к прошлому месяцу продажи идут рывками: отпуска, праздники, ушёл крупный клиент
Цены закупки прайс поставщиков на день сборки поставщики меняют цены внутри квартала, файл держит старые
Срок оплаты клиентов срок из договора или поступление в месяце продажи часто платят позже договора, крупные клиенты особенно
Постоянные расходы список на день сборки новый найм, аренда и подписки попадают в файл с опозданием или никогда
Разовые платежи делят поровну на двенадцать месяцев уходят одной суммой в конкретный день

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

Случается, что допущения верны, а модель всё равно врёт. Тогда ошибка сидит в самом файле.

Почему Excel не предупреждает об ошибках в модели

Excel показывает ошибку, когда формула сломана совсем: деление на ноль, ссылка в пустоту. Остальные промахи выглядят как обычные числа и честно попадают в итог. Чаще всего встречаются такие:

Такие поломки находятся за вечер внимательной проверки. С допущениями сложнее. Они ошибаются понемногу, и каждый промах по отдельности кажется терпимым. Как такие промахи складываются, видно на одном квартале условной компании.

Как три маленьких промаха в прогнозе дают кассовый разрыв

Самарская компания готовит и возит обеды в офисы. В июне её собственник обновил модель на третий квартал. Заложил выручку 3,2 млн ₽ в месяц, продукты на 38 % от выручки и оплату через 15 дней, как в договорах. На конец сентября модель показала остаток 1,7 млн ₽. Под эту цифру в июле компания взяла в лизинг второй фургон.

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

Лето пошло не по файлу. Два офиса на время отпусков урезали заказы, и выручка просела на 5 %, до 3,04 млн ₽ в месяц. Овощи и мясо подорожали, продукты съедали уже 41 % выручки. С июля клиенты платили в среднем через 30 дней. Ни одна из этих новостей тревоги не вызвала: лето, сезон, все так живут.

Теперь в деньгах. Продажи упали на 160 тыс. ₽ в месяц. Продукты на несостоявшиеся обеды тоже не покупали, поэтому ушла только наценка: 160 тыс. × 62 % = 99 200 ₽ в месяц. Подорожание забрало ещё три пункта выручки: 3 % от 3,04 млн ₽, то есть 91 200 ₽. Вместе 190 400 ₽ в месяц, за квартал 571 200 ₽.

С оплатами расчёт другой. По модели к концу сентября клиенты должны были компании полмесяца выручки, 1,6 млн ₽. По факту долг вырос до целого месяца, 3,04 млн ₽. Разница 1 440 000 ₽ так и не дошла до счёта.

Итог квартала против модели:

571 200 + 1 440 000 = 2 011 200 ₽

Модель обещала плюс 1,7 млн ₽. Вышел минус 311 200 ₽, и конец сентября компания прожила на овердрафте.

Откуда взялась дыра, видно по суммам. Обе ошибки в наценке вместе стоили 571 тыс. ₽ при выручке 9,12 млн ₽ за квартал. Неприятно, но такое компания переживает, если цены и заказы вернутся. Если нет, эта потеря будет повторяться каждый месяц. Больше двух третей разрыва дала строка, которую собственник считал формальностью. Эти деньги не потеряны: клиенты их заплатят. Но пока они платят через месяц, 1,44 млн ₽ постоянно сидят у них. Выходит, собственник следил ровно за теми строками, где ошибка была меньше.

Сверка плана и факта: как часто и по каким порогам

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

Собственник сверяет банковскую выписку и счета с сохранённым планом на ноутбуке.

Я работаю с моделью так:

  1. Допущения храню на отдельном листе. У каждого своя ячейка, рядом дата и фамилия того, кто его поставил. В формулах цифр нет, они всегда ссылаются на эту ячейку.
  2. План не стираю. Факт закрытого месяца ставлю в соседнюю колонку, исправленный прогноз пишу в третью. Через два-три квартала видно, какая догадка раз за разом отклоняется от плана.
  3. Срок оплаты смотрю по выписке, а не по договору. По клиентам с основной выручкой считаю, сколько дней на деле проходит от отгрузки до поступления денег. Договор говорит, как должно быть, выписка показывает, как есть.
  4. Пересчитываю по порогам, а не раз в квартал. Как только выручка отходит от плана на 3 % и больше, доля закупки сдвигается на пункт, а средний срок оплаты растёт на пять дней, в тот же день пересчитываю остаток денег на конец квартала.
  5. Решения на годы вперёд принимаю только по свежей модели. Найм, аренду и лизинг считаю по прогнозу, в который уже встроен факт последнего закрытого месяца.

Самарской компании хватило бы порога по срокам оплаты. Задержку собственник увидел бы ещё в начале августа, когда до конца квартала оставалось два месяца.

Первыми такой порядок ломают цифры, вписанные прямо в формулы: на листе допущений их нет, и сверка проходит мимо.

Насколько можно доверять своей финансовой модели

Главная величина самарского квартала — 2 011 200 ₽. Это ширина ошибки, внутри которой собственник принимал решения. Фургон брали, глядя на плюс 1,7 млн ₽, а настоящий остаток к тому времени уже шёл к минусу.

У вашей модели своя ширина ошибки тоже есть, просто её никто не мерил. Пока она неизвестна, каждый найм и каждая аренда по файлу держатся на надежде, что она близка к нулю. Ровно на эту сумму вы можете ошибиться в следующем решении, принятом по таблице.

Я Николай Соловьёв, Директор по цифрам. Больше 15 лет строю финансовые системы внутри компаний и сам пишу инструменты, которые берут данные напрямую из 1С — собственник видит деньги вплоть до первичного документа.

Как считать реальный срок оплаты по выписке и как устроить лист допущений, чтобы модель сверялась с фактом каждый месяц, пишу в телеграм-канале «Директор по цифрам».

Частые вопросы

Можно ли заложить в модель запас и не сверять её каждый месяц?

Запас помогает, если он взят из вашей истории, а не на глаз. Найдите в старых квартальных файлах, на сколько остаток денег расходился с прогнозом, и возьмите самое большое расхождение за год. Если старые прогнозы затёрты фактом, мерить не по чему. Начните хранить план рядом с фактом сейчас, и к весне цифра появится.

Что делать, если одни клиенты платят сразу, а другие тянут по два месяца?

Средний срок в такой ситуации обманывает: два аккуратных плательщика и один опаздывающий дают вполне приличную среднюю. Разбейте клиентов на две-три группы по дисциплине оплаты и ведите срок для каждой отдельно. Клиента, который один даёт заметную долю выручки, держите отдельной строкой. Его задержка сдвигает весь прогноз сразу.

Прогноз три квартала подряд оказывается выше факта — это невезение?

Нет, это перекос в допущениях. Случайные промахи ложатся в обе стороны, три подряд в одну говорят о системной ошибке. Посмотрите, кто ставит цифру продаж. Если это руководитель продаж с бонусом за план, он рисует цель, а не прогноз. Плановую выручку для модели берите с поправкой на средний недобор прошлых кварталов.

Контакты

Свяжитесь со мной или заполните форму

Николай Соловьев
Социальные сети
НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /  НИКОЛАЙ СОЛОВЬЕВ /