Как делать факторный анализ в excel
Перейти к содержимому

Как делать факторный анализ в excel

Однофакторный дисперсионный анализ в Excel

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

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

Дисперсионный анализ в Excel

Условно цель дисперсионного метода можно сформулировать так: вычленить из общей вариативности параметра 3 частные вариативности:

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

В программе Microsoft Excel дисперсионный анализ можно выполнить с помощью инструмента «Анализ данных» (вкладка «Данные» — «Анализ»). Это надстройка табличного процессора. Если надстройка недоступна, нужно открыть «Параметры Excel» и включить настройку для анализа.

Работа начинается с оформления таблицы. Правила:

  1. В каждом столбце должны быть значения одного исследуемого фактора.
  2. Столбцы расположить по возрастанию/убыванию величины исследуемого параметра.

Рассмотрим дисперсионный анализ в Excel на примере.

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

Внесем данные в таблицу Excel:

  1. Открываем диалоговое окно нашего аналитического инструмента. В раскрывшемся списке выбираем «Однофакторный дисперсионный анализ» и нажимаем ОК.
  2. В поле «Входной интервал» ввести ссылку на диапазон ячеек, содержащихся во всех столбцах таблицы.
  3. «Группирование» назначить по столбцам.
  4. «Параметры вывода» — новый рабочий лист. Если нужно указать выходной диапазон на имеющемся листе, то переключатель ставим в положение «Выходной интервал» и ссылаемся на левую верхнюю ячейку диапазона для выводимых данных. Размеры определятся автоматически.
  5. Результаты анализа выводятся на отдельный лист (в нашем примере).

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

Факторный анализ в Excel: пример

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

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

Рассмотрим на примере проведение факторного анализа. Допустим, нам известны продажи каких-либо товаров за последние 4 месяца. Необходимо проанализировать, какие наименования пользуются спросом, а какие нет.

  1. Посмотрим, за счет, каких наименований произошел основной рост по итогам второго месяца. Если продажи какого-то товара выросли, положительная дельта – в столбец «Рост». Отрицательная – «Снижение». Формула в Excel для «роста»: =ЕСЛИ((C2-B2)>0;C2-B2;0), где С2-В2 – разница между 2 и 1 месяцем. Формула для «снижения»: =ЕСЛИ(J3=0;B2-C2;0), где J3 – ссылка на ячейку слева («Рост»). Во втором столбце – сумма предыдущего значения и предыдущего роста за вычетом текущего снижения.
  2. Рассчитаем процент роста по каждому наименованию товара. Формула: =ЕСЛИ(J3/$I$11=0;-K3/$I$11;J3/$I$11). Где J3/$I$11 – отношение «роста» к итогу за 2 месяц, ;-K3/$I$11 – отношение «снижения» к итогу за 2 месяц.
  3. Выделяем область данных для построения диаграммы. Переходим на вкладку «Вставка» — «Гистограмма».
  4. Поработаем с подписями и цветами. Уберем накопительный итог через «Формат ряда данных» — «Заливка» («Нет заливки»). С помощью данного инструментария меняем цвет для «снижения» и «роста».

Теперь наглядно видно, продажи какого товара дают основной рост.

Двухфакторный дисперсионный анализ в Excel

Показывает, как влияет два фактора на изменение значения случайной величины. Рассмотрим двухфакторный дисперсионный анализ в Excel на примере.

Задача. Группе мужчин и женщин предъявляли звук разной громкости: 1 – 10 дБ, 2 – 30 дБ, 3 – 50 дБ. Время ответа фиксировали в миллисекундах. Необходимо определить, влияет ли пол на реакцию; влияет ли громкость на реакцию.

  1. Переходим на вкладку «Данные» — «Анализ данных» Выбираем из списка «Двухфакторный дисперсионный анализ без повторений».
  2. Заполняем поля. В диапазон должны войти только числовые значения.
  3. Результат анализа выводится на новый лист (как было задано).

Та как F-статистики (столбец «F») для фактора «Пол» больше критического уровня F-распределения (столбец «F-критическое»), данный фактор имеет влияние на анализируемый параметр (время реакции на звук).

Скачать пример факторного и дисперсионного анализа

скачать факторный анализ отклонений скачать пример 2

Для фактора «Громкость»: 3,16 0;I5;I5+L6) — т.е. если первый фактор больше нуля, то берем базовое значение, в противном случае берем базовое + значение изменения фактора (в нашем примере получается просто 100).

Для всех последующих:

=ЕСЛИ(L7>0;M6;M6+L7) — т.е. если фактор больше нуля, то берем полученное на предыдущем факторе результирующее значение, в противном случае берем базовое + значение изменения фактора.

Ахтунг! Не забывайте про правила сложения – если я говорю “плюс значение”, это значит, что подразумевается не абсолютное значение, а позитивное или негативное. Т.е. для третьего фактора получим следующую логику:

Значение изменения фактора меньше нуля, следовательно берем сумму предыдущего результирующего значения и значения изменения фактора, т.е. основа будет равна 170+(-30)=170-30=140.

Результирующее значение вычисляется по формуле:

=ЕСЛИ(L6>0;J6+L6;J6) – т.е. если изменения фактора позитивное, то результирующим значением будет сумма предыдущего результирующего значения и величины изменения фактора, а в противном случае – просто значение основы. Далее переходим уже непосредственно к построению диаграммы. Выделяем ячейки от названия категорий до столбца “Влияние фактора” включительно.

Рис.7. Выделяемая область.

И вставляем необходимый тип диаграммы (в данном случае – гистограмму).

Рис.8. Полученный результат

Дальше наводим красоту – переносим на новый лист диаграмму и заодно поправляем мою ошибку в выборе исходных данных (Отчетное значение принимаем 160, а не 150).

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

Дальше в свойствах ряда изменяем боковой зазор до 10% и ряду “Основа” выставляем отсутствие заливки и линий – т.е. делаем его невидимым.

В свойствах горизонтальной оси также поставим “Нет линий” (рис.10).

Рис.10. Делаем ось невидимой

Далее добавляем рядам “Влияние фактора” и “Значение” подписи данных. Но получается маленькая нестыковка – даже в тех случаях, когда изменение фактора было отрицательным у нас выводятся положительные значения. Для этого дальше переходим обратно на лист 1 и выставляем соответственные форматы для позитивных и негативных значений.

Для позитивных: +0,0

Для негативных, соответственно: –0,0 – рис.11

Рис.11. Изменение формата чисел в столбце “Влияние фактора”.

Получившийся результат показан на рис.12

Рис.12. Подписи данных после изменения формата

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

Рис.13. Окончательный результат.

Мы получили симпатичную диаграммку, которую не стыдно вставить в презентацию или в документ.

Расчет влияния факторов на изменение налогооблагаемой прибыли

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

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

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

Факторный анализ прибыли на рубль материальных затрат

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

Факторный анализ прибыли на рубль зарплаты

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

Факторный анализ прибыли от реализации продукции

Основную часть прибыли предприятия получают от реализации продукции и услуг. В процессе анализа изучаются динамика, выполнение плана прибыли от реализации продукции и определяются факторы изменения её суммы.

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

Факторный анализ прибыли от реализации отдельных видов продукции

Проанализируем выполнение плана и динамику прибыли от реализации отдельных видов продукции, величина которой зависит от трёх факторов первого порядка: объёма продажи продукции (VPПi), себестоимости (Цi) и среднереализационных цен (Сi). Факторная модель прибыли от реализации отдельных видов продукции имеет вид: П = VPПi * (Цi – Сi)

Расчет влияния факторов на изменение прибыли по продукции X

Для анализа прибыли от реализации одного вида продукции используется следующая формула: П = К * (Ц – V) – Н. Она позволяет определить изменение суммы прибыли за счет количества реализованной продукции, цены, уровня удельных переменных и суммы постоянных затрат.

Сравнение плана и факта достаточно частая задача в бизнес-среде. Рассмотрим различные виды план-факт анализа в Excel и способы их создания. Наиболее популярными видами сравнения являются таблицы и диаграммы. Таблицы удобно использовать, если сравнение между планом и фактом происходит по различным показателям, например, данные по продажам различных товаров компании, P&L, CF и т.д. Диаграммы удобнее для визуализации план-факта конкретного показателя, например, месячная динамика продаж конкретного товара.

Таблица план-факт

Стандартная план-факт таблица состоит из нескольких блоков: в левой части — название показателя, в центре — данные с планом и фактом, в правой — отклонение (в абсолютных величинах, в процентах).

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

Диаграмма план-факт

Предположим, что для план-факт графика у нас имеются помесячные данные по продажам (фактические и планируемые), а также отклонение между фактом и планом:

Разберем 2 различных варианта построения план-факт диаграмм.

Вариант 1. Добавление дополнительного ряда в виде гистограммы

Выделяем диапазон ячеек A1:M4 и добавляем стандартный график с маркерами (на панели вкладок выбираем Вставка -> График -> График с маркерами):

На графике отобразились 3 ряда — Факт, План и Отклонение, при этом ряд с отклонением получился существенно ниже первых двух за счет эффекта масштаба. Поэтому сделаем ряд «Отклонение» гистограммой (нажимаем правой кнопкой мыши на ряд, выбираем Изменить тип диаграммы -> Гистограмма -> Гистограмма с группировкой) и перенесем его на вспомогательную ось (снова выбираем ряд, нажимаем правую кнопку и выбираем Формат ряда данных -> Параметры ряда -> По вспомогательной оси)

Теперь приведем в порядок внешний вид план-факт диаграммы — скроем вспомогательную ось, установим минимальные и максимальные значение для основной и вспомогательной оси:

Добавляя подпись данных к рядам получаем окончательный вид план-факт графика:

Вариант 2. Полосы повышения-понижения

Еще одним вариантом план-факт анализа является диаграмма с использованием свойств полосы повышения-понижения. Повторяем действия из первого примера, выделяем диапазон ячеек A1:M3 (без отклонения) и строим график с маркерами:

Во вкладке Конструктор (Excel 2013 и старше) или Макет (Excel 2007-2010) добавляем Полосы повышения-понижения:

Между линией факта и плана появились прямоугольные полосы (полосы повышения-понижения), которые показывают изменение между линиями. При этом в зависимости от знака отклонения они окрашиваются в разные цвета (в данном примере, если факт больше плана, то в черный и наоборот, если факт меньше плана, то в белый). Изменим цвет полосы повышения на зеленый, а полосы понижения на красный, а также сделаем их полупрозрачными, чтобы полосы не наезжали на линии графика (нажимаем правой кнопкой мыши на полосу и выбираем Формат полос повышения/понижения):

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

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

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

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

Необходимо выяснить зависимость количества изготовленных деталей от производительности мастера. Уровень значимости равен α=0.05.

tablitsa-zadacha-odnofaktornyj-dispersionnyj-analiz-1.png

№ п/п Номер недели Количество изготовленных деталей
Первым мастером Вторым мастером Третьим мастером Четвертым мастером Пятым мастером
1. Первая неделя 260 253 258 257 251
2. Вторая неделя 257 255 255 252 250
3. Третья неделя 259 250 254 253 255
4. Четвёртая неделя 254 254 260 257 251

Переходим на вкладку Данные -> Анализ данных. Выбираем однофакторный дисперсионный анализ и жмём Ок.

analiz-dannyh-excel-odnofaktornyj-dispersionnyj-analiz-1.png

Появляется окно, здесь во входном интервале выбираем диапазон данный в нашей таблицы в нашем случае это диапазон ячеек $C$3:$G$6, альфа ставим 0,05 (обычно в Excel данная величина стоит по умолчанию) и в выходном интервале указываем произвольную ячейку на листе Excel, где желаете, чтобы отобразился результат, далее Ок.

odnofaktornyj-dispersionnyj-analiz-1.png

В результате получим решение в виде таблицы.

odnofaktornyj-dispersionnyj-analiz-reshenie-1.png

Однофакторный дисперсионный анализ
ИТОГИ
Группы Счет Сумма Среднее Дисперсия
Столбец 1 4 1030 257,5 7
Столбец 2 4 1012 253 4,666666667
Столбец 3 4 1027 256,75 7,583333333
Столбец 4 4 1019 254,75 6,916666667
Столбец 5 4 1007 251,75 4,916666667
Дисперсионный анализ
Источник вариации SS df MS F P-Значение F критическое
Между группами 94,5 4 23,625 3,800268097 0,025089214 3,055568276
Внутри групп 93,25 15 6,21666667
Итого 187,75 19

Из таблицы значения F-критерия равно Fнабл=3.8, а Fкрит=3, правосторонний интервал (3; +∞) Fнабл>Fкрит, отсюда следует, что Fнабл лежит в этом интервале, следовательно, нулевую гипотезу H о равенстве групповых матожиданий — отвергаем, следовательно фактор — количества изготовленных деталей зависит от признака — производительности мастера.

Найдём выборочный коэффициент детерминации:

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

Детерминированный факторный анализ с помощью надстройки EXCEL Variance Analysis Tool

history 20 февраля 2018 г.
    Группы статей

  • Управление проектом

Выполним детерминированный факторный анализ на примере модели, описывающей связь финансовых показателей предприятия. Рассмотрим наиболее общий способ цепных подстановок. Для проведения факторного анализа используем надстройку MS EXCEL Variance Analysis Tool от компании Fincontrollex .

Для выполнения детерминированного факторного анализа в среде MS EXCEL сначала кратко напомним читателям о самом методе, затем покажем, как провести факторный анализ самостоятельно на примере простой однопродуктовой модели, и наконец, воспользуемся специализированной надстройкой Variance Analysis Tool для более сложной многопродуктовой модели .

Немного теории

Сначала дадим сухое академическое определение факторного анализа , затем поясним его на примерах.

Детерминированный факторный анализ (ДФА) — это методика исследования влияния факторов на результативный показатель . Предполагается, что связь факторов с результативным показателем носит функциональный характер, которая выражена математической формулой.

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

Выручка=(Объем продаж изделия за период)*(Цена изделия)*Наценка

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

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

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

Примечание : Представленная выше модель является мультипликативной , т.е. чтобы получить результирующий показатель необходимо перемножить факторы. Также имеются аддитивные (Результат=Фактор1+Фактор2+…), кратные (Результат=Фактор1/Фактор2) и смешанные модели (Результат=Фактор1*Фактор2+Фактор3).

Для проведения ДФА нам понадобятся 2 набора значений факторов и соответствующих им результирующих показателей. Часто в качестве первого набора (называемого базовым) выбирают плановые значения, а в качестве второго – фактические.

Для нашей мультипликативной модели Выручка=Объем*Цена*Наценка заполним следующую таблицу с плановыми и фактическими значениями:

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

В детерминированном факторном анализе используют следующие способы анализа:

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

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

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

  • Сначала изменяют значение одного фактора с планового на фактическое (в нашем случае изменим Объем продаж ). При этом другие факторы ( Цену и Наценку ) нужно оставить неизменными (плановой). Затем вычисляют результирующий показатель ( Выручку ), а результат сравнивают с имеющимся предыдущим значением (с плановой Выручкой ). Далее находят их разность. Чем больше разность по абсолютной величине, тем больше влияние данного фактора на показатель.
  • На втором шаге изменяют значения сразу двух факторов на их фактические значения ( Объем и Цену ), при этом остальные факторы ( Наценку ) оставляют неизменными (плановыми). Далее вычисляют результирующий показатель ( Выручку ), и сравнивают его со значением, полученным на предыдущем шаге.
  • Далее повторяют замену значений факторов с плановых на фактические до тех пор, пока не будут заменены значения всех факторов модели на фактические.

Все вышесказанное можно записать с помощью простых математических выражений. Сделаем это на примере 3-х факторной мультипликативной модели).

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

Результат(План) = Фактор1(План) *Фактор2(План) *Фактор3(План)

Затем для всех факторов по очереди подставляем их фактические значения вместо плановых.

Результат(1)= Фактор1(Факт) *Фактор2(План) *Фактор3(План)

Результат(2)= Фактор1(Факт) *Фактор2(Факт) *Фактор3(План)

Результат(3)= Фактор1(Факт) *Фактор2(Факт) *Фактор3(Факт)

Примечание : Результат(3) = Результат(Факт), т.е. значению результирующего показателя с фактическими значениями всех факторов .

При этом общее изменение Результата будет равно:

Δ Результат = Результат(Факт) – Результат(План)

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

Δ Результат = Δ Результат(1) + Δ Результат(2) + Δ Результат(3)

Δ Результат(1) = Результат(1) – Результат(План)

Δ Результат(2) = Результат(2) – Результат(1)

Δ Результат(3) = Результат(Факт) – Результат(2)

И наконец, определим значение Δ Результат( i ), которое будет максимальным по абсолютной величине. Соответствующий фактор (i) и будет являться фактором, наиболее повлиявшим на результирующий показатель.

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

Вычисления в MS EXCEL

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

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

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

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

В этом можно непосредственно убедиться, проведя анализ изменений факторов модели:

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

К счастью, имеются специализированные программы для проведения факторного анализа . Так как среда MS EXCEL является гибким и одновременно мощным средством для проведения расчетов, то для сложных моделей рекомендуем использовать надстройку Variance Analysis Tool от компании Fincontrollex .

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

Надстройка Variance Analysis Tool

Скачать надстройку можно с сайта ]]> http://fincontrollex.com ]]> , выбрав ее в меню Продукты или соответствующую иконку на главной странице сайта.

На сайте также можно найти подробную справку к надстройке и очень полезный видеоурок ( ]]> http://fincontrollex.com/?page=products&id=3&lang=ru ]]> ).

На странице продукта нажмите кнопку «Скачать бесплатно». Надстройка будет скачана на компьютер в формате архива zip. В архиве содержится 2 файла надстройки *.xll: x64 – для 64 и x86 – для 32 – разрядной версии MS EXCEL. Чтобы узнать версию вашей программы в меню Файл выберите пункт Справка .

После установки надстройки появится новая вкладка fincontrollex.com.

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

Создание модели

Рассмотрим более сложную модель выручки предприятия, зависящую от 3-х факторов:

Выручка=СУММ(Объем продаж изделия( i )*(Цена за 1 шт. изделия( i ))+бонус( i ))

Как видно из формулы предприятие теперь продает несколько изделий, причем каждое изделие имеет свою цену. За своевременную оплату поставленной партии клиенту может быть начислен бонус (скидка): если платеж осуществлен в течение первых 3-х дней после отгрузки (поставки), то бонус составляет 20 000 руб. за партию; если оплата поступила не позже недели, то бонус составит 10 000 руб., если позже, то бонус не начисляется.

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

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

Руководители предприятия, очевидно, планировали продать изделия с артикулом с 1 по 5 в количестве по 1500 шт., а остальные изделия по 1750 шт. Фактические объемы продаж по некоторым позициям существенно отличаются. Также отличается и цена, по которой менеджеры по продажам договорились реализовать изделия. Наличие бонуса сыграло свою роль при оплате и большинство клиентов оплатили товар вовремя или даже ранее срока, которые прогнозировали руководители (от 3-х дней до 1 недели).

Но, какой из факторов оказал большее влияние на выручку? Кого из сотрудников нужно премировать: руководство, которое придумало систему Бонусов; менеджеров по продажам, которые договорились о цене и объемах каждого изделия или производственный отдел, которые обеспечили гибкое изготовление партий (существенно отличающееся по объемам от планового). Ответ далеко не очевиден.

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

Чтобы этого не произошло – разумно воспользоваться специальной надстройкой Variance Analysis Tool .

Расчет с помощью надстройки Variance Analysis Tool

Итак, у нас есть модель (формула) и таблица с исходными данными. Чтобы воспользоваться надстройкой нам потребуется немного изменить нашу формулу:

Выручка=СУММ(Объем продаж изделия( i )*(Цена за 1 шт. изделия( i )) +бонус( i ))

Для того, чтобы понять зачем нам придется менять казалось бы разумную формулу, рассмотрим более детально фактор Объем продаж изделия .

Очевидно, что важен как суммарный объем продаж (в штуках), так и ассортимент изделий . Можно получить рост суммарного объема продаж, но при этом потерять в выручке за счет снижения продаж более дорогих изделий, чем было запланировано. Например, менеджеры запланировали продать 2 товара по 100 шт. каждого. Один товар стоит 10 руб., другой 50 руб. Плановая выручка должна была составить 6000 руб.=100*10+100*50. Фактически же удалось продать 250 шт.: 200шт. по 10 руб. и 50 шт. по 50 руб. В итоге имеем снижение выручки до 4500 руб.!

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

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

Примечание : На сайте fincontrollex.com можно прочитать статью про факторный анализ выручки ( ]]> http://fincontrollex.com/?page=articles&id=6&lang=ru ]]> ), в которой подробно изложен материал о том, как учесть влияние различных каналов продаж продукции, оценить эффект от ввода новых продуктов, определить влияние скидок и учесть эффекты от других управленческих инициатив.

Новая формула, учитывающая влияние ассортимента и общего объема продаж на выручку, выглядит так:

Выручка=Общий объем продаж*СУММ(Доля продаж изделия( i )*(Цена за 1 шт. изделия( i )))+ СУММ(бонус( i ))

Или более кратко:

Выручка=Общ.объем*Доля*Цена+Бонус

Теперь настроим модель.

Во вкладке fincontrollex.com нажмите кнопку Выполнить .

Появится диалоговое окно надстройки Variance Analysis Tool .

Введите название модели (произвольный текст) и формулу модели.

Формула модели не должна содержать точек (.), но может содержать пробелы. После ввода формулы нажмите клавишу ENTER (ВВОД) или кликните на кнопку Параметры модели или в поле Название модели .

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

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

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

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

  • В столбце Описание нужно ввести ссылки на названия колонок факторов из исходной таблицы;
  • В столбце Базовыйдиапазон нужно ввести ссылки на соответствующие ячейки с плановыми значениями факторов;
  • В столбце Фактический диапазон нужно ввести ссылки на соответствующие ячейки с фактическими значениями факторов;

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

Осталось нажать кнопку меню Выполнить , и тем самым запустить расчет.

Расчет выполняется практически мгновенно. После выполнения расчета создается новая книга с 2-мя листами: Свод и Подробно .

Показатель База на листе Свод равен в нашем случае плановой выручке, а Факт – фактической выручке. Между ними расположены все 4 фактора модели. По значениям этих факторов можно быстро определить влияние этих факторов на результирующий показатель (выручку).

Очевидно, что факторы Цена и Бонус оказали практически одинаковое воздействие на выручку, но с противоположным знаком. Таким образом, менеджеры по продажам могут надеяться на премию, т.к. им удалось добиться существенного повышения цены и, соответственно, обеспечив самый значительный дополнительный вклад в выручку по сравнению с плановым. Также был правильно подобран ассортимент изделий (+7210 у фактора Доля ). Это означает, что было продано больше дорогих изделий, чем дешевых по сравнению с планом.

На листе Подробно можно увидеть детальный расчет с формулами.

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

Очевидно, что надстройка Variance Analysis Tool хорошо справилась со своим «предназначением», все расчеты произведены верно и что очень важно – быстро.

Освоение надстройки не занимает много времени. После просмотра видеоурока (10 минут) любой пользователь MS EXCEL сможет начать работу с надстройкой, построить модель и выполнить детерминированный факторный анализ способом цепных подстановок .

Добавить комментарий

Ваш адрес email не будет опубликован.