text
Журнал

Как упростить план-факт анализ с помощью Excel

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

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

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

Как разработать шаблон для заполнения плановых данных

Каждому сотруднику компании, ответственному за подготовку бюджета определенного центра затрат, стоит предоставить для заполнения унифицированную форму для ввода плановых данных – файл Excel с таблицей на одном листе (см. рисунок 1. Лист «DATA»).

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

В предлагаемом примере предусмотрен ввод данных по трем центрам затрат: «Маркетинг», «Центр затрат 1» и «Центр затрат 2». Запрос содержит следующие поля:

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

Какие справочники нужно создать для план-факт анализа исполнения бюджета с использованием Excel

Справочники нужны, чтобы упростить ввод данных в бюджетную модель в Excel и исключить внесение несопоставимых сведений.

В примере предусмотрено два классификатора – центры затрат и группы статей затрат (см. рисунки  2 и 3 соответственно).

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

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

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

Как создать таблицу для план-факт анализа исполнения бюджета

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

Для этого нужно сделать следующее.

1. Выделить полный диапазон таблицы с исходными данными (в примере – A5:R60), как показано на рисунке 4.

2. Выбрать на ленте меню Excel раздел «Вставка», в нем пункт «Сводная таблица», как показано на рисунке 5.

3. В открывшемся диалоговом окне проверить правильность исходного диапазона (A5:R60) и выбрать «На новый лист», указав таким образом место, куда следует поместить отчет сводной таблицы (см. рисунок 6. Создание сводной таблицы). В результате автоматически будет создан новый лист. Для удобства его можно сразу переименовать (в примере он назван «PIVOT»).

4. Установить фильтры и указать поля для строк и столбцов таблицы в зависимости от того, в каком виде пользователям нужно представить имеющуюся информацию (например, как показано на рисунке 7).

В предлагаемой модели выбраны фильтры «План/Факт», «Год», «Месяц», «Валюта План», «Валюта Факт». Если компания в планировании и учете использует одну валюту, то фильтр по полю «Валюта» не потребуется.

Столбцами таблицы могут быть, например, наименования центров затрат. Строками – наименования групп и статей затрат. Значения будут определяться по полю «План».

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

В итоге сформируется сводная таблица, представленная на рисунке 8.

Для удобства можно переименовать заголовки в шапке таблицы. Например, вместо «Сумма по полю План» указать «Плановые показатели», «Названия строк» заменить на «Статьи расходов», «Названия столбцов» – на «Центры затрат».

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

Как добавить фактические показатели в таблицу для план-факт анализа

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

Данные, выгруженные из учетной системы в Excel, необходимо построчно добавить на лист «DATA». Задачу можно упростить, например: «Код центра затрат», «Код группы затрат» и «Код статьи затрат» нужно внести в соответствующие столбцы, а названия «подтянутся» автоматически благодаря формуле (ее можно скопировать и вставить из строк выше). Сумму фактических затрат нужно внести в поле «Факт» (столбец N). Важно указывать валюту (в примере это рубли, RUB). В поле «План/Факт» необходимо указывать «Факт», как показано на рисунке 10.

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

Добавив на лист «DATA» фактические данные, нужно обновить таблицу с отчетом (лист «PIVOT»), так как ее исходный диапазон расширился (A5:R115). Сделать это можно так.

Выбрать на ленте меню Excel раздел «Параметры», в нем пункт «Изменить источник данных» (см. рисунок 11. Изменение источника данных сводной таблицы).

Откроется диалоговое окно, в котором необходимо указать новый диапазон (например, A5:R115) и нажать «ОК».

В фильтре сводной таблицы появится, кроме значения «План», значение «Факт» (как показано на рисунке 12).

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

В раздел «Значения» нужно добавить поле «Факт» (и сразу же установить значение «Сумма» для сведения данных в этом поле). Кроме того, потребуется перенести «лишнюю» детализацию в столбцах по полю «Наименование центра затрат» в фильтр, как показано на рисунке 13.

В результате будет сформирована таблица, представленная на рисунке 14.

В нее нужно добавить два вычисляемых поля «Отклонение в рублях» и «Отклонение в %», чтобы разница «План/Факт» считалась автоматически. Для этого на ленте меню Excel нужно выбрать раздел «Параметры», в нем пункт «Формулы», опцию «Вычисляемое поле», как показано на рисунке 15.

Откроется следующее диалоговое окно (см. рисунок 16. Настройка параметров вычисляемого поля).

Новое поле можно назвать «Откл.». Значения в нем будут рассчитываться как разница между фактическими и плановыми показателями. Чтобы обеспечить это, нужно выделить поле «Формула», двойным щелчком нажать на поле «Факт», поставить знак минус и двойным щелчком выбрать поле «План». Получится следующая формула, показанная на рисунке 17.

Так же можно добавить и вычисляемое поле «Отклонение в %». Для этого в поле «Формула» нужно указать требуемую формулу. Чтобы исключить ошибку деления на ноль, стоит использовать функцию ЕСЛИ, как показано на рисунке 18.

В результате будет сформирована сводная таблица, представленная на рисунке 19.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

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