Расчет модели Альтмана для прогнозирования риска банкротства в Excel

Для того что бы создать модель Альтмана (Z-score) для прогнозирования риска банкротства предприятий нам понадобится MS Excel, данные по форме№1 «Баланс предприятия» и форме№2 «Отчет о прибылях и убытках» российской бухгалтерской отчетности по анализируемому предприятию.

Попробуем вместе создать двух и пятифакторную модель Альтмана в Excel. Бухгалтерская отчетность посмотрите в приложении 1 к этой статье. Смело копируйте ее и вставляйте к себе в таблицу Excel.

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

Строки баланса, которые нам понадобятся для создания двух и пятифакторных моделей Альтмана в Excel Форма 1: стр. 230, стр.290, стр.300, стр. 490, стр. 590, стр. 690; Форма2: стр.010, стр.190.

Переходим на второй лист Excel и приступаем к созданию наших моделей. Пример вычисления представлен на рисунке 2. 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%”)


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

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

модель Альтмана
Рис3. Подсказка к модели

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

Скачать модель Альтмана в Excel

Автор: Жданов Василий
Be in trend

Приложение 1. Бухгалтерский баланс

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

Актив / Пассив

Код  строки

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

На конец года

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

 

 

 

 

Оцените статью
Adblock
detector