Финансовый директор

Контроль задолженностей и запасов в Excel: три этапа разработки отчетов

2388
Шуклов Лев
заместитель начальника управления бухгалтерской и налоговой отчетности ООО «НГК “ИТЕРА”», д.э.н.
В статье опыт разработки финансовой модели построения динамических отчетов в Excel для контроля оборачиваемости задолженности и запасов. Такие отчеты позволяют спрогнозировать погашение задолженности в нескольких сценариях, рассчитать финансовый цикл и оборачиваемость расчетов.

Чем компании помогут динамические отчеты в Excel

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

Мы разработали финансовую модель построения динамических отчетов в Excel (отчеты в динамике за 12–30 периодов, например за 30 последних месяцев. — Прим. авт.) об оборачиваемости задолженностей и запасов, которая помогает оперативно провести анализ изменений в финансовом состоянии контрагентов, выявить внутренние резервы ускорения оборачиваемости расчетов и повышения ликвидности. Главный результат всех расчетов — сводный анализ оборачиваемости дебиторской, кредиторской задолженностей и запасов. Иными словами, оценка взаимных соответствий оборачиваемости дебиторов, кредиторов и запасов, а также оценка изменения этих показателей во времени. Оборачиваемость в днях мы рассчитываем как отношение сальдо на конец каждого месяца к объему хозяйственных операций за период действия договора, умноженное на время, прошедшее с момента заключения договора. То есть, определяя, скольким оборотам за текущий и предыдущие месяцы равно сальдо, мы получаем срок, за который в среднем погашается задолженность на конец месяца.

Excel здесь выступает в роли универсального прикладного инструмента для настройки модели, поскольку построение динамических отчетов, например, в системе «1С» — достаточно дорогое удовольствие. Мы выгружаем в файл Excel (лист «Данные», столбцы от, А до Е) стандартный «Анализ счета» из «1С: Предприятие» в разрезе субконто и периодов, путем функции «Сохранить как» (см. табл. 1, здесь и далее по тексту будет рассматриваться файл Excel, который можно скачать в «Дополнительных материалах» к этой статье. — Прим. ред.). Такой отчет достаточно объемный и занимает по различным компаниям нашей группы от 50 до 120 тысяч строк, однако современный Excel обрабатывает его всего за одну-две минуты. Далее в три этапа формируем динамические таблицы в Excel. Поговорим о них подробнее

Таблица 1. Образец выгрузки отчета «Анализ счета» из «1С: Предприятие»

Образец выгрузки отчета «Анализ счета» из «1С: Предприятие»

Этап 1. Настройка параметров динамических отчетов

На листе «Переменные» настраиваем параметры обработки корреспонденций счетов и периодов. Для этого для начала необходимо определить, как модель будет обрабатывать хозяйственные операции: какой оборот считать поступлением, какой — погашением, какой — зачетом и т. д. Например, поступлением дебиторской задолженности является оборот Дт_62 Кт_90, а ее погашением — Дт_51 Кт_62. Для материалов все иначе — поступление — Дт_10 Кт_60, погашение — Дт_20 Кт_10. В учете могут быть также обороты переброски (перенос материалов с одного склада на другой) и взаимозачета, которые не должны влиять на расчет скорости оборачиваемости задолженности. В нашей модели пользователь может вручную определить, как обрабатывать каждый оборот. Для этого надо сделать следующее.

Строим таблицу «Настройка параметров обработки счетов»

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

  • поступление (В);
  • взаимозачет (З);
  • погашение (О);
  • списание ©.

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

Таблица 2. Настройка параметров обработки счетов (извлечение)

Номер счета по плану счетов Б/У Дт оборот / Кт оборот Корсчет Название операции Код

76

ДО

19

НДС

В

76

ДО

51

Оплаты

О

76

ДО

52

Оплаты

О

76

ДО

60

Зачеты

З

76

ДО

62

Зачеты

З

76

ДО

79

Зачеты

З

76

ДО

91

Поставки

В

76

КО

3

Поставки

В

76

КО

10

Поставки

В

76

КО

19

НДС

В

Настраиваем картографируемый счет

То есть счет, с которым возможны те или иные корреспонденции счетов. Его также проставляем вручную в ячейке «Р16» на листе «Переменные». Он влияет на результаты значений в столбце «Код» таблицы «Кодификация оборотов» (см. табл. 3). Наш пример построен для счета 62. Однако в любой момент его можно поменять, например, поставить 41, и все коды в таблице «Кодификация оборотов» изменятся в соответствии с новыми корреспонденциями счетов, указанными нами в таблице «Настройка параметров обработки счетов».

Таблица 3. Кодификация оборотов (извлечение)

Дт обороты Код Кт обороты Код

3

 

Н/Д

3

 

Н/Д

8

 

Н/Д

8

 

Н/Д

10

 

Н/Д

10

 

Н/Д

19

 

Н/Д

19

 

Н/Д

20

 

Н/Д

20

 

Н/Д

26

 

Н/Д

26

 

Н/Д

41

 

Н/Д

41

 

Н/Д

44

 

Н/Д

44

 

Н/Д

45

 

Н/Д

45

 

Н/Д

51

Возврат аванса

А

51

Оплата

О

52

 

Н/Д

52

Оплата

О

60

 

Н/Д

60

Зачет

З

62

Зачет

З

62

Зачет

З

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

В столбец «Код» таблицы «Кодификация оборотов» вписываем формулу

=ЕСЛИ(ЕНД(ВПР(СЦЕПИТЬ($P$16;«_КО_»;I20);$U:$Z; 6;0));«Н/Д»;ВПР(СЦЕПИТЬ($P$16;«_КО_»;I20); $U:$Z;6;0))

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

Для настройки параметров обработки периодов их также кодифицируем

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

1. В ячейке «Р19» указываем дату начала изучаемого периода — у нас — 01.08.12.

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

2. В ячейке «Р13» проставляем отчетную дату — 31.01.15.

Формируем таблицу «Настройка параметров обработки периодов» (см. табл. 4). Эта таблица также настраивается один раз и используется в дальнейшем для анализа. Далее в столбце «Название оборота в „1С“» нам надо вручную проставить все возможные варианты названия периода оборота, которые могут быть в «1С».

Таблица 4. Настройка параметров обработки периодов (извлечение)

Название оборота в «1С» Дата Код периода

Обороты за август-12

31.08.12

1

Обороты за август-13

31.08.13

13

Обороты за апрель-12

30.04.12

1

Обороты за апрель-13

30.04.13

9

Обороты за апрель-14

30.04.14

21

Обороты за декабрь-12

31.12.12

5

Обороты за декабрь-13

31.12.13

17

Обороты за июль-12

31.07.12

1

Нюанс: периоды время от времени нужно продлевать. Так, у нас в файле Excel одноименный столбец заканчивается периодом — июнь-17. Соответственно, по мере необходимости данный столбец продлеваем.

В столбце «Дата» указываем дату окончания соответствующего периода. Столбец «Код периода» — расчетный, в него включена формула:

=ЕСЛИ(ОКРУГЛ(30-($P$ 13-S3)/30;0)>0;ОКРУГЛ(30-($P$ 13-S3)/30;0);1)

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

Все данные готовы для формирования таблицы «Кодификация периодов» на листе «Переменные» (см. табл. 5). Нужные значения из одноименного столбца таблицы «Настройка параметров обработки периодов» перетаскиваем в столбец «Код периода» с помощью формулы:

= ВПР(B4;R:T;3;0)

На этом настройка данных завершена: в таблицах «Кодификация оборотов» и «Кодификация периодов» содержится вся необходимая для дальнейшей работы информация.

Таблица 5. Кодификация периодов (извлечение)

Название периода Код периода

Обороты за август-12

1

Обороты за август-13

13

Обороты за апрель-12

1

Обороты за апрель-13

9

Обороты за апрель-14

21

Обороты за декабрь-12

5

Обороты за декабрь-13

17

Этап 2. Подготовка данных

Для подготовки данных мы вернемся на лист «Данные» к нашему Анализу счета 62. Дополняем его столбцами, которые будут впоследствии учитываться при формировании сводных итоговых таблиц (см. табл. 6).

Таблица. 6. Дополнительные столбцы для формирования модели (извлечение)

Дополнительные столбцы для формирования модели

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

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

В столбцах 6–9 приведена информация о названиях и номерах конкретного контрагента и договора.

Столбец 10 «Период». Этот столбец формирует названия оборотов периодов по соответствующему контрагенту и договору с помощью формулы:

=ЕСЛИ(I13=1;A13;O12)

Столбец 11 «Код периода». В него из таблицы «Кодификация периодов» переносим коды периодов с помощью формулы:

=ЕСЛИ(I13<2;ВПР(O13;переменные! B:C;2;0);ЕСЛИ(I13=2;переменные!$AC$3;0))

Столбец 12 «Код оборота». Из таблицы «Кодификация оборотов» коды оборотов в него перетаскивает формула:

=ЕСЛИ(Ч(D13)-Ч(E13)>0;ВПР(ЕСЛИОШИБКА(ЗНАЧЕН (C13);«Начальное сальдо»);переменные! F:H;3;0);ЕСЛИ(Ч(D13)-Ч(E13)<0;ВПР(ЕСЛИОШИБКА(ЗНАЧЕН(C13);«Начальное сальдо»);переменные! I:K;3;0);" «))

Столбец 13 «Сумма». В данном столбце рассчитывается в тысячах рублей сумма операций по формуле:

=Ч(D13)/ 1000-Ч(E13)/1000

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

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

=ЕСЛИ(И(Q13=«СН»;I13=2);«СН»;" «)

Читайте также:

Этап 3. Формирование сводных таблиц

Заключительный этап — составляем сводные таблицы в Excel для каждого из кодов оборота. Для этого переходим на лист «Расчеты».

С помощью функции Excel «Сводные таблицы» строим отдельную таблицу

В ней по вертикали указаны контрагенты, по горизонтали — периоды. В поле «Данные» указываем величину поступлений «В». Аналогичные таблицы делаем для погашений — «О», взаимозачетов — «З» и списаний — «С».

Нюанс: поскольку мы рассматриваем в данной статье упрощенный пример, в котором нет взаимозачетов и списаний, то в файле Excel нет и этих таблиц. Однако если у вас в учете существуют такие операции, то таблицы по ним необходимо строить. Они дополнят таблицу об оборачиваемости расширенной аналитикой — например, в периодах, в которых произошло погашение задолженности взаимозачетом, вместо оборачиваемости может быть (по желанию пользователя) указана буква «З», а в периодах списания — буква «С».

Чтобы самостоятельно создать сводные таблицы, нужно сделать следующее.

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

В результате получается требуемая нам таблица.

Нюанс: если у вас немного договоров, например, не более 200, то можно построить нужные таблицы с помощью функции: СУММЕСЛИМН. Однако стоит учесть, что при больших объемах данных функция «Сводные таблицы» существенно быстрее обсчитывает Excel.

Настраиваем таблицу «Сальдо». Данная таблица — расчетная. Вычисляем сальдо для каждого договора в каждом месяце с помощью формулы:

=СУММЕСЛИМН(данные! R:R;данные! S:S;«СН»;данные! J:J;расчеты! A33) C6 C20

Определяем оборачиваемость

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

Таблица 7. Принцип расчета оборачиваемости задолженности

Показатель Январь Февраль Март Апрель Май Июнь Июль Август Сентябрь

Поступления

100

50

50

200

0

20

0

0

0

Оплаты

     

100

100

 

100

   

Сальдо

100

150

200

300

200

220

120

120

120

Оборачиваемость, дни

30

60

90

90

75

95

65

80

98

Покажу принцип расчета, например, за последние шесть месяцев:

январь = 100 : 100 × 30 = 30

февраль = 150 : (100 + 50) × 60 = 60

март = 200 : 200 × 90 = 90

апрель = 300 : 400 × 120 = 90

май = 200 : 400 × 150 = 75

июнь = 220 : 420 × 180 = 95

июль = 120 : (50 + 50 + 200 + 0 + 20 + 0) × 180 = 65 — сдвигаем период, берем только последние шесть месяцев. Первые 100 поступлений в январе не учитываем

август = 120 : (50 + 200 + 0 + 20 + 0 + 0) × 180 = 80

сентябрь = 120 : (200 + 0 + 20 + 0 + 0 + 0) × 180 = 98

Для унификации данных настроим округление возраста задолженности

Например, как показано в Таблице округления возраста на листе «Расчеты» (см. табл. 8). В первом столбце указаны пороговые значения диапазона, во втором — значение, до которого округляется возраст задолженности.

Таблица 8. Таблица округления возраста (дн.)

Пороговые значения диапазона

-9999

-1

1

30

40

60

75

90

120

150

180

181

9999

Значение, до которого округляется возраст задолженности

15

15

15

30

45

60

75

90

120

150

180

360

360

Например, мы, округляем возраст задолженности следующим образом:

  • если возраст получился 65 дней, то в таблице он округляется до 60 дней;
  • если оборачиваемость получилась отрицательной (например, это аванс и конечное сальдо меньше нуля), то вместо отрицательных чисел в этой таблице у нас проставляется 15 дней — время, в течение которого в среднем происходит зачет аванса. Таким образом, мы не учитываем возраст авансов при анализе оборачиваемости;
  • если расчетная оборачиваемость получилась больше 180 дней, то возраст мы считаем равным 360 дням — подразумеваем, что его невозможно достоверно оценить на основе данных за последние шесть месяцев, определенно ясно лишь то, что он больше 180 дней. Например, если есть входящее сальдо, но за шесть месяцев нет никаких оборотов, значит неизвестно, как давно оно появилось, и поэтому мы предполагаем, что оно появилось более 360 дней назад.

Итак, построение динамических таблиц оборачиваемости завершено.

Преимущества динамических таблиц

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

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

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

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

  • как давно начались расчеты с каждым клиентом;
  • сколько новых клиентов каждый месяц приходит в компанию и с какими объемами продаж.

2. Усиливать превентивный контроль за финансовым состоянием и оплатой клиентов с ухудшением расчетов.

3. Перераспределять продажи в пользу клиентов с улучшающейся динамикой расчетов.

4. Погнозировать минимальный / максимальный срок возможного погашения задолженности в будущем на основе ее истории погашения в прошлом.

5. Автоматизировать ряд трансформационных поправок в отчетности МСФО:

  • расчет резерва по сомнительным долгам;
  • расчет резерва по неликвидным материалам и т. п.

Методические рекомендации по управлению финансами компании



Ваша персональная подборка

    Подписка на статьи

    Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

    
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

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

    Это бесплатно и займет всего 1 минуту.

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