Учет ндс в финансовой модели

Опубликовано: 07.05.2024

За свою жизнь я преподавал финансовый менеджмент в Петрозаводском госуниверситете, строил систему бюджетирования и модели реорганизации на основе финансовых моделей энергокомпаний в РАО «ЕЭС», анализировал множество финансовых моделей, выносившихся на Инвестиционный комитет «РОСНАНО» и построил систему моделирования самого «РОСНАНО» на основе постоянного фин.мониторинга проинвестированных компаний, проанализировал более сотни финмоделей как «бизнес-ангел» и делал финансовые модели для десятка компаний из моего портфеля, привлекавших дополнительные инвестиции. В этой статье мне хотелось бы поделиться опытом составления финансовых моделей – в основном в инвестиционном контексте, но имеющих и вполне «операционное» применение. Надеюсь, эти рекомендации позволят сэкономить время как предпринимателям, составляющим финансовые модели не так регулярно, так и инвесторам, которые потом регулярно их рассматривают.

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

Тем не менее, как уже отмечалось многими инвесторами, формирование финмодели имеет смысл:

в этом процессе вы вынуждены будете «объективизировать» допущения, которые вы неявно закладываете в свою финмодель;
сможете проанализировать допущения, а главное – их сочетание, на предмет здравого смысла;
провести анализ чувствительности модели и выявить неочевидные риски и резервы роста;
протестировать различные сценарии – как стрессовые, так и связанные с изменением организации бизнеса вами по собственному решению.

Вообще говоря, полная финансовая модель компании описывается тремя «подмоделями» / бюджетами: бюджетом движения денежных средств (БДДС; «кэш-флоу», от англ. cash flow), бюджетом по начислениям (прибыли и убытки, profit andloss (P&L)) и балансом активов и обязательств (balance sheet).

Наиболее часто делают бюджет движения денежных средств, зачастую именно его и называют финансовой моделью в узком понимании. Это связано с тем, что БДДС наиболее понятен, его можно реально увидеть в движении по счетам и кассе компании. Кэш-флоу в основном используется для прогнозирования ликвидности, чтобы понимать, достаточно ли в каждый прогнозный период у компании средств, чтобы оплатить необходимые расходы – зарплаты, аренду, налоги, платежи различным поставщикам. Второй важной задачей, решаемой БДДС, является формирование итогового денежного потока по компании, который используется для расчета основных показателей эффективности инвестиций, таких как IRR и NPV (см. конец данной статьи).

В отличие от бюджета движения денежных средств, бюджет прибылей и убытков (еще его называют бюджетом по начислениям) формируется не на дату получения или осуществления какого-либо платежа, а на дату появления либо исчезновения обязательств – ваших или перед вами. Например, при продаже своей продукции вы можете попросить аванс, или например взять деньги за подписку на ваш сервис на год вперед. Соответственно, в БДДС у вас появится приход (денег), а вот в P&L – нет, доход (выручка) возникнет только в момент, когда вы отдадите товар покупателю (в случае подписки, как правило, вы будете признавать в качестве выручки 1/12 годового платежа ежемесячно).

Основные расхождения между кэш-флоу и начислениями связаны как раз с разнесением потоков денежных средств и прав на них в разные периоды. Например, когда вы получаете аванс на выполнение работы или доступ к вашему сервису, или наоборот ждете 90 дней оплаты за поставленный вами товар. «Автоматически» данная ситуация возникает с зарплатой (которая начисляется, например, за январь, а выплачивается в феврале), налогами (которые рассчитываются и выплачиваются после истечения периода, за который они начисляются), амортизацией (покупка основных средств оплачивается одномоментно, а вот в расходы принимается только частично – пропорционально ожидаемому сроку использования данного актива). Таким образом, расхождение показателей кэш-флоу и начислениях, особенно у быстрорастущей фирмы, продающей SaaS по модели годовой подписки, может быть довольно существенным[1]. Задача бюджета прибылей и убытков – оценить эффективность работы фирмы, а не просто генерирование денежных средств: ведь компания может набрать авансов (продать подписок), премию продажникам обещать заплатить по итогам года, налоги тоже время платить придет только в следующем году, и может выглядеть, что у компании генерится (в моменте) хороший денежный поток, хотя по факту компания будет убыточной. Второй задачей бюджета прибылей и убытков является планирование налога на прибыль.

Разницы между CF и P&L, а также их итоговые строки (остаток денежных средств и прибыль/убыток) «уходят» в баланс, увеличивая или снижая дебиторскую и кредиторскую задолженность, а также размер кредитов, привлекаемых для покрытия кассовых разрывов. Баланс показывает, насколько устойчива компания краткосрочном и долгосрочном периоде, насколько и как быстро она может погасить свои обязательства и что после этого останется акционерам.

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

Тут все зависит от того, какую задачу вы ставите. Если спрогнозировать ликвидность – достаточно и года, если показать инвестору финансовую привлекательность вашего проекта – на срок его жизни / срок функционирования создаваемого в ходе его реализации актива. Если проект не предполагает четкого срока его реализации, то финансовую модель строят на срок до выхода проекта на стабильные показатели выручки (когда он заканчивает «опережающий» рост и начинает расти на уровне экономики в целом).

С точки зрения «единицы» прогнозного периода целесообразно, чтобы он был соизмерим с производственным циклом. Если срок от вливания средств в рекламу до закрытия сделок – неделя, и каждую неделю компания увеличивает обороты в полтора-два раза, то целесообразно прогнозный период разбить на недели. В большинстве случаев, впрочем, практичнее использовать в качестве периода моделирования месяц. Увеличение его до, например, квартала, в большинстве случаев не снизит трудоемкость и сложность модели, а увеличит ее, поскольку нужно будет обеспечить корректный перенос между кварталами переходящих частей зарплат и других регулярных платежей, особенно если они носят сезонный характер. Если период модели очень большой, то может быть целесообразно после двух-трех лет помесячной разбивки дальнейшую модель, когда показатели должны будут меняться с меньшей скоростью, сделать в годовом разрезе.

Спор о том, как правильнее делать финмодель – в постоянных ценах или с учетом инфляции – является одним из незавершенных в корпоративных финансах. В практическом смысле учет инфляции имеет смысл в случае большого кредитного плеча или больших рассрочек (опять же, модель годовой подписки) и существенной инфляции. За исключением этих специфических случаев считаю целесообразным делать модель в постоянных ценах. Во-первых, при росте затрат вследствие инфляции вы всегда сможете сопоставимо повысить цены (и это же сделают ваши конкуренты, поскольку инфляция действует на всех); а во-вторых, у малых компаний явно нет ресурса самостоятельно прогнозировать макроэкономические показатели, а брать их у того или иного эксперта не имеет практической пользы – насколько вы можете быть в них уверены?

В случае, если компания все-таки закладывает в модель существенную кредитную нагрузку, то можно в модели вместо номинальной ставки по кредиту использовать реальную (за вычетом текущей инфляции).

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

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

Модель денежного потока принято разбивать на три раздела: операционный (притоки и расходы по основной деятельности, показывает, какой свободный денежный поток генерирует основной бизнес), инвестиционный (приобретение основных средств и других бизнесов и их продажа); финансовый (привлечение капитала и заемных средств, уплата процентов и погашение займов). Сумма приходов и платежей по каждому разделу дает соответственно операционный, инвестиционный и финансовый денежный поток. Операционный поток, как правило, должен быть положительным, чтобы обеспечивать инвестиционные потребности бизнеса и погашение долгов.

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

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

Итогом (суммой всех трех денежных потоков) является (свободный) денежный поток. Свободный денежный поток нарастающим итогом равняется остатку денежных средств на конец соответствующего периода.

Перед тем, как начать непосредственно формировать вашу финмодель, имеет смысл пройтись по вашей бизнес-модели – из ее «подвала», доходов и расходов, строится скелет финансовой модели. Убедившись, что бизнес-модель актуальна, либо актуализировав ее, статьи основных доходов и расходов нужно разнести по соответствующим разделам финансовой модели. Далее с максимально возможной детальностью вносятся прочие, как правило постоянные, статьи расходов, такие как зарплаты высшего менеджмента, аренда офиса, страховые взносы (начисления на зарплату) и т.п.

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

Допущения имеет смысл вынести на отдельный лист расчетного файла, чтобы было удобно менять их и анализировать последствия, для чего на этот же лист целесообразно продублировать результирующий денежный поток. С целью «читаемости» модели и снижения вероятности ошибок желательно все используемые данные явно выписывать в модели, а не «прятать» в формулах. Например, если вы хотите рассчитать количество производимых в месяц изделий, и предполагаете, что рабочий день составит 8 часов, в месяце будет 22 рабочих дня, в час один сборщик изготавливает 6 изделий, а сборщиков у вас трое, то лучше не вписывать числа в одну ячейку (=8*22*6*3), а выписать каждый показатель в отдельную строку и сделать формулу путем перемножения соответствующих ячеек. Кроме большей понятности и наглядности, это позволит гораздо легче менять модель в случае, если вы наймете четвертого сборщика или через год повысите производительность сборки до 8 изделий в час за счет эффекта обучения.

Следующим шагом будет прогнозирование (физического) объема продаж – сколько в штуках, граммах, подписках вы будете продавать в каждый из периодов. Это может быть как «ручной» прогноз, так и прирост к базовому периоду на некий коэффициент из допущений. При этом коэффициент этот не может быть одинаковым во все периоды: рост со временем будет затухать, пока не выйдет на уровень среднего роста по экономике. И в этот период объем продаж будет соответствовать вашей целевой доле рынка, что является одной из проверочных точек.

Умножив объем продаж на целевые цены (которые целесообразно заложить с некоторым постепенным снижением базовому периоду, по мере того как вы привлекаете все более широкий круг клиентов) и применив коэффициенты авансирования / отсрочек оплаты, вы получите поступления денежных средств по операционной деятельности.

Следующим шагом будет планирование расходов. В первую очередь нас интересуют переменные расходы, то есть те, которые связаны с объемом продаж – например, стоимость комплектующих для нашего изделия. Опять же, целесообразно эти расходы ставить не одной строкой, а разбить на основные составляющие с соответствующими нормами расхода (часть из этих составляющих может приобретаться за другие валюты, допущения по курсам которых мы ранее сделали). Величина переменных расходов на единицу, вероятно, также будет изменяться – с ростом объемов закупок поставщики вполне могут сделать скидку, в модель можно заложить соответствующие коэффициенты. А вот переменные расходы, связанные с маркетингом, скорее всего на единицу продаж будут возрастать, поскольку для обеспечения роста вам придется использовать все более дорогие / менее эффективные каналы. Не забываем, что поставщикам мы также платим авансом, а может быть, можем получить отсрочку платежа – соответствующие коэффициенты должны быть применены к расчету платежей по переменным затратам.

Разница между выручкой и переменными затратами (ценой и переменными затратами на единицу продукции – то, что сейчас принято называть «юнит-экономикой») образуют операционную маржу. Если она отрицательна, у бизнеса нет перспектив, если положительна – то при определенном объеме продаж она перекроет постоянные расходы и фирма выйдет в прибыль.

Финансовые модели иногда используют для анализа ассортимента – расчетов, продажи какого товара выгоднее нарастить при каких условиях (например, применении скидок), а какие вывести из оборота. В этом случае для определения доходности товара к объему переменных затрат нужно добавить прямые постоянные затраты, но только те, которые связаны исключительно с данным товаром и от которых фирма может отказаться при снятии товара (услуги) с производства. Использование различных методов распределения постоянных (накладных) затрат в данном случае неприемлемо, поскольку исказит вклад каждого товара в покрытие.

Итак, после расчета переменных затрат остается заполнить постоянные расходы (не зависящие от объема продаж), которые на самом деле являются условно-постоянными. Как правило, постоянные расходы после достижения компанией определенного размера начинают возрастать – компании требуется больший офис для размещения очередного сотрудника, дополнительный склад и т.п. Хорошим примером здесь являются сети – скажем, «дарк китчен». Освоив какой-то район, компания открывает новую точку в другом – при этом возникают новые расходы на аренду и обустройство соответствующего помещения, но они не зависят напрямую от объемов продаж этой новой точки. При этом часть постоянных расходов, например, зарплата СЕО и других топ-менеджеров, могут и не изменяться.

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

Сумма операционного и инвестиционного денежного потока определяет дефицит денежных средств по периодам – если остаток денежных средств на конец какого-либо периода по модели получается отрицательным, то необходимо поменять какие-либо допущения в модели (при том, что они все еще должны оставаться разумными) либо привлечь стороннее финансирование и отразить его в разделе финансового денежного потока. При этом не стоит «закрывать» любой кассовый разрыв краткосрочным кредитом – начинающая малая фирма его просто не получит. Если возникают разрывы на один-два периода, их необходимо компенсировать какими-то изменениями в операционной деятельности либо закладывать в привлекаемое стартовое финансирование. В любом случае, имеет смысл вести явный расчет размера (остатка) привлеченных заемных средств (в том числе для расчета уплачиваемых процентов), например, сразу после итогового денежного потока и остатка денежных средств.

По данной ссылке можно скачать образец реальной финансовой модели, которую мы делали пару лет назад с одной проинвестированной мною ИТ-компанией. Компания в основном работает в В2В, поэтому маркетинговые расходы в основном приходятся на зарплату, только один из сегментов - розничный, по которому есть переменные издержки. Обратите внимание, что в первом сегменте компания планирует достичь почти 80% рынка - как правило, это не реалистично, но в данном случае это довольно узкий сегмент, в котором "победитель получает все". Жизнь показала, что прогноз был вполне реалистичен - в настоящее время компания полностью контролирует рынок Москвы, на 80% - Санкт-Петербурга и скоро запустится в остальных миллионниках.

Итак, модель собрана. Теперь начинается самое интересное – «игра» с различными допущениями, чтобы определить различные варианты действий и возможные риски.

Одним из самых распространенных является анализ чувствительности – когда каждое допущение изменяется на 1 или 10% и определяется результирующее изменение денежного потока / IRR / NPV. Чем больше изменение результирующего показателя, тем чувствительнее финансовая модель к данному фактору. Например, если при увеличении цены на 10% итоговый денежный поток вырос на 20%, а при снижении переменных издержек на 10% денежный поток вырос на 5%, то чувствительности модели к цене составляет 2, а к переменным издержкам – 0,5. Соответственно факторам, к которым наиболее чувствительна модель, надо уделять максимальное внимание при проработке бизнес-плана и последующем ведении бизнеса.

Поработав с анализом чувствительности, имеет смысл составить несколько сценариев: основной, пессимистический и реалистический объем продаж, скачок курсов валют, высокий «отвал» потребителей или резкое удорожание каналов привлечь, высокая доля неплатежей потребителей, которым дали отсрочку, задержка ввода в эксплуатацию объекта на полгода – год и другие, которые могут быть релевантны для вашего бизнеса.

В итоге, на основе получившейся финансовой модели (по свободному денежному потоку), добавив ликвидационную или постпрогнозную стоимость, вы сможете рассчитать IRR (внутреннюю норму/ставку доходности, функция «ВСД» в электронных таблицах), и, определив ставку дисконтирования для вашего бизнеса, - дисконтированные денежные потоки и NPV (чистую приведенную стоимость). NPV с точки зрения доходного метода оценки представляет собой стоимость компании. Но это уже тема для другого, более теоретического руководства).

«Клерк» Рубрика Обзоры для бухгалтера

Как учитывать инфляцию и НДС, делать модель безопасной и устойчивой, подбирать финансирование и работать с циклическими ссылками и аккуратно отображать коэффициенты.

1. Учет инфляции при моделировании

Итак, допустим у нас есть финансовый план некой птицефабрики. Мы возьмем его фрагмент, полный план, конечно, состоит из множества статей доходов и затрат. В плане продаж у нас есть яйцо, оно стоит 2,5 тыс. руб. за тысячу штук и с третьего квартала проекта мы продаем его по 20 млн. в квартал. Вот такая упрощенная картина.

2,5 тыс. – это сегодняшняя цена. Уже даже в начале продаж она будет другой, и это надо учесть. Для этого в модели должен быть блок, отвечающий за прогнозную инфляцию. Он создается либо на отдельном листе в книге Excel, либо просто отдельным блоком.

Во-первых, нам нужно значение инфляции. Обратите внимание, что оно годовое, потому что мало кто оперирует квартальной инфляцией и это было бы непонятно.

Чтобы наша модель была универсальной, мы добавляем строку с длительностью периода в месяцах – тогда в последующем будет легко переделать эту таблицу для годовых или месячных расчетов.

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

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

Теперь этот блок должен быть задействован везде, где мы работаем с ценами.

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

В нем мы просто накапливаем инфляцию за все периоды.

И теперь мы умножаем не предыдущую, а начальную цену, на этот индекс.

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

2. Безопасность и устойчивость модели

Есть три полезных инструмента, которые делают модель устойчивее.

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

Это поможет и легче ориентироваться в модели, и делать меньше ошибок.

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

Если надо, вы можете добавить здесь же подсказки относительно вводимых данных. И изменить текст сообщения, которое будет выдаваться при попытке ввести недопустимое значение.

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

Особый случай – это когда ячейка должна содержать только одно из нескольких возможных значений. Например, в ячейке B11 у нас может быть только флаг: 1 или 0. Тогда мы можем определить проверку данных по списку.

Теперь ячейка не просто редактируется. Она превратилась в выпадающее меню и вы всегда видите какие варианты здесь допустимы.

Ну и наконец последнее. Не всегда достаточно чтобы при работе с моделью было видно что можно менять, а что нет. Иногда надо запретить редактирование части ячеек на уровне интерфейса, чтобы гарантировать стабильность модели. Делается это так.

Сначала мы выделяем все ячейки, которые будут оставаться редактируемыми. Вызываем диалог формата ячейки. И в последней закладке отключаем флаг Заблокировать ячейку. Теперь когда весь лист будет защищен от изменений, эти ячейки останутся незащищенными. Дальше мы выбираем защиту листа. Если вы хотите, чтобы никто кроме вас не мог отключить эту защиту, то установите пароль. Часть действий с защищенным листом можно разрешить включая и выключая соответствующие флаги.

И теперь когда защита установлена, никакие данные на листе поменять нельзя. Но выбранные нами ячейки по-прежнему остались редактируемыми.

3. НДС в платежах

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

Получается итоговая сумма чистых поступлений за отчетный период. И всю эту суммы мы должны передать государству в качестве налогового платежа. Это появится у нас в кэш-фло в строке Налоги. То есть компания как бы пропускает НДС через себя и отдает излишки государству. Модель должна это отражать.

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

Более частый случай это постепенный зачет переплаченного НДС. Но такой расчет потребует формирования баланса, это более долгая история и она не уложится в пять минут.

4. Учет и отображение шага планирования

Во-первых, нам надо просто пронумеровать периоды. Это может пригодиться и в формулах и просто для заголовков таблиц.

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

Пусть здесь у нас будет план по кварталам.

Теперь можно определить дату начала каждого следующего периода, это мы делаем с помощью функции ДАТАМЕС(). Она вычисляет сдвиг даты на указанное число месяцев, и таким образом у нас нет проблем в связи с тем, что в месяцах разное количество дней.

На всякий случай в этом блоке лучше иметь сразу и дату окончания каждого периода. Это та же функция ДАТАМЕС(), но минус один день.

Ну и наконец название периода. Я сделаю его немного упрощенным, оно будет рассчитано только на планирование по кварталам. Чтобы извлечь из даты номер месяца, мы используем функцию МЕСЯЦ(). Номер квартала это целое от месяца минус 1, деленного на 3 плюс 1. Теперь надо добавить слово квартал и номер года. Для того, чтобы объединять несколько фрагментов текста в одну строку, в Excel используется символ амперсанд. Добавляем обозначение квартала. И номер года. Названия периодов готовы.

Теперь мы добавим названия периодов в заголовки наших таблиц. Лучше сразу поставить знак доллара перед номером строки в формуле, тогда можно будет копировать этот заголовок в другие таблицы и ссылка не будет сбиваться.

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

Можно сделать эти две строки менее бросающимися в глаза. Теперь надо зафиксировать их. Для этого ставим курсор в следующую строку и выбираем в меню Окно Закрепить области. Всё, теперь вопрос отображения периодов решен и у нас есть все данные, которые могут понадобиться в расчетах и формировании отчетов.

5. Модель на двух языках

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

Итак, изначально модель готовится на одном языке.

Затем мы создаем новый лист. Пусть он так и называется – Язык. В нем нам нужны три колонки. Начнем со второй и третьей – там у нас хранятся данные для двух языков. Соответственно, русский и английский. В русский мы копируем данные из основного листа, английский переводим. Ну у меня для экономии времени заготовлен фрагмент перевода.

Теперь идея состоит в том, что все текстовые ячейки на расчетных листах будут ссылаться на первую колонку листа Язык, а в этой первой колонке всегда будет версия на текущем языке. Для этого мы делаем первую ячейку флагом языка. Ноль – базовый для нас русский язык, 1 – английский.

А во всех остальных ячейках совершенно одинаковая формула. Нули будут появляться там, где нет данных, эти строки потом лучше вообще удалить, так как это мусор.

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

6. NPV, IRR – профессиональный расчет

Итак. Во-первых, функцией NPV мы вообще не будем пользоваться. Гораздо лучше иметь все расчеты в явном виде перед глазами, тем более, что они не сложные. Это выглядит следующим образом.

Остается рассчитать IRR. Простой вариант – функция ВСД(), которая применяется к недисконтированному денежному потоку. Он оставляет нам кучу проблем. Что если у нас денежные потоки не по концу периода, а по началу? Что если как здесь, планирование сделано по кварталам? Что наконец если первый период не полной длины, то есть планирование например по годам, но проект начинается с апреля? Все эти проблемы оптом мы решаем, если переходим от функции ВСД() к другой, менее известной функции Excel, которая по-английски называется XIRR(), а в русском языке имеет длинное название ЧИСТВНДОХ().

Мы получили те же NPV и IRR, но наши расчеты стали прозрачнее, а модель более универсальной.

7. Подбор финансирования и циклические ссылки

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

Например, я вижу, что мне не хватает примерно миллиард 587 млн. но если ввести эту сумму в качестве кредита, то денег опять будет не хватать – появились процентные платежи.

Для того, чтобы учесть такую циклическую связь, Excel поддерживает итеративные вычисления. Включаем…

=МАКС(-'Фин. отчеты'!F43+'Фин. отчеты'!F36;0)

=МИН(МАКС('Фин. отчеты'!I43-'Фин. отчеты'!I37;0);I244)

Теперь если например мы меняем что-то в инвестициях. Пусть нам надо еще 500 млн. График кредита меняется автоматически.

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

8. Таблицы и графики чувствительности

Что такое график чувствительности в модели? Это график, который показывает как меняется какой-нибудь результирующий параметр при изменении тех или иных исходных данных. Например довольно типичный вариант графика чувствительности это зависимость NPV от, например, колебаний в цене продукции, или например от ставки дисконтирования. Вот вокруг этих двух зависимости мы сейчас и построим пример.

Первое что нам нужно это таблица в которой будут все интересующие нас значения NPV для каждого из значений параметров.

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

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

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

Все заготовки сделаны, и теперь последний шаг – мы используем специальную функцию Excel, которая называется Таблица подстановки.

Таблица заполнилась и теперь значение в каждой ячейке – это значение NPV для цены продаж, указанной в строке и ставки, указанной в колонке. Эта таблица всегда будет обновляться при любом изменении в модели, всегда будет актуальна и по ней можно построить себе графики чувствительности.

Последнее, что надо сказать об этом инструменте – он прекрасен и очень удобен, но надо иметь в виду, что вот например сейчас если я изменю что-то в затратах проекта, то модель пересчитается не один раз, а столько раз, сколько ячеек в таблице подстановки, то есть 7х7 = 49. С точки зрения вычислений, модель сейчас утяжелилась в 50 раз. Тут этого еще не заметно, но если исходная модель будет покрупнее или компьютер послабее, то вы обнаружите, что после каждой введенной цифры Excel подвисает на несколько секунд, и это конечно никуда не годится.

Для того, чтобы устранить эту проблему, в Excel есть специальная опция…

Теперь все расчеты выполняются автоматически, но таблицы чувствительности обновляются только когда вы нажмете F9. Вот теперь действительно все, модель осталась быстрой и в ней есть анализ чувствительности.

9. Сценарное планирование

Разработка такой модели начинается с создания обычной модели для одного сценария. А дальше мы добавляем в нее механизм управления сценариями. Для этого у нас будет отдельный лист Сценарии.

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

Второй блок это собственно наши сценарии. Здесь будут те данные которые между сценариями различаются. Допустим у нас есть три сценария которые отличаются друг от друга ценой и графиком продаж. начнём с базового сценария. Сделаем поле для его названия и перенесем сюда данные из основной модели.

Теперь размножаем это на три сценария.

И пусть это будут сценарии под названием Рост и Кризис, и у них будут немного разные данные.

А блок Текущий сценарий заполняется у нас как выборка данных того сценария, номер которого написан сейчас вверху. Для этого мы используем функцию ВЫБОР().

Теперь в этой таблице всегда текущий сценарий. Осталось перенести данные в основную модель. Желательно при этом как-то обозначить, что это теперь не редактируемые данные, а информация из сценариев.

Всё, одна финансовая модель содержит данные по нескольким сценариям, и мы можем свободно переключаться между ними.

10. Аккуратное отображение коэффициентов

Поэтому обычно нельзя просто указать формулу как деление одной ячейки на другую. Она будет немного сложнее.

У нас есть два варианта. Первый – мы можем воспользоваться функцией ЕСЛИОШИБКА(). Например, рентабельность продаж здесь в первые два года будет давать деление на ноль, потому что нет продаж. Добавим ЕСЛИОШИБКА(). Теперь функция подставляет аккуратный прочерк вместо сообщения об ошибке.

Но не обязательно проблема это математический сбой. Математически показатель может быть и вычисляется, но не имеет смысла. Так здесь происходит с коэффициентом покрытия долговых платежей. Вроде все цифры в порядке, но какой смысл считать этот коэффициент, когда денежные потоки просто отрицательные?

В данном случае, нам нужна функция ЕСЛИ(). Мы ставим проверку того, что денежные потоки положительные и только в этом случае рассчитываем коэффициент, а для отрицательных просто показываем прочерк. В результате модель выглядит аккуратнее и легче читается.

Любой бизнес-план нуждается в финансовых расчетах. А наиболее удобный инструмент для этого – табличный процессор Excel. Популярность программы объясняется простотой использования и многофункциональностью. Рассмотрим возможности редактора, которые будут полезны при составлении бизнес-модели.

Основы построение финансовой модели в Excel

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

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

Инвестиционный план

строительство и/или покупка зданий;
покупка оборудования;
расходы будущих периодов;
инвестиции в ЧОК (чистый оборотный капитал).

Исходные данные для расчета затрат на покупку или строительство зданий:

График оплаты без НДС = Затраты с НДС / (1 + ставка НДС). Формула в Excel для ячейки С6: =C4/(1+$B$5).
Незавершенные вложения – сумма вложений в активы без учета НДС до периода их постановки на баланс. Формула в Excel для ячейки С8: =ЕСЛИ(C1 $B$7;ЕСЛИ(C12>0;ЕСЛИ(D9*$B$10/4>C12;C12;D9*$B$10/4);0);0).
Балансовая стоимость актива – разница между начальной стоимостью и амортизационными отчислениями за весь период существования актива. Формула в Excel для ячейки D12: =D9-СУММ($C11:D11).
Формула для расчета первоначальной стоимости актива - =ЕСЛИ(C1>=$B$7;$G$6;0).
НДС к зачету (в период постановки актива на баланс) – общая величина налога. Формула для ячейки С13: =ЕСЛИ(C1>$B$7;$G4-$G6;0).
Формула для расчета кредиторской задолженности: =ЕСЛИ(C1>=$B$7;$G6-СУММ($C6:C6);0).

Затраты на приобретение оборудования и элементы расходов будущих периодов в инвестиционном плане составляются аналогично. Особенности затрат будущих периодов:

оприходуются на баланс в составе текущих активов;
не облагаются налогом на имущество (в отличие от оборудования);
амортизируются быстрее, в течение 1-2 лет.

Прогнозирование доходов

Чтобы построить план продаж, нужно определить объем в натуральном выражении (для каждого вида продукции) и цену реализации (каждого вида продукции). Выручка определяется по каждому виду товара (работ и услуг) как произведение объема и цены.

В Excel составляются таблицы для каждого периода планирования и для каждого вида продукции с планируемым объемом выпуска (в натуральных единицах).

Из цены реализации нужно вычленять сумму налога на добавленную стоимость. Эти деньги не входят в состав выручки – они перечисляются в бюджет.

Цена без НДС = цена с НДС / (1 + налоговая ставка).
Величина НДС = (цена с НДС * налоговая ставка) / (1 + налоговая ставка).

Расчетная таблица может выглядеть следующим образом:

Формула для расчета цен без НДС: =C7/(1+$B$8).
Расчет налога на добавленную стоимость: =C7-C9.
Выручка с налогами: =C4*C7.

План текущих расходов

сырье и материалы;
оплата труда;
начисления на зарплату;
амортизация;
прочие расходы.

При учете затрат на материалы выделяем налог добавленной стоимости. Это необходимо для учета подлежащих возврату сумм (задолженность перед бюджетом уменьшится).

Затраты на сырье и материалы увеличиваются прямо пропорционально объему выпуска. Если, к примеру, на пошив одной сорочки требуется полтора метра ткани, то на две единицы продукции – 3 метра и т.д. Расход считается по формуле:

Количество материалов = удельный вес * объем производства.

Пример таблицы учета текущих затрат на сырье и материалы:

Формула для расчета налога на добавленную стоимость – в строке формул.

Формула вычисления цены без НДС: =C5/(1+$B$6).

Расчет затрат с НДС: =C4*C5.

Налог на ДС: =C4*C6.

Затраты без НДС: =C4*C7.

аренда,
реклама,
оплата связи;
ремонт и т.д.

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

Финансовая модель предприятия в Excel

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

План доходов и расходов финансовой модели:

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

План движения денежных средств:

Предполагается, что предприятие не будет привлекать заемные средства. Поэтому раздел «Финансовая деятельность» отсутствует.

Как использовать НДС в своих целях, какой отчет запросить у бухгалтера и на что там смотреть, как не вляпаться в кассовый разрыв из-за НДС ― ответы на эти и другие вопросы найдете в нашем обзоре.

Что такое НДС

НДС ― это аббревиатура налога на добавленную стоимость. Когда говорят об НДС, вспоминают приписку «в том числе НДС» в магазинных чеках. Так и есть, налог на добавленную стоимость чаще всего встречается при продаже товаров и услуг.

Кроме этого, компания должна платить его в таких случаях:

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

Кто и кому платит НДС — и в чем его суть

НДС государству платит покупатель, а не предприниматель. Это транзитный налог: государство берет деньги с покупателей, когда те приобретают что-то у компаний. Но покупатели платят этот налог не напрямую: в бюджет его передает компания-продавец. Кошелек предпринимателя ― промежуточная остановка для НДС, конечный пункт ― это государственный бюджет.

У Марины кондитерская. Она продает пирожные, их финальная цена для покупателя — 120 рублей: 100 из них идут Марине, а 20 — в бюджет. Покупатель платит государству и сам того не замечает, ведь Марина исправно перечисляет за него эти 20%.

Итак, бизнес НДС не платит, а просто перечисляет. Это должны делать индивидуальные предприниматели и организации на общей системе налогообложения, а также те, кто ввозят товары из-за границы ― импортеры ― вне зависимости от системы налогообложения.

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

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

Эти и другие условия есть в 161 статье налогового кодекса.

Ставки НДС в 2020 году

Все описания ставок можно посмотреть в 164 статье налогового кодекса. Всего их три.

20% ― основная ставка налога . Обычно ее и платят.
10% ― пониженная , такую ставку применяют на определенные продовольственные, медицинские, детские товары и книжную продукцию.
0% ― нулевая ставка. НДС как бы есть, но его как бы нет. Это относится к экспортерам, международным перевозчикам товаров, авиакомпаниям и другим счастливчикам.

Марина продавала пирожные, на них НДС был 20%. Еще на прилавке были ароматные булочки, на них НДС был снижен до 10%. Кажется, что в булочной все одинаковое, но для сладкоежек и общероссийского классификатора ― нет. Применение сниженной ставки НДС зависит от кодов, которые присваиваются продукции: на одни ставка будет 20%, а на другие ― 10%, потому что они указаны в специальном постановлении правительства.

Сроки уплаты НДС

Чтобы заплатить налог, нужно заполнить декларацию. Для этого есть отдельная форма, здесь можно посмотреть, как ее нужно заполнить. Декларацию отправляют в налоговую так же, как и остальную отчетность ― электронно, лично или по почте ценным письмом с уведомлением.

Декларацию отправляют раз в квартал, до 25 числа месяца, следующего за отчетным периодом. При этом платить сразу всю сумму не нужно. Ее делят на равные три части и перечисляют в бюджет тоже частями до 25 числа каждого месяца следующего квартала. Можно оплатить досрочно.

За первый квартал Марина отправляет декларацию до 25 апреля. Ей нужно заплатить за весь первый квартал 99 тысяч рублей. После отправки декларации она перечисляет 33 тысячи рублей, в следующем месяце до 25 мая в бюджет от Марины уходит еще 33 тысячи, а оставшиеся деньги за первый квартал она перечисляет до 25 июня.

Виды НДС: входящий и исходящий

Марина открывает кондитерскую и арендует помещение. Она купила оборудование: кофемашину, печку для разморозки слоек, холодильник для тортов. Заказала услуги и работы ― дизайн и косметический ремонт кондитерской. Обратилась в агентство за рекламой. Выбрала у поставщиков товары ― слойки, булочки, пирожные, торты, кофе, чай, сахар, картонные стаканчики, салфетки. Цена всего этого ― и холодильника, и салфеток, и услуг дизайнера ― включает налог на добавленную стоимость. За все это Марина заплатила 500 тысяч рублей, из них 100 тысяч ― входящий НДС. Государство возвратит его Марине, и получится, что она потратила не 500, а 400 тысяч.

Исходящий НДС. Компания продает товары, услуги, имущественные права. К стоимости своих товаров и услуг она прибавляет налог на добавленную стоимость. Клиенты покупают и тут же оплачивают НДС — это и есть для компании исходящий НДС, и она должна перечислить его в бюджет.

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

Когда выгодно работать с НДС

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

Марина решила закупать торты. Она нашла двух поставщиков ― у них одинаковые торты и цены на них. Кого же ей выбрать? Поставщик «Юрец-лакомец» работает с НДС, набор тортов у него стоит 1 200 рублей, из них 200 рублей ― это НДС. У поставщика «Захар-сахар» по ценам и тортам все то же самое, но он не работает с НДС.

Набор вкусных тортиков

Работает ли с НДС

1 000 рублей цена + 200 рублей (20%) НДС

Что получит Марина кроме тортиков

Работая с компанией «Юрец-лакомец», Марина имеет отсроченную «скидку» в виде входящего НДС: сейчас она купит на 1 200, а позже ей вернется силуановский кэшбек — 200 рублей из бюджета.

Как отражать НДС в управленческой отчетности

Давайте посмотрим, как НДС отражается в трех основных управленческих отчетах: отчете о движении денег (ДДС), отчете о прибылях и убытках (ОПиУ), балансе.

ДДС. В отчете о движении средств не запариваясь пишем сколько денег потратили и сколько получили. Пришла тысяча рублей — записываем эту тысячу, неважно, есть в ней НДС или нет.

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

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

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

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

3 правила безопасной работы с НДС

Как работать с НДС так, чтобы не было мучительно больно перечислять его государству?

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

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

Просрочки по платежам налогов ― это штрафы и головная боль. Чтобы избежать этого, внесите обязательные отчисления в налоговую в платежный календарь.

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

Удачи вам! НДС дело непростое, но разобраться можно.

12 мая 2021

Финансовое моделирование в Excel

Кандидат экономических наук, доцент. Автор двух монографий, шести учебных пособий и нескольких десятков статей по вопросам бухгалтерского учета, финансового менеджмента и анализа. Лауреат премии губернатора в сфере науки, техники и инновационной деятельности за 2012 г. За плечами — опыт работы главбухом бюджетного учреждения и преподавателем государственного вуза.

Финансовая модель бизнеса: что это

Финансовая модель предприятия – это плановые показатели его деятельности по:

доходам;
расходам;
прибыли;
денежным потокам;
активам;
обязательствам.

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

Обычно финансовая модель строится в Excel или Google-таблицах. Часть исходных данных вносится вручную (план по объему продаж, месячный фонд оплаты труда, нормы потребления материалов на единицу изделия и т.д.). Зависимые от них показатели задаются с помощью формул. Они обеспечивают моментальный пересчет итоговых значений выручки, операционной прибыли, дебиторки, денежных притоков и т.д.

Итоговый результат финансового моделирования – три формы отчетности:

баланс;
отчет о финансовых результатах (ОФР);
отчет о движении денежных средств (ОДДС).

Финансовое моделирование проекта: что надо знать

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

Вот пошаговый план реализации.

Рисунок 1. Построение финансовой модели: рекомендуемые этапы

Опробуем данный подход на практике. Рассмотрим пример для организации, которая занимается розничной торговлей. Такой выбор не случайный. Любое производство сложнее торговли, так как в нем предполагаются:

материальные затраты;
амортизация оборудования;
возможно, незавершенный производственный процесс на конец месяца, поэтому необходимость разделения затрат и расходов;
разнородные складские запасы в виде материалов и продукции.

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

Финансовая модель (ФМ) в Excel: считаем доходы

Отправная точка на данном этапе – объем продаж. Можно пойти простым путем и внести только денежное значение выручки. Позже детализировать его как произведение цены и количества в разрезе номенклатурных позиций.

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

Основная сложность этапа: откуда взять данные по плану продаж? Все зависит от вашей цели:

если она пока в том, чтобы построить и «обкатать» работоспособность ФМ, то берите любые данные. Например, за прошлый год или произвольные;
если вам одновременно нужны и модель, и качественные результаты расчета по ней, то займитесь планированием отдельно.

Мы ратуем за то, чтобы на данном этапе не уходить в детали. Но разумно уже сейчас подумать: чем будете усложнять и этим приближаться к реальности?

Первый. Покажите отдельно НДС, который «сидит» в цене реализации. Он приходит в поступлениях от покупателей, но это не доход вашей организации. Добавив его в таблицу, вы посчитаете выручку-нетто или чистый доход. Заодно определите будущие платежи в бюджет по данному налогу*.

Примечание: в бюджет платится разница между НДС, который начислен к уплате, и тем, который предъявляется к возмещению по собственным закупкам.

Второй. Введите аналитику по продажам. Самое простое – это номенклатурные позиции. Дополнительно к ним:

процент возврата товаров;
категория клиента (физическое лицо или организация);
номер магазина или наименование региона, если есть несколько точек продаж.

Помним: пока излишняя детализация лишь запутает. Поэтому получаем в Excel простой план по доходам.

Рисунок 2. Упрощенный план по доходам в Excel

Построение финансовых моделей: пример планирования расходов

Определимся с расходами также пока в первом приближении. Обобщим полный перечень трат компании в разрезе элементов. Это будут:

материальные траты. В упрощенном варианте предположим, что к ним относятся только траты на покупку товара без НДС и что организация продает в течение месяца столько, сколько закупает. Значит, величину определим как произведение цены приобретения и объема продаж;
зарплата работников. Ограничимся единой суммой фонда оплаты труда за месяц без разбивки по составляющим;
социальные отчисления. Для них составим формульную зависимость. Обычно это 30% плюс процент взносов «на травматизм» в Фонд социального страхования от начисленной оплаты труда. Данный процент зависит от класса профессионального риска по организации. Мы взяли минимальное значение 0,2%;
амортизация. Вводим строку в ФМ, если в собственности предприятия есть основные средства и нематериальные активы. Указываем единую сумму для каждого месяца на основе средних фактических или предполагаемых значений;
прочие расходы. Это отчасти уход в детализацию. Однако если в компании есть постоянные легко планируемые траты, то зафиксируйте их. К ним относятся: арендные платежи, траты на рекламу, на командировки и т.п.

Для упрощенной ФМ такого списка достаточно. А это четыре совета на будущее о том, чем усложнить.

Первый. Введите дополнительные позиции в материальные расходы. Например, такие:

отопление, водоснабжение, электроэнергия;
упаковка;
ГСМ на автотранспорт, если он есть на балансе;
канцелярские принадлежности и т.п.

Второй. Отдельной строкой покажите входной НДС. Когда ставка – одна (20%), то его сумма – это произведение 20% на совокупные материальные траты без НДС. Когда примешивается еще 10%, тогда:

либо упрощайте и считайте налог по максимальной ставке;
либо усложняйте, применяя средневзвешенное значение ставки исходя из доли в закупках с НДС по 20% и 10% в прошлые периоды.

Третий. Разделите оплату труда на составляющие:

выделите в зарплате продавцов оклад (постоянную часть) и премию, зависящую от объема продаж (переменную компоненту);
покажите отдельной строкой зарплату других работников, которая не завязана на количественные показатели и является постоянной.

Четвертый. Учтите изменения амортизации, если в планах организации значатся покупка или продажа основных средств и нематериальных активов.

Рисунок 3. Финансовая модель бизнеса в Excel: план по расходам

То, что мы посчитали, является основой для:

вычисления прибыли/убытка в ОФР;
корректировки накопленного финансового результата в балансе;
построения притоков и оттоков в ОДДС.

Перейдем ко второму этапу – обобщению данных в системе финансовых отчетов. Рассмотрим, как это делается.

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

Формирование фактической отчетности начинается с подсчета прибыли/убытка за период. Такое же правило действует для моделей и бюджетов. Ведь чтобы заполнить раздел баланса про собственный капитал, нужна величина финансового результата.

Мы подсчитали плановые доходы и расходы по операционной деятельности. Этого достаточно, чтобы составить упрощенный отчет. Вот его возможный вариант.

Рисунок 4. Упрощенный ОФР в Excel-модели

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

ОФР тесно связан с балансом и ОДДС. Смотрите в таблице, куда и какую информацию он передает.

Таблица 1. Взаимосвязь плановых ОФР, баланса и ОДДС

Строка ОФР	Связанная строка баланса	Связанная строка ОДДС
Операционная прибыль (убыток)	Нераспределенная прибыль (убыток) – добавляется к значениям предыдущего периода	–
Выручка (операционные доходы)	–	Поступления от покупателей и заказчиков – сумма выручки корректируется на авансы полученные и дебиторскую задолженность по формуле: Поступления = Выручка + Авансы полученные – Дебиторская задолженность*
Операционные расходы	–	Платежи по текущей деятельности – сумма денежных** расходов корректируется на авансы выданные и кредиторскую задолженность по формуле: Платежи = Денежные расходы + Авансы выданные – Кредиторская задолженность
Примечание: * в примере ФМ формируется по организации розничной торговли. Поэтому полагаем: авансов и дебиторки от покупателей нет. Значит, поступления равняются выручке; ** денежные расходы – те, которые приводят к оттоку денег (зарплата, налоги, материальные). Для сравнения: к неденежным относится амортизация

Чтобы плановый ОФР оказался более информативным, дополним строками:

рентабельность продаж. Это расчетный показатель, который равняется отношению прибыли (в данном случае – операционной) к выручке;
точка безубыточности в денежном выражении. Для ее расчета предварительно разделили расходы на постоянные и переменные. Об особенностях классификации затрат по такому принципу читайте в статье «Расчет себестоимости».

Формируем плановый отчет о движении денежных средств

обобщить денежные притоки и оттоки;
рассчитать чистый денежный поток как разницу между ними;
прибавить его значение к остатку денег на начало периода и вычислить остаток денег на конец. Через эту сумму ОДДС связан с балансом.

Для создаваемой ФМ исходим из допущений:

организация занимается только основной деятельностью. При этом не покупает оборудование и ценные бумаги, не берет и не дает взаймы, не принимает новых учредителей. Значит, формируется один вид денежных операций – текущий. О двух других – инвестиционных и финансовых – читайте в статье «Анализ движения денежных средств компании»;
товары оплачиваются поставщику двумя платежами: 60% – аванс в месяце, который предшествует поставке, 40% – окончательный расчет сразу после принятия на склад;
зарплата выплачивается двумя переводами: 50% – аванс в месяце начисления, 50% – перевод остатка в периоде, который следует за отработанным;
страховые взносы перечисляются в месяце, идущем за их начислением;
прочая составляющая расходов по обычным видам деятельности представлена арендой. Платежи по ней производятся в месяце начисления арендной платы.

Получаем вариант ОДДС.

Рисунок 5. Упрощенный отчет о движении денежных средств в ФМ

Сводим плановый баланс

Предположим, что у организации:

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

Схема заполнения балансовых строк окажется следующей.

Таблица 2. Техника сведения упрощенного баланса

Показатель на начало месяца	Расчетная формула	Пояснения
Основные средства	Основные средства на начало предыдущего месяца (баланс) – Амортизация за месяц (ОФР)	–
Запасы	–	Неизменны и равняются нулю из-за допущения: закупаемые товары полностью распродаются в течение месяца
Дебиторская задолженность	Дебиторская задолженность на начало предыдущего месяца (баланс) + Платежи поставщикам за товары (ОДДС) – Материальные расходы (ОФР)	В примере нет дебиторки покупателей, так как организация торгует в розницу
Денежные средства	Денежные средства на начало предыдущего месяца (баланс) + Чистый денежный поток за месяц (ОДДС)	–
Уставный капитал	–	Неизменен и равняется 1000 тыс. руб. из-за допущения: новых учредителей в течение года не было
Нераспределенная прибыль	Нераспределенная прибыль на начало предыдущего месяца (баланс) + Прибыль за месяц (ОФР)	–
Кредиторская задолженность	Кредиторская задолженность на начало предыдущего месяца + Начисления по зарплате, социальным отчислениям и аренде (ОФР) – Платежи по зарплате, социальным отчислениям и аренде (ОДДС)	–

Формат баланса будет таким.

Рисунок 6. Упрощенный баланс в финансовой модели в Excel

Детализация финансовой модели в Excel

О возможных вариантах усложнения исходных данных рассказывали выше. Здесь остановимся на детализации строк плановой отчетности. Вот несколько советов:

используйте разные группировки расходов в ОФР. Мы обобщили их по элементам. Такой подход называется «по характеру расходов». Но он – не единственный. Можно одновременно с ним или вместо него использовать функциональную классификацию с выделением себестоимости продаж, а также расходов на сбыт и управление. Подробнее про такие варианты читайте в статье «БДР: бюджет доходов и расходов»;
добавьте другие виды прибыли в ОФР. Например, валовую. Она покажет разницу между продажной и закупочной ценами товаров. Если в организации есть прочие операции, то в отчете не обойтись без чистой прибыли. Читайте о видах финансового результата в материале «Анализ финансовых результатов деятельности компании»;
включите в ОФР строки для прочих доходов и расходов. Растущая организация не сможет обходиться без них. Например, взятие кредита приведет к появлению процентных платежей по нему. Они – это прочий расход;
расширьте ОДДС за счет инвестиционных и финансовых операций, если собираетесь приобретать оборудование и брать взаймы. Не смешивайте такие денежные потоки с текущими;
детализируйте сложносоставные строки в балансе. Например, из запасов выделите товары, материалы, готовую продукцию и незавершенное производство (последние два пункта – неактуальны для торговли). Из состава дебиторки: авансы выданные поставщикам и долги покупателей. По аналогии поступите с кредиторкой и покажите в отдельных статьях: авансы полученные, задолженность перед поставщиками, перед персоналом по оплате труда, по налогам и страховым взносам, перед учредителями по дивидендам.

Не забудьте скачать Excel-файл с полученной финансовой моделью из начала статьи.

Финансовое моделирование в Excel помогает понять:

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

Важна роль ФМ и в план-фактном анализе. В течение отчетного периода сравнивайте ожидания из нее с тем, как получилось в реальности. Это хороший способ контролировать ситуацию: понимать суть нарождающихся тенденций и того, к чему они приведут бизнес.

Читайте также: