Автоматизация учета

Разработка модуля для анализа деятельности подразделения средствами MS Excel

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

Геннадий КОМАРОВ

В данной статье продемонстрирована технология разработки таблиц средней сложности для практического применения, доступная для специалиста, имеющего опыт работы с Microsoft Excel. В качестве объекта разработки принят анализ производственно-экономической деятельности молочно-товарной фермы. Вообще же, таким объектом может быть любое производственное подразделение в любой области деятельности.
Модуль, будучи хорошо продуманным, представляет собой удобное средство для анализа в течение года. Время выполнения приведённого анализа с помощью разработанного модуля сокращается до 10-15 минут - против 3-5 часов при обычном способе расчётов.
Во избежание случайного стирания формул все листы защищены автором без установки пароля, что позволяет раскрывать и изменять формулы при корректировке модуля.

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

Все данные, приведённые в разработке для демонстрации действия модуля, являются условными и не относятся к конкретному предприятию.
Чтобы принять правильное управленческое решение, необходимо знать реальное состояние производства. Его можно выявить при выполнении систематического анализа.
Бухгалтерский учёт, проводимый в соответствии с установленными правилами (ПБУ), в литературе называется финансовым учётом. Данные финансового учёта представляются в налоговые и статистические органы. Он отражает свершившийся факт и в силу этого мало пригоден для принятия оперативных управленческих решений. Информация финансового учёта, как правило, общедоступна и является основной характеристикой деятельности предприятия.
Кроме финансового учёта на многих предприятиях для служебного пользования параллельно ведётся учёт управленческий (как бы двойная бухгалтерия в хорошем смысле слова). Управленческий учёт должен давать необходимую информацию для оперативных анализов, результаты которых являются основой для принятия конкретных управленческих решений.
Оперативный анализ должен выполняться регулярно, с установленным интервалом времени, который может равняться месяцу, декаде или суткам. Всё зависит от характера производства и возможности сбора первичной информации.
Выполнение такого анализа традиционным способом может в силу трудоёмкости запаздывать к моменту принятия определённых решений, что снижает их ценность. Для небольших и небогатых сельскохозяйственных предприятий в качестве наиболее гибкого и подходящего средства для выполнения подобной работы вполне пригодны табличные процессоры, в частности Microsoft Excel.
В данной статье довольно подробно рассматривается технология разработки модуля (формы) для выполнения оперативного анализа. Для практической разработки подобного модуля необходимы навыки работы с Microsoft Excel и знания методов расчёта показателей анализируемого объекта.
В качестве объекта разработки формы для анализа принята молочно-товарная ферма с обычной технологией содержания коров, молодняка и скота на откорме. Анализируются выход продукции и издержки производства путём сопоставления соответствующих плановых (нормативных) параметров и фактических показателей предыдущего года по форме, представленной в таблице.
Молочно-товарная ферма производит молоко, приплод, прирост живой массы и побочную продукцию - органическое удобрение (навоз). Товарной продукцией являются молоко и мясо.
Разработку модуля для анализа любого производственного подразделения (объекта) целесообразно проводить в указанной ниже последовательности.

Наименование Ед. Факт План Факт В том числе Отклонение от %от
показателя изм. 2003 г. 2004 г. 2004 г. за июль факта плана факта плана
 
Перечень показателей
Речь идёт о перечне показателей, по которым будет производиться анализ деятельности производственного подразделения. Он может быть самым разнообразным в зависимости от целей проводимого анализа. В предлагаемом примере возможный перечень показателей, характеризующих производственно-экономическую деятельность, приведён полностью. Все они сведены в три группы - А, Б, В - и показаны ниже.
А. Общие показатели по молочно-товарной ферме:
 -  выручка от реализации продукции (без НДС);
 -  себестоимость реализованной продукции;
 -  результат от реализации продукции;
 -  рентабельность реализации продукции;
 -  доля затрат на оплату труда от выручки;
 -  средняя численность рабочих;
 -  среднемесячная заработная плата;
 -  средняя оплата 1 чел.-ч.;
 -  средняя цена одной кормовой единицы.
Б. Показатели по дойному стаду:
 -  среднее поголовье коров;
 -  удой на среднюю (фуражную) корову;
 -  валовое производство молока;
 -  получено телят (приплода);
 -  реализовано молока в физическом весе;
 -  расход молока на кормление (выпойку) телёнка;
 -  товарность молока;
 -  жирность реализованного молока;
 -  затраты труда по дойному стаду - всего;
 -  затраты по дойному стаду - всего, в том числе:
а) зарплата с отчислениями;
б) стоимость кормов;
в) содержание основных средств;
г) энергоснабжение, водоснабжение;
д) работы и услуги;
е) ветеринарные препараты;
ж) прочие затраты;
з) организация производства и управление;
 -  расход кормов на 1 ц молока;
 -  затраты труда на 1 ц молока;
 -  себестоимость производства одного литра молока, в том числе по пунктам а-з (см. выше);
 -  выручка от реализации молока (без НДС);
 -  себестоимость реализованного молока - всего, в том числе по пунктам а-з (см. выше) и затраты на реализацию;
 -  себестоимость 1 л реализованного молока;
 -  средняя цена 1 л реализованного молока;
 -  результат от реализации молока;
 -  рентабельность реализации молока.
В. Показатели по молодняку на выращивании и откорме:
 -  среднее поголовье молодняка;
 -  среднесуточный прирост живой массы;
 -  валовой прирост живой массы;
 -  реализовано на мясо в живой массе;
 -  затраты труда на прирост - всего;
 -  затраты по молодняку - всего, в том числе по пунктам а-з (см. выше);
 -  расход кормов на 1 ц прироста живой массы;
 -  затраты труда на 1 ц прироста живой массы;
 -  себестоимость 1 кг прироста живой массы, в том числе по пунктам а-з (см. выше);
 -  выручка от реализации мяса (без НДС);
 -  себестоимость реализованного мяса всего;
 -  себестоимость 1 кг реализованного мяса в живой массе;
 -  средняя цена 1 кг реализованного мяса;
 -  результат реализации мяса;
 -  рентабельность реализации мяса.
 
Определение перечня первичных данных
Эти данные нужны для расчёта указанных выше показателей. Определение их перечня производится с учётом методов анализа и характера анализируемого объекта.
Необходимо заметить, что некоторые показатели являются производными и расчёт осуществляется по определённым формулам на основании первичных данных; другие показатели образуются прямой подстановкой первичных данных. Сами данные могут быть постоянными на год и более (константы) и вводимыми за каждый отчётный месяц. Константы вводятся в таблицу на листе Реквизиты (рис. 1). К этим постоянным данным относятся:
 -  отчётный год;
 -  годовой фонд рабочего времени;
 -  живая масса поголовья на начало отчётного года;
 -  стоимость поголовья на начало отчётного года;
 -  живая масса телёнка при его рождении;
 -  базисная жирность молока;
 -  годовая сумма амортизации основных средств производства;
 -  норма затрат на организацию управления производством, % от фонда оплаты труда;
 -  доля производственных затрат по ферме, относимых на дойное стадо;
 -  доля производственных затрат по дойному стаду, относимых на приплод;
 -  норма выхода побочной продукции (навоза) от одной коровы в год;
 -  норма выхода побочной продукции (навоза) от одной головы молодняка в год;
 -  средняя цена реализации побочной продукции (навоза);
 -  процент отчислений от фонда заработной платы на социальные нужды.
Приводимый ниже перечень исходных данных, вводимых за каждый отчётный месяц, является достаточным для расчёта всех показателей, которыми являются:
 -  количество календарных дней (дни);
 -  количество кормо-дней (дней содержания) коров, кормо-дни;
 -  количество кормо-дней (дней содержания) молодняка, кормо-дни;
 -  валовой надой молока, кг;
 -  реализация молока в физической массе, кг;
 -  расход молока на выпойку телят - всего, кг;
 -  прочий расход - выдача в счёт зарплаты, кг;
 -  получено приплода (телят), гол.;
 -  валовой прирост живой массы, кг;
 -  зачётная масса реализованного молока, кг, в том числе:
- 1-м сортом, охлаждённое;
- 2-м сортом, неохлаждённое;
- 2-м сортом;
- несортовое;
 -  реализация мяса в живой массе - всего, кг;
 -  реализация поголовья в живом виде, кг;
 -  выручка от реализации молока, руб.;
 -  выручка от реализации мяса, руб.;
 -  выручка от реализации поголовья, руб.;
 -  выручка от реализации прочей продукции (навоза), руб.;
 -  расход кормов по дойному стаду, корм. ед.;
 -  расход кормов по молодняку, корм. ед.;
 -  затраты труда по ферме - всего, чел.-ч.;
 -  заработная плата работников фермы - всего, руб.;
 -  стоимость кормов - всего, руб.;
 -  затраты на ремонт основных средств, руб.;
 -  затраты на электроснабжение, руб.;
 -  затраты на теплоснабжение, руб.;
 -  затраты на водоснабжение и канализацию,
руб.;
 -  автотранспортные услуги, руб.;
 -  тракторные услуги, руб.;
 -  прочие услуги, руб.;
 -  затраты на средства защиты животных, руб.;
 -  прочие затраты, руб.;
 -  затраты на реализацию продукции, руб.;
 -  амортизация основных средств, руб.;
 -  затраты на организацию и управление, руб.
 
Разработка основной базовой таблицы (лист База3)
Это наиболее сложная составляющая разрабатываемого модуля. По выполняемым функциям она состоит из следующих частей:
 -  области ручного ввода данных за отчётный месяц;
 -  области суммирования отчётных данных с начала года;
 -  вывода всех данных (за месяц и с начала года) за определённый месяц;
 -  расчёта всех показателей по данным за определённый месяц;
 -  области постоянных данных (констант);
 -  таблицы для выполнения вспомогательных расчётов за определённый месяц.
Область ручного ввода данных за отчётный месяц расположена в диапазоне ячеек D19:О52 Все данные, за исключением количества календарных дней, амортизации основных средств и затрат на организацию и управление производством, вводятся вручную из первичных ежемесячных отчётов по молочно-товарной ферме.
Количество календарных дней по месяцам, за исключением февраля, не меняется по годам. В Е18 для февраля записана формула ЕСЛИ(ИЛИ (W13=2004;ИЛИ(W13=2008;W13=2012));29;28), в которой применяется вложение функций ЕСЛИ(ИЛИ(ИЛИ))). Применение этой формулы избавит от необходимости производить корректировку количества календарных дней в феврале високосного года. Если в W13 записано значение "2004", или "2008", или "2012" (високосные годы), то в Е18 выводится значение "29" (дней), в противном случае - "28" (дней). Значение года в W13 передаётся из листа Реквизиты (формула РЕКВИЗИТЫ!G18).
Амортизация основных средств и затраты на организацию и управление производством производятся автоматически по установленным нормативам.
Сумму амортизационных отчислений целесообразнее определить по ферме на целый год и распределять её равномерно по месяцам. Например, расчёт амортизации основных средств (для января) производится по формуле (ячейка В53): ЕСЛИ(СУММ(D41:D52)=0;0;$W$20/12).
Одна двенадцатая часть годовой суммы амортизации вводится только в том случае, если введены остальные затраты по данному месяцу; в противном случае (сумма затрат за текущий месяц равна нулю) вводится значение ноль. Сумма годовой амортизации в W20 (таблица "Константы для расчёта показателей") передаётся из листа Реквизиты. В формуле ячейка $W$20 имеет абсолютную адресацию, что позволяет скопировать формулу без корректировок на последующие месяцы (E53, F53 и т. д.). Все формулы, рассматриваемые ниже, приведены для января в виде, который позволяет копировать их на последующие месяцы без корректировки.
Затраты на организацию и управление производством (накладные расходы) распределяются в конце года по производственным объектам. Для анализа можно установить норматив на эти затраты в процентах от фонда заработной платы. Это позволит ежемесячно выводить результат деятельности молочно-товарной фермы.
Распределение затрат на организацию и управление производством осуществляется по формуле (например, для января - ячейка D54): $W$21*D41.
Автоматический ввод амортизации основных средств и затрат на организацию и управление производством по установленным нормативам снижает количество данных, вводимых ручным способом при выполнении текущего анализа.
Таким образом, диапазон (D19:O52) служит для ручного ввода данных за отчётный месяц. На небольших и средних фермах все затраты, за исключением расхода кормов, обычно учитываются в целом по ферме. Для их распределения, как правило, устанавливается процент отнесения общих затрат на дойное стадо и на молодняк расчётным путём. Для построения и анализа бухгалтерской модели безубыточности (в данной статье этот вопрос не рассматривается) необходимо разделить эти затраты на условно-постоянные (не зависящие от объёма производства и реализации продукции) и условно-переменные (зависящие от объёма производства и реализации продукции).
Все эти вычисления производятся для каждого месяца, и результаты размещаются ниже диапазона ячеек для ручного ввода данных.
Затраты по дойному стаду записаны по строке 55. Для января в D55 записана с виду довольно сложная формула: ЕСЛИ((D38+D39)=0;0; D42/(D38+D39)*D38+(D41*(1+$W$27)+СУММ (D43:D53;D53:D54))*$W$22).
Её анализ полезен для понимания одной из важнейших составляющих успешного применения электронных таблиц на практике - разработки алгоритма решения задачи (составления формул).
Выражение, стоящее в формуле D42/(D38+D39), определяет стоимость одной кормовой единицы. В D42 находится значение стоимости израсходованных кормов по ферме, в D38 - расход кормов (в кормовых единицах) по дойному стаду и в D39 - расход кормов по группе молодняка крупного рогатого скота. Далее стоимость одной кормовой единицы умножается на расход кормов (в кормовых единицах) по дойному стаду - D42/(D38+D39)*D38. Таким образом определяется стоимость израсходованных кормов по дойному стаду.
Выражение (D41*(1+$W$27)+СУММ(D43:D54))* $W$22 определяет затраты на оплату труда с начислениями ((D41*(1+$W$27)) плюс остальные затраты (СУММ(D33:D51;D53:D54). Общая сумма затрат умножается на долю затрат дойного стада ($W$22).
Таким образом, затраты по дойному стаду определяются выражением D42/(D38+D39)*В38+(D41* (1+$W$27)+СУММ(D43:D51;D53:D54)*$W$22.
Ячейка W22, в которой имеется значение процента распределения затрат на дойное стадо, имеет абсолютную адресацию, что позволяет копировать формулу на последующие месяцы без её корректировки.
Применение функции ЕСЛИ() в данной формуле вызвано следующим обстоятельством (оно очень часто будет встречаться в ходе работы). Если в формуле встречается действие деления, то может возникнуть ситуация, когда знаменатель будет равен нулю. Как известно, в математике деление на нуль невозможно, поэтому компьютер выдаёт сообщение об ошибке #ДЕЛ/0!. Чтобы избежать этого, во всех формулах, где производится деление и знаменатель является ссылкой на какую-либо ячейку или выражение, применяется функция ЕСЛИ(), действие которой сводится к следующему выражению: ЕСЛИ знаменатель равен НУЛЮ, то вывести НУЛЬ, в противном случае произвести ДЕЛЕНИЕ.
Рассмотрим формулу, которая производит выборку условно-постоянных затрат для дойного стада. Для января в D56 записана формула: СУММЕСЛИ($A$43:$A$54;$A$16;D43:D54)*$W$22.
В данном случае применяется функция СУММЕСЛИ() выборочного суммирования. Синтаксис этой функции таков: СУММЕСЛИ(диапазон; условие;диапазон_сумма).
Диапазон, в котором указан критерий (признак) выборки для суммирования, представлен как А43:А54. Этот диапазон охватывает те статьи затрат, которые могут быть условно-постоянными. Сюда можно отнести их все, за исключением затрат на оплату труда и кормов. В этот диапазон ячеек вводится знак (критерий), который определяет порядок выборочного суммирования. В качестве критерия выбран (произвольно) знак u. В Microsoft Excel нет возможности выбора символов, так что можно воспользоваться возможностями Microsoft Word. Командой Вставка њ Символ њ Вставить раскрываем карту символов, выбираем понравившийся и вставляем его на произвольное место пустой страницы (рис. 3). Затем производим копирование из страницы Word в разрабатываемую таблицу. Обычно в самой таблице, где-нибудь в заголовке, всегда можно найти пустую ячейку для подобной вставки. Чтобы символ не выделялся, его можно сделать невидимым. Для этого цвет шрифта должен сливаться с фоном, т. е. быть идентичным ему.
Далее в диапазоне ячеек А43:А54 командой Данные њ Проверка... њ Список организуем ввод критерия (знака) по списку. Список - это диапазон ячеек с заранее введёнными данными для последующего ввода. В нашем случае список будет включать в себя ячейки А16 (с выбранным знаком u) и А17 (пустая). При такой организации ввода можно простым действием вставлять или убирать этот знак против наименования условно-постоянной статьи затрат.
Амортизационные отчисления и затраты на организацию и управление производством явно относятся к условно-постоянным затратам. Поэтому в ячейки А53 и А54 этот знак необходимо установить простым копированием. Конечно, вместо выбранного нами знака можно применить любой другой символ, например букву или цифру, что несколько проще. Однако использование знака u более наглядно.
Условие является следующим аргументом функции СУММЕСЛИ() выборочного суммирования. В данном случае условием является знак u, расположенный в А16 (ссылка на А16).
Диапазон_сумма является третьим аргументом функции СУММЕСЛИ(). Это диапазон, в котором производится выборочное суммирование по строкам, отмеченным знаком (условием). Для данных за январь это будет диапазон D43:54.
Наконец, выборочная сумма умножается на процент распределения затрат на дойное стадо ($W$22).
Общие затраты по молодняку определяются как разница между общими затратами по ферме и затратами на дойное стадо. Например, формула для января (D58) имеет вид: (D41*(1+$W$27)+СУММ (D42:D51;D53:D54))-D55 (заплата с начислениями плюс все остальные затраты, за исключением затрат на реализацию продукции, и минус затраты по дойному стаду).
Группировка затрат на условно-постоянные и условно-переменные позволяет при желании построить бухгалтерскую модель безубыточности средствами MS Excel, что расширяет аналитические возможности разрабатываемого модуля. Однако этот вопрос находится за рамками данной статьи.
Таблица для суммирования первичных данных
Разработка таблицы для суммирования первичных данных с начала года производится в следующем порядке.
Производим копирование части разработанной таблицы (диапазон В18:О60) на В61.
Затем в диапазоне D61:O97 вводим формулу суммирования с начала года. Например, в D61 запишем формулу: СУММ ($D$18:D18). При копировании этой формулы по месяцам диапазон суммирования будет последовательно изменяться: ($D$18:D18);($D$18:E18);($D$18:F18)..... ($D18:O18) - суммирование по месяцам с начала года.
По каждой строчке показателей введём аналогичные формулы.
Созданная таблица (база) состоит из двух частей. Первая часть служит для ввода первичных данных по месяцам - диапазон D19:O52, вторая (остальная) часть - для суммирования данных с начала года и прочих вычислений.
Так как первая часть таблицы предназначена для ввода данных, с ячеек, входящих в этот диапазон, должна быть снята защита. Вся остальная часть таблицы, содержащая различные формулы, должна быть защищена. Для этого необходимо командой Формат њ Ячейка њ Защита установить для ячейки (диапазона ячеек) флажок защиты. Она включается командой Сервис њ Защита њ Защитить лист (рис. 4).
Для первой части таблицы пригодится следующий приём, который снизит трудоёмкость ввода данных. Например, прочий расход молока можно не вводить, а в соответствующую ячейку (D24) записать формулу ЕСЛИ ((D21- (D22+D23)) Если не сходится баланс по молоку (производство - продажа - на корм телятам - прочий расход = 0), то выдаётся сообщение "Ошибка!", в противном случае выводится остаток (прочий расход). Аналогичный подход применён для ввода количества молока, реализованного как несортовое. Естественно, ячейки с формулами в этой части таблицы также должны быть защищены.
Для облегчения ввода столбик для ввода первичных данных по отчётному месяцу можно выделить цветом. Например, для диапазона D19:O52 выполнено условное форматирование командой Формат њ Условное форматирование... њ Формула и т. д. Формула, определяющая условное форматирование (цвет фона ячейки и шрифта), для D19 имеет вид: D$16=$W$14. При копировании формулы на последующие ячейки указанного диапазона происходит изменение (модификация) номера столбца. При совпадении порядкового номера отчётного месяца ($W$14) с порядковым номером, который установлен в диапазоне D16:O16, происходит выделение столбца установленным цветом.
Условное форматирование применяется для выделения цветом условно-постоянных затрат.
Следующим этапом разработки этой таблицы является выборка данных за месяц и с начала года для каждого месяца. Например, в листе Отчёт по установленному месяцу (апрель) из нашей таблицы должен быть выделен в отдельное место столбец данных за месяц и с начала года, соответствующий этому месяцу. В дальнейшем эти данные будут использованы для расчёта показателей анализа. Работу можно выполнить в следующей последовательности.
Диапазону D16:O103, в котором содержатся все данные за месяц и с начала года, командой Вставка њ Имя њ Присвоить даём имя База3.
Диапазон Q16:S103 определяем для размещения блока выборки. Для удобства дальнейшей работы производим копирование наименования показателей с диапазона B16:B103 на R16:R103. Установим необходимую ширину столбца R по длине размещаемых наименований. Для переноса наименований со столбца B в столбец R можно применить метод ссылок на соответствующие ячейки. Например, в R16 записать формулу (=В1) и скопировать её вниз на необходимое количество ячеек (клеток).
В диапазоне Q16:Q103 вводим нумерацию строк в блоке База3. Например, показатели "Порядковый номер месяца", "Наименование месяца", "Число календарных дней" и т. д. в блоке База3 расположены в 1-й, 2-й, 3-й и т. д. строчках соответственно. Ускорить ввод нумерации строк можно следующим образом. В Q16 и Q17 нужно ввести числа 1 и 2. Далее - активизировать эти две ячейки, установить курсор на чёрный квадратик, расположенный в правом нижнем углу ячейки Q17 и, не отпуская кнопку, протянуть курсор на необходимое количество ячеек вниз.
До начала разработки всей таблицы необходимо выделить блок ячеек для размещения констант, которые передаются из листа Реквизиты. Константы необходимы для расчётов, выполняемых на данном листе. Блок с константами (таблица "Константы для расчёта показателей") размещён в диапазоне V13:W27.
В S16 введём формулу ГПР($W$14;База3;Q16) и скопируем её вниз на необходимое количество ячеек.
Действие функции ГПР() заключается в следующем. Она просматривает верхнюю строчку блока База3, в которой расположены порядковые номера месяцев, сравнивает их с установленным (в листе Реквизит) порядковым номером. При их равенстве выводится значение соответствующего столбца и указанной в функции строки. Синтаксис функции ГПР: искомое значение;таблица;номер строки.
Искомое значение - это значение, которое требуется найти в первой строке (в данном случае - порядковый номер месяца).
Таблица - это блок ячеек (под именем База3), в котором производится поиск данных.
Номер строки таблицы, данные из которой выводятся в случае успешного поиска искомого значения, - это ссылка на ячейку (Q16), в которой находятся номера строк блока База3.
Чтобы в случае отсутствия искомого значения функция не выводила сообщение #Н/Д (нет данных), вышеприведённую формулу можно видоизменить: ЕСЛИ($W$14=0;0;ГПР($W$14;База3; Q16).
Если искомое значение ($W$14) равно нулю, то надо выводить нуль, в противном случае - произвести поиск и вывести найденное значение.
 
Блок расчёта показателей
Следующим этапом является разработка блока для расчёта показателей. Этот блок включает в себя следующие диапазоны:
 -  V29:Y40 - расчёт общих по ферме показателей за отчётный месяц и с начала года;
 -  V43:Y89 - расчёт показателей по дойному стаду;
 -  V92:Y125 - расчёт показателей по молодняку;
 -  АА49:AI66 - распределение затрат за минусом стоимости побочной продукции (навоза) на молоко, приплод и прирост живой массы за отчётный месяц;
 -  АК49:AS66 - распределение затрат за минусом стоимости побочной продукции (навоза) на молоко, приплод и прирост живой массы за отчётный период (с начала года);
 -  АА70:AG77 - расчёт себестоимости реализационного мяса на отчётный месяц и с начала года.
Все расчёты выполняются на основании данных, полученных из диапазона S16:S103 (блок выборки данных на отчётный месяц).
Для краткости рассмотрим расчёт показателей за отчётный месяц. Формулы для расчёта показателей с начала года аналогичны.
А. Общие показатели по молочно-товарной ферме
1. Выручка от реализации продукции (без НДС). Суммируется выручка от продажи молока, мяса, поголовья скота и прочей продукции (навоза). Расчётная формула (ячейка Х32): СУММ(S34:S37)/1000.
2. Себестоимость реализованной продукции - сумма себестоимости реализованного молока и мяса. Расчётная формула (ячейка Х33): Х76+Х121.
3. Результат от реализации продукции - разница между выручкой и себестоимостью реализованной продукции (прибыль или убыток). Расчётная формула (ячейка Х34): Х32-Х33.
4. Рентабельность реализованной продукции - деление результата от реализации продукции на себестоимость реализованной продукции. Расчётная формула (ячейка Х35): ЕСЛИ(Х33=0;0; Х34/Х33). Функция ЕСЛИ() применена, чтобы избежать деления на нуль. В дальнейшем при анализе формул объяснение применения функции ЕСЛИ() для этой цели опускается.
5. Доля затрат на оплату труда - деление затрат на оплату труда с начислениями на общую сумму выручки. Расчётная формула (ячейка Х36): ЕСЛИ(СУММ(S34:S37)=0;0;S41*(1+$W$27)/СУММ (S34:S37)).
Выражение S41*(1+$W$27) вычисляет затраты на оплату труда с начислениями.
Выражение СУММ(S34:S37) суммирует выручку от реализации молока, мяса, поголовья скота и побочной продукции.
6. Средняя численность рабочих определяется для отчётного месяца и с начала года по различным формулам.
Для отчётного месяца - деление затрат труда на среднемесячный фонд рабочего времени. Последний определяется делением годового фонда рабочего времени на 12 месяцев. Среднесписочная численность работников определяется по формуле (ячейка Х37): ЕСЛИ($W$15=0;0; S40/$W$15/12).
Для периода с начала года результат, полученный при расчёте по вышеуказанной формуле, необходимо умножить на количество месяцев с начала года (на порядковый номер месяца). Формула для расчёта средней численности работников с начала года имеет следующий вид: ЕСЛИ (($W$15/ 12*$W$14)=);0;S83/$W$15/12*$W$14).
7. Среднемесячная заработная плата определяется умножением месячного фонда рабочего времени на 1 чел.-ч. Расчётная формула (ячейка Х38): $W$15/12*X39.
8. Оплата 1 чел.-ч определяется делением фонда заработной платы (без начислений) на затраты труда в человеко-часах. Расчётная формула (ячейка Х39): ЕСЛИ(S40=0;0;S41/S40).
9. Средняя цена одной кормовой единицы определяется делением стоимости корма на расход кормов (в кормовых единицах) по группе дойного стада и молодняка крупного рогатого скота. Расчётная формула (ячейка Х40): ЕСЛИ((S38+S39)=0;0; S42/(S38+S39)).
Б. Показатели по дойному стаду
1. Среднее поголовье коров определяется делением количества кормо-дней на календарное число дней. Расчётная формула (ячейка Х46): ЕСЛИ(S18=0;0;S19/S18).
2. Удой на среднюю фуражную корову определяется делением валового надоя молока на количество кормо-дней и умножением на количество календарных дней. Расчётная формула (ячейка Х47): ЕСЛИ(S19=0;0;S21/S19*S18).
3. Валовой надой молока (в ценах) определяется по формуле (ссылка на ячейку): S21/100.
4. Количество полученного приплода (голов) определяется по формуле (ссылка на ячейку): S25.
5. Реализация молока в физическом весе определяется как сумма молока, реализованного перерабатывающему предприятию, и молока, выданного в счёт натуральной оплаты и прочей реализации. Расчётная формула (ячейка Х50): (S22+S24)/100.
6. Расход молока на выпойку одного телёнка определяется делением количества молока, израсходованного на выпойку телят, на поголовье телят. Расчётная формула (ячейка Х51): ЕСЛИ(S25=0;0;S23/S25).
7. Товарность молока определяется делением количества реализованного молока на валовой объём производства. Расчётная формула (ячейка Х52): ЕСЛИ (S21=0;0;(S22+S24)/S21).
8. Жирность молока вычисляется делением зачётного веса реализованного молока на физический вес реализованного молока и умножением на базисную жирность молока. Расчётная формула (ячейка Х53): ЕСЛИ(S22=0;0;S27/S22*$W$19). На практике жирность молока определяется при реализации перерабатывающим предприятиям (молочным заводам), поэтому расчёт жирности производится для этой части продукции.
Расчёт затрат по молочно-товарной ферме имеет свои особенности. Общие по ферме затраты распределяются на дойное стадо и молодняк. Затем по дойному стаду и молодняку вычисляется выход побочной продукции (навоза) в денежном выражении (стоимость побочной продукции). Стоимость побочной продукции вычитается из общих затрат по дойному стаду и молодняку, после чего производится корректировка всех элементов затрат. Скорректированные затраты по дойному стаду распределяются в пропорции 90 % на молоко и 10 % на приплод (телят). Вся цепочка расчётов выполняется для отчётного месяца в диапазоне АА49:AI66 и с начала года - в диапазоне AK49:AS66.
Все затраты по ферме выводятся в диапазоне АВ56:АВ63.
В АВ56 выводится заработная плата с начислениями. Расчётная формула: S41*(1+$W$27).
В АВ57 выводится стоимость кормов. Расчётная формула (ссылка): S42.
В АВ58 суммируются затраты на содержание основных средств (амортизация и затраты на ремонт). Расчётная формула: S43+S53.
В АВ59 суммируются затраты на энергоснабжение и водоснабжение. Расчётная формула: СУММ(S44:S46).
В АВ60 суммируются работы и услуги. Расчётная формула: СУММ(S47:S49).
В АВ61 выводятся затраты на ветеринарные препараты. Расчётная формула (ссылка): S50.
В АВ62 выводятся прочие затраты (затраты на охрану труда, на приобретение инвентаря и пр.). Расчётная формула (ссылка): S51.
В АВ63 выводятся затраты на организацию и управление производством (накладные расходы). Расчётная формула (ссылка): S54.
Все статьи (элементы) затрат суммируются в АВ55. Расчётная формула: СУММ(АВ56:АВ63).
В АВ54 выводятся затраты труда. Расчётная формула (ссылка): S40.
В диапазоне АС56:АС63 производится выделение затрат на дойное стадо. Все статьи (элементы) затрат, кроме стоимости кормов, по дойному стаду определяются умножением соответствующей статьи по ферме на процент отнесения затрат на дойное стадо ($W$22). Например, заработная плата с начислениями на дойном стаде определяется по формуле АВ56*$W$22, которая размещена в АС56.
Затраты на корм распределяются пропорционально израсходованному корму (в кормовых единицах) по дойному стаду и молодняку. Расчётная формула размещена в АС57 и имеет вид: ЕСЛИ((S38+S39)=0;0;S42/(S38+S39)*S39).
Все затраты по дойному стаду суммируются в АС55 по формуле: СУММ(АС56:АС63).
Стоимость побочной продукции по дойному стаду определяется в ячейке АС64 по формуле: ЕСЛИ(S18=0;0;S19/S18)*$W$24*$W$26/12. Первая часть формулы - ЕСЛИ(S19=0;0;S19/S18) - среднее поголовье (формула использовалась выше). Вторая часть формулы - $W$24*$W$26/12 - определяет нормативную стоимость побочной продукции за месяц умножением нормы выхода навоза от одной головы в год на стоимость навоза и делением на 12 месяцев.
В АС65 вычисляется общая сумма затрат за минусом стоимости побочной продукции по формуле: АС55-АС64.
По молодняку статьи затрат определяются в диапазоне АD56:AD63 как разность между общими затратами и затратами по дойному стаду. Стоимость побочной продукции определяется по аналогичной формуле по нормативам выхода навоза для молодняка.
Далее производится корректировка статей затрат с учётом снижения затрат на стоимость побочной продукции. Все статьи затрат по дойному стаду и молодняку последовательно перемножаются на коэффициент корректировки, который представляет собой частное от деления затрат без стоимости побочной продукции на общие затраты (с учётом побочной продукции). Общая сумма затрат за минусом стоимости побочной продукции по дойному стаду (AF55) и молодняку (АI55) определяется суммированием соответствующих затрат.
По правилам бухгалтерского учёта 10 % затрат по дойному стаду относят на приплод. В соответствии с этим производится распределение затрат по дойному стаду на молоко и приплод. Например, заработная плата на приплод (АН56) определяется по формуле AF56*$W$23, на молоко (AG56) - по формуле AF56-AH56.
Аналогичный расчёт производится с затратами с начала года. Скорректированные и распределённые затраты передаются для расчёта показателей.
9. Затраты труда по дойному стаду. Расчётная формула (ссылка): AF54.
10. Затраты по дойному стаду. Расчётная формула (ссылка): AF55/1000 и т. д. по всем статьям затрат.
11. Расход кормов на производство молока определяется делением кормов, приходящихся на производство молока, на валовое производство молока в центнерах. Расчётная формула: ЕСЛИ(S21=0; 0;S38*(1-$W$23)/(S21/100)).
12. Затраты труда на производство центнера молока определяются аналогичным способом. Расчётная формула: ЕСЛИ(S21=0;0;AG54/(S21/100)).
13. Себестоимость производства литра молока определяется делением затрат на производство молока на валовой объём производства молока. Расчётная формула: ЕСЛИ(S21=0;0;AG55/S21). Статьи (элементы) затрат на производство одного литра молока вычисляются при помощи аналогичной формулы.
14. Выручка от реализации молока. Расчётная формула: S34/1000.
15. Себестоимость реализованного молока определяется суммированием статей (элементов) затрат. Расчётная формула: СУММ(Х77:Х85). Все статьи (элементы) себестоимости, кроме затрат на реализацию, определяются умножением каждой статьи производственных затрат на коэффициент пересчёта себестоимости. Данный коэффициент ($AG$66) - это товарность молока, которая рассчитывалась выше. Например, затраты на оплату труда в себестоимости молока определяются по формуле: $AG$66*AG56/1000. В себестоимость реализованного молока включены реализационные расходы (S52/1000). Для упрощения все реализационные затраты по ферме отнесены на молоко.
16. Себестоимость реализованного молока определена по формуле: ЕСЛИ((S22+S24)=0;0; (AG55*$AG$66+S52)/(S22+S24)).
Из вышесказанного следует, что представленная формула вполне понятна. Производственные затраты умножены на коэффициент пересчёта себестоимости (товарность молока) плюс затраты на реализацию, а полученная сумма поделена на количество реализованного молока.
17. Средняя цена реализации литра молока определяется по формуле: ЕСЛИ((S22+S24)=0;0; S34/ (S22+S24)).
Расчёты прибыли и рентабельности рассмотрены выше. Расчёт этих показателей в данном случае аналогичен предыдущему расчёту.
 
(Продолжение следует)

Начать дискуссию