Прогнозирование риска банкротства с помощью Altman model. Пример модели Альтмана в Excel 2010

Создание двух и пятифакторной модели Альтмана (Z-score) в Excel 2010.

Часть бухгалтерской отчетности в таблице Excel

Бухгалтерская отчетность в конце статьи.

Строки баланса, которые нам понадобятся для создания двух и пятифакторных моделей Альтмана в Excel:

Форма 1: стр. 230, стр.290, стр.300, стр. 490, стр. 590, стр. 690

Форма2: стр.010, стр.190.

Переходим на второй лист Excel и приступаем к созданию наших моделей. Пример вычисления представлен на рисунке. X1,Xn – коэффициенты в моделях, а Z – результирующее число.

Расчет коэффициентов для двухфакторной модели:

X1=290/690
X2=(590+690)/490

Для пятифакторной:

Х1=(290-230-690)/300
Х2=190/300
Х3=140/300
Х4=490/(590+690)
Х5=010/300

Помимо этого автоматически происходит вычисление вероятности банкротства предприятия с помощью функции «ЕСЛИ» следующим образом:

ЕСЛИ(Z=0;”50%”;ЕСЛИ(Z<0;”меньше 50%”;”больше 50%”)

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

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

Подсказка к модели

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

Бухгалтерский баланс

Бухгалтерский  баланс
Актив / Пассив	Код  строки
		На начало года	На конец года
1. Внеоборотные  активы
Нематериальные активы	110	981.0	1 387.0
Основные средства	120	20 092.0	22 040.0
Незавершенное строительство	130	0.0	0.0
Доходные вложения в материальные ценности	135	0.0	0.0
Долгосрочные финансовые вложения	140	20 969.0	2 926.0
Отложенные финансовые активы	145	16 284.0	0.0
Прочие внеоборотные активы	150	0.0	0.0
Итого  по  разделу  № 1	190	58 326.0	26 353.0
2. Оборотные  активы
Запасы	210	33 591.0	16 630.0
сырье, материалы и другие аналогичные ценности	211	9 223.0	4 359.0
животные на выращивании и откорме	212	0.0	0.0
затраты в незавершенном производстве	213	1 985.0	1 978.0
готовая продукция и товары для перепродажи	214	21 186.0	10 175.0
товары отгруженные	215	0.0	0.0
расходы будущих периодов	216	1 197.0	118.0
прочие запасы и затраты	217	0.0	0.0
Налог на добавленную стоимость по приобретенным ценностям	220	26 313.0	23 667.0
Дебиторская задолженность (платежи по которой ожидаются более чем через 12 месяцев после отчетной даты)	230	0.0	0.0
в том числе покупатели и заказчики		0.0	0.0
Дебиторская задолженность (платежи по которой ожидаются в течение 12 месяцев после отчетной даты)	240	147 193.0	158 681.0
в том числе покупатели и заказчики	241	93 659.0	122 721.0
Краткосрочные финансовые вложения	250	33 478.0	2 272.0
Денежные средства	260	174.0	1 794.0
Прочие оборотные активы	270	0.0	0.0
Итого  по  разделу  № 2	290	240 749.0	203 044.0
Актив  (Сумма  строк  190+290)	300	299 075.0	229 397.0
3. Капитал  и  резервы
Уставный капитал	410	3 066.0	3 066.0
Собственные акции, выкупленные у акционеров
Добавочный капитал	420	1 037.0	1 037.0
Резервный капитал	430	1 238.0	1 238.0
резервы, образованные в соответствии с законодательством	431	1 238.0	1 238.0
резервы, образованные в соответствии с учредительными документами	432	0.0	0.0
Нераспределенная прибыль (непокрытый убыток)	470	53 760.0	40 160.0
Итого  по  разделу  № 3	490	59 101.0	45 501.0
4.  Долгосрочные  обязательства
Займы и кредиты	510	0.0	0.0
Отложенные налоговые обязательства	515	0.0	0.0
Прочие долгосрочные обязательства	520	0.0	0.0
Итого  по  разделу  № 4	590	0.0	0.0
5.  Краткосрочные  обязательства
Займы и кредиты	610	7 896	0.0
Кредиторская задолженность	620	232 078	183 896.0
поставщики и подрядчики	621	158 062	140 901.0
задолженность перед персоналом организации	622	28 660	3 660.0
задолженность перед государственными внебюджетными фондами	623	0.0	0.0
задолженность по налогам и сборам	624	1 134.0	1 729.0
прочие кредиторы	625	1 731.0	445.0
Задолженность перед участниками (учредителям) по выплате доходов	630	0.0	0.0
Доходы будущих периодов	640	0.0	0.0
Резервы предстоящих расходов	650	0.0	0.0
Прочие краткосрочные обязательства	660	0.0	0.0
Итого  по  разделу  № 5	690	239 974.0	183 896.0
Пассив  (Сумма  строк  490+590+690)	700	299 075.0	229 397.0
Проверка  баланса		0.0	0.0
Отчет  о  прибылях  и  убытках
Выручка (нетто)  от  реализации  товаров,  продукции, работ, услуг,  в  том  числе:	010	130 697.0	540 471.0
Себестоимость  реализации  товаров,  продукции,  работ,  услуг,  в  том  числе:	020	120 154.0	476 123.0
Валовая  прибыль	029	10 543.0	64 348.0
Коммерческие  расходы	030	0.0	4 325.0
Управленческие расходы	040	5 262.0	27 466.0
Прибыль  от  реализации (010-020-030-040)	050	5 281.0	32 557.0
Проценты  к  получению	060	0.0	0.0
Проценты  к  уплате	070	0.0	0.0
Доходы  от  участия  в  других  организациях	080	0.0	0.0
Прочие  операционные  доходы	090	11 460.0	134 247.0
Прочие  операционные  расходы	100	11 459.0	139 560.0
Прочие  внереализационные  доходы	120	10.0	609.0
Прочие  внереализационные  расходы	130	1 001.0	7 713.0
Прибыль  до  налогообложения                                                                 (050+060+080+090+120-070-100-130)	140	4 291.0	20 140.0
Отложенные налоговые активы	141	0.0	0.0
Отложенные налоговые обязательства	142	0.0	0.0
Текущий налог на прибыль	150	440.0	7 435.0
Чистая  прибыль	190	3 851.0	12 705.0

Автор: Жданов Иван