Как провести бизнес анализ с использованием Excel
Пример бизнес анализа в Excel на основе данных 1С
Анализ оборачиваемости дебиторской задолженности
Для использования возможностей Excel, необходимо правильно сформировать исходные данные. Мы предлагаем взять за основу оборотно-сальдовую ведомость в разрезе контрагентов. Анализ оборачиваемости дебиторской задолженности нужно делать по данным ОСВ счета 62. Развернутая оборотно-сальдовая ведомость содержит информацию о входящих остатках по каждому покупателю на начало периода, обороты за год (отгружено/оплачено), конечное сальдо расчетов.
На основе данных бухгалтерского счета 62 «Расчеты с покупателями и заказчиками» мы составляем новую таблицу со следующими колонками:
- Среднегодовой остаток задолженности, руб.
- Период оборачиваемости, дн.
- Проверка на предмет того, есть ли контрагент в списке банкротов.
- Резерв по сомнительным долгам, руб.
В результате будет сформирована сводная диаграмма прогнозных сроков погашения дебиторской и кредиторской задолженности, на которой видно насколько отличаются их графики погашения и когда именно возникает дефицит денежных средств.
Такая сводная диаграмма - это один из способов расчета финансового цикла на основе данных бухгалтерского учета.
Среднегодовой остаток задолженности
Данный показатель рассчитывается как среднее от показателей сальдо начального и сальдо конечного за период. К примеру, для контрагента №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 показателя:
- Среднегодовой остаток кредиторской задолженности, руб.
- Период оборота, дней
Период оборота в данном случае равен отношению среднегодового остатка КЗ к объему закупок или, другими словами, кредитовому обороту (столбец E таблицы данных листа поставщики!).
Система бизнес анализа на основе диаграмм в Excel
Готовые значения используем для построения наглядной диаграммы. Цель – проанализировать скорость погашения ДЗ и КЗ на основе параметров оборачиваемости. Исходные данные берем из таблиц результатов на листах покупатели и поставщики.
Для покупателей ранжируем периода погашения долга следующим образом: 30-60-90-120-180-260 дн. Для поставщиков: 30-60-90-120-180-360. Отдельно заполняем диаграмму для сомнительного долга, под который создан резерв. Это период оборота 180-360 дн. и свыше 360 дн. Обратите внимание, размер сомнительного долга необходимо исключить из общей величины ДЗ. Во всех случаях используем формулу: СУММЕСЛИ. При заполнении диаграммы для поставщиков, вычитаем долг предыдущего периода из общей КЗ. Например, нужно получить сумму долга со сроком 60-90 дней, нужно из общей суммы задолженности < 90 дней вычесть сумму долга < 60 дней.
Что же мы получили в итоге на диаграмме?
Скорость оборота характеризует прогноз поступлений от покупателей и платежей кредиторам. Если вычесть сумму платежей в оплату ДЗ из суммы КЗ к перечислению в разрезе периодов погашения задолженности, получим прогноз дефицита денежных средств.
Выводы для нашего конкретного примера:
Хотя дебиторский долг превышает кредиторскую задолженность, в периоде 31-60 дней возникает дефицит денег в размере 48 млн. руб.
Результативность процесса подготовки отчетности зависит от координации сотрудников в ходе данного процесса, четкости постановки целей, качество обучения сотрудников и качества проведенного внедрения IT систем. Перечисленные ниже риски являются ключевыми в ходе оценки бизнес процессов, связанных с ведением учета и подготовкой отчетности
Все существующие программы по финансовому анализу рассчитывают различные коэффициенты на основе финансовой отчетности. Среди них есть программы, которые выдают готовое аналитическое заключение. Они используются в основном для проверки надежности внешних контрагентов или для проверки соответствия финансовой устойчивости предприятия нормативам банков.
Поддержание оптимального для организации уровня запасов обеспечивает высокую рентабельность бизнеса. Управление буфером запасов помогает избежать перебоев с закупками и упущенных продаж, а также появления неликвидных запасов на складах. Для этих целей используются принципы теории ограничений, которые могут применяться в торговых, производственных, дистрибуционных компаниях, а также в ритейле.