Категории каталога
Каталог / Бизнес / Автоматизация предприятий / Microsoft / Разработка модуля для анализа деятельности подразделения средствами MS Excel

Разработка модуля для анализа деятельности подразделения средствами MS 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(заплата с начислениями плюс все остальные затраты, за исключениемзатрат на реализацию продукции, и минус затраты по дойному стаду).
Группировка затрат на условно-постоянные и условно-переменные позволяетпри желании построить бухгалтерскую модель безубыточности средствами MSExcel, что расширяет аналитические возможности разрабатываемого модуля.Однако этот вопрос находится за рамками данной статьи.
Таблица для суммирования первичных данных
Разработка таблицы для суммирования первичных данных с начала года производится в следующем порядке.
Производим копирование части разработанной таблицы (диапазон В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;"Ошибка!";(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)).
Расчёты прибыли и рентабельности рассмотрены выше. Расчёт этихпоказателей в данном случае аналогичен предыдущему расчёту.
 
(Продолжение следует)
Материал предоставлен: Клерк.РУ

Реклама:
Где заказать рерайтинг текстов узнай на сайте eTXT.ru