Как сделать накопительную ведомость в excel
Обновлено: 04.07.2024
В этой статье я расскажу как в одной таблице сделать анализ выполнения бюджетных показателей за месяц и накопительным итогом.
Вот эта таблица
В этой таблице отражены все бюджетные расходы предприятия в разрезе статей и ЦФО.
Данные по исполнению бюджетов по месяцам находятся в этом блоке
Плановые значения за месяц заносятся, либо как значения из утвержденных планов сначала в данную общую таблицу, а потом уже распределяются по ЦФО. Из сначала разносятся по бюджетам ЦФО, потом собираются в общей таблице.
Фактические значения также, либо подтягиваются из отчетов бухгалтерского учета (например, из ОСВ) и потом распределяются по бюджетам ЦФО, либо, наоборот, данные сначала разносятся по бюджетам ЦФП, а затем аккумулируются в сводной таблице. (Как разнести данные из ОСВ читайте здесь , как переносить данные из ЦФО в сводную таблицу написано тут )
В следующем блоке исполнение бюджета анализируется накопительным итогом за прошедшие месяцы, в нашем случае за январь-февраль.
Чтобы автоматизировать расчет сумм накопительным, а не изменять формулу суммирования ежемесячно, можно использовать формулу СУММЕСЛИ и добавить вспомогательную строку
Во вспомогательной строке плановым показателям, которые должны включаться в расчет суммы, присвоим значение "нп", фактическим - "нф". Когда придет время включать в расчет март, в соответствующих столбцах по марту нужно будет проставить значения "нп" и "нф", формулу менять не нужно. При этом необходимо, чтобы формула СУММЕСЛИ ссылалась на столбцы вплоть до факта декабря.
В третьем блоке анализируется исполнение общего плана за год, в нашем примере за квартал.
Для суммирования также используется формула СУММЕСЛИ
Формула СУММЕСЛИ по плану ссылается на строку 2, по большому счету, для плана можно использовать простое суммирование, так как эта величина не будет меняться.
В столбце "факт-прогноз" плановые значения постепенно будут заменяються фактическими, то есть в феврале суммы за январь и февраль берем по факту, а март по плану. Чтобы автоматизировать замену плановых значений на фактические во вспомогательной строке 3, на которую ссылается формула СУММЕСЛИ, в столбцах, которые должны включаться сумму ставим "1", то есть в марте нужно будет удалить "1" из столбца с планом и поставить ее в столбец с фактом.
Эта таблица удобна и в случае корректировки планов, для этого нужно будет добавить столбец со скорректированными значениями и столбец с отклонениями "старого" плана от корректировок. В этом случае будет видно отклонение и при корректировке планов, сразу будет видно правильно или нет внесены данные. (после корректировке добавленные столбцы можно будет удалить).
Читайте также: