Как применять метод факторного анализа в Excel

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

Факторный анализ в Эксель — пример расчета прибыли

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

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

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

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

ПР = V · (P – Pед), в которой:

• ПР — прибыль;
• V — объем продаж, количество проданных единиц;
• P — торговая стоимость единицы;
• Ред — себестоимость единицы.

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

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

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

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

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

Анализируем факторы, влияющие на изменение выручки

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

1. Из исходной таблицы на новый лист копируем столбцы «Период», «Объем продаж», «Выручка» и «Стоимость». Это основные параметры, которые влияют на итоговый доход. В нашем примере мы будем оценивать изменение этих параметров за каждый месяц.
2. Далее определяем структуру для каждого месяца. Простыми словами, структура — это процент из общей части. В нашем случае мы считаем, какую долю от общей годовой выручки составляет доход от продаж за каждый месяц. Каждое значение из ячейки «Выручка», начиная с января, делим на значение «Всего». Ссылку на ячейку нужно выделить значками $. Декабрь в данном расчете не учитывается. Формат в ячейке нужно поменять на процентный для корректного отображения данных.
3. После этого можем продолжить анализировать изменение выручки. Мы определили процентную долю каждого месяца от общего размера дохода от продаж. Теперь выясним, как меняется выручка ежемесячно. Для этого достаточно отнять от значения дохода текущего месяца значения дохода предыдущего месяца. Именно для этого мы оставили данные для прошлогоднего месяца «Декабрь», который прописан в первой строке. Таким образом видим, что выручка в январе упала по сравнению с декабрем, а в феврале по сравнению с январем наоборот выросла.
4. Теперь можем определить факторы, которые повлияли на изменение дохода. Их несколько, однако в контексте нашей таблицы можно выделить 2 основных — объем продаж и стоимость. Для начала определяем, как меняется объем продаж каждый месяц. Для этого, так же как и в предыдущем случае, отнимаем значение текущего периода от предыдущего.
5. Аналогичным образом вычисляем и ценовую разницу. Стоимость товара практически каждый месяц изменяется, что оказывает влияние на итоговую выручку. Этот фактор показывает, как изменение цены за единицу товара отражается на доход от продаж в итоге.
6. Эти же факторы можно выразить через проценты. Сделать это можно с помощью формулы процентного соотношения в Эксель. Чтобы узнать разницу в процентах, отнимаем от значения текущего месяца значение предыдущего месяца, а потом делим результат на значение предыдущего месяца. Если проще, данную формулу можно выразить в виде (B – A)/A.

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

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

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

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

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

Подводим итоги

Факторный анализ — аналитический метод, предусматривающий оценку переменных на основе корреляционных параметров. Чаще всего данный инструмент применяют в предпринимательской деятельности, оценивая показатели продаж, выручки, общей прибыли компании. Для выполнения факторного анализа могут использоваться стандартные инструменты MS Excel. Расчеты выполняются при помощи стандартных арифметических формул и операций. Надо также учесть, что данный тип анализа можно выполнять только на основе числовых данных.

Видео по теме