Как провести бизнес анализ с использованием Excel

Пример бизнес анализа в Excel на основе данных 1С

Анализ оборачиваемости дебиторской задолженности

Для использования возможностей Excel, необходимо правильно сформировать исходные данные. Мы предлагаем взять за основу оборотно-сальдовую ведомость в разрезе контрагентов. Анализ оборачиваемости дебиторской задолженности нужно делать по данным ОСВ счета 62. Развернутая оборотно-сальдовая ведомость содержит информацию о входящих остатках по каждому покупателю на начало периода, обороты за год (отгружено/оплачено), конечное сальдо расчетов.

На основе данных бухгалтерского счета 62 «Расчеты с покупателями и заказчиками» мы составляем новую таблицу со следующими колонками:

1. Среднегодовой остаток задолженности, руб.
2. Период оборачиваемости, дн.
3. Проверка на предмет того, есть ли контрагент в списке банкротов.
4. Резерв по сомнительным долгам, руб.

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

Среднегодовой остаток задолженности

Данный показатель рассчитывается как среднее от показателей сальдо начального и сальдо конечного за период. К примеру, для контрагента №1 эта величина считается так:

Формула – (F9-G9+B9-C9)/2;

В цифровом выражении – (49 548,42-0+0-25 297,77)/2;

Результат 12 125 руб. (округляем до целого без копеек).

Далее копируем формулу и «протягиваем» ее для всех ячеек таблицы.

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

Показатель представляет собой отношение среднегодового сальдо (мы его рассчитали выше и поставили значения в столбец I таблицы результатов) к выручке периода (выручка отражается проводкой: Дт сч. 62 Кт сч. 90 и указана в столбце D таблицы данных), скорректированное на длину периода (в нашем случае это 365 дн., но может быть и 366).

Формула выглядит так: I9/D9*365 (результат получается в днях).

Давайте рассчитаем данный показатель для 1-ого контрагента: 12 125/517 208,51х365 = 8,5567521=9 (округляем до целого значения).

«Протягиваем» формулу для всех ячеек. В некоторых клетках получаем данные: #ДЕЛ/0! Ошибка в выдаче связана с отсутствием данных о выручке по ряду контрагентов. Реализации по ним просто не было, а деление на 0 невозможно. В любом случае, по ним получается, что период оборачиваемости более года, значит, логично поставить в таблице результатов в колонке «Период оборота» значение 366 дн. Для этого корректируем формула следующим образом:

=ЕСЛИОШИБКА(I9/D9*365;366)

Теперь по контрагентам с нулевой выручкой в столбце I стоит значение 366.

Наличие в списке банкротов

Мы постарались автоматизировать и процедуры проверки компаний на наличие в списке банкротов. Для этого на отдельном листе в Ecxel ведется перечень организаций-банкротов из числа контрагентов поставщика. Названия компаний должны быть идентичный как в таблице данных, так и в перечне банкротов. Тогда можно легко использовать функцию ВПР для заполнения столбца L таблицы результатов.

Формула выглядит так: =ВПР(А9;банкроты!А:А;1;0)

Функция ВПР выдает название контрагента, если находит его в списке банкротов. Чтобы вместо названия или значения #Р/Д получить да/нет, дополнительно используем функции ЕСЛИ или ЕНД.

Откорректированная формула будет следующей: =ЕСЛИ(ЕНД(ВПР(А9;банкроты!А:А;1;0));«нет»;«да»)

Расчет резерва по сомнительным долгам

Финансовая модель в excel рассчитывает резерв сомнительных к получению долгов с учетом следующих критериев отбора:

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

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

Тогда формула расчета резерва выглядит так: =если(или(J9>180;К9=«да»);F9;0)

Для унификации формулы ставим значение 180 в ячейку L4, тогда уточненная формула будет иметь вид: =если(или(J9>$L4$;К9=«да»);F9;0)

Теперь аналогично проведем анализ оборачиваемости кредиторской задолженности. Наша цель: сопоставить скорость оборота дебиторского и кредиторского долга.

Анализ оборачиваемости кредиторской задолженности

Расчет показателей оборачиваемости КЗ проводим аналогично ДЗ, но за основу берем ОСВ по сч. 60 в разрезе поставщиков. В таблице результатов заполняем только 2 показателя:

1. Среднегодовой остаток кредиторской задолженности, руб.
2. Период оборота, дней

Период оборота в данном случае равен отношению среднегодового остатка КЗ к объему закупок или, другими словами, кредитовому обороту (столбец E таблицы данных листа поставщики!).

Система бизнес анализа на основе диаграмм в Excel

Готовые значения используем для построения наглядной диаграммы. Цель – проанализировать скорость погашения ДЗ и КЗ на основе параметров оборачиваемости. Исходные данные берем из таблиц результатов на листах покупатели и поставщики.

Для покупателей ранжируем периода погашения долга следующим образом: 30-60-90-120-180-260 дн. Для поставщиков: 30-60-90-120-180-360. Отдельно заполняем диаграмму для сомнительного долга, под который создан резерв. Это период оборота 180-360 дн. и свыше 360 дн. Обратите внимание, размер сомнительного долга необходимо исключить из общей величины ДЗ. Во всех случаях используем формулу: СУММЕСЛИ. При заполнении диаграммы для поставщиков, вычитаем долг предыдущего периода из общей КЗ. Например, нужно получить сумму долга со сроком 60-90 дней, нужно из общей суммы задолженности < 90 дней вычесть сумму долга < 60 дней.

Что же мы получили в итоге на диаграмме?

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

Выводы для нашего конкретного примера:

Хотя дебиторский долг превышает кредиторскую задолженность, в периоде 31-60 дней возникает дефицит денег в размере 48 млн. руб.