Как провести анализ отклонений в Excel: пошаговое руководство

Чтобы провести анализ отклонений в Excel, расположите бюджетные и фактические показатели рядом для каждой статьи. Вычтите бюджет из факта, чтобы получить отклонение в денежном выражении. Разделите это отклонение на бюджет, чтобы получить процент. Затем отметьте каждую строку как благоприятную или неблагоприятную и объясните самые большие разрывы.
В этом руководстве рассматриваются формулы, структура рабочего листа и полный практический пример. Также описывается, как построить диаграмму результатов и как разделить разрыв в выручке на цену и объем. Шаги в Excel основаны на страницах поддержки Microsoft, изученных 10 ноября 2026 года.
Что показывает анализ отклонений
Отклонение — это разрыв между тем, что вы планировали, и тем, что произошло. Анализ отклонений перечисляет эти разрывы построчно и объясняет те, которые имеют значение.
Большинство команд сравнивают фактические показатели с одной из трех баз:
- Бюджет. План, установленный в начале года.
- Прогноз. Обновленный план, составленный в течение года.
- Предыдущий период. Прошлый месяц, прошлый квартал или тот же период прошлого года.
Собственный навык Copilot от Microsoft для этой задачи хорошо описывает работу. Его навык анализа отклонений «сравнивает фактические результаты с бюджетами, прогнозами или предыдущими периодами и объясняет различия по цене, объему, структуре, ставке или срокам».
Результатом обычно является таблица плюс несколько письменных заметок. Таблица показывает каждый разрыв. Заметки объясняют три или четыре отклонения, на которые руководителям необходимо отреагировать.
Основные формулы
Большую часть работы выполняют три формулы. Предположим, что бюджет находится в столбце B, а факт — в столбце C.
| Показатель | Формула | Что это говорит |
|---|---|---|
| Денежное отклонение | =C2-B2 | Насколько факт отличается от бюджета в валюте |
| Процентное отклонение | =IFERROR((C2-B2)/ABS(B2),"") | Насколько велик разрыв относительно плана |
| Статус | =IF(D2=0,"On plan",IF(D2>0,"Above plan","Below plan")) | В каком направлении изменилась строка |
Функция ABS сохраняет знак корректным, когда бюджет отрицательный. IFERROR оставляет ячейку пустой, если бюджет равен нулю, вместо отображения ошибки.
Направление — это не то же самое, что «хорошо» или «плохо». Выручка выше плана — это хорошие новости. Затраты выше плана — плохие новости. В следующих разделах показано, как с этим работать.
Как настроить рабочий лист
Чистая структура позволяет легко копировать формулы. Используйте одну строку для каждой статьи и следующие столбцы:
- Статья. Выручка, себестоимость реализованной продукции, фонд оплаты труда, маркетинг и так далее.
- Тип. Введите «Выручка» или «Затраты», чтобы Excel знал, какое направление является благоприятным.
- Бюджет. Плановая сумма на период.
- Факт. Сумма из вашей выгрузки из бухгалтерии.
- Отклонение. Факт минус бюджет.
- Отклонение %. Отклонение, деленное на бюджет.
- Благоприятное или неблагоприятное. Метка, основанная на типе и направлении.
- Объяснение. Краткая заметка для существенных строк.
Соблюдайте соответствие бюджета и факта за один и тот же период и с одинаковым сопоставлением счетов. Анализ отклонений хорош лишь настолько, насколько точно это совпадение. Если фонд оплаты труда в бюджете разбит на два счета, разбейте его так же и в фактических данных.
Отформатируйте таблицу как таблицу Excel с помощью Ctrl+T. Тогда новые строки будут подхватывать формулы автоматически.
Как рассчитать отклонения в Excel
Когда структура готова, шаги будут короткими. Предположим, что столбцы выше начинаются в строке 2, где Тип в B, Бюджет в C, а Факт в D.
- В E2 введите =D2-C2 для денежного отклонения.
- В F2 введите =IFERROR((D2-C2)/ABS(C2),"") и отформатируйте как процент.
- В столбце Статус введите тест на благоприятность, показанный ниже.
- Скопируйте формулы во все строки.
- Добавьте строку итогов и рассчитайте ее отклонение от итоговых сумм, а не путем сложения процентов.
Вот тест на благоприятность для строки 2 столбца Статус:
=IF(E2=0,"On plan",IF(B2="Revenue",IF(E2>0,"Favorable","Unfavorable"),IF(E2<0,"Favorable","Unfavorable")))
Формула сначала считывает столбец Тип. Для выручки положительное отклонение является благоприятным. Для затрат отрицательное отклонение является благоприятным, так как вы потратили меньше запланированного.
Проценты требуют осторожности в строке итогов. Сложение процентов по строкам даст неверный ответ. Вместо этого разделите общее отклонение на общий бюджет.
Как отметить отклонения, которые имеют значение
Не каждый разрыв требует пояснения. Большинство команд устанавливают правило существенности, например «более 5% и более $2,000». Строки, прошедшие оба теста, получают объяснение.
Простая формула помечает их. Добавьте столбец «Существенно» и введите =AND(ABS(F2)>0.05,ABS(E2)>2000) в строке 2. В ячейке отобразится TRUE для существенных строк.
Цвет ускоряет просмотр таблицы. Условное форматирование Excel может добавлять наборы значков. Microsoft утверждает, что набор значков может «аннотировать и классифицировать данные по трем-пяти категориям, разделенным пороговым значением».
Чтобы добавить его, выделите столбец Отклонение %. Затем перейдите в Главная > Условное форматирование > Наборы значков. Microsoft отмечает, что в наборе «3 стрелки» «зеленая стрелка вверх представляет более высокие значения». Для строк затрат измените порядок значков на обратный, чтобы рост отображался как предупреждение.
Как объяснить факторы влияния
Цифры показывают, где возник разрыв. Факторы объясняют почему. Описание навыка Microsoft перечисляет пять распространенных факторов: цена, объем, структура, ставка и сроки.
Цена. Вы продали или купили по цене, отличной от запланированной. Отклонение по цене = (фактическая цена − бюджетная цена) × фактическое количество единиц.
Объем. Вы продали или использовали другое количество. Отклонение по объему = (фактическое количество − бюджетное количество) × бюджетная цена.
Структура (Mix). Изменилась доля продуктов, регионов или клиентов. Сдвиг в сторону товаров с более низкой маржой снижает прибыль, даже если объем сохраняется.
Ставка. Изменилась стоимость ресурса, например почасовая ставка оплаты труда или комиссия поставщика.
Сроки. Деньги переместились между периодами. Мартовский счет, оплаченный в апреле, отображается как разрыв в обоих месяцах.
Цена и объем вместе объясняют весь разрыв в выручке. Эту проверку стоит делать каждый раз, так как она подтверждает правильность расчетов.
Пример анализа отклонений
Вот практический пример для вымышленной компании Cedar and Pine Coffee. Ее финансовый руководитель сравнивает фактические показатели за третий квартал с бюджетом.
| Статья | Тип | Бюджет | Факт | Отклонение | Отклонение % | Статус |
|---|---|---|---|---|---|---|
| Выручка | Revenue | $250,000 | $236,500 | −$13,500 | −5.4% | Unfavorable |
| Себестоимость | Cost | $100,000 | $97,200 | −$2,800 | −2.8% | Favorable |
| ФОТ | Cost | $60,000 | $64,800 | $4,800 | 8.0% | Unfavorable |
| Маркетинг | Cost | $20,000 | $26,000 | $6,000 | 30.0% | Unfavorable |
| Аренда | Cost | $15,000 | $15,000 | $0 | 0.0% | On plan |
| Операционная прибыль | $55,000 | $33,500 | −$21,500 | −39.1% | Unfavorable |
Итоговая сумма сходится. Выручка снизилась на $13,500, себестоимость сэкономила $2,800, ФОТ обошелся на $4,800 дороже, а маркетинг — на $6,000 дороже. В сумме это −$21,500, что соответствует разрыву в прибыли.
Три строки проходят правило существенности (более 5% и более $2,000): выручка, ФОТ и маркетинг. Себестоимость изменилась всего на 2.8%, поэтому она не требует пояснений.
Разрыв в выручке требует более детального рассмотрения. Бюджет предполагал 50,000 единиц по $5.00. За квартал было продано 43,000 единиц по $5.50.
- Отклонение по объему. (43,000 − 50,000) × $5.00 = −$35,000.
- Отклонение по цене. ($5.50 − $5.00) × 43,000 = +$21,500.
- Итого. −$35,000 + $21,500 = −$13,500, что соответствует строке выручки.
Таким образом, повышение цены оправдалось, но это стоило объема продаж. Именно это и сообщается руководителям. Расходы на маркетинг выросли на 30% выше плана, однако продажи в штуках упали. Это ставит вопрос о том, какие кампании сработали.
ФОТ требует своей проверки. Разрыв в $4,800 может быть вызван фактором ставки, например повышением зарплаты, или фактором объема, например дополнительными часами. Финансовый руководитель спрашивает операционного менеджера и узнает, что в двух магазинах были добавлены смены в выходные дни. Это делает отклонение объемным, связанным с часами работы, а не с повышением оплаты.
Итоговое резюме для руководителей умещается в одну строку. Прибыль оказалась на $21,500 ниже бюджета, так как рост цен привел к большей потере объема, чем принес выгоды, а расходы на маркетинг и смены в выходные превысили план.
Как писать заметки к отклонениям
Таблица — это только половина анализа отклонений. Руководители читают заметки, поэтому каждая из них должна отвечать на три вопроса: что изменилось, почему это изменилось и что будет дальше.
Хорошая заметка следует простому шаблону:
- Что. «Расходы на маркетинг превысили бюджет на $6,000, что на 30.0% выше плана».
- Почему. «Две платные кампании длились дольше, чем планировалось».
- Что дальше. «Мы приостановили одну кампанию 3 октября и ожидаем, что показатели 4-го квартала будут соответствовать плану».
Каждая заметка должна состоять из двух-трех предложений. Используйте тот же порядок строк, что и в таблице, чтобы читатели могли сопоставить заметки с цифрами.
Отделяйте разовые разрывы от тенденций. Разовый счет за ремонт отличается от повышения зарплаты, которое будет повторяться каждый месяц. Четко помечайте повторяющиеся разрывы, так как они меняют прогноз.
Пишите заметки вместе с владельцем каждой статьи. Финансовая команда видит цифры, но владелец бюджета знает причину. Короткая сверка с каждым владельцем превращает догадку в объяснение.
Наконец, добавьте резюме в одну строку в самом верху. Для примера выше оно звучало бы так: «Операционная прибыль за 3-й квартал оказалась на $21,500 ниже бюджета, в основном из-за снижения объема продаж и более высоких расходов на маркетинг».
Своевременность важна так же, как и формулировки. Большинство команд проводят анализ отклонений каждый месяц после закрытия книг. Ежемесячный ритм позволяет выявлять проблемы, пока еще есть время для действий.
Ежеквартальные обзоры проводятся глубже. В них добавляется разделение на цену и объем, пересмотр прогноза и проверка того, имеет ли смысл сам бюджет. Годовые обзоры рассматривают весь год в сравнении с первоначальным планом.
Используйте один и тот же шаблон каждый раз. Когда столбцы и правила остаются фиксированными, анализ отклонений занимает меньше времени каждый месяц, а тенденции легче отследить в разных периодах.
Как визуализировать отклонения
Диаграмма помогает руководителям увидеть путь от бюджета к факту. Лучше всего подходит каскадная диаграмма (waterfall chart).
Microsoft утверждает, что каскадная диаграмма «показывает нарастающий итог по мере добавления или вычитания значений». Она добавляет, что «каскадные диаграммы также называют мостовыми диаграммами (bridge charts)».
Чтобы построить ее, перечислите шаги от бюджетной прибыли к фактической. Начните с $55,000, затем добавьте влияние каждой строки на прибыль и закончите на $33,500. Выберите данные и выберите Вставка > Вставить каскадную > Каскадная.
Установите первый и последний столбцы как итоги. На странице Microsoft отмечается, что начальный и конечный столбцы «часто начинаются на горизонтальной оси», в то время как промежуточные столбцы «парят». Увеличения и уменьшения кодируются цветом, поэтому самые крупные факторы выделяются.
Для ежемесячного отслеживания также подходит простая гистограмма отклонений в %. Наше руководство о том, как построить отчет «план-факт», охватывает полный ежемесячный отчет на основе этой таблицы.
Проведение анализа отклонений с помощью ИИ
Формулы занимают минуты, когда таблица уже существует. Самая медленная часть — это часто сопоставление счетов, поиск факторов и написание заметок. Инструмент ИИ может подготовить черновики этих шагов на основе ваших выгрузок.
Вот метод в три шага с использованием Powerdrill Bloom.
Шаг 1: Загрузите бюджет и фактические данные
Экспортируйте бюджет и фактические данные из вашего инструмента бухгалтерского учета или планирования. AI Budget Analysis Tool принимает «экспорты в Excel, CSV или PDF из бухгалтерии и FP&A».
Включите данные о единицах и ценах, если вы хотите разделить отклонение на цену и объем. Сохраняйте названия счетов одинаковыми в обоих файлах.
Шаг 2: Запросите отклонения и факторы на естественном языке
Опишите нужный вам результат. Например: «Сравни фактические показатели за 3-й квартал с бюджетом построчно. Отметь строки с отклонением более 5% и $2,000 и раздели разрыв в выручке на цену и объем».
На странице инструмента «Отклонение по статьям с применением порогов существенности» указано как один из видов представления. Также там указана рабочая книга пересмотренного прогноза с «первоначальным бюджетом, фактическими данными с начала года и пересмотренным прогнозом на конец года».
Шаг 3: Проверьте цифры и экспортируйте отчет
Сравните итоги со своей таблицей, прежде чем делиться чем-либо. Затем экспортируйте результат. На странице анализа бюджета указана «Панель отклонений с выделенными существенными строками и прикрепленными факторами влияния».
Для постоянного мониторинга AI Finance Dashboard Builder охватывает P&L, денежные средства, оборотный капитал и прогноз. Попробуйте Powerdrill Bloom с бюджетом и фактом за прошлый квартал.
Распространенные ошибки, которых следует избегать
Несколько ошибок встречаются почти в каждом первом анализе отклонений.
- Путаница со знаком. Решите один раз, является ли отклонение «факт минус бюджет», и используйте это везде.
- Называние любого положительного разрыва хорошим. Более высокие затраты неблагоприятны, даже если число положительное.
- Сложение процентов. Пересчитывайте общие проценты на основе общих денежных сумм.
- Сравнение разных периодов. Факт с начала года в сравнении с месячным бюджетом дает ложный разрыв.
- Игнорирование сроков. Запоздалый счет может создать два противоположных разрыва в двух месяцах.
- Объяснение всего подряд. Заметки о небольших разрывах скрывают те, которые действительно важны.
- Ввод цифр вручную. Вставляйте или связывайте фактические данные из экспорта, чтобы опечатка не создала ложный разрыв.
- Пропуск проверки итогов. Отклонения по строкам должны в сумме давать общее отклонение. Если это не так, строка пропущена или учтена дважды.
Часто задаваемые вопросы
Какова формула отклонения в Excel?
Для отклонения от бюджета используйте «факт минус бюджет», например =C2-B2. Для процента используйте =IFERROR((C2-B2)/ABS(B2),"") и отформатируйте ячейку как процент.
Как узнать, является ли отклонение благоприятным?
Это зависит от типа строки. Для выручки факт выше бюджета — это благоприятно. Для затрат факт ниже бюджета — это благоприятно. Столбец Тип и формула IF могут пометить каждую строку.
Что такое существенное отклонение?
Это разрыв, достаточно большой, чтобы его объяснять. Команды устанавливают свои правила, например более 5% и более $2,000. Строки, прошедшие оба теста, получают письменную заметку.
Какая диаграмма лучше всего подходит для анализа отклонений?
Каскадная диаграмма, также называемая мостовой диаграммой, показывает, как каждая строка изменяет итог от бюджета к факту. В Excel она находится в меню Вставка > Вставить каскадную.
Может ли Copilot в Excel проводить анализ отклонений?
Microsoft указывает навык анализа отклонений для Copilot в Excel. Он сравнивает фактические показатели с бюджетами, прогнозами или предыдущими периодами и готовит черновики объяснений для проверки. Для этого требуется лицензия Copilot.
Источники: Поддержка Microsoft, Использование встроенных навыков с Copilot в Excel · Поддержка Microsoft, Создание каскадной диаграммы · Поддержка Microsoft, Использование условного форматирования для выделения информации в Excel.