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

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

Финансовая модель, о которой будет рассказано в этом пособии, построена на примере малого предприятия и включает в себя процессы от закупки и наценки товаров до формирования бюджетов движения денежных средств и бюджета доходов и расходов. Данная методика не требует наличия специальных программ для бухгалтерского учета и бюджетирования. Учет хозяйственных операций и формирование отчетов осуществляется только в 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 - Реализовать динамику в отчетах по реестру можно без преобразования в «умную таблицу», а при помощи ссылок, см. п. 2.2. (при этом в сводных таблицах диапазон придется обновлять).]