Консолидация данных эксель

Консолидированный отчет о движении денежных средств в МСФО

Марианна Мамцова

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

1. Цель, способы составления и структура ОДДС

2. Учет изменений в оборотном капитале

3. Курсовые разницы

4. Проценты и дивиденды

5. Классификация компонентов операций

6. Приобретение дочерних компаний

7. Выбытие дочерних компаний

8. Увеличение и уменьшение доли владения в дочерней компании без потери контроля

Цель, способы составления и структура ОДДС

IFRS 7 «Отчет о движении денежных средств» устанавливает правила и принципы составления ОДДС. ОДДС содержит информацию о денежных потоках в разрезе операционной, инвестиционной и финансовой деятельности. Ниже в таблице приведены примеры:

Притоки

Оттоки

Операционная деятельность

денежные поступления от продажи товаров и предоставления услуг;

денежные выплаты поставщикам за товары и услуги;

денежные поступления в виде роялти, гонораров, комиссионных и прочая выручка;

денежные выплаты работникам и от имени работников;

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

денежные выплаты или возвраты налога на прибыль, если они не могут быть непосредственно соотнесены с финансовой или инвестиционной деятельностью;

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

Инвестиционная деятельность

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

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

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

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

денежные поступления от возврата авансов и займов, предоставленных другим лицам (кроме авансовых платежей и займов финансовых институтов);

денежные авансы и займы, предоставленные другим лицам (кроме авансов и займов, предоставляемых финансовыми институтами);

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

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

Финансовая деятельность

денежные поступления от эмиссии акций или других долевых инструментов;

денежные выплаты собственникам для приобретения или погашения акций предприятия;

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

денежные выплаты по заемным средствам;

денежные выплаты арендатора для уменьшения непогашенной задолженности по финансовой аренде.

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

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

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

В таблице ниже можно увидеть сравнение двух методов представления изменения денежных средств от операционной деятельности.

Свернутый вид (косвенный метод)

Развернутый вид (прямой метод)

тыс.руб

тыс.руб

ОПЕРАЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

ОПЕРАЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Прибыль/(Убыток) за год

Денежные поступления от покупателей

1 000

Корректировки по статья:

Денежные средства уплаченные поставщикам и персоналу

(600)

Амортизация

Проценты уплаченные

(50)

Проценты начисленные

Налог на прибыль уплаченный

(30)

Налог на прибыль начисленный к уплате

Денежные средства, использованные в операционной деятельности

Прибыль/Убыток от операционной деятельности до учета изменений в оборотном капитале и резервах

Уменьшение/(увеличение) дебиторской задолженности

(26)

Уменьшение/(увеличение) запасов

(Уменьшение)/увеличение кредиторской задолженности

(13)

Денежные средства, использованные в операционной деятельности до уплаты процентов и налога на прибыль

Проценты уплаченные

(50)

Налог на прибыль уплаченный

(30)

Денежные средства, использованные в операционной деятельности

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

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

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

Наверх

Учет изменений в оборотном капитале

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

Задача №1.

Балансы на начало и конец 2010 года представлены в таблице. Задача — составить ОДДС.

Выручка

Себестоимость

(100)

Прибыль за отчетный год

Решение

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

Теперь, сделаем анализы для дебиторской, кредиторской задолженностей и по запасам.

Таким образом, отток денежных средств рассчитан как 140-180= 40. Т.е. изменение дебиторской задолженности, запасов и кредиторской задолженности дает изменение денежных средств от операционной деятельности.

Баланс

ДЗ

Запасы

ДС

УК

НП

КЗ

(10)

(110)

(100)

(10)

(130)

(10)

Изменение

(90)

ОПЕРАЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Прибыль/(Убыток) за год

Уменьшение/(увеличение) дебиторской задолженности

Уменьшение/(увеличение) запасов

(Уменьшение)/увеличение кредиторской задолженности

(90)

(90)

Денежные средства, использованные в операционной деятельности

(40)

ИНВЕСТИЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

ФИНАНСОВАЯ ДЕЯТЕЛЬНОСТЬ

Чистое (уменьшение)/увеличение денежных средств и их эквивалентов

(40)

Денежные средства и их эквиваленты на начало года

Денежные средства и их эквиваленты на конец года

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

Наверх

Курсовые разницы

Для ОДДС курсовые разницы нужно разделить на две части – связанные с операционной деятельностью, и прочие (кредиты, инвестиции и т.д.).

Рассмотрим простой пример, чтобы понять, в чем заключается разница.

Кредит

Кред. Зад-ть

тыс.руб

тыс.руб

На начало года

1 200

Начислены курсовые разницы

Оплачено

(170)

(155)

На конец года

1 070

1 вариант

2 вариант

тыс.руб

тыс.руб

ОПЕРАЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Прибыль/(Убыток) за год

(65)

(65)

(Уменьшение)/увеличение кредиторской задолженности

(130)

(155)

Корректировки по статьям:

Курсовые разницы

Денежные средства, использованные в операционной деятельности

(155)

(155)

ФИНАНСОВАЯ ДЕЯТЕЛЬНОСТЬ

Погашение кредитов и займов

(170)

(170)

Денежные средства от финансовой деятельности

(170)

(170)

Чистое (уменьшение)/увеличение денежных средств и их эквивалентов

(325)

(325)

Денежные средства и их эквиваленты на начало года

Денежные средства и их эквиваленты на конец года

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

Первый – не выделять в изменении рабочего капитала курсовые разницы. В данном случае операционная деятельность корректируется на курсовые разницы, относящиеся ко всем остальным статьям, кроме рабочего капитала – кредиты, инвестиции и т.д. В нашем случае, курсовые разницы по кредитам составили 40 тыс. руб., а изменение кредиторской задолженности составило 130 тыс. руб. Таким образом, курсовые разницы отраженные в ОПУ не будут по сумме совпадать с корректировкой по курсовым разницам.

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

Наверх

Проценты и дивиденды

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

Наверх

Классификация компонентов операций

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

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

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

Задача № 2. Баланс и ОПУ представлен ниже.

ОПУ

тыс. руб.

Выручка

1,200

Себестоимость товаров

(550)

ФОТ

(150)

Амортизация НМА

(30)

Амортизация ОС

(15)

Себестоимость

(745)

Коммерческие расходы

(68)

Обесценение НМА

(140)

Списаны по непригодности ОС

(8)

Убыток от продажи ОС

(10)

Прибыль от продажи НМА

Прочие расходы и доходы

(98)

Курсовые разницы

Доход от инвестиций

Проценты к уплате

(185)

Списано ДЗ в резерв

(10)

Финансовые доходы и расходы

(168)

Текущий налог на прибыль

(40)

Отложенный налог на прибыль

Прибыль за год

Дополнительные данные к задаче :

НМА : приобретено НМА на сумму 250 тыс. руб., оплачено поставщику НМА 210 тыс. руб., капитализировано процентов 15 тыс. руб.

ОС: приобретено ОС на сумму 100 руб.

Кредит: 400 руб. получено, 150 руб. погашено.

Курсовые разницы начислены: по кредитам – 18 тыс. руб. отрицательная курсовая разница, задолженности по процентам – 3 тыс. руб. отрицательная курсовая разница, и по рабочему капиталу – 30 тыс. руб. положительная курсовая разница.

Инвестиции: приобретено на сумму 50 руб. , получены дивиденды 6 руб.

Решение

Для начала необходимо сделать анализы к основным балансовым статьям. Если составляется полный комплект отчетности, то это не составляет особого труда, так как в раскрытиях, например по ОС и НМА, можно увидеть полное движение.

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

Также, в «шахматке» можно увидеть рабочую строку «реклассификация». Данная строка корректирует задолженность по приобретенным НМА и капитализированные проценты.

НМА

ОС

На начало года

На начало года

Приобретено

Приобретено

Капитализированы %

Амортизация

(15)

Амортизация

(30)

Списаны по непригодности

(8)

Обесценение

(140)

Проданы

(30)

Проданы

(70)

На конец года

На конец года

Поступления от выбытия ОС

Поступления от выбытия НМА

Убыток от продажи ОС

(10)

Прибыль от продажи НМА

Проданы ОС

(30)

Проданы НМА

(70)

Кредит

Зад-ть по процентам

На начало года

(870)

На начало года

(30)

Получен кредит

(400)

Начислены проценты

(200)

Начислены курсовые разницы

(18)

Начислены курсовые разницы

(3)

Погашен кредит

Уплачены проценты

На конец года

(1,138)

На конец года

(73)

Инвестиции

На начало года

Приобретены инвестиции

Признан доход от инвестиций

Получены дивиденды

(6)

На конец года

Задолженность по налогу на прибыль

Отложенные налоги

На начало года

(30)

На начало года

(70)

Начислен текущий налог

(40)

Начислен отложенный налог

Уплачен налог

На конец года

(5)

На конец года

(44)

Баланс

НМА

Инв

Зап-сы

ДЗ

Резерв по ДЗ

Ден. Средства

УК

НП

Кредит

О ННП

Зад-ть по ННП

КЗ

Зад-ть по %

(30)

(10)

(430)

(870)

(70)

(30)

(300)

(30)

(40)

(10)

(537)

(1,138)

(44)

(5)

(373)

(73)

Изменение

(25)

(47)

(62)

(115)

(280)

(26)

(25)

ОПЕРАЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Прибыль/(Убыток) за год

Корректировки по статьям:

Амортизация

Обесценение НМА

Убыток от выбытия нематериальных активов

(60)

(60)

Убыток от продажи ОС

Списаны ОС по непригодности

Убыток от продажи ОС

Доход от инвестиций

(18)

(18)

Курсовые разницы

Налог на прибыль

(26)

Процентные расходы

Прибыль/(Убыток) от операционной деятельности до учета изменений в оборотном капитале и резервах

Уменьшение/(увеличение) дебиторской задолженности

(270)

(280)

Уменьшение/(увеличение) запасов

(115)

(115)

(Уменьшение)/увеличение кредиторской задолженности

Денежные средства, использованные в операционной деятельности, до уплаты налога на прибыль и процентов

Налог на прибыль уплаченный

(65)

(65)

Проценты уплаченные

(160)

(160)

Денежные средства, использованные в операционной деятельности

(125)

ИНВЕСТИЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Приобретение нематериальных активов

(210)

(210)

Приобретение основных средств

(100)

(100)

Поступления от выбытия нематериальных активов

Поступления от выбытия основных средств

Приобретение инвестиций

(50)

(50)

Дивиденды от инвестиций

Денежные средства, использованные в инвестиционной деятельности

(204)

ФИНАНСОВАЯ ДЕЯТЕЛЬНОСТЬ

Получение кредитов и займов

Погашение кредитов и займов

(150)

(150)

Денежные средства от финансовой деятельности

Чистые (уменьшение)/увеличение денежных средств и их эквивалентов

(79)

Денежные средства и их эквиваленты на начало года

Денежные средства и их эквиваленты на конец года

Реклассификация (рабочая строка)

(15)

Реклассификация (рабочая строка)

(40)

(25)

(47)

(62)

(115)

(280)

(26)

(25)

(25)

(47)

(62)

(115)

(280)

(26)

(25)

Наверх

Приобретение дочерних компаний.

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

По строке ОДДС «приобретение дочерней компании» нужно расположить – активы, кроме денежных средств, и пассивы приобретенной компании, гудвил и долю неконтролирующих акционеров (ДНА). Их сумма будет равна денежным средствам, потраченным на приобретение компании. Ниже приведен наглядный пример.

Задача №3.

31.12.2010 года компания А приобрела 80% компании Б за 300 тыс.руб. Никаких более операций компания А в 2010 году не совершала. Балансы группы А на начало и конец 2010 следующие:

Решение:

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

тыс. руб.

УК

НП

Чистые активы

ДНА 20%

ДНА 20%

Вознаграждение

Чистые активы

(282)

Гудвил

Ден. Средства прибретены

Ден. Средства уплачены

(300)

Отток ден.средств

(253)

Теперь методом «шахматки» составим ОДДС.

НМА

ОС

Запасы

Дебиторкая Зад-ть

Ден. Средства

УК

НП

ДНА

Кредит

Кредиторская Зад-ть

(10)

(500)

(950)

(450)

1,124

(10)

(500)

(56)

(1,364)

(518)

(253)

(56)

(414)

(68)

ИНВЕСТИЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Приобретена дочерняя компания

(56)

(414)

(68)

Денежные средства от инвестиционной деятельности

Чистое (уменьшение)/увеличение денежных средств и их эквивалентов

Денежные средства и их эквиваленты на начало года

Денежные средства и их эквиваленты на конец года

Наверх

Выбытие дочерних компаний.

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

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

Задача №4.

В продолжение предыдущей задачи – компания на следующий год продала инвестицию в компанию Б за 450 тыс. руб. Никаких операций больше у группы не было. Балансы группы А на начало и конец 2011 следующие:

Ден. Средства выбывшей компании

(47)

Ден. Средства получены

Отток ден.средств

НМА

ОС

Запасы

Дебиторкая Зад-ть

Ден. Средства

УК

НП

ДНА

Кредит

Кредиторская Зад-ть

1 124

(10)

(500)

(56)

(1 364)

(518)

(10)

(650)

(950)

(450)

(274)

(450)

(32)

(35)

(150)

ОПЕРАЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Прибыль/(Убыток) за год

Корректировки по статья:

Прибыль/(Убыток от выбытия объектов инвестиций

(150)

(150)

Денежные средства, использованные в операционной деятельности

ИНВЕСТИЦИОННАЯ ДЕЯТЕЛЬНОСТЬ

Выбытие объектов инвестиций

(56)

(414)

(68)

Денежные средства от инвестиционной деятельности

Чистое (уменьшение)/увеличение денежных средств и их эквивалентов

Денежные средства и их эквиваленты на начало года

Денежные средства и их эквиваленты на конец года

Наверх

Увеличение и уменьшение доли владения в дочерней компании без потери контроля.

Следует обратить внимание, что денежные средства, уплаченные или полученные в результате данных сделок, следует отражать «Финансовая деятельность».

Рассмотрим простой пример.

Задача №5.

В продолжение задачи №3 – компания А на следующий год увеличила долю владения в компании Б с 80% до 90%. Вознаграждение составило 50 тыс. руб. Никаких операций больше у группы не было. Балансы группы А на начало и конец 2011 следующие:

НМА

ОС

Запасы

Дебиторкая Зад-ть

Ден. Средства

УК

НП

ДНА

Кредит

Кредиторская Зад-ть

1 124

(10)

(500)

(56)

(1 364)

(518)

1 124

(10)

(422)

(85)

(1 364)

(518)

(50)

(28)

ФИНАНСОВАЯ ДЕЯТЕЛЬНОСТЬ

Увеличение доли владения в дочерней компании

(28)

Денежные средства от инвестиционной деятельности

Чистые (уменьшение)/увеличение денежных средств и их эквивалентов

Денежные средства и их эквиваленты на начало года

Денежные средства и их эквиваленты на конец года

Наверх

Консолидация (объединение) данных из нескольких таблиц в одну

7554 27.11.2012

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

  1. Заранее откройте исходные файлы
  2. Создайте новую пустую книгу (Ctrl + N)
  3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация (Data — Consolidate). Откроется соответствующее окно:
  4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
  5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

Ссылки по теме

  • Макрос для автоматической сборки данных с разных листов в одну таблицу
  • Макрос для сборки листов из нескольких файлов

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

Условия для выполнения процедуры консолидации

Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:

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

Создание консолидированной таблицы

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

  1. Открываем отдельный лист для консолидированной таблицы.
  2. На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
  3. Находясь во вкладке «Данные» кликаем по кнопке «Консолидация», которая расположена на ленте в блоке инструментов «Работа с данными».
  4. Открывается окно настройки консолидации данных.

    В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:

    • сумма;
    • количество;
    • среднее;
    • максимум;
    • минимум;
    • произведение;
    • количество чисел;
    • смещенное отклонение;
    • несмещенное отклонение;
    • смещенная дисперсия;
    • несмещенная дисперсия.

    В большинстве случаев используется функция «Сумма».

  5. В поле «Ссылка» указываем диапазон ячеек одной из первичных таблиц, которые подлежат консолидации. Если этот диапазон находится в этом же файле, но на другом листе, то жмем кнопку, которая расположена справа от поля ввода данных.
  6. Переходим на тот лист, где расположена таблица, выделяем нужный диапазон. После ввода данных жмем опять на кнопку расположенную справа от поля, куда был внесен адрес ячеек.
  7. Вернувшись в окно настроек консолидации, чтобы добавить уже выбранные нами ячейки в список диапазонов, жмем на кнопку «Добавить».

    Как видим, после этого диапазон добавляется в список.

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

    Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.

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

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

    Когда все настройки выполнены, жмем на кнопку «OK».

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

    Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.

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

Мы рады, что смогли помочь Вам в решении проблемы.
Добавьте сайт Lumpics.ru в закладки и мы еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

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

Контроль бюджета

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

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

План движения денежных средств на месяц

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

В общем виде БДДС состоит из следующих блоков:

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

В результате мы получаем плановый чистый поток за рассматриваемый период и выводим прогноз остатка денежных средств на конец периода. 📌 Реклама Отключить

Таблица 1. Бюджет движения денежных средств, руб.

Код

Статья

Контрагент

Задолженность на 1-е число

Начисления/
Продажи/
Поставки

Бюджет

Задолженность на 31-е число

001

Остаток на начало периода

65 000

На расчетном счете

50 000

В кассе

15 000

100

Поступления от продаж

185 000

1 210 000

1 105 000

290 000

101

Розничные продажи

25 000

1 000 000

925 000

100 000

102

Оптовые продажи

150 000

200 000

170 000

180 000

103

Прочие поступления

10 000

10 000

10 000

10 000

200

Расходы от операционной деятельности

505 200

965 500

1 117 700

353 000

200.1

Оплата поставщикам

450 000

650 000

800 000

300 000

Оплата поставщикам

ООО «Альфа»

300 000

450 000

550 000

200 000

Оплата поставщикам

ООО «Омега»

150 000

200 000

250 000

100 000

200.2

Операционные затраты

55 200

315 500

317 700

53 000

201

Выплата зарплаты

50 000

125 000

125 000

50 000

201.1

Окладная часть

Сотрудники

50 000

100 000

100 000

50 000

201.2

Премии

Сотрудники

25 000

25 000

202

Затраты на персонал

2200

5000

7200

202.1

Такси

ООО «Такси»

1500

3000

4500

202.1

Такси

ООО «Экспресс»

2000

2000

202.2

Медицинский осмотр

Поликлиника № 1

700

700

203

Затраты на содержание здания

3000

73 000

73 000

3000

203.1

Аренда помещений

ООО «Терем»

25 000

25 000

203.1

Аренда помещений

ООО «Теремок»

30 000

30 000

203.2

Коммунальные расходы

Горводоканал

15 000

15 000

203.2

Коммунальные расходы

ТеплоЭлектроСтанция

203.3

Охрана

ЧОП «Добрыня»

3000

3000

3000

3000

218

Налоги в бюджет

112 500

112 500

218.1

НДС к оплате

45 000

45 000

218.2

Налог на прибыль

15 000

15 000

218.3

Налог на имущество

218.4

НДФЛ

15 000

15 000

218.5

Налоги с зарплаты

37 500

37 500

300

Итого поток от операционной деятельности

244 500

–12 700

400

Поток от финансовой деятельности

1 500 000

–25 000

1 500 000

Привлечение кредитов

Банк № 2

500 000

Гашение кредитов

Банк № 1

500 000

Оплата процентов по кредитам

Банк № 1

25 000

500

Поток от инвестиционной деятельности

–15 000

–5000

–10 000

–10 000

Доход от продажи ОС

Покупка ОС

Ремонт ОС

ИП Иванов П. А.

15 000

5000

10 000

10 000

600

Чистый поток от деятельности

–47 700

002

Остаток на конец периода

17 300

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

В нашем примере чистый поток по месяцу прогнозируется отрицательным (–47,7 тыс. руб.), но за счет начальных остатков в 65 тыс. руб. мы в состоянии выполнить заявленный бюджет на данный месяц. При этом мы наращиваем дебиторскую задолженность от наших покупателей со 185 тыс. руб. до 290 тыс. руб. и уменьшаем кредиторскую задолженность перед поставщиками товара с 450 тыс. руб. до 300 тыс. руб. В целом по месяцу картина оптимистичная.

Однако стоит обратить внимание, что в этом месяце планируется перекредитовка в 500 тыс. руб.: у нас заканчивается срок кредита в Банке № 1, мы рассчитываем получить кредит на такую же сумму в Банке № 2. И если получить кредит в Банке № 2 мы сможем чуть позже, чем истекает срок гашения в Банке № 1, то в течение месяца нам нужно накопить на счетах 500 тыс. руб. (примерно половина нашей месячной выручки). То есть почти полмесяца мы не сможем тратить большие суммы на операционные расходы: все платежи по ним начнутся только после получения кредита в Банке № 2.

📌 Реклама Отключить

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

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

План поступления выручки по неделям

План поступления выручки от розничного и оптового направления формируется по разным принципам. Поступления от оптовых клиентов легко спрогнозировать через отсрочку платежа. Для этого воспользуемся штатным отчетом «Валовая прибыль», который находится в блоке «Продажи» закладки «Отчеты» на панели инструментов Excel (рис. 1). 📌 Реклама Отключить

Настроим отчет «Валовая прибыль» под наши требования:

  1. Заходим в настройки отчета, нажимаем галочку «Расширенная настройка».
  2. На закладке «Общие»:
  • задаем период продаж, за который ожидаем поступления денежных средств от клиентов (обычно он равен максимальной отсрочке, предоставляемой нашим покупателям);
  • в блоке «Параметры» нажимаем галочки «Выводить общие итоги» и «Выводить детальные записи»;
  • в блоке «Показатели» оставляем только «Стоимость продажи, руб.» и «с НДС», на остальных показателях убираем галочки (рис. 2).
  1. На закладке «Группировки» удаляем все группировки, которые предусмотрены отчетом по умолчанию (рис. 3).

📌 Реклама Отключить

  1. На закладке «Отборы» устанавливаем отбор: нас интересуют продажи только оптового подразделения (рис. 4).
  1. На закладке «Дополнительные поля» выводим поля «Покупатель» и «По датам», для всех полей в столбце «Размещение» задаем тип «В отдельных колонках», в столбце «Положение» — «Вместо группировки» (рис. 5).
  1. Нажимаем на кнопку «Сформировать» и получаем отчет, который представлен в табл. 2.

Скопируем полученный отчет в Excel и дополним необходимыми нам данными: добавим отсрочку оплат и рассчитаем срок оплаты как сумму двух столбцов: Дата продажи + Отсрочка платежа (табл. 3). 📌 Реклама Отключить Теперь сгруппируем даты оплат по неделям с помощью сводной таблицы: 📌 Реклама Отключить

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

В появившемся окне «Список полей сводной таблицы» зададим ее вид (рис. 7):

  • в блок «Названия строк» перетащим мышкой поле «Дата оплаты»;
  • в блок «Значения» перетащим поле «Стоимость продажи, руб.».

📌 Реклама Отключить

  1. Получаем отчет, представленный в табл. 4.
  1. Видно, что в таблицу попали даты оплат за предыдущий месяц. Уберем их с помощью фильтра сводной таблицы. Встаем на любую ячейку с датой и вызываем контекстное меню правой кнопкой, в нем выбираем «Фильтр» > «Фильтр по дате», задаем фильтр «После» > «01.07.2016» (рис. 8).

📌 Реклама Отключить

  1. Теперь таблица содержит только продажи со сроком оплаты в июле. Снова вызываем контекстное меню и выбираем «Группировать». В появившемся диалоговом окне задаем диапазон: с 04.07.2016 по 31.07.2016 с шагом «Дни», количество дней — 7 (рис. 9).
  1. Мы получили прогноз поступления денежных средств от оптовых продаж по неделям (табл. 5).

Теперь сделаем прогноз поступления денежных средств в розничном направлении. Здесь есть два важных момента, которые нужно учесть при планировании денежного потока: 📌 Реклама Отключить

  1. розничные продажи имеют ярко выраженную сезонность по дням недели: покупатели чаще посещают магазины по выходным дням (на них и приходится пик продаж);
  2. воспользоваться выручкой от розничных продаж для оплат по расчетному счету мы можем только после ее инкассации в банк, которая осуществляется в рабочие дни с задержкой в один-два дня. То есть выручка от продаж в понедельник поступает на расчетный счет во вторник-среду (в зависимости от условий инкассации), выручка за пятницу-воскресенье будет зачислена на расчетный счет в понедельник или вторник. Таким образом, выручкой за 29–31 июля мы сможем воспользоваться только в августе. Зато 1 июля к нам поступит инкассация выручки за 30 июня.

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

При использовании данных за прошлый год нужно делать сопоставление не по датам, а по дням недели. Так, 01.07.2016 приходится на пятницу, в 2015 г. первая пятница июля была 3 июля. Следовательно, для выведения пропорций сезонности нам нужно взять продажи с 3 июля по 02.08.2015. То есть чтобы получить дату прошлого года, аналогичную дню недели этого года, нужно отнять 364 дня (ровно 52 недели).

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

📌 Реклама Отключить

Таблица 6. План поступления выручки от розничных продаж на расчетный счет, руб.

День недели Дата прошлого года Выручка прошлого года Дата текущего года Выручка текущего года Инкассация Итого за неделю
Четверг

30.06.2016

25 000

25 000

Пятница

03.07.2015

28 446

01.07.2016

29 771

25 000

Суббота

04.07.2015

32 520

02.07.2016

34 035

Воскресенье

05.07.2015

31 247

03.07.2016

32 703

Понедельник

06.07.2015

28 599

04.07.2016

29 932

96 510

224 031

Вторник

07.07.2015

32 266

05.07.2016

33 770

29 932

Среда

08.07.2015

29 375

06.07.2016

30 744

33 770

Четверг

09.07.1205

31 603

07.07.2016

33 076

30 744

Пятница

10.07.2015

32 315

08.07.2016

33 820

33 076

Суббота

11.07.2015

32 286

09.07.2016

33 791

Воскресенье

12.07.2015

32 107

10.07.2016

33 604

Понедельник

13.07.2015

29 238

11.07.2016

30 600

101 215

231 621

Вторник

14.07.2015

31 986

12.07.2016

33 476

30 600

Среда

15.07.2015

31 800

13.07.2016

33 282

33 476

Четверг

16.07.2015

31 577

14.07.2016

33 048

33 282

Пятница

17.07.2015

31 572

15.07.2016

33 044

33 048

Суббота

18.07.2015

27 867

16.07.2016

29 165

Воскресенье

19.07.2015

26 924

17.07.2016

28 178

Понедельник

20.07.2015

31 220

18.07.2016

32 675

90 387

222 007

Вторник

21.07.2015

33 039

19.07.2016

34 579

32 675

Среда

22.07.1205

32 395

20.07.2016

33 905

34 579

Четверг

23.07.2015

29 105

21.07.2016

30 462

33 905

Пятница

24.07.2015

30 815

22.07.2016

32 251

30 462

Суббота

25.07.2015

30 564

23.07.2016

31 988

Воскресенье

26.07.2015

31 045

24.07.2016

32 491

Понедельник

27.07.2015

28 194

25.07.2016

29 507

96 730

222 340

Вторник

28.07.2015

31 594

26.07.2016

33 066

29 507

Среда

29.07.2015

30 564

27.07.2016

31 988

33 066

Четверг

30.07.2015

29 666

28.07.2016

31 048

31 988

Пятница

31.07.2015

31 041

29.07.2016

32 487

31 048

Суббота

01.08.2015

34 454

30.07.2016

36 060

Воскресенье

02.08.2015

30 052

31.07.2016

31 453

Итого

955 475

1 000 000

925 000

925 000

График платежей

Мы сформировали план поступления денежных средств по неделям. Теперь разнесем в БДДС обязательные платежи (в табл. 7 выделены цветом): 📌 Реклама Отключить

  • выплата заработной платы: остатки зарплаты за прошлый месяц нужно выплатить до 10-го числа, премия платится до 15-го числа, аванс за текущий месяц — до 25-го числа. Ставим 50 % зарплаты к выплате на вторую неделю, 100 % премии — на четвертую и 50 % зарплаты — на последнюю неделю месяца;
  • оплата аренды: согласно договорам крайний срок оплаты аренды за текущий месяц — 10-е число. Ставим к оплате на вторую неделю;
  • коммунальные платежи нужно осуществить до 25-го числа, ставим их к оплате 25-го числа, то есть на последнюю неделю;
  • охрана по заключенному с ЧОП договору оплачивается до 20-го числа, ставим на оплату на четвертую неделю;
  • налоги с заработной платы нужно оплатить до 15-го числа, значит, деньги на них нам потребуются на третьей неделе;
  • налог на доходы физических лиц платится одновременно с выплатой заработной платы, поэтому разносим его по неделям в той пропорции, что и выплату зарплаты, премий;
  • по остальным налогам срок оплаты с 25-го по 31-е число (последняя неделя июля);
  • погашение кредитов и оплата процентов — до 22-го числа (привлечение кредитов — после 25-го числа).

Все прочие платежи в предстоящем месяце сразу относим на последнюю неделю (когда сможем пополнить оборотные активы за счет нового кредита, получение которого намечено на 25 июля). 📌 Реклама Отключить

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

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

Таблица 7. Понедельное планирование оплат, руб.

Статья

Контрагент

Срок оплат

Бюджет на месяц

1–3 июля

4–10 июля

11–17 июля

18–24 июля

25–31 июля

Остаток на начало периода

65 000

65 000

50 000

148 031

92 153

3160

На расчетном счете

50 000

В кассе

15 000

Поступления от продаж

1 105 000

25 000

249 031

271 621

237 007

322 340

Розничные продажи

925 000

25 000

224 031

231 621

222 007

222 340

Оптовые продажи

170 000

15 000

40 000

15 000

100 000

Прочие поступления

До 10-го числа

10 000

10 000

Расходы от операционной деятельности

1 117 700

40 000

151 000

77 500

301 000

548 200

Оплата поставщикам

800 000

40 000

40 000

40 000

270 000

410 000

Оплата поставщикам

ООО «Альфа»

550 000

30 000

30 000

30 000

200 000

260 000

Оплата поставщикам

ООО «Омега»

250 000

10 000

10 000

10 000

70 000

150 000

Операционные затраты

317 700

111 000

37 500

31 000

138 200

Выплата зарплаты

125 000

50 000

25 000

50 000

Окладная часть

Сотрудники

Зарплата — до 10-го числа, аванс — до 25-го числа

100 000

50 000

50 000

Премии

Сотрудники

До 15-го числа

25 000

25 000

Затраты на персонал

7200

7200

Такси

ООО «Такси»

4500

4500

Такси

ООО «Экспресс»

2000

2000

Медицинский осмотр

Поликлиника № 1

700

700

Затраты на содержание здания

73 000

55 000

3000

15 000

Аренда помещений

ООО «Терем»

До 10-го числа

25 000

25 000

Аренда помещений

ООО «Теремок»

До 10-го числа

30 000

30 000

Коммунальные расходы

Горводоканал

До 25-го числа

15 000

15 000

Коммунальные расходы

ТеплоЭлектроСтанция

До 25-го числа

Охрана

ЧОП «Добрыня»

До 20-го числа

3000

3000

Налоги в бюджет

112 500

6000

37 500

3000

66 000

НДС к оплате

До 25-го числа

45 000

45 000

Налог на прибыль

До 28-го числа

15 000

15 000

Налог на имущество

До 30-го числа

НДФЛ

Вместе с зарплатой

15 000

6000

3000

6000

Налоги с зарплаты

До 15-го числа

37 500

37 500

Итого поток от операционной деятельности

–12 700

–15 000

98 031

194 121

–63 993

–225 860

Поток от финансовой деятельности

–25 000

–250 000

–25 000

250 000

Привлечение кредитов

Банк № 2

После 25-го числа

500 000

250 000

250 000

Гашение кредитов

Банк № 1

До 22-го числа

500 000

250 000

250 000

Оплата процентов по кредитам

Банк № 1

До 22-го числа

25 000

25 000

Поток от инвестиционной деятельности

–10 000

–10 000

Доход от продажи ОС
Покупка ОС
Ремонт ОС

ИП Иванов П. А.

10 000

10 000

Чистый поток от деятельности

–47 700

–15 000

98 031

–55 879

–88 993

14 140

Остаток на конец периода

17 300

50 000

148 031

92 153

3160

17 300

Создание форм бюджетов для контролера статей бюджета

Теперь рассмотрим разные способы получения месячного плана БДДС. Если компания небольшая и контрагентов мало, то экономист в состоянии самостоятельно спланировать предстоящие оплаты на месяц. Достаточно собрать текущую задолженность перед поставщиками и подрядчиками на счетах 60, 76 и проанализировать ежемесячные начисления по всем контрагентам. 📌 Реклама Отключить

В нашем примере всего два поставщика товаров и девять подрядчиков и поставщиков услуг (см. табл. 7), бoльшая их часть ежемесячно выставляет одни и те же счета (аренда, охрана, коммунальные платежи и услуги такси). Понятно, что спрогнозировать оплаты по ним достаточно легко. Сложность может возникнуть только в планировании налогов. Значит, нужно обратиться за помощью к главному бухгалтеру, так как он отвечает за своевременные оплаты налогов.

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

📌 Реклама Отключить

В таблице 8 представлена форма бюджета распорядителя блока затрат по содержанию здания, из которой легко перенести данные в общую форму БДДС. Если статей в БДДС много, то лучше ввести код статьи. Тогда с помощью функции СУММЕСЛИМН() вы сможете автоматически переносить данные из бюджетов контролера в общий бюджет.

Таблица 8. Форма бюджета распорядителя статей затрат

Код

Статья/Контрагент

Крайний срок оплаты (если есть)

Задолженность на 1-е число, руб.

Затраты на текущий месяц, руб.

Бюджет для оплаты, руб.

Задолженность на 31-е число, руб.

203.1 Аренда помещений

0

55 000

55 000

0

203.1 ООО «Терем»

До 10-го числа

25 000

25 000

0

203.1 ООО «Теремок»

До 10-го числа

30 000

30 000

0

203.2 Коммунальные расходы

0

15 000

15 000

0

203.2 Горводоканал

До 25-го числа

15 000

15 000

0

203.2 ТеплоЭлектроСтанция

До 25-го числа

0

203.3 Охрана

3000

3000

3000

3000

203.3 ЧОП «Добрыня»

До 20-го числа

3000

3000

3000

3000

Итого

3000

73 000

73 000

3000

Нужно учитывать несколько моментов, касающихся разработки форм: 📌 Реклама Отключить

  • контролер не должен изменять количество и последовательность столбцов (иначе формулы, настроенные на его бюджет, будут работать некорректно). Если он хочет внести дополнительные пояснения по статье, пусть делает это справа от утвержденной формы;
  • контролер может добавлять в отчет строки, если у него увеличилось количество контрагентов по какой-либо статье затрат. При этом добавление новых строк не должно приводить к необходимости изменять результирующие строки;
  • все ячейки с расчетными формулами должны быть защищены от редактирования (во избежание случайного затирания или изменения формулы на неверную);
  • итоговые значения по бюджету распорядителя необходимо сверять с данными, которые попали в сводный БДДС, чтобы исключить возможность искажения информации.

Рассмотрим, как реализовать эти требования, используя возможности Excel. 📌 Реклама Отключить

  1. Защита ячеек.

Для защиты ячеек от редактирования нажмите кнопку «Защитить лист» на вкладке «Рецензирование». Обратите внимание, что по умолчанию Excel защитит все ячейки листа от изменений, а нам нужно, чтобы распорядители не испортили результирующие строки. Поэтому следует снять защиту с ячеек, в которых распорядителям разрешено вносить изменения. Снять защиту с ячейки можно в меню «Формат ячейки» на вкладке «Защита» (рис. 10).

Контролеру разрешено изменять количество строк (добавлять и удалять), поэтому при установке защиты листа не забудьте отметить галочками «вставку строк» и «удаление строк» (рис. 11). Обязательно задайте пароль на снятие защиты. Иначе сотрудники, знающие, как работать с защитой листа, легко обойдут это ограничение.

📌 Реклама Отключить

  1. Учет в формуле СУММ() новых строк, которые может добавить контролер.

Главное правило при разработке бюджета произвольной длины: всегда использовать функцию СУММ(). Применение этого правила не всегда гарантирует, что в результирующие строки попадут все данные. На рисунке 12 приведен пример, когда контролер бюджета добавил новую строку в конец блока «Аренда помещений» (вполне логично с его точки зрения), но в итоговую формулу она не попала.

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

📌 Реклама Отключить

Следующий этап управления финансами — сбор заявок на оплату и ведение ежедневного платежного календаря.

Вместо заключения

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

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

📌 Реклама Отключить

Статья опубликована в журнале «Планово-экономический отдел» № 11, 2016

About the author

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *