Автоматизация и моделирование бизнес-процессов в Excel

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

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

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

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

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

Классификация затрат

Классификация издержек по времени отнесения

По отношению к готовому продукту издержки предприятия подразделяются на две группы:

• издержки, относящиеся к периоду времени;
• издержки, относящиеся к готовому продукту.

Издержки первой группы учитываются при расчете прибыли предприятия, в течение периода, когда они были фактически понесены, и, как правило, это непроизводственные издержки.

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

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

Классификация издержек по характеру поведения

Основным признаком классификации издержек является их количественная зависимость в связи с изменением какого-либо показателя, например объема реализации. В соответствии с этим признаком, издержки подразделяют на два вида: постоянные и переменные.

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

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

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

Классификация издержек на прямые и косвенные

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

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

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

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

Методы учета затрат

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

На практике используют два метода:

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

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

Задача создания компьютерной модели

Следующая предстоящая задача — создание компьютерной модели в Excel для исследования поведения затрат некоторого виртуального предприятия. Это компьютерное моделирование позволит в будущем правильно сделать свой выбор системы управления затрат для внедрения ее на предприятии.

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

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

Описание предприятия

В качестве примера будет рассмотрен небольшой виртуальный завод по производству стеклоизделий — АО "Стеклодув". Этот завод имеет заводскую административную дирекцию и два цеха, каждый из которых имеет по две производственные линии выпускающими различную стеклопродукцию.

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

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

• материалы;
• силовую электроэнергию, потребляемую производственными линиями;
• зарплату основным рабочим, вместе с начислениями на нее.

Среди цеховых затрат (рис. 13.4.) выделяются прямые постоянные издержки на амортизацию производственных линий, а также постоянные издержки по:

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

Общезаводские расходы (рис. 13.5.) разделены на:

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

Состав модели

Создаваемой модели присвойте имя Затраты. Книга состоит из 13 рабочих листов, каждый из которых будет рассмотрен далее.

Переменные издержки

Консолидация переменных издержек

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

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

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

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

Рис. 13.1. Группировка переменных издержек по различным моделям чашек с целью получения усредненных данных

Используемые в таблице формулы просты и в основном заключаются в умножении норм переменных издержек на производство единицы продукции на выпускаемую партию определенной модели продукции. Основная задача таблицы — вывести в области ячеек С18:С21 усредненное значение в денежном выражении: цены реализации и необходимых для производства переменных издержек. Для этого суммарная статья переменных затрат, например, по материалам делится на количество выпускаемых производственной линии чашек. Как видите, в зависимости от доли производимой модели чашек в общем объеме выпускаемых чашек, итоговые значения усредненных переменных издержек и усредненная цена реализации усредненной чашки могут изменяться.

Собранные данные с нормами переменных издержек для выпуска продукции

Рабочий лист Нормы (рис. 13.2.) предназначен для ввода данных переменных издержек на единицу продукции, выраженную в денежном выражении, по следующим статьям:

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

В создаваемой модели эти данные представлены как некоторые усредненные константы. На практике это могут быть ссылки на ячейки таблиц, находящихся в других рабочих книгах, содержащие итоговые результаты. Например, в ячейках с указанием значений переменных издержек по чашкам, введены ссылки на соответствующие ячейки листа Чашка (рис. 13.1.). Статей переменных издержек может быть значительно больше. Это не принципиально.

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

В столбец С введите наименования статей переменных затрат, а в столбец Е — нормы их расхода в денежном выражении на одну единицу продукции.

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

Для присвоения имен в столбце D введите текст названия имен, которые будут присваиваться ячейкам. Чтобы не набивать текст будущих имен вручную с клавиатуры, введите формулы, которые помогут выполнить эту работу быстрее. На рис. 13.2. в строке формул показана формула, соединения текста в ячейке D5. Для присвоения имен сразу всем ячейкам, содержащим данные в столбце D, выделите область D4:Е19 и наберите комбинацию клавиш Ctrl+Shift+F3. В появившемся диалоговом окне Создать имена нажмите кнопку ОК. После присвоение имен, столбец D можно удалить или скрыть.

Присвоенные имена

В создаваемой модели будут использовано много имен, и чтобы удобно было работать, на листе Имена (рис. 13.3.) собраны все имена, которые будут использованы в книге Затраты.

Рис. 13.3. Лист Имена с именами используемыми в книге Затраты и адресами ячеек, которым они присвоены

Общецеховые издержки

Рабочий лист Цех (рис. 13.4.) можно условно разделить на три области:

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

Суммы амортизационных отчислений по каждой производственной линии введите в диапазон ячеек D6:D9 рабочего листа Цех. Эти издержки являются прямыми постоянными издержками по каждому виду выпускаемых продуктов.

В нижнюю часть рабочего листа в область ячеек D14:D21 введите постоянные накладные издержки по каждому цеху, наименование которых введено в диапазоне ячеек В14:В21. На практике в этих ячейках могут быть ссылки на итоговые данные таблиц, находящихся в других рабочих книгах, в которых косвенные общецеховые издержки более детализированы. В скрытом столбце С введены имена, которые присвоены ячейкам столбца D.

В правой стороне рабочего листа находятся шесть групп переключателей, объединенных по горизонтали элементом управления Рамка. По горизонтали эти элементы управляют распределением постоянных косвенных общецеховых затрат, наименование которых находится в столбце В и соотносят их по базам, наименование которых указано в строке 12. Описание методики создания групп элементов управления и принципа разнесения общецеховых затрат по различным базам будет описано далее.

Рис. 13.4. Рабочий лист Цех

Общезаводские издержки

Рабочий лист Администрация предназначен для ввода и разнесения косвенных общецеховых издержек. Этот лист отличается от рабочего листа Цех, тем, что с помощью переключателей можно выбрать дополнительно произвольное разнесение общезаводских издержек по выбранной базе распределения (производственной линии). Для этого предназначен правый вертикальный ряд переключателей, при активизации какого-либо из которых, выбранная статья общезаводских издержек распределяется произвольно по производственным линиям в зависимости от введенных в диапазон ячеек I6:L9 пропорций. Для этого в диапазон ячеек I6:L9 вводятся в процентах значение размера распределения статьи. Но необходимо следить, чтобы в одной строке области ячеек I6:L9 сумма введенных соотношений была равна 100%.

Для избежания ошибок по вводу данных на этом листе введите предупреждающие надписи. Например, обнаружение возможной ошибки при произвольном выборе базы распределения постоянных общезаводских издержек, если затраты могут распределяться в размере отличном от 100%. В ячейке В12 формула, которая при обнаружении суммарных отклонений в диапазоне ячеек М6:М9 возвращает текст Нарушены пропорции распределения издержек!, который привлечет внимание пользователя:

=ЕСЛИ(СУММ(M6:M9)=0;0;"Нарушены пропорции распределения издержек!")

Формула же в ячейке М6 анализирует — сумма введенных значений в диапазон ячеек I6:L6 равна 100%:

=ЕСЛИ(СУММ(I6:L6)=0;0;1-СУММ(I6:L6))

Формула в ячейке В11, показанная в строке формул на рис. 13.5. будет описана далее.

Рис. 13.5. Рабочий лист Администрация

Таблица распределение затрат

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

Рис. 13.6. Таблица распределения накладных расходов предприятия по видам продукции на рабочем листе РаспределениеЗатрат

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

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

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

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

В таблицу распределения накладных расходов предприятия по видам продукции осуществляется только ввод данных (рис. 13.6.):

• количество изготавливаемых/реализуемых изделий по каждому виду продукции — в ячейки диапазона D8:G8;
• цена реализации по каждому виду продукции — ячейки диапазона D9:G9

Диапазону ячеек D8:G8 и D9:G9 присвоены имена Объем_реализации и Цена_реализации соответственно, по тексту, введенному в ячейки С8 и С9. Для присвоения имен используется тот же метод присвоения имен диапазону ячеек. Выделяется диапазон ячеек C8:G9 и после набора клавиш Ctrl+Shift+F3, в появившемся диалоговом окне Создать имена (рис. 7.22.) нажмите кнопку ОК. Но применение имен в этой таблице несколько отличается от рассмотренных ранее.

Рассмотрим на примере имени Объем_реализации, которое присвоено одновременно 4-м ячейкам диапазона D8:G8. Обратите внимание — не только области ячеек, а и каждой ячейке в отдельности. Следующий момент, на который следует обратить внимание — горизонтальной и однострочное расположение именованного диапазона. При таком присвоении имени диапазону ячеек указание имени возможно:

• ссылками на именованную ячейку, находящуюся в том же столбце, что и ячейка, из которой будет сделана ссылка. При этом копирование ссылки в другие ячейки того же столбца не изменит возвращаемое этой ссылкой значения, то есть она все равно будет ссылаться на именованную ячейку этого столбца. При копировании ссылки на именованную ячейку в другие столбцы, но не выходящие за пределы таблицы, она будет возвращать значения именованной ячейки, но находящейся в этом же столбце. Таким образом, применение имени в таблице заменяет смешанную ссылка, в которой указана абсолютная ссылка на строку;
• ссылка на именованный диапазон, но с указанием операции проводимой с содержимым ячеек этого диапазона. Например, ссылка =Объем_реализации возвратит значение ошибки, а операция суммирования = СУММ(Объем_реализации), возвратит сумму значений в этом диапазоне ячеек. Указание ссылки на именованный диапазон возможно с любой области листов рабочей книги.

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

Область расчета прямых издержек

Расчет прямых издержек (диапазон ячеек D11:G17), необходимых для выпуска продукции производится в областях:

• расчета переменных издержек на изготовление продукции — диапазон ячеек D12:G15;
• расчета постоянных издержек — D16:G17;
• расчета всех прямых издержек — D11:G11.

Расчет переменных издержек

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

В ячейках D13:G15 находятся однотипные формулы, производящие умножение значения количества объема реализации продукции, содержащегося в ячейках D8:G8 на значение суммы переменных затрат по каждому виду затрат на единицу продукции, введенных на рабочем листе Нормы:

=Объем_реализации*МатериалЧашка
=Объем_реализации*ЗарплатаЧашка
=Объем_реализации*ЭлектроэнергияЧашка

Для минимизации потерь времени на ввод этих формул выполните следующие действия:

выделите диапазон ячеек D13:D15; нажмите клавишу F3 и в появившемся диалоговом окне Вставка имени выберите имя Объем_реализации, по которому дважды клацните правой кнопкой мыши; для одновременного ввода ссылки на ячейку, которой присвоено это имя, во все три выделенные ячейки, нажмите комбинацию клавиш Ctrl+Enter; не перемещая табличного курсора, нажмите клавишу F2 и введите с клавиатуры знак умножения, после чего нажмите клавишу F3 и в появившемся диалоговом окне Вставка имени выберите имя МатериалЧашка, после ввода которого в формулу, нажмите клавишу Enter, что активизирует следующую ячейку; в выделенной ячейке D14 аналогично добавьте вторую половину формулы и так далее.

После заполнения формулами области D13:D15 эта диапазон копируется в область Е13:Е15. Корректировку формул удобно осуществить с помощью диалогового окна Заменить. Для этого, после вставки скопированного диапазона в область Е13:Е15, не перемещая табличного курсора, комбинацией клавиш Ctrl+Н, вызовите диалоговое окно Заменить, и введите в поле Что текст Чашка, а в поле Заменить текст Тарелка и нажмите на кнопку Заменить все. Текст имени второго сомножителя во всех трех ячейках будет автоматически отредактирован, после чего это диалоговое окно можно будет закрыть.

Аналогично изменяются имена и в формулах диапазонов ячеек G13:G15 и F13:F15. В области ячеек D12:G12 производится суммирование переменных издержек по каждому виду продукции

Разнесение прямых постоянных издержек

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

=АМЛчашки

Разнесение прямых издержек

Расчет прямых издержек по каждому виду продукции производится в диапазоне ячеек D11:G11, в которые введите формулы суммирования переменных и постоянных издержек.

Разнесение общецеховых издержек

Общецеховые расходы распределяются в зависимости от принятой на предприятии методологии разнесения этих затрат и могут быть:

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

Величина общецеховых издержек на каждый вид продукции, вычисляется в диапазоне ячеек D20:G23. Формулы в этих ячейках оперируют данными, введенными в рабочий лист Цех, но в зависимости от принятой методики разнесения общецеховых затрат могут возвращать различные значения. В таблицах показанных на рис. 13.7., разнесение всех общецеховых затрат на каждый вид продукции, осуществляется по удельному "весу" заработной платы основных рабочих (вместе с начислениями на зарплату). Пропорции, в которых распределяются цеховые издержки на различные виды продукции, осуществляется в дополнительно созданных таблицах.

Таблицы разнесения общецеховых расходов

На рис. 13.7. показаны две таблицы разнесения общецеховых расходов. Таблицы выполняют одни и те же задачи, только каждая для "своего" цеха. Рассмотрим последовательность создания таблицы распределения общецеховых издержек для цеха посуды, которая расположена в диапазоне ячеек АВ11:АЕ22

Рис. 13.7. Таблицы разнесения общецеховых расходов с числовым примером

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

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

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

В соответствии с принятой методологией, в ячейки диапазона АС12:АС15 (рис. 13.7.) введите наименования баз разнесения, а в ячейки АЕ12:АЕ15 формулы, определяющие суммарную величину этой базы по цеху в целом. В диапазоне ячеек АВ12:АВ15 введите порядковые номера, необходимые для поиска выбираемого количественного значения базы разнесения.

Каждая из строк 20:22 этих таблиц предназначена для определения соотношения, в котором будут распределяться три основных составляющих косвенных общецеховых издержек, приведенные на рис. 13.4. При этом для выбора базы, по которой будет осуществляться разнесение затрат, в ячейки АВ20:АВ22 будут вводиться значения от единицы до четырех. В зависимости от введенного в них значения, формулы в ячейках АС20:АЕ22 основанные на функции ЕСЛИ будут возвращать значения из заданных ячеек таблицы распределения затрат, в которых находятся данные о затратах на изготовление первой продукции цеха посуды. В ячейках диапазона АЕ20:АЕ22 находятся формулы, которые с помощью функции ВПР по значению, введенному в ячейки АВ20:АВ22, производят поиск значения общецеховой выбранной базы разнесения в диапазоне ячеек АЕ12:АЕ15. Использование в формуле смешанных ссылок на ячейки, позволяет скопировать содержимое ячейки АС20 в диапазон АС20:АD22, не производя после этого никакого редактирования.

Для большего понимания этих таблиц и находящихся в них формул, рассмотрим процесс вычислений на примере распределения сумм амортизационных отчислений на обслуживающее оборудование цеха посуды, равному 30 тысяч рублей (рис. 13.4.), зарплаты непроизводственного персонала (300 тысяч) и административным издержкам (700 тысяч). В ячейках АВ20:АВ22 введены значения 3 и согласно порядковому номеру с таким же значением в диапазоне АВ12:АВ15, в качестве базы разнесения принята зарплата основных производственных рабочих цеха посуды, общая сумма сдельной зарплаты которых по цеху составила 286 тысяч рублей. Введенные в ячейки АЕ20:АЕ22 формулы исходя из заданных условий поиска вернули это же значение. Формулы в ячейках АС20:АС22 вернули значение зарплаты основных рабочих по линии чашек из ячейки D14 таблицы распределения издержек (рис. 13.6.) равное 126 тысячам рублей, а в ячейках AD20:AD22 вернули значение зарплаты основных рабочих по линии тарелок из ячейки Е14 равную 160 тысячам рублей.

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

• линия чашек — 126 тысяч : 286 тысяч = 0,4406
• линии тарелок — 160 тысяч : 286 тысяч = 0,5594

Эти соотношения рассчитываются непосредственно в формулах ячеек D20:Е22, в которых указаны ссылки на диапазон ячеек АС20:АЕ22 (рис. 13.10.). Расчет распределения сумм общецеховых расходов показаны в таблице 13.1. Для изменения базы разнесения общецеховых расходов, измените значения в ячейках АВ20:АВ22 (рис. 13.07.), что изменит соотношение распределения в таблице распределения затрат.

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

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

Создание элементов управления распределения общецеховых затрат на листе Цех

Для более удобного процесса распределения общецеховых издержек на выпускаемые продукты, а как следствие, для управления ячейками диапазонов АВ20:АВ22 и АG20:АG22 в таблицах распределения (рис. 13.7.), на листе Цех созданы 24 элемента управления Переключатель (рис. 13.9.), которые с помощью элемента управления Рамка, объединены по четыре элемента, в одну группу. Каждая группа соответствует статье общецеховых расходов указанных в имени группы. Соответственно, трем статьям общецеховых расходов, для каждого цеха отведены три группы элементов управления. Активизация переключателей по вертикали позволяет распределять общецеховые издержки соответственно базам разнесения, которые указаны в ячейках Е12:Н12.

Создание такого "пульта управления" начинайте с создания элемента управления Рамка. После изменения текста наименования элемента управления соответственно статье общецеховых издержек, выделите его и, нажав клавишу Ctrl, перетяните контур вниз. Таким образом, получите его точную копию. Создав три элемента управления Рамка и изменив текст, оставшиеся три, для второго цеха, создаются также копированием элемента с соответствующим текстом.

Создание элементов управления Переключатель начинайте с левого верхнего угла. Желательно, чтобы он имел небольшие размеры и располагался по вертикали против соответствующей базы распределения. Создав элемент командой Ctrl+1 вызовите диалоговое окно Формат элемента управления и на вкладке Цвета и линии задайте ему в области Заливка тот цвет, в который потом будут залиты ячейки, в области которых будут расположены все элементы управления. Методом копирования создайте еще три переключателя внутри элемента Рамка, обязательно соблюдая последовательность их размещения — слева направо.

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

Выделив любой элемент управления Переключатель в первой группе элементов управления, и вызвав диалоговое окно Формат элемента управления, на вкладке Элемент управления в поле Связать с ячейкой установите связь с ячейкой АВ20 на листе РаспределениеЗатрат. Все переключатели указанной группы автоматически будут связаны с этой ячейкой и будут изменять значение в ней от единицы до четырех в зависимости от последовательности их создания в этой группе.

Аналогичную операцию проделайте и в остальных группах, связывая с остальными ячейками диапазонов АВ21:АВ22 и АG20:АG22.

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

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

Формулы области расчета распределения общецеховых издержек по видам продукции в таблице распределения издержек показаны на рис. 13.10. Формулы производят умножение суммы косвенных общецеховых расходов, введенных на рабочем листе Цех на коэффициент, рассчитанный в таблицах разнесения общецеховых расходов (рис. 13.7.).

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

Распределение общезаводских расходов

На предприятии принято решение распределять общезаводские издержки, принимая базами разнесения:

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

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

Распределение общезаводских расходов осуществляется по такому же принципу, что и общезаводские затраты. На рис. 13.11 и 13.12. приведена таблица распределения по базам разнесения с числовыми значениями и формулами.

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

Формула в ячейке АG33 при активизации 4-го принципа, возвращает значение рассматриваемой статьи общезаводских расходов, а по остальным трем производит поиск с помощью функции ВПР.

Рис. 13.11. Таблица распределения общезаводских расходов с числовыми значениями

Рис. 13.12. Фрагмент таблицы распределения общезаводских расходов с формулами

Элементы управления (рис. 13.5.) на рабочем листе Администрация создаются аналогично операции создания элементов управления на рабочем листе Цех (рис. 13.9.).

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

Область расчета финансовых показателей

Автор не ставил перед собой целью полное теоретическое описание финансовых показателей. Они представлены лишь в той мере, чтобы произвести необходимые расчеты. Область расчета финансовых показателей находится в области D30:Н46 и показана на рис. 13.13. с числовыми данными и рис. 13.15. с формулами расчета.

Рис. 13.13. Область расчета финансовых показателей с числовыми значениями

Рис. 13.14. Область расчета финансовых показателей с формулами

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

• сумма реализации по каждому виду продукции — произведение количества единиц продукции на его цену (строка 30);
• прибыль до налогообложения — разность сумм реализации и затрат предприятия на изготовление и реализацию этой продукции (строка 32);
• рентабельность продаж — результат деления прибыли до налогообложения на сумму реализации (строка 33).

Определение точки безубыточности

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

Ценность анализа безубыточности и планирования прибыли состоит в том, что этот показатель позволяет:

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

Составляющие стоимости единицы продукции

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

• удельные переменные затраты — определяет какую долю в денежном выражении в единице продукции занимают стоимость переменных составляющих и равна отношению всей суммы переменных издержек к объему выручки (строка 37);
• удельные постоянные затраты — определяет какую долю в денежном выражении в единице продукции занимают стоимость постоянных составляющих и равна отношению всей суммы постоянных издержек к объему выручки (строка 38);
• прибыль, полученная от реализации единицы продукции — определяет какую долю в денежном выражении в единице продукции занимают полученная прибыль и равна отношению полученной прибыли к объему выручки или равна разности между ценой реализации и суммарными издержками на единицу продукции (строка 39);
• вклад на покрытие — определяется какую долю в денежном выражении в единице продукции занимают полученная прибыль и удельные постоянные затраты. Равен разности выручки от реализации продукции и переменными затратами на производство единицы этой продукции. Или же определяется суммой постоянных издержек и прибылью от реализации единицы продукции (строка 40).

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

Вклад на покрытие при неизменных цене реализации продукции, постоянных и переменных издержках величина постоянная и не изменяется от объема реализации продукции. При производстве продукции в убыток (ниже точки безубыточности), вся сумма "излишних" постоянных издержек закрывается отрицательной прибылью.

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

Расчет маржинального дохода

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

Запас финансовой прочности

Запасом финансовой прочности является оценка объема продаж свыше уровня безубыточности. Запас прочности определяется, как результат деления разности объема продаж и точки безубыточности на объем продаж и показывает, на сколько процентов может снизиться объем продаж, прежде чем будет достигнута точка безубыточности. Если запас прочности имеет отрицательное значение, то показывает, на сколько процентов нужно поднять объем продаж, чтобы достигнуть точку безубыточности (строка 45).

Операционный рычаг

Операционный рычаг равен соотношению вклада на покрытие единицы продукции и полученной прибыли от реализации ее и показывает, на сколько процентов изменится прибыль при изменении выручки на 1% (строка 46).

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

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

Группировка данных

Показанная на рис. 13.6. таблица отображает все данные, Но иногда в этом нет необходимости, особенно если таблица велика, а есть необходимость отображения только итоговых данных. Например, для формирования отчетов. На рис. 13.15. показана та же самая таблица после ее вертикального структурирования. Возможно и горизонтальное структурирование.

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

Рис. 13.15. Структурированная таблица распределения затрат

Автоматическое создание структуры

Для автоматического создания структуры:

• поместите табличный курсор в любую точку таблицы;
• выполните команду Данные/Группа и структура/Создание структуры.

Excel проанализирует формулы и самостоятельно создаст структуру.

Ручное создание структуры

Для создания структуры вручную:

• выделите группу строк (столбцов);
• выполните команду у Данные/Группа и структура/Группировать;
• повторите действия для других строк (столбцов).

Удаление структуры

Удаление структуры осуществляется с помощью команды Данные/Группа и структура/Удаление структуры.

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

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

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

Рис. 13.16. Таблица распределения и анализа издержек различных моделей продукции чашка

В этой таблице, объем и цена реализации могут быть заданы ссылками на лист Чашки (рис. 13.1.)., например, переменные издержки в диапазоне ячеек D13:F15. В ячейке D13 формула, которая копируется в этот диапазон ячеек

=Чашка!C12

Для разнесения амортизационных отчислений на производственную линию по моделям продукции, в ячейку D17 введена формула умножения суммы амортотчислений на долю выпускаемой продукции каждой модели, которая копируется в диапазон ячеек D17:F15:

=АМЛчашки*(D$8/СУММ($D$8:$F$8))

Косвенные издержки в диапазонах ячеек D20:F22 и D24:F27 определяются долей прямых затрат на производство каждой модели продукции. Непосредственно сумма этих косвенных издержек рассчитывается на листе РаспределениеЗатрат, на ячейку которой указана ссылка. Формула в ячейке D20 копируется в указанный диапазон:

=РаспределениеЗатрат!$D20*(D$11/СУММ($D$11:$F$11))

В остальном все проводимые на этом листе вычисления аналогичны проводимым на листе РаспределениеЗатрат.

Итоги

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