Расчет бизнес-плана для производства 15 видов продукции

В данной статье рассмотрим расчет производства 15 видов продукции из 20 материалов/ингридиентов/комплектующих. Расчет сделан на три года с получением три основных форм:

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

Итак, первый лист:

Лист “Инвестиции”

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

  • наименование;
  • количество;
  • цену единицы.

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

Лист “ФЗП”

ФЗП – расшифровывается как фонд заработной платы. На этом листе планируются затраты на персонал.

Данные вручную вносятся только в ячейки выделенные оранжевым цветом.

То есть вы указываете должность работника, его зарплату в месяц, количество штатных единиц. Все остальные расчеты автоматические. Но! Возможно вам понадобится вводить штатные единицы не все с первого месяца, тогда вам необходимо удалить формулу из первого столбца расчета (это столбец D) и поставить эту формулу, а именно:

Ставка (столбец В) * Количество единиц (столбец С)

в тот месяц, с которого эта штатная единица появляется на вашем предприятии. Подобная операция сделана в таблице по должностям “Менеджер по продажам” и “2й рабочий-грузчик”. Эти штатные единицы введены со второго месяца проекта. По всем остальным месяцам расчет произойдет автоматически.

Таблица рассчитана на весь срок проекта (то есть на 36 месяцев). В таблице скрыта часть месяцев (это видно по утолщению линии раздела ячеек между столбцами I и АВ. Это сделано исключительно для наглядности, в самом файле этого скрытия не будет, но во всех последующих таблицах в этой статье я буду скрывать часть столбцов, где это необходимо. Повторюсь: исключительно для наглядности убирая часть повторяющейся информации.

Лист “Себестоимость”

Ключевой лист этого расчета. Здесь вы вручную вносите данные в несколько таблиц. По порядку:

Таблица “Цена закупки за 1 кг, грн”

Столбец “А” – вносятся название используемых материалов и комплектующих.

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

Таблица “Норма отходов при обработке закупленного сырья и комплектующих, %%”

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

А вот во все последующие столбцы выделены оранжевым цветом. В них вы указываете сколько отходов у вас возникает при обработке закупленного сырья и материалов. Причем прошу обратить внимание, что если у вас при обработке в отходы попадает половина приобретенного сырья, то в данной таблице нужно ставить не 50%, а 100% (пример в таблице “Ананас”). То есть здесь берется отношение отходов не к приобретенному сырью, а к уже обработанному сырью-полуфабрикату. Заполнять таблицу необходимо только по тем видам сырья, по которым будут отходы. Процент отходов в разных периодах вы можете менять по тому же принципу, как и цену в таблице “Цена закупки за 1 кг”.

Таблица “Структура наполнения лотка с продукцией”

Столбец А в этой таблице не трогаем.

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

Непосредственно в поле таблицы вы вносите количество сырья в производимом продукте. Например: в продукте “Микс 4 овоща” будет:

  • 0,125 кг помидор;
  • 0,13 кг огурцов;
  • 0,134 кг болгарского перца;
  • 0,151 кг грибов.

Кроме того, на 1 штуку этого продукта пойдут материалы в следующем количестве:

  • 1/12 короба для лотков (или 0,08 в десятичном варианте). Так как в короб умещается 12 штук любого вида производимой продукции;
  • 1 лоток;
  • 0,5 метра пленки для упаковки;
  • 1 этикетка;
  • 2 вилки;
  • 1 салфетка;
  • 1 кубический метр газа;
  • 1 метр скотча.

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

Таблица “Производственная себестоимость лотка Микс 4 овоща” и все последующие таблицы на данном листе

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

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

Ниже в столбце А – указываются названия сырья, материалов и комплектующих.

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

Обратите внимание на заголовок таблицы. В нем название товара меняется автоматически, когда вы изменяете название товаров в шапке таблицы “Структура наполнения лотка с продукцией”.

Кроме того, строки таблицы изменяют цвет на светлозеленый, если ингредиент, указанный в столбце А данной строки используется в данном продукте. Это сделано для того, чтобы было сразу видно какие исходные материалы используются при производстве продукта. Например в продукте Микс 4 овоща (см. таблицу выше) используются: помидоры, огурцы, болгарский перец и грибы, а так же комплектующие. И все эти ячейки окрашены зеленым цветом.

А в продукте Лоток 2 используются: ананас, виноград, груша, киви + комплектующие. И именно эти строки автоматически окрашены светлозеленым цветом (см. таблицу ниже)

Все остальные таблицы на листе “Себестоимость” аналогичны вышеуказанным двум таблицам.

Лист “Закупка”

Полностью автоматический лист. Здесь ничего вручную вносить не нужно.

На листе находятся три таблицы.

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

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

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

Лист “Склад”

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

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

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

Как только вы зашли на этот лист вы видите первые две таблицы

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

Во второй таблице рассчитывается минимальный складской остаток. Для этого в столбце А вручную вносится количество дней, то есть в данном случае минимальный складской остаток является трехдневным. Это значит, что на складе всегда будет остаток данного вида сырья необходимого для бесперебойной трехдневной работы. Расчет ведется на рабочие дни. В расчете принято, что в каждом месяце 22 рабочих дня. Ячейка, куда данные вносятся вручную выделена оранжевым цветом.

Третья таблица находится в тех же строках, что и первая таблица, но смещена относительно нее вправо.

Здесь рассчитывается движение того же самого материала, что и в первых двух таблицах, но в денежном выражении учитывая цену этого самого материала с листа “Себестоимость”.

Все остальные блоки на данном листе совершенно идентичны.

Лист “Производство”

Полностью автоматический лист.

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

В первой таблице на листе показан помесячный объем производства продукции.

Данные в таблице в натуральных показателях. Как и во всех остальных таблицах на данном листе.

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

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

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

Лист “Склад ГП”

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

Как видите все тоже самое, что и на листе “Склад”. И даже ячейка в столбце А второй таблицы, куда вы вручную вносите количество дней для определения минимального складского остатка. Напомню: для расчета используются рабочие дни и в расчете сделано допущение, что в месяце 22 рабочих дня.

Третья таблица на листе точно так же как и на листе “Склад” смещена вправо относительно первой таблицы и отражает движение по складу готового продукта в денежном выражении.

Уникальными таблицами листа “Склад ГП” являются обобщающие таблицы по движению товара в денежном выражении.

Эти таблицы смещены вправо и находятся над третьей таблицей рассмотренного выше блока.

Первая обобщающая таблица это “Общий складской отчет по готовой продукции”

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

Вторая обобщающая таблица это “Справочные данные об отгрузках продукции по себестоимости в денежном выражении”

Фактически это таблица расшифровывает строку “Расход” из предыдущей таблицы. То есть здесь показано, какая именно продукция была реализована.

Лист “Реализация”

На этом листе мы определяем цену и объемы реализуемой продукции.

Первая таблица “Цена реализации”. 

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

Столбец AL – это последний столбец таблицы и в нем автоматически рассчитывается среднемесячная рентабельность продукта при указанных ценах и рассчитанной себестоимости.

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

Таблица формируется полностью автоматически.

Следующая таблица на данном листе – “Реализация продукции в натуральных показателях”

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

Далее идет блок таблиц по реализации в денежном выражении:

 

Весь блок таблиц полностью автоматический.

В таблице “Реализация продукции в денежном выражении” показан объем продаж помесячно на протяжении всего проекта. Для получения объема продаж в денежном выражении перемножаются соответствующие строки таблиц “Цена реализации” и “Реализация продукции в натуральных показателях”.

Следующая таблица, выраженная в одной строке, – “Возвраты”. В ней показан в денежном выражении объем возвращенной продукции за период.

Путем вычитания итогов таблицы “Реализация продукции в денежных показателях” и таблицы “Возвраты” получаем данные в таблице “Чистый объем реализации в денежном выражении”.

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

Последняя таблица блока – “Дебиторская задолженность”.

Отражает сформированную по результатам продаж дебиторскую задолженность покупателей.

Лист “Возвраты”

На этом листе определяем количество возвратной продукции от покупателей.

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

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

Вторая таблица – “Количество возвратной продукции, грн”

Полностью автоматическая таблица в которой вычисляется количество возвратов в денежном выражении. Итог этой таблицы попадает на лист “Реализация” в таблицу “Возвраты” и уменьшает объем реализованной продукции за планируемый период.

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

Четвертая таблица – “Общее количество возвратных полуфабрикатов (справочно), кг”

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

Пятая таблица – “Возврат полуфабрикатов с лотка Микс 4 овоща”

Автоматически рассчитывается потенциально возможный приход сырья при возврате продукта “Микс 4 овоща”. Зеленым цветом окрашиваются те строки таблицы, в которых есть данные по возврату.

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

Лист “ОПУ”

ОПУ – отчет о прибылях и убытках.

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

Рассмотрим построчно:

Объем продаж – объем реализованной продукции. Данные с листа “Реализация” итоги таблицы “Реализация продукции в денежных показателях”;

Возвраты от покупателей – данные с листа “Реализация”, таблица “Возвраты”.

Чистый объем продаж – разница между строками “Объем продаж” и “Возвраты от покупателей”.

Себестоимость проданной продукции – данные с листа “Склад ГП” таблица “Общий складской отчет по готовой продукции” строка “Расход”.

Валовая прибыль – разница между строками “Чистый объем продаж” и “Себестоимость проданной продукции”.

Норма валовой прибыли, % – определяет сколько валовой прибыли содержится в общем объеме реализации. Рассчитывается как отношение строки “Валовая прибыль” к строке “Объем продаж”.

Операционные расходы

В данном разделе собраны постоянные расходы предприятия не связанные напрямую с объемом производства или объемом продаж.

Зарплата – зарплата персонала. Сумма берется из итога таблицы с листа “ФЗП”.

Начисления на ФОТ – налоги на фонд оплаты труда. Вычисляется как 38% от суммы по строке “Зарплата”.

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

Итого операционные расходы – сумма по разделу “Операционные расходы”.

Операционная прибыль – разница между строками “Валовая прибыль”.

НДС – налог на добавленную стоимость. В данном расчете рассматривается не как косвенный налог, а как ещё одна издержка составляющая 7% от объема продаж.

Налог на прибыль – рассчитывается как 5% от объема продаж.

Чистая прибыль – результат вычитания из строки “Операционная прибыль” строк “НДС” и “Налог на прибыль”.

Норма чистой прибыли, % – определяет сколько чистой прибыли содержится в общем объеме реализации. Рассчитывается как отношение строки “Чистая прибыль” к строке “Объем продаж”.

Лист “ОДДС”

ОДДС – отчет о движении денежных средств.

 

Отчет разделен на три части: операционная деятельность, инвестиционная деятельность и финансовая деятельность. Это традиционное разделение для подобного отчета. Подробнее про формирование этого отчета можете почитать здесь.

Рассмотрим построчно:

Поступления от продаж товаров, услуг – поступления денежных средств от покупателей из таблицы “Поступление денежных средств” с листа “Реализация”.

Оплата поставщикам и подрядчикам – оплаты за поставку сырья, материалов и комплектующих. Данные поступают из второй таблицы с листа “Закупка”.

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

Итого прямые издержки – сумма по разделу прямых издержек.

Оплата постоянных расходов – расходы указанные в разделе операционных расходов на листе “ОПУ”. Прошу обратить внимание, что выплата заработной платы смещена на один месяц, так как заработная плата выплачивается с опозданием на месяц.

Оплата налогов – сумма строк “Начисление на ФОТ”, “НДС и “Налог на прибыль” с листа “ОПУ”. Оплата налогов так же смещена на месяц как и выплата заработной платы.

Итого постоянные расходы – сумма по разделу постоянных расходов.

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

Закупка ОС – в данной строке отражаются денежные затраты на первоначальные вложения с листа “Инвестиции”.

Продажа ОС – в этой строке были бы данные, если в проекте предполагалась продажа основных средств предприятия.

Чистые денежные средства от инвестиционной деятельности – разница между строками “Продажа ОС” и “Закупка ОС” причем вычитается строка “Закупка ОС”.

Финансовый раздел ОДДС

Единственный раздел отчета, который заполняется вручную. Поэтому строки в отчете окрашены в оранжевый цвет.

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

Как только денежных средств становится достаточно, остаток средств становится черным.

В строку “Изъятия” – данные вносятся тогда, когда принимается решение об изъятии средств из проекта.

Чистые денежные средства от финансовой деятельности – разница между строками “Вложения” и “Изъятия”.

Денежные средства на начало периода – данные в ячейку поступают из строки “Денежные средства на конец периода” предыдущего месяца.

Денежные средства на конец периода – значение в ячейке формируется в результате сложения строк “Денежные средства на начало периода”, “Чистые денежные средства от операционной деятельности”, “Чистые денежные средства от инвестиционной деятельности” и “Чистые денежные средства от финансовой деятельности”.

Лист “Баланс”

Все расчеты на данном листе автоматические. Единственная ячейка, куда данные вносятся вручную – это ячейка А2 в шапке таблицы. В ней указывается наименование предприятия.

Таблица баланса может быть свернутой и выглядеть вот так:

 

Но некоторые разделы актива могут быть развернутыми и выглядеть вот так:

Рассматривать построчно разделы актива баланса не вижу смысла. Данные подтягиваются автоматически из соответствующих листов проекта. По строке “Активы всего” суммируются итоги по всем раздела актива баланса.

А вот пассив баланса стоит рассмотреть подробнее.

Дополнительные вложения – источник данных для строки: лист “ОДДС” строка “Чистые денежные средства от финансовой деятельности”. Кроме того, эта строка накопительная: к данным поступающим в эту строку всегда прибавляются значения из предыдущего периода этой строки.

Финансовый результат с начала года – накопительная строка. Определяется как сумма значения этой строки из предыдущего месяца и значения строки “в т.ч. за отчетный период” текущего месяца.

в т.ч. за отчетный период – в эту строку выводятся данные из строки “Чистая прибыль” лист “ОПУ”. При определении итога по разделу “Собственный капитал” эта строка не суммируется.

Собственный капитал итого – сумма строк “Капитал на начало года”, “Дополнительные вложения” и “Финансовый результат с начала года”.

Кредиторская задолженность перед поставщиками – данные с листа “Закупка”.

Расчеты по оплате труда – данные из отчета о прибылях и убытках. Лист “ОПУ” строка “Зарплата”.

Расчеты с бюджетом – данные с листа “ОПУ” сумма строк “Начисления на ФОТ”, “НДС” и “Налог на прибыль”.

Текущие обязательства итого – сумма строк “Кредиторская задолженность перед поставщиками”, “Расчеты по оплате труда” и “Расчеты с бюджетом”.

Пассивы всего – сумма строк “Собственный капитал итого” и “Текущие обязательства итого”.

Проверка – самая важная строка на листе. Проверяет равенство Актива и Пассива баланса. Если в строке есть хоть какая-то цифра – это значит, что Пассив не равен Активу. А значит вы где-то нарушили алгоритм расчета.

Финансовые показатели

На листе “Баланс” есть два финансовых показателя. Эти показатели демонстрируют эффективность собственного капитала и вложенных средств. При желании финансовых показателей можно добавить великое множество.

Рентабельность собственного капитала (ROE) –  показывает какова прибыльность собственного капитала предприятия. Определяется как отношение строки “Финансовый результат с начала года” к строке “Собственный капитал итого”.

Рентабельность вложенного капитала – показывает прибыльность вложенных средств. Определяется как отношение строки “Финансовый результат с начала года” к строке “Дополнительные вложения”.

Лист “Полная себестоимость”

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

Когда вы зайдете на этот лист, то увидите вот такую картинку

Это расчеты полной себестоимости по каждому продукту. Причем в каждой таблице скрыты строки для облегчения восприятия информации.

Обратите внимание, что лист начинается с 21й строки. Это означает, что на листе есть скрытая часть. Если вы её раскроете, то увидите таблицу и отдельно стоящую строку:

В скрытой таблице показаны объемы реализации продукции помесячно. Полная копия таблицы “Реализация продукции в натуральных показателях” с листа “Реализация”

В строке под таблицей показаны операционные расходы (включая НДС и налог на прибыль) с листа “ОПУ”. Эти таблицы нужны на этом листе исключительно для облегчения составления формул, поэтому они скрыты в файле.

Рассмотрим таблицу полной себестоимости продукта “Микс 4 овоща” при этом скрытые внутри таблицы строки раскроем:

Как видите таблица от шапки до строки “Всего производственная себестоимость” является полной копией таблицы производственной себестоимости с листа “Себестоимость”.

Единственное отличие – это две последние строки.

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

Итого полная себестоимость – сумма строк “Всего производственная себестоимость” и “Доля операционных расходов и налогов”.

Лист “Справочник”

На этом листе находится вспомогательная информация для создания динамических заголовков при помощи формулы “Сцепить”. Поменяв на этом листе, например в ячейке А8, “грн” на “руб”  вы измените название всех заголовков в файле, где применена формула “Сцепить”.

Где приобрести этот файл

Если вас заинтересовал этот расчет, то приобрести его можно в нашем магазине.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *