Факторный анализ прибыли от продаж в Excel
Деятельность любой коммерческой компании направлена на получение прибыли. Основные факторы, влияющие на прибыль это объем реализованной продукции, ее ассортимент, себестоимость и расходы на реализацию. Анализ этих факторов поможет компании выявить недостатки, повысить рентабельность продаж и подготовить бизнес план по продажам. В нашей статье мы расскажем о том, как можно провести факторный анализ прибыли от продаж с помощью Excel, какие формулы использовать, как строить графики и диаграммы по результатам анализа.
Факторный анализ – это способ комплексного и системного исследования влияния отдельных факторов на размер итоговых показателей. Основная цель проведения такого анализа – нахождение способов увеличения доходности фирмы.
Факторный анализ позволяет определить общее изменение прибыли в текущем периоде по отношению к предыдущему (базовому) периоду или изменение фактических показателей прибыли по отношению к плану и какое влияние на эти изменения оказали следующие факторы:
- объём продажи продукции,
-себестоимость реализованной продукции,
- цены реализации,
- ассортимент реализуемой продукции.
Итак, с помощью факторного анализа можно установить объем продаж, себестоимость или цену реализации, которые увеличат прибыль компании, а факторный анализ по ассортименту реализуемой продукции, даст возможность выявить какой товар продается лучше, а какой товар пользуется наименьшим спросом.
Показатели для факторного анализа берутся из бухгалтерского учета. Если анализируются итоги за год, то используют данные бухгалтерской отчетности формы № 2 «Отчет о финансовых результатах».
Факторный анализ можно проводить 2 способами:
- способом абсолютных разниц,
- способом цепных подстановок.
Математическая формула модели факторного анализа прибыли от продаж:
ПР = Vпрод. х (Ц – Sс/с. 1 ед-цы), где
ПР – прибыль от продаж плановая или базовая,
Vпрод. - объём продаж продукции (товаров) в натуральных величинах (шт., тонны, метры и т.п.)
Sс/с1 ед-цы - себестоимость 1 единицы реализованной продукции,
Ц – продажная цена 1 единицы реализованной продукции.
Способ абсолютных разниц.
За основу факторного анализа берется математическая формула:
ПР = Vпрод. х (Ц – Sс/с 1 ед-цы продукции)
Такая формула включает 3 анализируемых фактора – объем продаж в натуральных единицах, цену, себестоимость 1 единицы продаж.
Далее изучаются ситуации, влияющие на прибыль. Определяется изменение величины прибыли за счет каждого фактора. Расчет строится на последовательной замене плановых значений факторных показателей на их отклонения, а затем на фактический уровень этих показателей. Приведем формулы расчета для каждой ситуации, оказавшей влияние на прибыль.
Ситуация 1. Влияние на прибыль объёма продаж:
Δ ПР объема. = Δ V прод. х (Ц План –S с/с 1 ед-цы продукции План) = (V прод. Факт - V прод. План) х (Ц План –S с/с 1 ед-цы продукции План).
Ситуация 2. Влияние на прибыль продажной цены:
Δ ПР цены = V прод. Факт х Δ Ц = V прод. Факт х (Ц Факт – Ц план)
Ситуация 3. Влияние на прибыль себестоимости 1 единицы продукции:
Δ ПР с/с. 1 ед-цы продукции = V прод. Факт х (- Δ S с/с 1 ед-цы продукции) = V прод. Факт х (– (S с/с 1 ед-цы продукции Факт - S с/с 1 ед-цы продукции План)).
Способ цепной подстановки.
При таком методе сначала рассматривается влияние одного фактора, при неизменности прочих, затем второго и т.д.
За основу берется все та же математическая формула модели факторного анализа прибыли от продаж:
ПР План = Vпрод. План х (Ц План – Sс/с. 1 ед-цы продукции План).
Далее выявляется влияние факторов на сумму прибыли.
Ситуация 1. Изменение объёма продаж.
ПР 1 = Vпрод. Факт х (Ц План – S с/с 1 ед-цы продукции План)
Δ ПР объем. = ПР 1 – ПР План
Ситуация 2. Изменение цены продаж.
ПР 2 = Vпрод. Факт х (Ц Факт – S с/с 1 ед-цы продукции План)
Δ ПР цена. = ПР 2 – ПР 1
Ситуация 3. Изменение себестоимости.
ПР (с/с 1ед-цы продукции) = Vпр. Факт х (Ц Факт – S с/с 1 ед-цы продукции Факт)
Δ ПР (с/с 1ед-цы продукции) = ПР 3 – ПР 2
Условные обозначения, применяемые в формулах, приведенных выше:
ПР План – прибыль от реализации (плановая или базовая),
ПР 1 – прибыль, полученная под влиянием фактора изменения объема продаж (Ситуация 1)
ПР 2 – прибыль, полученная под влиянием фактора изменения цены (Ситуация 2)
ПР 3 – прибыль, полученная под влиянием фактора изменения себестоимости продаж 1 единицы продукции (Ситуация 3)
Δ ПР объем – сумма отклонения прибыли при изменении объема продаж,
Δ ПР цена – сумма отклонения прибыли при изменении цены,
Δ ПР (с/с 1ед-цы продукции) – сумма отклонения прибыли при изменении себестоимости 1 единицы реализованной продукции,
Δ Vпрод. – разница между фактическим и плановым (базисным) объемом продаж,
Δ Ц – разница между фактической и плановой (базисной) ценой продаж,
Δ S с/с 1 ед-цы продукции – разница между фактической и плановой (базисной) себестоимостью единицы реализованной продукции,
Vпрод.Факт – объем продаж фактический,
Vпрод.План – объем продаж плановый,
ЦПлан – цена плановая,
ЦФакт – цена фактическая,
S с/с 1 ед-цы продукции План – себестоимость 1 единицы реализованной продукции плановая,
S с/с 1 ед-цы продукции Факт – себестоимость 1 единицы реализованной продукции фактическая.
Способ цепной подстановки дает те же результаты, что и способ абсолютных разниц Суммарное отклонение прибыли будет равно сумме отклонений под влиянием всех факторов, по которым проводиться факторный анализ.
Проведем факторный анализ прибыли от продаж с помощью Excel. Математические действия и расчетные формулы для факторного анализа будут проводиться в Excel-Таблицах. Будем сравнивать фактические и плановые показатели в Excel-таблицах, далее по результатам построим графики или диаграммы, которые наглядно покажут результаты и отклонения проведенного факторного анализа.
В Excel можно построить стандартную план-факт таблицу, которая будет состоять из нескольких блоков: в левой части Таблицы в колонке будет стоять название показателя, в центре — данные с планом и фактом, в правой — отклонение (в абсолютных величинах и в относительных в процентах).
Пример.1
Организация реализует металлопрокат. Косвенные расходы распределяются на себестоимость реализованной продукции, т.е. формируется полная себестоимость продукции. Проведем факторный анализ прибыли от продаж методом цепных подстановок и определим какие из показателей оказали наибольшее влияние на прибыль компании. Плановые показатели взяты из бизнес-плана по продажам, фактические из бухгалтерской отчетности (Форма № 2 «Отчет о финансовых результатах») и бухгалтерского учета – (Отчеты о продажах в натуральных единицах). Данные о результатах финансовой деятельности компании фактические и плановые представлены в Excel- Таблице 1.
Данные о результатах финансовой деятельности компании
Таблица 1
| Фактор | План | Факт | Отклонения от плана | |
| Абсолютные (Гр.3-Гр.2) | В процентах (Гр.4/Гр.2 х100) | |||
| 1.Объем продаж, тыс. тн | 520,2 | 510,1 | -10,1 | -1,94 |
| 2.Цена (тыс.руб.) | 35,15 | 0,15 | 0,43 | |
| 3.Выручка (тыс. руб.) (Стр. 1 х Стр.2) | 17930,02 | -276,99 | -1,52 | |
| 4.С/с продаж (тыс. руб.) | 1130,00 | 11,06 | ||
| 5. С/с продаж 1 тн, тыс.руб. | 19,65 | 22,25 | 2,60 | 13,26 |
| 6.Прибыль (тыс. руб.) ((Стр. 2 – Стр.5) х Стр.1) | 7985,07 | 6580,29 | -1404,78 | -17,59 |
Из данных Таблице 1 следует, что объем продаж фактический ниже планового на 10,1 тыс.тн, продажная цена была выше плановой на 0,15 тыс.руб., при этом сумма фактической выручки меньше плановой на 276,99 тыс. руб., а себестоимость продаж наоборот выше плановой на 1130 тыс. руб. Все выше перечисленные факторы привели к снижению фактической прибыли по сравнению с плановой на 1404,78 руб.
Динамику финансовых показателей Таблицы 1 можно наглядно увидеть на диаграмме, которую легко построить с помощью Excel- Таблицы 1. Для этого выделяем Таблицу 1 в программ Excel, нажимаем на панели окошко «Вставка» в этом окошке выбираем гистограмму и цветная гистограмма сразу появляется в Excel рядом с Таблицей 1, которую вы видите ниже.
Динамика фактических показателей по отношению к плановым
Рис.1
На гистограмме в цвете изображены фактические (бордовый цвет), плановые (синий цвет) показатели и абсолютные отклонения (зеленый цвет) таких факторов как объем продаж, цена за 1 тн, выручка, себестоимость продаж, себестоимость продаж 1 тн, прибыль.
В диаграмме наглядно видно, что плановый объем продаж и выручка выше фактических показателей, а себестоимость продаж и прибыль ниже факта
Проведем факторный анализ прибыли от продаж двумя способами.
Для факторного анализа используем математическую формулу модели факторного анализа прибыли от продаж. В данном случае она базируется на плановых показателях, так как мы сравниваем фактические и плановые данные. В расчетах участвуют три фактора:
- объем продаж,
- цена продаж,
- себестоимость единицы реализованной продукции.
Математическая формула трех факторной модели выглядит следующим образом:
ПР План = Vпрод.План х (Ц План – S с/с 1 ед-цы продукции План),
где ПР План – плановая прибыль от реализации, тыс.руб..
Vпрод. План – объём продаж плановый, тн.
Ц План– цена 1 тн металлопроката плановая, тыс.руб.
S с/с 1 ед-цы продукции План – себестоимость продаж 1 тн металлопроката плановая, тыс. руб.