Финансовая модель в Excel: допущения, прибыль и деньги кофейни по месяцам

Бюджет, собранный из чисел, вбитых прямо в формулы, ломается при первом вопросе «а если аренда вырастет?». Справка Microsoft советует давать ячейкам имена: формулы с понятными именами вместо адресов легче читать^[3]. Когда каждое допущение лежит в своей ячейке, его меняют один раз, а пересчитывается вся модель. Финансовая модель в Excel отвечает на вопрос «что будет, если», не требуя переписывать формулы.

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

Ключевые факты: финансовая модель в Excel

• ЧПС считает чистую приведённую стоимость вложения по ставке дисконтирования: выплаты — отрицательные значения, поступления — положительные^[1].
• ЧПС принимает от 1 до 254 значений, а ВСД делает до 20 попыток с точностью 0,00001 процента^[1][2].
• Вложение в начале первого периода в значения ЧПС не включают, а прибавляют к результату^[1].
• Справка Microsoft советует давать ячейкам понятные имена: формулы с ними легче читать^[3].
• P&L в отличие от денежного потока не учитывает вложение: на примере 886 500 прибыли против 286 500 денег^[7].

Что такое финансовая модель в Excel: допущения, расчёт и отчёт

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

Допущения удобно назвать: имя вроде Средний_чек понятнее, чем $B$1. Справка ставит условия: в имени нет пробелов, разделители — подчёркивание или точка, имя не может совпадать с адресом ячейки и бывает длиной до 255 символов^[3]. Список имён открывает Name Manager на вкладке «Формулы»^[3].

Главное правило модели — ни одного числа внутри формулы. Формула =C10*0,3 прячет допущение о себестоимости, а =C10*$B$2 показывает, откуда оно берётся. Через полгода никто не вспомнит, что значит 0,3 в середине листа, а модель с допущениями в отдельных ячейках читается без автора.

Разобравшись со слоями, переходим к примеру — на нём видно, как модель собирается по месяцам.

Пошаговый пример: модель кофейни на шесть месяцев

Финансовая модель в этом примере считает выручку, прибыль и деньги кофейни по месяцам, начиная с вложения 600 000. Допущения стоят в B1:B7, месяцы — в столбцах C:H.

1. В строке 9 введите число чеков в день по месяцам: 40, 50, 60, 70, 80, 90.
2. В C10 посчитайте выручку =C9*30*$B$1 и протяните до H10.
3. В C11 — себестоимость =C10*$B$2, в C12 — постоянные затраты =$B$3+$B$4+$B$5.
4. В C13 — прибыль месяца =C10-C11-C12: −36 000 в первом месяце и 331 500 в шестом^[7].
5. В C14 — деньги нарастающим итогом =-$B$6+C13, в D14 — =C14+D13, затем протяните до H14.

Деньги уходят в плюс только в шестом месяце: −45 000 в пятом и 286 500 в шестом^[7]. Знак доллара в ссылках на допущения держит их при протягивании, а относительные ссылки на месяц сдвигаются. Окупаемость модели наступает в шестом месяце, когда нарастающий итог впервые становится положительным. Ставка в B7 понадобится для оценки проекта. Соберите эту модель в уроке тренажёра: урок меняет допущения и проверяет, что формулы ссылаются на них, а не на числа.

Однако прибыль и деньги в этой модели расходятся, и это отличие стоит разобрать отдельно.

Прибыль или денежный поток: чем отличаются P&L и cash flow

P&L модели отличается от денежного потока тем, что показывает заработанное за период, а cash flow — сколько денег реально пришло и ушло. В примере сумма прибыли за шесть месяцев — 886 500, а деньги на конец периода — 286 500^[7].

Разница ровно 600 000 — это вложение: оно уменьшило деньги, но не стало расходом периода^[7]. В реальной модели расхождений больше: отсрочка оплаты покупателями, предоплата поставщикам, налоги, которые платят позже. Каждое такое расхождение — отдельная строка денежного потока.

Поэтому отчёт модели обычно состоит из двух листов. По P&L видно, прибыльна ли кофейня, по денежному потоку — хватит ли денег дожить до прибыли. Кассовый разрыв в примере — самая глубокая точка нарастающего итога, −636 000 в первом месяце^[7]: именно столько денег нужно иметь на старте.

Именно поэтому денежный поток, а не прибыль, идёт в функции оценки проекта.

Проверка модели на практике: контрольные суммы, ЧПС и ВСД

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

ЧПС принимает от 1 до 254 значений, и они должны идти через равные промежутки в конце каждого периода^[1]. Поэтому ставка в модели месячная, как и потоки: годовую ставку с месячными потоками смешивать нельзя. ВСД возвращает ставку, при которой ЧПС равна нулю; для месячных потоков примера с вложением 600 000 она равна 8,26% в месяц^[7]. Функции нужен хотя бы один отрицательный и один положительный поток, а без ответа за 20 попыток она возвращает #ЧИСЛО!^[2].

Чувствительность к двум допущениям сразу — например, к чеку и числу посетителей — показывает таблица с двумя переменными: справка Microsoft допускает в ней не больше двух^[4]. В Google Таблицах ошибки деления в модели можно заменить понятным текстом через ЕСЛИОШИБКА с вторым аргументом^[6].

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

Когда финансовая модель в Excel не подойдёт

Финансовая модель в Excel не подойдёт, если данные, связи или число допущений вышли за пределы одного понятного файла. Ниже — признаки, по которым это видно заранее, и что сделать в каждом случае, чтобы не переделывать расчёт с нуля.

• Если допущений, которые нужно менять одновременно, три и больше, одна таблица с переменными их не охватит: в ней не больше двух переменных^[4].
• Когда потоки идут через неравные промежутки, ЧПС и ВСД дадут искажённый ответ: обе функции рассчитаны на равные периоды^[1].
• При потоках без единого отрицательного значения ВСД не посчитается и вернёт #ЧИСЛО!^[2].
• Если числа вписаны прямо в формулы, менять допущения долго и опасно — сначала вынесите их в отдельные ячейки.
• Если несколько человек правят файл одновременно, файл с перекрёстными ссылками легко сломать — договоритесь, кто меняет допущения, и держите копию последней проверенной версии.

Учёт по каждой продаже таблица допущений тоже не заменит: план строят по средним, а учёт — по документам.

Для Mac справка тоже подходит: ЧПС описана для Excel 2024 для Mac, Excel 2021 для Mac и Microsoft 365 для Mac^[1]. Порог продаж, при котором модель выходит в ноль, разобран на странице о расчёте порога продаж, а сами функции платежей и доходности — на странице о ЧПС, ВСД и платежах по кредиту.