Excel план факт шаблон - блог о рисовании и уроках фотошопа

Детальный анализ данных по месяцам

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

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

В строке 8 выведены только месяцы, в которые производились работы (периоды простоя не учитываются, т.к. там анализировать нечего). Всего 7 месяцев.

Примечание: Если пользователь с помощью Полосы прокрутки выбирает значение более длительности проекта, то счетчик текущего месяца в ячейке В15 автоматически перебрасывает на 1-й месяц проекта. Это позволяет не перестраивать полосу прокрутки при изменении длительности проекта.

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

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

При изменении месяца таблица будет перестроена автоматически, а вместе с ней и график Плана-Фактного анализа.

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

Метод абсолютных разниц

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

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

$Y (a) = a • b_0 • c_0$, фактор $а$ рассматривается в динамике его изменения, остальные факторы исследуются в их базисном значении.
$Y(b) = a_1 • b • c_0$, то есть рассматриваемый фактор $b$ берется в динамике, предыдущий фактор a оценивается по его фактическому значению, а последующий фактор c по базисному.
$Y (c) = a_1 • b_1 • c$, здесь соблюдается тот же принцип, что и вышеуказанных моделях.

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

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

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

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

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

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

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

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

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

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

Анализ данных с помощью 3D-карт

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

Как работать с 3D-картами в Excel

Последовательность действий, которую вам необходимо выполнить, чтобы работать с 3Д-картами, следующая:

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

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

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

ФАКТОРНЫЙ АНАЛИЗ: ОБЩАЯ ХАРАКТЕРИСТИКА И СПОСОБЫ ПРОВЕДЕНИЯ

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

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

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

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

Показатели для факторного анализа берут из бухгалтерского учета. Если анализируют итоги за год, то используют данные формы № 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 точки.

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

Определим первый месяц работ — используем первую дату в исходной таблице (напоминаю, что таблица должна быть отсортирована по возрастанию по датам).
Вычислим общую плановую длительность проекта в месяцах. Это сделано в ячейке I9 на основании первой и последней даты проекта (с использованием элементарных очевидных вычислений).
В столбце Месяц будем указывать первый день месяца. Его можно рассчитать с помощью функции =КОНМЕСЯЦА(). Подробнее см. здесь.
Столбцы План и Факт заполняются с помощью функции =СУММЕСЛИМН(). Подробнее про суммирование с условием см. здесь.

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

Хотя наш проект длится 17 месяцев, мы расширим вышеуказанную таблицу, скажем, до 24 месяцев, чтобы иметь более универсальную форму. Затем создадим 3 именованных диапазона: 2 для плана и факта, 1 для даты. Эти диапазоны будут автоматически изменять свою длину в зависимости от плановой длительности проекта.

Теперь вручную определим ряды для диаграммы:

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

Как видно из картинки ниже, именованный диапазон автоматически вырезал из увеличенной таблицы нужный нам временной интервал

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

Анализ «план-факт» деятельности ТРЦ с помощью надстройки Power Query

Этап 1. Подготавливаем исходные данные для анализа «план-факт» в Power Query

Шаг 1. Формируем плановые показатели (лист книги Excel «Модель БДР план»)

Ежегодные показатели доходов и расходов, запланированные планово-экономическим подразделением ТРЦ на 5-летний период, приведены в табл. 3. Диапазону ячеек указанной плановой таблицы присваивается имя «БДР-план» (именованный диапазон для формирования соответствующего запроса ).

Шаг 2. Формируем фактические показатели (лист книги Excel «Модель Отчет факт»)

Ежегодные показатели доходов и расходов, зарегистрированные в отчетности ТРЦ в течение 5-летнего периода, приведены в табл. 3. Диапазону ячеек указанной отчетной таблицы присваивается имя «ОТЧЕТ-факт» именованный диапазон для формирования соответствующего запроса ).

Этап 2. Формируем запросы для план-фактного анализа в Power Query

Шаг 3. Формируем запрос в модели данных

Запрос включает в себя следующие операции (рис. 1):

1) выбор любой ячейки таблицы на листе «Модель БДР план»;

2) выбор вкладки «Данные» ленточного интерфейса Excel 2016;

3) выбор вида запроса «Из таблицы» в разделе «Скачать & преобразовать».

Power Query формирует проект запроса по данным таблицы «Модель БДР план»;

4) запрос именуется БДР-план, из меню ленты запроса выбирается «Закрыть и загрузить в…», применяя опции «Только создать подключение», «Добавить эти данные в модель данных»;

5) после подтверждения (нажимаем ОК) запрос загружается в модель данных.

Шаг 4. Формируем запрос в модели данных

Запрос включает следующие операции (аналогично запросу ):

1) выбор таблицы на листе «Модель Отчет факт»;

2) выбор вкладки «Данные» ленточного интерфейса Excel 2016;

3) выбор вида запроса «Из таблицы» в разделе «Скачать & преобразовать».

Power Query формирует проект запроса по данным таблицы «Модель Отчет факт»;

4) запрос именуется Отчет-факт, в меню выбирается «Закрыть и загрузить в…», применяя опции «Только создать подключение», «Добавить эти данные в модель данных». После подтверждения (нажимаем ОК) запрос загружается в модель данных.

Этап 3. Объединяем запросы для план-фактного анализа в Power Query

БДР (бюджета доходов и расходов), БДДС (бюджета движения денежных средств), ББЛ (бюджета по балансовому листу).

Дашборд (англ. Dashboard) — аналитический инструмент, наглядное представление информации о бизнес-процессах, о состоянии какого-то объекта в виде динамических онлайн-диаграмм.

Power BI — это комплексное программное обеспечение бизнес-анализа (BI) компании Microsoft, объединяющее несколько программных продуктов, имеющих общий технологический и визуальный дизайн, соединителей (шлюзов), а также web-сервисов. Power BI относится к классу self-service BI, и BI с резидентным вычислением (англ. in-memory computing). Является частью единой платформы Microsoft Power Platform.

Ключевой и самый первый продукт линейки — Power BI Desktop состоит из трех интегрированных компонентов, имеющих каждый свой интерфейс:

Power Query (редактор запросов) — выполняет загрузку и очистку данных (ETL);
PowerPivot (наборы данных и модели данных) — интерфейс работы с табличными данными в оперативной памяти где выполняются запросы к данным, агрегация, расчёты и т. п.;
Power View — подсистема визуализации и построения отчётов (Reporting).

Квадратными скобками (напр., ) обозначены объекты модели данных.

Выбор варианта (опции) загрузки запроса «Таблица» создаст дополнительную таблицу в книге Excel. Этот выбор имеет смысл после внесения всех данных анализа «план-факт» в Модель Данных»

Факторный анализ прибыли от продаж (пример расчета)

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

Рассмотрим важность каждого из показателей для формирования прибыли. Объем проданной продукции и прибыль организации

Объем проданной продукции и прибыль организации

Для анализа необходимо пересчитать количество проданной продукции по базисным ценам: 12 000 / 1,25 = 9 600 тыс. рублей. Таким образом, изменение объема продаж составляет: 9 600 / 11 500 * 100% = 83,5%. Иными словами, количество проданного товара упало на 16,5%. В связи с этим снизилась и прибыль предприятия: 1 600 * (-0,165) = -264 тыс. рублей.

Расходы на производство или покупку товара

Для анализа влияния себестоимости продукции следует пересчитать ее показатель базисного периода на изменения объема проданной продукции: 8 000 * 0,835 = 6 680 тыс. рублей. Выявим разницу с реальной себестоимостью текущего периода: 6 680 — 7 700 = -1 020 тыс. рублей. Этот показатель говорит о том, что себестоимость продукции увеличилась и повлекла за собой уменьшение прибыли.

Коммерческие и управленческие расходы

Анализ влияния расходов производится при сопоставлении показателей базисного года и текущего года. Коммерческие расходы в примере увеличились, в связи с этим прибыль уменьшилась на 200 тыс. рублей (1 500 — 1 300). Увеличение управленческих расходов также повлекло за собой уменьшение прибыли на 150 тыс. рублей (750 — 600). Таким образом, увеличение расходов влечет за собой сокращение прибыли.

Изменение цен

При расчете влияния цен на прибыль организации необходимо сравнить объем полученных доходов за отчетный период в текущих и базисных ценах. Объем продаж в базисных ценах составит: 12 000 / 1,25 = 9 600 тыс. рублей. Влияние цены рассчитывается как: 12 000 — 9 600 = 2 400 тыс. рублей. Поскольку в текущем периоде цены на продаваемую продукцию увеличились, то на результат расчета фактор цены повлиял положительно, то есть прибыль с ростом цены увеличилась на 2 400 тыс. рублей.

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

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

3.4. Виды моделей. Моделирование

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

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

Основные свойства детерминированного подхода к анализу:

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

Моделирование — процесс представления исследуемого показателя с факторами, которое передается в форме конкретного математического уравнения.

Различают четыре типа детерминированных моделей.

1. Аддитивные модели представляют собой алгебраическую сумму показателей и имеют вид:

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

2. Мультипликативные модели в обобщенном виде могут быть представлены формулой:

Примером мультипликативной модели является двухфакторная модель объема производства продукции:

где Ч — среднесписочная численность работников;
CB — средняя выработка на одного работника.

3. Кратные модели:

Примером кратной модели служит показатель срока оборачиваемости товаров (в днях) Т_ОБ.Т:

где З_Т — средний запас товаров;О_Р — однодневный объем реализации.

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

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

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

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

A = a + b; b = c + d; A = a + c + d или

A = a * b; b = c * d; A = a * c * d

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

Кратные модели преобразуются следующими способами:

1. Удлинение.

2. Формальное разложение.

b = b1 + b2 + b3

3. Расширение.

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

4. Сокращение.

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

Процесс моделирования сложный и ответственный момент. От реальности и точности моделей зависят конечные результаты анализа.

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

В основе их построения лежат следующие принципы:

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

Построение факторной модели — первый этап детерминированного анализа. Далее определяют способ оценки влияния факторов.

Бальжинов А.В., Михеева Е.В. Анализ и диагностика финансово-хозяйственной деятельности предприятия: Учебн.пособ., — Улан-Удэ, 2003.

Факторный анализ пример расчета по себестоимости складских запасов

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

Проделаем те же шаги, что и на предыдущих двух этапах.
Видим, что у Магазин 3, который показал высокий рост выручки и совсем небольшой рост операционной прибыли, очень сильно выросла сумма складских запасов.
Какой можно сделать предварительный вывод? Например, что Магазин 3, увидев тенденцию к росту продаж, арендовал дополнительную площадь для хранения товарных запасов. А рост выручки хоть и был достаточно высок — но меньше ожидаемого, и в итоге аренда дополнительных площадей для хранения сказалась на прибыли не лучшим образом.

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

Более подробно о факторном анализе с примерами расчета можно прочитать в статьях: