Шаблон бюджета продаж имеет такую форму:
Данные по выручке о объему продаж будем брать из реестра продаж и закупок (как его делать я рассказывала в этой статье)
Выглядит он так
Данные из реестра в бюджет продаж будем переносить при помощи формулы СУММЕСЛИМН
Заполненная часть таблицы выглядит так
Данные заполнены только за январь и февраль, так как в реестре других данных нет.
Правильно ли отработала формула проверим при помощи сводной таблицы
Настройки для сводной таблицы
Для расчета себестоимости нужно рассчитать средние цены закупки, так как они могут меняться в течение месяца
Для расчета средней цены закупки можно сделать такую таблицу
Данные по остаткам по дням берем из отчета "Остатки по дням"
Отчет "Остатки по дням" выглядит так (как его делать я рассказывала здесь)
Для расчета стоимости остатка берется цена закупки, если остатка по товару не было. Если остатки по товару были, то в качестве цены остатка берется средняя цена закупки предыдущего месяца.
Данные о поступлении товара за месяц берем из реестра продаж и закупок, аналогично данным по продажам.
=СУММЕСЛИМН(ПродажиЗакупки[Сумма без НДС];ПродажиЗакупки[Признак];"Покупка";ПродажиЗакупки[Месяц];1;ПродажиЗакупки[Наименование];"Товар 1") - эта формула суммирует, все закупки за январь по Товару 1.
Средняя цена рассчитывается по формуле средней взвешенной. Сумма стоимости остатка и закупки делится на общее количество остатка и закупки.
На основании данных по объемам продаж в количественных показателях за месяц и средней цены закупки рассчитывает себестоимость продаж и заполняем оставшиеся ячейки бюджета продаж.
Формулы
В нашем примере себестоимость складывается только из стоимости закупки. При оказании услуг или производстве расчет себестоимость может быть более сложным, тогда его лучше делать на отдельном листе, а данных в форму бюджета продаж переносить при помощи ссылки на нужную ячейку.
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке