План факторный анализ пример

Качественный план-фактный анализ в Excel

План факторный анализ пример

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

Для качественного анализа необходимо иметь плановые данные и фактические значения. Из статьи вы узнаете, как c помощью Excel:

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

Формирование базы данных в виде таблицы

Сначала подготовим файл Excel. Первый лист будет содержать плановые и фактические данные, поэтому назовем его «План-факт».

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

Аналитика отчета может содержать любые показатели (на усмотрение руководства компании):

  • товарное направление;
  • товарная группа;
  • товарная подгруппа;
  • номенклатура и т. д.

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

Выводы на основании первичных данных отчета:

1) общий размер фактической производственной прибыли больше на 4786 тыс. руб., план по производственной прибыли перевыполнен на 34 %.

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

2) по товарной группе 7 выпуска не было. Причины могут быть разные:

  • поломка оборудования;
  • отсутствие комплектующих;
  • отсутствие заказов;
  • ввод нового продукта (товарная группа 8) и замена им товарной группы 7.

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

Второй лист файла Excel будем использовать для ежемесячного план-фактного анализа. Назовем его «Анализ».

На этот лист с помощью функции ВПР переносят данные с листа «План-факт» (таблицы одинаковые).

Необходимо соблюдать некоторые требования относительно оформления таблиц при использовании функции ВПР:

1. Данные аналитики в крайнем левом столбце (A) в обеих таблицах должны совпадать, так как формула ВПР осуществляет поиск именно по информации, указанной в этом столбце.

Если в фактические данные попадает новое наименование, оно обязательно должно быть отражено в таблице на листе «Анализ».

2. Допускается несоответствие в порядке расположения аналитики встолбце А.

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

Функция ВПР позволит отсортировать по возрастанию диапазон поиска.

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

При отсутствии какого-либо значения обязательно ставим «0».

Используя функцию ВПР, переносим данные с листа «План-факт» на лист «Анализ» (рис. 2). При заполнении первой ячейки для переноса данных следует указать формулу:

=ВПР($A5;Факт!$A$4:$J$12;2;0).

Пояснения к формуле:

$A5 — значение для поиска;

A$4:$J$12 — массив, в котором будет произведен поиск необходимого значения;

2 — номер столбца заданного массива, из которого нужно перенести значение. Важный момент: при копировании формулы номер столбца в первой строчке меняют. Далее эта формула копируется на все строчки: =ВПР($A5;'План-Факт'!$A$5:$I$12;3,4,5 и т. д.;0);

0 — указывает, что диапазон поиска будет отсортирован автоматически (как было сказано выше, данные в таблице для поиска не обязательно должны быть расположены в том же порядке, как в таблице с перенесенными данными; главное — соблюдать количество строк);

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

ABCDEFGHIJK
1План-факт
2Товарные группыМай 2017 г.
3План (выпуск), тыс. руб.Факт (выпуск), тыс. руб.Отклонение, тыс. руб.Отклонение, %
4количество, шт.себестоимость выпускасумма выпуска (прайс)производственная прибыльколичество, шт.себестоимость выпускасумма выпуска (прайс)производственная прибыль
5ТГ 1103917 83525 4797644115820 93029 5878658101413
6ТГ 29415545729617511020580974071598–153–9
7ТГ 3459417556421467536466761181451–16–1
8ТГ 438 59136114815120439 65041925529133713411
9ТГ 514 8692337307573818 0212547354099325535
10ТГ 6298175725107533211825258175630
11ТГ 7187143717963590000–359–100
12ТГ 80000536930513 212390739070
13Итого56 38436 69650 61113 91561 24249 27467 97418 701478634

Рис. 1. Данные план-факт

ABCDEFGHIJ
1Плановые и фактические показатели по выпуску
2Товарные группыМай 2017 г.Отклонение
3План (выпуск), тыс. руб.Факт (выпуск), тыс. руб.производственная прибыль, тыс. руб.
4количество, шт.себестоимость выпускасумма выпуска (прайс)производственная прибыльколичество, шт.себестоимость выпускасумма выпуска (прайс)производственная прибыль
5ТГ 1103917 83525 4797644115820 93029 58786581014
6ТГ 29415545729617511020580974071598–153
7ТГ 3459417556421467536466761181451–16
8ТГ 438 59136114815120439 650419255291337134
9ТГ 514 8692337307573818 02125473540993255
10ТГ 629817572510753321182525817563
11ТГ 7187143717963590000–359
12ТГ 80000536930513 21239073907
13Итого56 38436 69650 61113 91561 24249 27467 97418 7014786

Рис. 2. Использование функции ВПР

Таблица с исходными данными для анализа готова. Чтобы проанализировать показатели за отчетный месяц, ее заполняют данными за анализируемый период с листа «План-факт», расширяя диапазон поиска и меняя номер столбца для выбора значений. Важная деталь: данные с анализом за отчетный месяц можно скопировать и сохранить на отдельном листе (например, «Анализ — май»).

Алгоритм план-фактного анализа

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

К СВЕДЕНИЮ

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

Важнейшие факторы, влияющие на величину производственной прибыли:

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

Е. И. Полевая, начальник финансового отдела

Источник: https://www.profiz.ru/peo/article_plan-faktnyj_analiz/

Факторный анализ прибыли от продаж с помощью Excel

План факторный анализ пример

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

Факторный анализ: общая характеристика и способы проведения

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

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

  • объем продажи продукции;
  • себестоимость реализуемой продукции;
  • цены реализации;
  • ассортимент реализуемой продукции.

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

Показатели для факторного анализа берут из бухгалтерского учета. Если анализируют итоги за год, то используют данные формы № 2 «Отчет о финансовых результатах».

Факторный анализ можно проводить:

1) способом абсолютных разниц;

2) способом цепных подстановок.

Математическая формула модели факторного анализа прибыли от продаж:

ПР = Vпрод × (Ц – Sед),

где ПР — прибыль от продаж (плановая или базовая);

Vпрод — объем продаж продукции (товаров) в натуральных величинах (штуки, тонны, метры и т. д.);

Ц — продажная цена единицы реализованной продукции;

Sед — себестоимость единицы реализованной продукции.

Способ абсолютных разниц

За основу факторного анализа берется математическая формула ПР (прибыль от продаж). Формула включает три анализируемых фактора:

  • объем продаж в натуральных единицах;
  • цену;
  • себестоимость одной единицы продаж.

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

Ситуация 1. Влияние на прибыль объема продаж:

ΔПРобъем = ΔVпрод × (Цплан – Sед. план) = (Vпрод. факт – Vпрод. план) × (Цплан – Sед. план).

Ситуация 2. Влияние на прибыль продажной цены:

ΔПРцена = Vпрод. факт × ΔЦ = Vпрод. факт × (Цфакт – Цплан).

Ситуация 3. Влияние на прибыль себестоимости единицы продукции:

ΔПРSед = Vпрод. факт × (–ΔSед) = Vпрод. факт × (–(Sед. факт – Sед. план)).

Способ цепной подстановки

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

Выявим влияние факторов на сумму прибыли.

Ситуация 1. Изменение объема продаж.

ПР1 = Vпрод. факт × (Цплан – Sед. план);

ΔПРобъем = ПР1 – ПРплан.

Ситуация 2. Изменение цены продаж.

ПР2 = Vпрод. факт × (Цфакт – Sед. план);

ΔПРцена = ПР2 – ПР1.

Ситуация 3. Изменение себестоимостипродаж единицы продукции.

ПРSед = Vпрод. факт × (Цфакт – Sед. факт);

ΔПРSед = ПР3 – ПР2.

Условные обозначения, применяемые в приведенных формулах:

ПРплан — прибыль от реализации (плановая или базовая);

ПР1 — прибыль, полученная под влиянием фактора изменения объема продаж (ситуация 1);

ПР2 — прибыль, полученная под влиянием фактора изменения цены (ситуация 2);

ПР3 — прибыль, полученная под влиянием фактора изменения себестоимости продаж единицы продукции (ситуация 3);

ΔПРобъем — сумма отклонения прибыли при изменении объема продаж;

ΔПРцена — сумма отклонения прибыли при изменении цены;

ΔПSед — сумма отклонения прибыли при изменении себестоимости единицы реализованной продукции;

ΔVпрод — разница между фактическим и плановым (базисным) объемом продаж;

ΔЦ — разница между фактической и плановой (базисной) ценой продаж;

ΔSед — разница между фактической и плановой (базисной) себестоимостью единицы реализованной продукции;

Vпрод. факт — объем продаж фактический;

Vпрод. план — объем продаж плановый;

Цплан — цена плановая;

Цфакт — цена фактическая;

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

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

Замечания

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

Факторный анализ прибыли от продаж

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

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

ПРИМЕР 1

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

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

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

Таблица 1. Данные о результатах финансовой деятельности компании, тыс. руб.
ФакторПланФактОтклонения от плана
абсолютныев процентах
1234 = [3] – [2]5 = [4] / [2] × 100 %
Объем продаж, тыс. т520,20510,10–10,10–1,94
Цена 1 т35,0035,150,150,43
Выручка18 207, 0017 930,02–276,99–1,52
Себестоимость продаж10 220,0011 350,001130,0011,06
Себестоимость продаж 1 т19,6522,252,6013,26
Прибыль7985,076580,29–1404,78–17,59

Из данных табл. 1 следует, что объем продаж фактический ниже планового на 10,1 тыс. т, продажная цена была выше плановой на 0,15 тыс. руб. При этом сумма фактической выручки меньше плановой на 276,99 тыс.

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

Е. В. Акимова, аудитор

Источник: https://www.profiz.ru/peo/article_pribyl_ot_prodazh/

Пример факторного анализа прибыли от продаж

План факторный анализ пример

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

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

  • изменение объема продаж;
  • изменение ассортимента реализованной продукции;
  • изменение себестоимости продукции;
  • изменение цены реализации продукции.

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

Для проведения анализа составим аналитическую таблицу,источником информации служат данные бухгалтерского баланса и отчет оприбылях/убытках компании (1 и 2 форма баланса):

Исходные данные для факторного анализа прибыли от продаж

Показатели Предыдущий период,тыс. руб. Отчетный период,тыс. руб. Абсолютное изменение,тыс. руб. Относительноеизменение, %
12345
Выручка от продажи продукции, работили услуг57 80054 190-3 610-6,2%
Себестоимость41 82939 780-2 049-4,9%
Коммерческие расходы2 6151 475-1 140-43,6%
Управленческие расходы4 8163 765-1 051-21,8%
Прибыль от продаж8 5409 1706307,4%
Индекс изменения цен1,001,150,1515,0%
Объем реализации в сопоставимыхценах57 80047 122-10 678-18,5%

Таблица данных в xls.

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

1. Для определения влияния объема продаж на прибыльнеобходимо прибыль предыдущего периода умножить на изменение объема продаж.

Выручка от реализации товаров предприятия в отчетном периодесоставила 54 190 тыс. рублей, для начала необходимо определить объемпродаж в базисных ценах (54 190/1,15), который составил 47 122 тыс.рублей.

С учетом этого изменение объема продаж за анализируемый периодсоставило 81,5% (47 122/57 800*100%), т.е. произошло снижение объемареализованной продукции на 18,5%.

За счет снижения объема продажипродукции прибыль от продажи продукции, работ, услуг снизилась: 8 540 *(-0,185) = -1 578 тыс. рублей.

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

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

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

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

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

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

  • выручка от продажи отчетного периода в ценах базисного периода 47 122 тыс. рублей;
  • фактически реализованная продукция, рассчитанная по базисной себестоимости (41 829*0,815) = 34 101 тыс. рублей;
  • коммерческие расходы базисного периода 2 615 тыс. рублей;
  • управленческие расходы базисного периода 4 816 тыс. рублей;
  • прибыль отчетного периода, рассчитанная по базисной себестоимости и базисным ценам(47 122-34 101-2 615-4 816) = 5 590 тыс. рублей.

Таким образом, влияние сдвигов в структуре ассортимента навеличину прибыли от продаж равно: 5 590 – (8 540*0,81525) = -1 373 тыс. рублей.

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

3. Влияние изменения себестоимости наприбыль можно определить, сопоставляя себестоимость реализациипродукции отчетного периода с затратами базисного периода,пересчитанными на изменение объема продаж: (41 829*0,815) – 39780= -5 679 тыс. рублей. Себестоимость реализованнойпродукции увеличилась, следовательно, прибыль от продажи продукцииснизилась на ту же сумму.

4. Влияние изменения коммерческих и управленческихрасходов на прибыль компании определим путем сопоставления ихвеличины в отчетном и базисном периодах. За счет снижения размеракоммерческих расходов прибыль выросла на 1 140 тыс. рублей (1 475 -2 615), а за счет снижения размера управленческих расходов– на 1 051 тыс. рублей (3 765 – 4 816).

5. Для определения влияния цен реализациипродукции, работ, услуг на изменение прибыли необходимо сопоставитьобъем продаж отчетного периода, выраженного в ценах отчетного ибазисного периода, т.е.: 54 190 – 47 122 = 7 068 тыс. рублей.

Подводя итог, посчитаем общее влияние всех перечисленныхфакторов:

  1. влияние объема продаж -1 578 тыс. рублей;
  2. влияние структуры ассортимента реализованной продукции -1 373 тыс. рублей;
  3. влияние себестоимости -5 679 тыс. рублей;
  4. влияние величины коммерческих расходов +1 140 тыс. рублей;
  5. влияние величины управленческих расходов +1 051 тыс. рублей;
  6. влияние цен реализации +7 068 тыс. рублей;
  7. общее влияние факторов +630 тыс. рублей.

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

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

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

Источник: http://afdanalyse.ru/publ/finansovyj_analiz/1/1/11-1-0-93

Факторный анализ в 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

Для примера также прилагаем факторный анализ отклонений в маржинальном доходе.

В предыдущих статьях мы рассмотрели основы построения графиков и диаграмм в Excel (см рубрику Диаграммы и Графики). Сегодня мы усложним задачу и попробуем провести факторный анализ в Excel (упрощенный конечно). Допустим у нас есть точка продаж каких-либо товаров, например мобильных телефонов и продажи от недели к неделе могут то расти то падать.

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

Для того чтобы наглядно увидеть какой из брендов «просел» в продажах нам и поможет факторный анализ в Excel (в нашем примере построение гистограммы по определенным условиям).

Итак, у нас есть данные о продажах за 4 недели:

Данные к графику

Мы ходим понять за счет каких телефонов произошел основной рост по итогам второй недели. Представим данные несколько в другом виде:

Преобразование Данных

Если произошел рост по сравнению с прошлой неделей по отдельному бренду, то положительную дельту мы запишем в столбец «Рост», а отрицательную в «Снижение».

Например в ячейке К4 у нас будет прописана формула =ЕСЛИ((C3- B3)>0;C3-B3;0) а в ячейку L4     =ЕСЛИ(K4=0;B3-C3;0) . (Можно прописать Рост и Снижение через другие функции — в примере то, что первое пришло на ум).

В столбце  J указана сумма предыдущего значения плюс предыдущий рост без текущего снижения =J3+K3-L4. 

Теперь рассчитаем вклад каждого из брендов (% роста) =ЕСЛИ(K4/$J$11=0;-L4/$J$11;K4/$J$11) :

Данные для построения гистограммы

теперь осталось только выделить всю область для построения диаграммы (в подпись данных нужно включить и столец «% роста» и «факторы») Можно выделить весь диапазон H1:L10 затем перейядя на вкладку «Вставка» выбрать «Гистограмма» (подробнее смотри в статье «Как построить график в Excel»):

Полученный график

Поработаем с подписями данных и цветами (уберем накопительный итог оставив только Рост и Снижение):

Факторный анализ в Excel

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

При желании можно сделать график «динамическим». Например, сделать всплывающий список из недель (1ая, 2ая …), а в формулы столбца Роста (Снижения и остальных стобцов) включить формулу ВПР, которая в зависимости от указанной недели будет подтягивать в таблицу для факторного анализа соответствующие данные из основной таблицы и график будет меняться!

скачать grafik

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Спасибо за внимание.

Предлагаю вашему вниманию шаблон для проведения простейшего факторного анализа продаж.

Шаблон позволяет разложить общее изменение выручки в текущем периоде по отношению к предыдущему (базовому) периоду на влияние изменения объема продаж и цены продаж.

Также можно анализировать фактический период по отношению к плановому.

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

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

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

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

Отклонение под влиянием фактора объема:

Откл.Объем = (Объем.Факт – Объем.План) х Цена.План

Отклонение под влиянием фактора цены:

Откл.Цена = (Цена.Факт – Цена.План) х Объем.Факт

Для просмотра файла в полном размере нажмите на «квадратики» в правом нижнем углу.

Источник: http://word-office.ru/faktornyy-analiz-v-excel-kak-sdelat.html

Как провести факторный анализ отклонений?

План факторный анализ пример

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

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

Пример 6.6

Рассмотрим отклонения от гибкого бюджета по основным материалам и выявим причины этих отклонений. Данные табл. 6.12 позволяют провести факторный анализ для компании по производству посуды “КрЕНЗа”.

Таблица 6.12

Исходные данные для анализа отклонений по основным материалам

Показатели

Гибкий бюджет

Факт

Отклонения

Стоимость глины, руб.

303559

373580,1

70021,6

Расход глины, кг

1043,6

1153,6

110

Средняя стоимость 1 кг глины

290,9

323,8

32,9

Влияние отклонения расхода глины (норм расхода) рассчитывается следующим образом:

(1153,6 – 1043,6) × 290,9 = 31 999 руб.

Влияние отклонения цены материалов рассчитаем так:

(323,8 – 290,9) × 1153,6 = 37 953,4 руб.

Таким образом, увеличение стоимости основных материалов на 70022 руб. произошло на 54% (37953 руб.) за счет увеличения цены материала и на 46% (31999 руб.) за счет превышения норм расхода.

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

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

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

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

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

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

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

Анализируя отдельные отклонения, менеджер должен понимать, что это не является самоцелью. Основная задача на данном этапе – поиск резервов снижения себестоимости – может быть решена разными путями.

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

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

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

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

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

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

Источник: https://studme.org/1101122723533/ekonomika/kak_provesti_faktornyy_analiz_otkloneniy

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