Полная версия
Финансовая модель предприятия в Excel
Наталья Лидл
Финансовая модель предприятия в Excel
1. Введение
Финансовая модель описывает основные бизнес-процессы предприятия: продажи, закупки, учет остатков, движение денежных средств, расчет цен, расчет себестоимости и прибыли.
Данные для формирования бюджета движения денежных средств и бюджета доходов и расходов формируются на основе реестров. Реестры – это таблицы с данными, отражающие основные бизнес-процессы предприятия.
В данном пособии рассказывается, как вести такие реестры, если программы для бухгалтерского учета на предприятии нет. Если бухгалтерский учет ведется в специальной программе, то данные для реестра, в полном объеме или частично, можно автоматически выгружать из учетной системы.
Реестр является достаточно эффективным аналитическим инструментом, так как он позволяет оперативно добавлять необходимые группировки продукции, затрат, структурных подразделений, статей. Учетные системы не всегда позволяют сделать такие группировки, или могут возникнуть трудности при их изменении. Реестр позволяет делать любые группировки и преобразовывать данные в любой формат. Финансовая модель, построенная на основании реестров, легко адаптируется к изменениям форматов, позволяет понять, как формируется тот или иной экономический показатель, систематизировать изменения и выработать рекомендации для изменения в учетной системе.
Книга состоит из трех разделов.
В первом разделе описано, как организовать учет продаж в реестре. А именно – как фиксировать в Excel процессы закупки, наценки, продаж, переоценки остатков. Как формировать на основании реестра различные отчеты. Как рассчитывать остатки в зале на конкретную дату. Как вести в реестре историю работы с покупателями и поставщиками.
Во втором разделе описана методика ведения реестра платежей и формирования на его основе бюджета движения денежных средств.
В третьем разделе рассказывается, как рассчитывать себестоимость, обосновывать цены и наценки и отражать результаты деятельности в бюджете доходов и расходов.
1. Учет и анализ закупок и продаж при помощи реестра «Продаж и закупок»
1.1. Реестр «Продаж и закупок»
Реестр «Продаж и закупок» имеет такой формат.
Рис. 1. Реестр «Закупок и продаж»
Этот реестр был сделан в качестве примера для торгового предприятия, в котором товар закупается для последующей перепродажи.
Если предприятие занимается производством, то процесс закупки можно заменить процессом передачи готовой продукции из производства для последующей продажи.
Как и для любого реестра, создание этого реестра начинается с «шапки», в процессе работы с реестром «шапка» может меняться, Excel позволяет делать это достаточно легко. Состав столбцов «шапки» определяются исходя из той информации, которая у вас есть в первичных документах, и из тех признаков, которые необходимо отследить.
Пояснения по «шапке» на рис. 1.
1. Месяц и дата (число месяца). Все параметры даты: число, месяц, год (в данном примере его нет) лучше заносить в разные столбцы, так легче работать с формулами и фильтрами.
2. Наименование – наименование у товара должно быть уникальным и во все реестры должно заноситься одинаково, иначе отчеты и фильтры будут работать некорректно и не позволят получить необходимую информацию.
3. Столбец «Код (артикул)» – это уникальный код товара в вашей учетной системе. Его основная цель – идентифицировать товар для последующей аналитики. При помощи кода можно, например, проводить анализ продаж товаров в зависимости от поставщика, партии, акции, региона продаж и т. д. В качестве этого кода может быть использован штрих-код или артикул продавца, в большинстве случаев так даже удобней, но, если нужно отслеживать более мелкие признаки, нужна будет своя параллельная кодировка. Для удобства в реестре может быть два столбца, например, штрих-код и внутренний код.
При помощи кодов можно вводить группировки товара, что будет полезно при большой номенклатуре или при значительном разбросе цен на похожие товары.
4. Столбец «Признак» – это признак, обозначающий движение товара, например:
остаток – остаток товара в зале на начало;
покупка – поступление товара от поставщика;
зал – передача товара в зал по цене продажи;
продажа – продажа товара в зале конечным покупателям;
заказ – предварительный заказ (без движения товара);
заказ выполнен / не выполнен – результат исполнения заказа.
Этот признак присваивать нужно обязательно, и для однотипных процессов названия должны быть написаны всегда одинаково, иначе формулы и фильтры будут работать некорректно.
5. Прейскурант – цена, по которой товар продается в зале.
6. Столбцы «Поставщик», «Доставка», «Оплата (отсрочка), «Примечание (поставщик)», Документ покупки» – столбцы, характеризующие работу с поставщиком. Наименование поставщика, как все наименования, должно быть одинаковым во всех реестрах и отчетах. Остальные столбцы заполняются в произвольной форме и носят справочный характер.
В данном реестре отражаются только поставщики товара, который предназначен для продажи. Учет прочих поставок материалов и услуг в данном реестре не ведется, при необходимости для них нужно сделать отдельный реестр.
6) Столбцы «Количество», «Сумма», «Цена ед., с НДС», «Ставка НДС», «Цена ед., без НДС» – блоки про цену и количество закупок и продаж.
7) Оставшаяся часть столбцов реестра характеризует работу с покупателем, этот блок заполняется в произвольной форме и носит справочный характер.
Важно!!! Все наименования товаров, названия поставщиков и покупателей в реестре пишутся всегда одинаково. Чтобы легче было заполнять названия, в соответствующей ячейке нужно нажать на правую кнопку мыши и выбрать пункт «Выбрать из раскрывающего списка». Если таблицы Excel являются только дополнительным аналитическим инструментом, и параллельно учет ведется в бухгалтерской программе, лучше заносить названия контрагентов и товаров как в этой программе.
В нашем примере рассмотрен случай, когда весь товар хранится в зале, склада нет. При наличии склада в столбце «Признак» нужно будет указывать признак склада.
Если на момент начала ведения реестра в торговом зале были остатки, то их можно занести таким образом.
Рис. 2. Занесение остатков товара в реестр «Продаж и закупок»
Далее рассмотрим отражение в реестре закупки, передачи в зал и продажи.
Результат заполнения в реестре покупки товара у поставщика имеет следующий вид.
Рис. 3. Занесение данных по покупке товара у поставщика
Передача товара в зал отражается отдельной строкой. Наименование товара, артикул, количество переносим из строк о покупке. Сумма определяется расчетным путем: цена из прейскуранта умножается на количество. НДС также выделяется расчетным путем, исходя из действующего режима налогообложения. Признак ставим «Зал».
Рис. 4. Отражение в реестре передачи товара в зал
Продажа в зале отражается аналогично передаче товаров в зал, только нужно писать признак «Продажи» и количество – фактически проданное за день.
Рис. 5. Отражение в реестре продаж
1.2. Как добавить динамику в реестр (умные таблицы)[1]
Реестр «Продажи и закупка» будет обновляться ежедневно. Чтобы при каждом обновлении не переписывать ссылки и формулы, реестр нужно преобразовать в «умную таблицу». (Для этого нужно выделить таблицу, войти в меню в раздел «Вставка» и нажать кнопку «Таблица», после чего таблица примет формат как на рисунках выше.)
Рис. 6
При занесении новых данных формат «умной таблицы» распространяется на новую строку автоматически, при этом переносятся все формулы, которые были в реестре. Для нашего реестра формула расчета цены для «Покупки» отличается от формул для строк с признаками «Зал» и «Продажа», поэтому при работе с умными таблицами нужно быть внимательным, но из соответствующих строк формула легко копируется и вставляется в нужном формате.
1.3. Сводные отчеты по реестру «Продажи и закупки»
Учет движения товара в форме реестра позволяет не только работать с фильтрами, но и строить на его основе большое количество аналитических таблиц.
Вот несколько примеров сводных таблиц.
Рис. 7. Сводные таблицы по реестру «Продажи и закупки»
Сводные таблицы удобно использовать для быстрой аналитики и проверки правильности занесения данных, но удобнее работать в отчетах с постоянными формами.
1.4. Отчеты по движению товаров
В реестре «Продажи и закупки» отражаются следующие движения товара: закупка от поставщика – передача в зал – продажа покупателям.
В процессе передачи товара в зал ему присваивается цена продажи, поэтому важно, чтобы эта операция была занесена в реестр. В отчете «Движение товара (закупка)» сравнивается количество товара, поступившего от поставщика и переданного в зал.
Рис. 8. Отчет «Движение товара (закупка)»
Если весь товар, который поступил от поставщика, передан в зал с наценкой, то остаток будет равен 0.
В этой же форме можно провести предварительную проверку уровня наценки. Для этого в столбце «Рентабельность» рассчитывается соотношение «суммы продаж, без НДС» и «суммы покупки, без НДС». Из таблицы видно, почему нельзя для расчета рентабельности использовать суммы с НДС. При разных режимах налогообложения у покупателя и продавца рентабельность будет рассчитываться некорректно.
Данные из реестра заносятся при помощи формулы СУММЕСЛИМН
=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Наименование];A6;ПродажиЗакупки[Признак];"Покупка")[2] – эта формула суммирует данные по количеству товара, приобретенного у поставщика.
Так она выглядит в диалоге «Мастера формул».
Рис. 9. Занесение формулы для ячейки В6 (из рис. 7)
В отчете «Движение товара (закупка)» для того, чтобы просуммировать данные по закупкам без НДС, в реестр «Продажи и закупки» был добавлен столбец «Сумма без НДС».
Рис. 10. Реестр «Продажи и закупки» с добавленным столбцом «Сумма без НДС»
Учет операций в реестре позволяет легко добавлять новые столбцы, на формулы отчетов это не влияет.
Контролировать остатки товара в зале можно при помощи этого отчета.
Рис. 11. Отчет «Движение товара (продажи)»
Рис. 12. Формулы для отчета «Движение товара (продажи)»
В отчете стоимость рассчитывается только для остатков, так как отчет формируется на текущую дату, а цены на товар могут меняться, поскольку реестр «Продажи и закупки» ведется в рамках года.
На следующих рисунках будет показано, как изменения в реестре отражаются на данных отчетов по движению товаров.
Продемонстрируем ситуацию, когда товар принят от поставщика, но в зал не передан.
Рис. 13. Отражение в отчете «Движение товара (закупка)» операций по покупке без последующей передачи в зал
В столбце «Остаток» отчета появилась красная ячейка (выделение сделано при помощи условного форматирования), которая показывает, что 100 единиц товара не были переданы в зал.
После добавления в реестр передачи в зал отчет примет такой вид.
Рис. 14. Отражение в отчете «Движение товара (закупка)» операций по покупке без последующей передачи в зал
В отчете «Движение товара (продажи)» данная операция отразится следующим образом.
Рис. 15. Количество поступившего товара с кодом 102 увеличилось на 100 по сравнению с предыдущим отчетом из рис. 10
Посмотрим, как отразится в отчете продажа товара, превышающая остаток.
Рис. 16. Отражение продаж из реестра «Продажи и закупки» в отчете «Движение товара (продажи)»
1.5. Отчет по остаткам товаров в зале на конкретную дату
В п. 1.3 на рис. 6 был рассмотрен отчет «Движение товаров (продажи)», в котором рассчитывались остатки в торговом зале. Это отчет показывает остатки только на текущую дату, что не всегда удобно, например, при изменении цен или при необходимости поднять информацию на определенную дату.
Для отслеживания движения и остатков товаров по дням нужно сделать более сложный отчет.
Рис. 17. Отчет об остатках товара по дням
Рассмотрим новый отчет более подробно по выделенным блокам.
Первый блок (синий): Движение товаров
Рис. 18. Отчет об остатках товара по дням (количественные показатели)
В этом блоке отражено ежедневное движение товаров только по количественным показателям без цены.
Теперь более подробно о формулах. Формулы ссылаются на реестр «Продажи и закупки».
Начальный остаток вводится один раз, его можно занести значениями или простой ссылкой на нужное поле из реестра.
Формула для блока «Поступило в зал»
=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Месяц];A5;ПродажиЗакупки[Дата];B5;ПродажиЗакупки[Признак];"Зал";ПродажиЗакупки[Код (артикул)];'остатки по дням'!$C$3)
Из формулы видно, что из реестра «Продажи и закупки» выбираются и суммируются значения по группе признаков: количество, месяц, дата, признак, код (артикул)
'остатки по дням'!$C$3 – это название листа в книге Excel, на котором находится отчет
Формула для блока «Продано»
=СУММЕСЛИМН(ПродажиЗакупки[Количество];ПродажиЗакупки[Месяц];A5;ПродажиЗакупки[Дата];B5;ПродажиЗакупки[Признак];"Продажа";ПродажиЗакупки[Код (артикул)];'остатки по дням'!$C$3)
Формула для блока «Продано» аналогична формуле из блока «Поступило в зал», только отбор идет по другому признаку: ПродажиЗакупки[Признак];"Продажа" вместо ПродажиЗакупки[Признак];"Зал" из предыдущей формулы.
В блоке «Остаток» стоит простая формула
=K4+C5-G5 (Предыдущий остаток + Поступило в зал – Продано)
Переходим к следующей части таблицы – это стоимостная часть.
Рис. 19. Отчет об остатках товара по дням (стоимостной блок)
Так как цены имеют обыкновение меняться, то в форме отчета предусмотрен столбец «Прейскурант», в котором указана дата начала действия прейскуранта. В нашем примере данные о ценах содержатся на отдельном листе в таком формате.
Рис. 20. Таблица с ценами (Прейскурант)
Цены из таблицы с ценами на отдельном листе подтягиваются при помощи функции ВПР
=ВПР($P$2;Прейскурант!$A$3:$B$4;2;0)
При переходе на новый прейскурант формулу нужно будет скорректировать, чтобы она ссылалась на 3-й столбец.
Рис. 21. Переход на новый прейскурант
Если номенклатура небольшая, то в пределах действия одного прейскуранта цены в этот отчет «Остатки товара по дням» можно заносить как значения без формул. (Ведение таблицы с ценами на отдельном листе полезно не только для отчетов, но и позволяет анализировать динамику цен.)
Формулы для расчета стоимости обычные: цена * количество.
На практике ведение отчета остатков по датам можно совмещать с отчетом «Движение товара (продажи)», который формируется на текущую дату. Во-первых, отчет на текущую дату более компактный и удобный, во-вторых, для сверки данных в двух отчетах (остаток на рис. 16 равен остатку на рис. 19).
Рис. 22. Отчет «Движение товара (продажи)»
1.6. Как отразить изменение цен в реестре «Продажи и закупка» и в отчетах, построенных на его данных
Изменение цен включает в себя две операции: 1) переоценка остатков в зале; 2) изменение цены передачи в зал.
Например, цены изменяются с 15.02. В нашей модели цены отражаются на отдельном листе «Прейскурант» в таком формате.
Рис. 23. Прейскурант
Из рис. 24 видно, что 15.02 цена на Товар 2 изменится. Согласно отчету об остатках товаров на 14.02 в зале «Товара 2» остается 180 единиц.
Рис. 24. Остатки товаров на 14.02
Следующая строка отчета на 15.02 будет содержать уже новые цены по Товару 2.
Рис. 25. Изменение цен в отчете
На рис. 22 видно, что 15.02 поменялось название прейскуранта (столбец «Прейскурант») и цена на Товар 2, стоимость пересчитывается автоматически.
Важно!!! При изменении прейскуранта нужно проконтролировать корректность ссылок в формулах, так как в нашем случае (см. рис. 20) новый прейскурант – это новый столбец, ссылок в формулах на него просто нет.
В таблице с ценами на листе «Прейскурант» изменение цен обязательно нужно отражать в новом столбце. Если просто поменять данные в столбце «В» на 01.01.2020, то все цены в отчете изменятся на новые, что приведет к ошибкам при расчете сумм товарных остатков за предыдущие периоды.
В сокращенном формате отчета тоже нужно изменить актуальные цены.
Рис. 26. Отчет «Движение товара (продажи)» с новой ценой
Так как в реестре «Продажи и закупки» хранится вся информация о движении товаров, переоценку остатков следует отразить и там. Для этого нужно присвоить этой операции новый признак «Переоценка».
Рис. 27. Отражение в реестре «Продажи и закупка» переоценки остатков
Внимание!!! В столбце «Прейскурант» реестра «Продажи» нужно будет поменять цену. Если цена заносилось формулой ВПР, то при изменении ссылок в «умной таблице» – реестре «Продажи» могут поменяться цены за предшествующий период (так произошло у меня, но после нажатия кнопки отмены действия формула стала работать правильно).
Если не получается корректно отразить цены за предыдущий и текущий период в одном реестре, то их можно заносить не формулой, а просто значением. Или цены предыдущего периода при каждом изменении копировать и вставлять как значения, чтобы формулы ссылались только на актуальный прейскурант.
Теперь отразим передачу товара в зал по новым ценам. Следите, чтобы формулы в столбце «Прейскурант» работали правильно и подтягивали актуальную цену (напоминаю, что, если с формулами сложно, лучше заносить как значение).
Конец ознакомительного фрагмента.
Текст предоставлен ООО «ЛитРес».
Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.
Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.
1
Реализовать динамику в отчетах по реестру можно без преобразования в «умную таблицу», а при помощи ссылок, см. п. 2.2. (при этом в сводных таблицах диапазон придется обновлять).
2
В данном случае ссылки в формуле СУММЕСЛИМН идут на «умную таблицу», в которую был преобразован реестр «Продаж и закупок». При использовании обычных таблиц ссылки будут иметь другой вид, для обеспечения динамики ссылаться нужно на весь столбец, без обозначения номера строки.
