Как Сделать Факторный Анализ в Excel Пошагово • Ретроспективный анализ
Факторный анализ в excel пример гистограмма. План-факт анализ в Excel
Хотите узнать, как лучше всего распространять информацию о точном прогнозе надежности ваших продуктов и компонентов? Например, анализ распределения выживания позволяет указать непараметрическое распределение и количество параметрических распределений и визуально сравнить настройки.
Результаты были помещены в файл данных Factor.sta. Открыть этот файл можно с помощью опции Файл — Открыть; наиболее вероятно, что этот файл данных находится в директории /Examples/Datasets. Ниже приводится распечатка переменных этого файла (для получения списка выберите Все спецификации переменных в меню Данные).
Цель анализа . Целью анализа является изучение соотношений между удовлетворенностью в различных сферах деятельности. В частности, желательно изучить вопрос о числе факторов, «скрывающихся» за различными областями деятельности и их значимость.
Выбор анализа. Выберите Факторный анализ в меню Анализ — Многомерный разведочный анализ для отображения стартовой панели модуля Факторный анализ. Нажмите на кнопку Переменные на стартовой панели (см. ниже) и выберите все 10 переменных в этом файле.
Анова: два фактора с репликацией. Этот инструмент анализа подходит, когда можно классифицировать данные в соответствии с двумя разными размерами. Представьте себе исследование по высоте растений с различными марками удобрений и которые хранятся при разных температурах. Для каждой пары (удобрения, температура) для высоты растений получено равное количество наблюдений.
Альтернативная гипотеза состоит в том, чтобы предположить, что есть эффекты, связанные с определенными парами (удобрениями, температурами) за пределами различий, которые связаны только с удобрением или только с температурой. Анова: два фактора без репликации.
Этот инструмент анализа полезен, когда данные классифицируются в соответствии с двумя различными измерениями, как в случае двухфакторного теста с репликацией. Однако использование этого инструмента предполагает одно наблюдение для каждой пары. Коэффициент корреляции, как и ковариация, позволяет узнать, до какой степени две числовые переменные «меняются вместе».
Просмотр описательных статистик. Теперь нажмите на кнопку Просмотреть корр./средние/ст.откл. в этом окне для того, чтобы открыть окно Просмотр описательных статистик.
Эти два инструмента предоставляют таблицу результатов, матрицу, которая соответственно показывает коэффициент корреляции или ковариацию между каждой парой числовых переменных. Разница между этими двумя инструментами связана с тем, что коэффициенты корреляции находятся в шахматном порядке между -1 и 1 включительно. Ковариации не пошатнулись.
Коэффициент корреляции и ковариация показывают, насколько две переменные «меняются вместе». Средство статистического анализа генерирует одномерный статистический отчет из данных диапазона ввода и предоставляет информацию о центральном тренде и изменчивости данных.
Теперь вы можете рассмотреть описательные статистики графически или с помощью таблиц результатов.
Вычисление корреляционной матрицы. Нажмите на кнопку Корреляции во вкладке Дополнительно для того, чтобы отобразить таблицу результатов с корреляциями.
Инструмент анализа экспоненциального сглаживания. Инструмент анализа экспоненциального сглаживания вычисляет значение, основанное на предсказании, установленном для предыдущего периода. Это значение корректируется в соответствии с ошибкой этого предыдущего прогноза.
Константы сглаживания обычно принимают значения от 0, 2 до 0. Эти значения показывают, что прогноз должен быть скорректирован до 20 или 30 процентов для предыдущей ошибки прогноза. Более высокие константные значения приводят к более быстрым ответам, но могут генерировать противоречивые прогнозы.
Все корреляции в этой таблице результатов положительны, а некоторые корреляции имеют значительную величину. Например, переменные Hobby_1 и Miscel_1 коррелированны на уровне 0.90. Некоторые корреляции (например, корреляции между удовлетворенностью на работе и удовлетворенностью дома) кажутся сравнительно малыми. Это выглядит так, как будто матрица имеет некоторую отчетливую структуру.
Просмотр результатов. Вы можете просмотреть результаты факторного анализа в окне диалога Результаты факторного анализа. Сначала выберите вкладку Объясненная дисперсия.
Если население слишком велико для обработки или представления графически, вы можете использовать репрезентативную выборку. Вы также можете создать образец, который содержит только значения определенного цикла, если вы считаете, что данные являются периодическими.
Теперь выполним вращение по методу варимакс. Во вкладке Нагрузки диалогового окна Результаты факторного анализа в поле Вращение факторов установите значение Варимакс исходных.
Нажмем кнопку Факторные нагрузки для отображения в таблице результатов получаемых факторных нагрузок.
Теперь посмотрим, насколько хорошо может быть воспроизведена наблюдаемая ковариационная матрица по двухфакторному решению.
Воспроизведенная и остаточная корреляционная матрица. Нажмите на кнопку Воспроизведенные и остаточные корреляции во вкладке Объясненная дисперсия, для того чтобы получить две таблицы с воспроизведенной корреляционной матрицей и матрицей остаточных корреляций (наблюдаемых минус воспроизведенных корреляций).
Другие результаты . Прежде, чем сделать окончательное заключение, дадим краткие комментарии к другим результатам.
Этот пример взят из справочной системы ППП STATISTICA фирмы StatSoft
Итак, имеем два значения – одно плановое, второе проектное (или базовое и отчетное) и имеем значения отклонения факторов. Задача: построить в Excel красивую диаграмму отображения этих факторов.
Создаем в Excel таблицу, в которой у нас находятся необходимые данные (см.рис.1).
Теперь подпишем столбцы – столбец I – Значение, далее – Основа, далее Влияние фактора (рис.3).
В качестве базовой диаграммы мы будем использовать либо гистограмму либо линейчатую с наполнением.
Теперь поясню на рис.5 что я имею в виду под основой – это такое значение некоторого ряда которое позволит построить нам диаграмму максимально точно.
В вычислении значений этого ряда поступаем следующим образом:
1. Значение первой основы (сразу после базового значения) принимаем равным либо базовому значению (если первый фактор имеет позитивное влияние) либо (базовое значение – величина влияния) – если фактор имеет негативное влияние.
2. Для последующих основ применяется та же схема. Если значение фактора положительное, то за основу берем результирующее значение, полученное на предыдущем факторе. Если же отрицательное, то берем (результирующее – абсолютное значение негативного фактора).
Ту величину, которую я назвал “Влияние фактора” вычисляем как значение изменения фактора по модулю (абсолютное значение) с помощью функции ABS() – рис.6.
ЕСЛИ(L6>0;I5;I5+L6) — т.е. если первый фактор больше нуля, то берем базовое значение, в противном случае берем базовое + значение изменения фактора (в нашем примере получается просто 100).
ЕСЛИ(L7>0;M6;M6+L7) — т.е. если фактор больше нуля, то берем полученное на предыдущем факторе результирующее значение, в противном случае берем базовое + значение изменения фактора.
Ахтунг! Не забывайте про правила сложения – если я говорю “плюс значение”, это значит, что подразумевается не абсолютное значение, а позитивное или негативное. Т.е. для третьего фактора получим следующую логику:
Значение изменения фактора меньше нуля, следовательно берем сумму предыдущего результирующего значения и значения изменения фактора, т.е. основа будет равна 170+(-30)=170-30=140.
ЕСЛИ(L6>0;J6+L6;J6) – т.е. если изменения фактора позитивное, то результирующим значением будет сумма предыдущего результирующего значения и величины изменения фактора, а в противном случае – просто значение основы. Далее переходим уже непосредственно к построению диаграммы. Выделяем ячейки от названия категорий до столбца “Влияние фактора” включительно.
И вставляем необходимый тип диаграммы (в данном случае – гистограмму).
Удаляем вертикальную ось, удаляем основные вертикальные и горизонтальные линии осей и у нас получается нечто вроде рис.9.
В свойствах горизонтальной оси также поставим “Нет линий” (рис.10).
Далее добавляем рядам “Влияние фактора” и “Значение” подписи данных. Но получается маленькая нестыковка – даже в тех случаях, когда изменение фактора было отрицательным у нас выводятся положительные значения. Для этого дальше переходим обратно на лист 1 и выставляем соответственные форматы для позитивных и негативных значений.
Рис.11. Изменение формата чисел в столбце “Влияние фактора”.
Как видим, уже все изменения отображаются логически верно. Остался маленький штришок – находим точки ряда с негативным изменением и изменяем им цвет заливки на красный, а также меняем цвета подписей данных для этого ряда для большей наглядности (рис.13).
Мы получили симпатичную диаграммку, которую не стыдно вставить в презентацию или в документ.
Факторный анализ в excel пример гистограмма. План-факт анализ в Excel
Ахтунг! Не забывайте про правила сложения – если я говорю “плюс значение”, это значит, что подразумевается не абсолютное значение, а позитивное или негативное. Т.е. для третьего фактора получим следующую логику:
Факторный анализ: оцениваем причины изменения показателей
28 октября 2024
Факторный анализ: оцениваем причины изменения показателей
Кандидат экономических наук, доцент. Автор двух монографий, шести учебных пособий и нескольких десятков статей по вопросам бухгалтерского учета, финансового менеджмента и анализа. Лауреат премии губернатора в сфере науки, техники и инновационной деятельности за 2012 г. За плечами — опыт работы главбухом бюджетного учреждения и преподавателем государственного вуза.
Факторный анализ: что это и зачем нужен
Факторный анализ – это оценка влияния одних показателей на другие. Например, вы хотите узнать причину снижения финансового результата. Для этого нужно посчитать, какое воздействие на него оказали доходы и расходы. Или вам важно понять, почему просела рентабельность собственного капитала. Тогда исследуйте, какой вклад в процесс внесли прибыль на рубль активов и структура пассивов.
Предлагаем скачать Excel-файл. В него встроили четыре формулы для анализа рентабельности активов, продукции и собственного капитала.
Вот три вопроса, ответы на которые понадобятся, чтобы провести подобные расчеты.
Это формульная взаимосвязь между основным показателем, который вы анализируете, и теми, что на него влияют.
Откуда ее взять? Есть готовые варианты. К примеру, факторные модели DuPont для рентабельности активов и собственного капитала. Вот они:
Рисунок 1. Факторные модели DuPont для рентабельности активов и собственного капитала
Можно составить зависимость самим. Иногда это несложно. Особенно, если отталкиваться от базовой формулы расчета показателя. Приведем два простых примера, основанных на таком подходе:
Рисунок 2. Примеры факторный моделей
Условимся называть искомое значение в формулах «результатом», а прочие показатели «факторами».
Второй. Почему иногда универсальной зависимости между результатом и факторами нет.
Примеры, которые привели выше, относятся к первому варианту. Их особенность в том, что взаимосвязь между показателями однозначна и работает для любой организации. Допустим, чистая прибыль выросла в два раза при неизменном значении выручки. Значит, рентабельность продаж тоже увеличится в два раза. По-другому не бывает.
Чтобы не усложнять, далее в статье все сведем только к детерминированным зависимостям.
Третий. Как посчитать влияние факторов на результат.
Для этого есть специальные методы. К примеру, метод цепных подстановок, абсолютных и относительных разниц. Это не полный перечень. Но знать все необязательно. В следующих частях материала расскажем теорию про два основных подхода и покажем примеры использования.
Модели и методы факторного анализа
Начнем с видов моделей. Они – это форма взаимосвязи между факторами. Выше приводили примеры, где показатели складывались, перемножались или делились один на другой. Это и определяет вид. Смотрите на схеме четыре возможных варианта.
Теперь расскажем про методы. То есть про то, как посчитать влияние факторных показателей на результирующий. Остановимся на двух подходах:
Первый вариант хорош своей универсальностью. Подойдет для модели любого типа.
Второй отличается чуть большей простотой вычислений, однако используется с ограничениями. Пригодится, когда анализируете мультипликативные модели или смешанные мультипликативно-аддитивные.
- сначала подставляете в формулу значения базового периода. Так делаете для факторов и результата. Базовый период обозначим индексом 0. Это тот, что самый ранний по времени. Например, для факторного анализа изменений за 2024-2022 гг. цифры из 2024 г. – базовые;
- затем считаете первое условное значение результата. Для этого у первого фактора базовое значение меняете на отчетное. Индекс поменяется с нуля на единицу. Отчетные цифры – это величины из последнего по времени периода. В примере выше – из 2024-го;
- из полученного первого условного результата вычитаете базовый. Так находите влияние первого фактора;
- вычисляете второй условный результат. Для этого работаете со следующим фактором в модели – меняете его цифру с базовой на отчетную. Заметьте: первый фактор так и остается отчетным, его не трогаем;
- считаем разницу между вторым и первым условными результатами. Она показывает силу воздействия второго фактора;
- повторяем процесс до тех пор, пока все факторы в модели не окажутся с отчетными значениями.
- значения результата называются условными потому, что в реальности компания их не достигает. Они нужны только для подсчета факторного влияния;
- условных значений на единицу меньше, чем факторов. В примере использовали трехфакторную модель. Условных результатов вышло два;
- главное – не перепутать что из чего вычитать. Сначала из первого условного вычитаем отчетную величину. Затем из второго – первое, из третьего – второе и т.д. Идем словно по цепочке от последнего к предыдущему. Отсюда и название подхода;
- для проверки правильности расчета сложите все факторные влияния. Сумма должна сравняться с изменением результата за анализируемый период. Если не получается, значит, закралась ошибка;
- главный недостаток метода – так называемый неразложимый остаток. Это взаимное влияние факторов друг на друга. Выражается в том, что оценка воздействия последнего фактора в модели всегда завышена. Причем, если поменять порядок смены базовых значений на отчетные, то станет иной и величина влияния. Например, если бы сначала меняли значения З, а не СК, то получили бы другие цифры. Это не ошибка. Это особенность, с которой в рамках цепных подстановок ничего не сделать. Если нужна повышенная точность, тогда используйте интегральный или логарифмический методы факторного анализа.
- рассчитайте влияние первого фактора. Для этого включите в формулу его абсолютное изменение. Обозначим эту величину значком ∆. Считается как разница между отчетным и базовым значениями. Все остальные факторы должны быть с базовыми цифрами;
- определите воздействие второго фактора. Включайте в формулу его абсолютное изменение ∆. Фактор, который стоит слева от него в модели, берется с отчетным значением. Те, что находятся справа, пойдут с базовыми;
- повторите вычисления для третьего и последующих факторов, если они есть. Логика та же. Фактор, чье влияние оцениваем, включается как ∆. Стоящие от него слева как отчетные. Находящиеся справа как базовые.
Согласитесь, этот подход проще, чем цепные подстановки. Все бы с ним хорошо, если бы не упомянутое выше ограничение в применении по типам моделей.
Хотя запутаться в подобных расчетах негде, однако есть смысл сделать проверку. Сложите все факторные влияния и сравните полученное с абсолютным изменением результата за период. Если сходится, значит, все правильно.
Как провести факторный анализ: пример
Теперь закрепим написанное расчетами для реальной компании. Понадобится ее финансовая отчетность. А еще нужно определиться с моделями.
Будем основываться на цифрах из баланса и отчета о финансовых результатах за 2024 г. для ПАО «КАМАЗ». Воспользуемся четырьмя факторными моделями. Их формулы показали на схеме. Первая и последняя – это простая и расширенная формулы DuPont.
Анализируем рентабельность активов по двухфакторной модели
Исходные данные и расчетные значения свели в таблицу. Так как модель мультипликативная, то применили способ абсолютных разниц.
Суммарное влияние факторов 1,61 соответствует абсолютному изменению рентабельности активов за 2019-2022 гг. Значит, ошибки в расчетах нет. Есть смысл перейти к анализу цифр. Вот краткие выводы:
- за год предприятие ушло от убыточности активов к их рентабельности. В чем главная причина? Во влиянии рентабельности продаж. За счет нее показатель прирос на 1,57%. Она характеризует, сколько чистого финансового результата содержится в одном рубле выручки. Когда увеличивается, это означает: доходы компании прирастают быстрее, чем расходы. Так происходит за счет оптимизации последних, грамотной ценовой политики, стабильного спроса на продукцию предприятия;
- оборачиваемость ресурсов тоже внесла положительный вклад в изменение рентабельности активов. Правда, он в разы меньше – 0,04. Выходит, с точки зрения управления имуществом предприятию есть куда расти. В 2024-м прирост выручки с каждого рубля, вложенного в активы, оказался всего 5 копеек. Отлично, что он был. Однако, возможно, стоит поискать резервы для дальнейшего улучшения ситуации.
Анализ по первой модели рассказал о трех особенностях функционирования компании:
- в 2024-м она попала в зону чистой прибыли за счет оптимального соотношения между совокупными доходами и расходами;
- в этом же году немного выросла эффективность управления активами или ресурсоотдача;
- вместе эти факторы определили рост рентабельности активов на 1,57%.
Оцениваем рентабельность активов по трехфакторной модели
По проведенным расчетам заметили: доходная отдача от активов у ПАО «КАМАЗ» не столь велика. Посмотрим, какая их составляющая «проседает» в этом отношении особенно сильно. Поможет трехфакторная модель. Цифры по ней находятся в таблице. Для определения факторного влияния применили способ цепных подстановок, так как абсолютные разницы для модели данного смешанного типа не подходят.
– коэффициент закрепления оборотных средств (Кз), руб./руб.
В отношении оборотных активов ситуация иная: они тоже увеличились, но выручка приросла значительнее. Выходит, отдача с каждого вложенного в них рубля стала больше.
Аналогичное мнение относительно суммы вложений в имущество и ожидаемой от него доходности разумно сформулировать по каждому значимому элементу первого раздела баланса.
Выявляем влияние факторов на рентабельность продукции
Данная модель поможет понять структуру себестоимости. А еще, какой ее элемент растет скорее остальных, а потому особенно сильно «съедает» прибыль бизнеса. Расчеты привели в таблице. Влияние факторов оценивали цепными подстановками.
Как сделать финансовый анализ предприятия в excel?
- за год предприятие ушло от убыточности активов к их рентабельности. В чем главная причина? Во влиянии рентабельности продаж. За счет нее показатель прирос на 1,57%. Она характеризует, сколько чистого финансового результата содержится в одном рубле выручки. Когда увеличивается, это означает: доходы компании прирастают быстрее, чем расходы. Так происходит за счет оптимизации последних, грамотной ценовой политики, стабильного спроса на продукцию предприятия;
- оборачиваемость ресурсов тоже внесла положительный вклад в изменение рентабельности активов. Правда, он в разы меньше – 0,04. Выходит, с точки зрения управления имуществом предприятию есть куда расти. В 2024-м прирост выручки с каждого рубля, вложенного в активы, оказался всего 5 копеек. Отлично, что он был. Однако, возможно, стоит поискать резервы для дальнейшего улучшения ситуации.
Вычисление корреляции в Excel намного проще, чем t- тест или ANOVA. Используйте кнопку « Анализ данных» , чтобы открыть окно «Инструменты анализа» и выбрать « Корреляция» .
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.