Как оптимизировать учет выпуска пищевой продукции в Excel-модели

Внедрение Excel-модели, настроенной под специфику бизнес-процессов компании, позволит оптимизировать:

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

Чтобы оценить преимущества Excel-модели, кратко рассмотрим положение дел «как было» до оптимизации учета.

«КАК БЫЛО»

До оптимизации учет вели в отдельных таблицах Excel (рис. 1). Например, объем выпущенной продукции учитывали суммированием натуральных показателей в одной ячейке Excel согласно ежедневным производственным актам. Так, объем выпуска хлеба Городского составляет на текущую дату 2960 буханок (470 + 1000 + 1490). Ввиду многономенклатурного ежедневного выпуска хлебобулочной и кондитерской продукции к концу месяца в одной ячейке (см. рис. 1) вручную суммировались более 60 показателей (предприятие работает в три смены).

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

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

• после ручного просчета «первички» сотрудники могли дать ответ на вопрос «Сколько?». На вопрос «Когда?» ответ получить было сложно;
• производственные акты, накладные и другие документы оформлялись в трех экземплярах — для экономистов, бухгалтеров материального и расчетного отделов. Отсюда высокие затраты на бумагу, необходимость масштабной архивной системы;
• фактически обработку одних и тех же документов вели сразу несколько сотрудников (каждый для целей своего участка учета);
• не хватало времени на анализ хозяйственной деятельности, поскольку сотрудники были заняты техническим рутинным учетом.

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

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

СПЕЦИФИКА

Основные задачи учета готовой продукции в хлебопекарном и кондитерском производстве:

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

Работу в хлебопекарных и кондитерских цехах регламентируют ежедневными План-заказами по выпуску продукции в натуральном выражении. План-заказ составляет руководитель отдела продаж, утверждает руководитель компании (см. План-заказ № 58).

План-заказ одновременно является документом для:

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

План-заказ на производство формируют как на продукцию по заказам покупателей, так и в виде корректировки — только на продукцию, которую нужно довыпустить.

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

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

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

Моментом готовности продукции хлебопечения считается время ее передачи из производства на склад. Для учета передаваемых из производства на склад готовых изделий применяют Паспорт на готовую продукцию (форма № П-8 хлеб или разработанная специалистами предприятия с учетом требований действующего законодательства).

К СВЕДЕНИЮ

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

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

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

Важный момент: План-заказ и Паспорт на готовую продукцию — два главных оперативных документа. На их основе оптимизируют учет выпуска продукции в Excel.

ЖУРНАЛ ПЛАН-ЗАКАЗОВ

Начинают с организации Журналов. Надлежаще оформленные план-заказы экономист вносит в Журнал план-заказов (табл. 1). Форма Журнала лаконичная, трудоемкость его ведения в Excel ниже, чем в бумажной книге. По таблице 1 можно проследить, как внесен представленный выше План-заказ № 58.

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

• графа «№ план-заказа» должна быть в Журнале первой. Контролируют последовательную цифровую нумерацию. Это важные условия для того, чтобы в дальнейшем для получения данных из Журнала использовать функцию «ВПР». Функция ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

По умолчанию таблица должна быть отсортирована по возрастанию. Если два обозначенных выше условия соблюсти невозможно, вместо функции «ВПР» используют комбинацию функций «ИНДЕКС» и «ПОИСКПОЗ»;

• графы «Рецептура» и «Цех» заполняют с помощью выпадающего списка с заранее подготовленными справочниками (рис. 2). Каждый справочник отформатирован как умная таблица (вкладка Главная → Форматировать как таблицу). Таблицам-справочникам присвоены имена. Далее вкладка Данные → Работа с данными → Проверка данных → тип данных Список. В поле Источник указывают диапазон ячеек соответствующего справочника или функцию «ДВССЫЛ», где в качестве имени таблицы указывают название столбца (рис. 3). Важное условие: название столбца Журнала и имя таблицы-справочника должны точно совпадать;

• для графы «Дата сдачи план» и графы «Время сдачи» задают формат ячеек Дата и Время соответственно;
• Журнал План-заказов форматируют как умную таблицу;
• добавляют вспомогательные столбцы (табл. 2), которые содержат введенные данные табл. 1. Их в рабочем режиме скрывают. Вспомогательные столбцы нужны для построения сводных таблиц на основании Журнала план-заказов. В сводных таблицах текстовые значения по умолчанию выводятся в разные строки.

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

Чтобы получить содержимое табл. 2, использована сцепка: =[@[№ План-Заказа]]&" # "&[@Рецептура]&", "&[@[Ед.изм.]]. Для удобства чтения добавлен разделитель # и пробелы (в кавычках).

Журнал план-заказов оптимален для оперативного контроля производства (см. рис. 3). С помощью выключателя «Срочные заказы» визуализированы план-заказы со сроком сдачи сегодня (в нашем примере — 11.06.2019). Это план-заказы № 67–72.

Чтобы вставить выключатель, нужна вкладка Разработчик. Если Разработчика в ленте нет, то выполняют команду: Файл → Параметры → Настройка ленты. В перечне «Основные вкладки» напротив пункта «Разработчик» проставляют галочку.

После появления вкладки Разработчик вставляют флажок оперативной подсветки: вкладка Разработчик → Элементы управления → Вставить → Элементы управления формы → Флажок (рис. 4). Размещают флажок в удобном месте интерфейса. В нашем случае — возле текущей даты (ячейка D1). Задают размеры выключателя и изменяют его название правой кнопкой мыши: Изменить текст → Срочные заказы.

Чтобы флажок включал и выключал визуализацию, его связывают с любой ячейкой на листе. Выбирают в контекстном меню элемента команду Формат объекта, в открывшемся окне задают ячейку (любую) в поле «Связь с ячейкой» — G1.

В связанную ячейку G1 должно выводиться значение ИСТИНА, когда флажок включен (см. рис. 4), или ЛОЖЬ, когда выключен.

Чтобы автоматически выделять цветом всю строку Журнала с нужным план-заказом в зависимости от текущей даты, выделяют столбцы A–I табл. 1 (см. рис. 4) без шапки и задают условное форматирование: вкладка Главная → Условное форматирование → Правила выделения ячеек → Другие правила. В диалоговом окне «Создание правила форматирования» выбирают пункт «Использовать формулу для определения форматируемых ячеек».

Формулой задают условие: если плановая дата сдачи продукции равна текущей дате, Exсel нужно автоматически выделить ячейки зеленой заливкой (рис. 5). Обратите внимание, что ячейка «Дата сдачи план» закреплена наполовину жестко ($Н4) — столбец зафиксировали, а строка свободна. В ячейке с текущей датой абсолютное закрепление ($D$1).

Далее применительно к нужному диапазону табл. 1 в Диспетчере правил условного форматирования делают клик по пункту «Создать правило». Выбирают тип правила «Использовать формулу для определения форматируемых ячеек», вводят формулу: =$J$1<>ИСТИНА. Формат не задают и нажимают ОК.

Созданное правило поднимают на первую строку стрелками (если его там нет) и включают напротив него флажок «Остановить, если истина». В результате Диспетчер правил условного форматирования должен выглядеть, как на рис. 6.

Включенный выключатель «Срочные заказы» на рис. 3 показывает, что сегодня (11.06.2019) в 4:00 должны были сдать на склад и отправить покупателям заказы № 68-71 хлебного цеха и заказ № 67 на 10 тортов Пражских кондитерского цеха. Специалист по оперативному контролю выясняет, так ли это на самом деле и выполнены ли в полном объеме плановые заказы.

Выполнение план-заказов важно контролировать по следующим причинам:

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

ОПЕРАТИВНЫЕ РЕЕСТРЫ

Подсветка срочных заказов автоматически изменится в соответствии с новой датой, если указать другую дату в ячейке D1 (см. рис. 3). Однако выделения срочных заказов недостаточно, спектр задач экономической службы более широкий.

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

Вопрос № 1

Какую продукцию будут изготавливать цеха предприятия 10.06.2019? Ответ — в табл. 3. Если предприятие небольшое или испытывает дефицит ликвидности, сгруппированный в табл. 3 план запуска продукции поможет выяснить, достаточно ли сырья на складе для производства, и проконтролировать своевременность доставки эксклюзивных ингредиентов (например, семена льна и тмин для хлеба Диетического на 500 буханок). Это позволит принять оперативное решение: закупить сырье у другого поставщика по более высоким ценам, чтобы не терять заказы, или отказаться от выпуска продукции, ускорить доставку сырья другим способом.

Вопрос № 2

Каков объем работ на вторую смену 11.06.2019? Ответ — в табл. 4. Планируется изготовление трех видов тортов. В соответствии с разработанными на предприятии нормативами для выполнения план-заказов № 74–76 в смену нужно пять кондитеров. Согласно графику во вторую смену работают только четыре человека, поэтому дополнительно вызывают работника с выходного.

Вопрос № 3

Каков объем подтвержденных заказов на новую продукцию — торт «Грибная поляна» и хлеб Цельнозерновой? Торт «Грибная поляна»: за период 01.06–15.06.2019 получены заказы на 22 торта (табл. 5). Показатель оценивают исходя из плана вывода новой рецептуры на рынок, доли в объеме продаж компании, отзывов от покупателей. Показатель в 22 единицы признан успешным.

Объем заказов на хлеб Цельнозерновой намного ниже ожидаемого — всего 450 буханок при плане в 1200 (табл. 6). Необходима активизация усилий со стороны торгового отдела.

Чтобы оперативно получать ответы на перечисленные и другие актуальные вопросы, на основе Журнала план-заказов сформирован интерактивный интерфейс (рис. 7). Применены сводные таблицы (вкладка Вставка → Таблицы → Сводная таблица) и срезы. Список полей сводной таблицы (реестров):

• названия столбцов — Цех, Дата запуска;
• названия строк — «Заказ + Рецептура + Ед. изм.» (вспомогательный столбец табл. 2) и Дата запуска;
• значения — объем (натуральные единицы).

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