Как сделать скоринговую модель в excel

Обновлено: 05.07.2024

Исторически скоринг как подход был впервые использован в биологических исследованиях во второй половине 30-х гг. 20 века для сортировки объектов, которые было невозможно рассортировать на основании какого-либо одного признака, а другим способом или сильно затруднено, или даже невозможно. К примеру, так сортировались черепа (по принадлежности одному или другому племени) или луковицы ирисов (по принадлежности тому или иному сорту).

Кредитный скоринг – это использование скоринговых решений в процессе кредитования, причем как физических лиц, так и юридических (особенно предприятий малого и среднего бизнеса).

Первостепенная задача, которую решают при кредитовании с помощью скоринга, – это управление рисками.

Spiegel – большой американский ритейлер – весьма рано начал использовать кредитный скоринг. Другой такой фирмой стала Household Finance Corp. Уже в 1946 г. ее президент Е.Ф. Вандерлик разработал Credit Guide Score для оценки новых заявителей, однако внедрение шло с трудом (впоследствии менеджеры его филиалов признавались, что они сначала выдавали кредиты, а потом подгоняли баллы, чтобы оправдать принятое решение).

В 1956 г. на американском финансовом рынке произошло событие, которое кардинально изменило ситуацию в сфере кредитного скоринга. Американцы – инженер Бил Файр и математик Эрл Айзек, работавшие в Стэнфордском исследовательском институте, придумали первую кредитную скоринговую модель. Партнеры разработали математический алгоритм, вычисляющий уровень кредитоспособности заемщика в цифровом выражении. Другими словами, алгоритм позволяет просчитывать кредитные риски в виде трехзначного числа, которое является кредитным рейтингом. Они организовали компанию Fair, Isaac and Company (в 2003 г. она была переименована в Fair Isaac Corporation, а в 2009 г. – FICO).

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

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

Когда в середине девяностых годов в России началось постепенное внедрение скоринговых систем, то отечественные банки столкнулись с дилеммой: разрабатывать их самостоятельно или покупать у западных производителей. Спустя 15 лет появился и третий вариант: отдать скоринг на аутсорсинг.

С 29 июля 2013 г. Сбербанк при выдаче розничных кредитов использует интегральную оценку заемщика, которая основана на Скоринг Бюро 3 поколения (сервис предоставляется Объединенным Кредитным Бюро (ОКБ)) и системе внутреннего скоринга самой кредитной организации.

Тестирование сервиса показало, что совместное использование двух скоринговых моделей дает дополнительный эффект, повышая качество интегральной модели банка более чем на 10%.

С появлением в конце 60-х гг. кредитных карт и банки, и другие эмитенты поняли полезность кредитного скоринга. Большое количество клиентов, подающих заявки на кредитные карты каждый день, сделало невозможным – ни экономически, ни с точки зрения трудозатрат – никакое другое решение, кроме как автоматизация принятия решения о кредитовании. При использовании кредитного скоринга эти организации быстро обнаружили, что эта методика является существенно более надежным прогнозом, нежели экспертные оценки (процент дефолтов снизился на 50% и более).

Виды кредитного скоринга

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

скоринг заявлений (application scoring);
поведенческий скоринг (behaviour scoring);
скоринг по взысканию (collection scoring).

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

При этом принимается не только решение о предоставлении кредита, но и о размере и условиях кредитования.

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

В первую очередь это оценка риска просрочки платежа и/или невозврата и определение тех действий, которые необходимо предпринять, – в этом поведенческий скоринг переходит в скоринг по взысканию (collection scoring).

В этом же классе определение таких важных параметров клиентов, как вероятность их ухода (attrition), склонность их к использованию данного или других продуктов (propensity), а также к увеличению объемов использования (up-sale) или приобретения других продуктов (cross-sell).

Следует сделать два крайне важных замечания:

1) данные по транзакциям используются для выявления и предотвращения мошенничества (и это тоже относят к поведенческому скорингу);

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

Прагматический подход, т.е. отказ от поиска причинно-следственных связей между параметрами и использование выявленных зависимостей между параметрами для прогнозирования поведения клиента (вероятности дефолта по кредиту), вызывает у многих довольно сильное отторжение и приводит к определенным законодательным ограничениям в этой области в некоторых странах.

Основы разработки рейтинговой таблицы, ее проверки и настройки

1. Личные данные . Пол, возраст, семейное положение, наличие или отсутствие иждивенцев, образование.

Пол . Система с большей симпатией относится к женщинам, так как по статистике слабый пол более ответственно подходит к погашению своих обязательств.

Возраст . Наиболее привлекательным для банка является клиент в возрасте от 25 до 45 лет. Чем дальше человек находится от этого возрастного диапазона, тем меньше его балл.

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

Иждивенцы . Если у потенциального заемщика всего один ребенок, это, как правило, не лишает балла, однако чем больше детей, тем ниже балл.

Образование . Человек со средним образованием воспринимается как недостаточно успешный и стабильный, потенциально способный пренебречь своими обязательствами по кредиту, в отличие от человека, получившего высшее образование.

2. Финансовые показатели . Общий трудовой стаж, общий стаж на последнем или последних двух-трех местах работы, профессия, уровень заработной платы и общих расходов.

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

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

Несомненно, банк обратит внимание на соотношение расходов и доходов . С одной стороны, система анализирует, насколько сопоставимы расходы на оплату кредита с финансовыми возможностями клиента и не будет ли обременительным для него дополнительный кредитный договор. С другой стороны, если заемщик заявляет о высоких доходах, но при этом запрашивается незначительная сумма, это вызывает как минимум настороженность. Для чего человеку с доходом в 50 тыс. руб. кредит в 6 тыс.?

Очень внимательно банки относятся к наличию ранее взятых и непогашенных кредитов . Если у заемщика уже имеются ежемесячные выплаты по кредитному договору, то банк спрогнозирует платежеспособность человека с очень большим (для себя) запасом.

3. Сопутствующая информация .

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

Построение скоринговых моделей

Эмпирические требования к базе данных, используемых для построения скоринговой модели:

Кредитная рейтинговая таблица, например, не показывает, какой уровень риска следует ожидать (скажем, какой процент кредитов данного типа, вероятно, не будет возвращен); вместо этого она показывает, как данный заем, скорее всего, будет вести себя по отношению к другим займам. Например, ожидается ли, что процент невозвратов или дефолтов для кредитов с данным набором атрибутов будет больше или меньше, чем у кредитов с другим набором.

Большинство рейтинговых таблиц построены с помощью расчета регрессионной модели – статистической модели, которая проверяет, как отдельный параметр (характеристика) влияет на другой параметр или (чаще всего) на целый набор других параметров.

Регрессионная модель дает в результате своего применения набор коэффициентов (factors), называемых регрессионными, которые можно интерпретировать как корреляцию между искомыми параметрами (которые необходимо определить) и объясняющими параметрами, сохраняя неизменными все остальные воздействия на искомые параметры. Эти коэффициенты превращаются в веса баллов (point weights) в рейтинговой таблице.

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

Рассмотрим модели банкротства предприятия, и более детально методы оценки платежеспособности предприятия.

Что такое скоринговая модель оценки предприятия?

Скоринговый подход к оценке платежеспособности предприятия заключается в анализе статистики по предприятиям по их исполнению обязательств перед кредиторами, информация о которых содержится в бюро кредитных историй. Поэтому скоринговые модели иногда в литературе называют кредитные скоринговые модели (credit-score) или кредитные оценочные модели. Таким образом, можно сказать, что кредитные скоринговые модели – статистические модели оценки платежеспособности предприятия.

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

История скорингового подхода к оценке

Ранее скоринговые модели разрабатывались исключительно для оценки кредитоспособности физических лиц в целях выдачи кредитов банками. Данный подход был впервые предложен Д. Дюраном в 1941 году для классификации клиентов банков по двум классам: кредитоспособные и некредитоспособные. Для определения класса рассчитывались показатели, позволяющие сделать вывод о его риске банкротства. Баллы для скоринговых моделей рассчитываются с помощью инструмента логистической регрессии. На ее основе, к слову, также строятся logit-модели оценки риска банкротства физических лиц и предприятий.

Задача скорингового подхода оценки платежеспособности предприятия

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

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

Скоринговые модели оценки платежеспособности предприятия

Рассмотрим отечественные скоринговые модели оценки платежеспособности предприятия. Проанализируем две отечественные скоринговые модели Донцовой-Никифоровой и Савицкой. Данные модели предназначены для оценки риска банкротства отечественных предприятий. Итак, начнем.

Скоринговая модель Донцовой-Никифоровой (1999 г.)

Донцова Л.В.

Экономисты Донцова Л.В. и Никифорова Н.А. предлагают скоринговую модель оценки платежеспособности предприятия, которая позволяет отнести предприятие к одному из шести классов платежеспособности, на основании оценки шести финансовых коэффициентов.

Профессор Г.В. Савицкая предлагает свою скоринговую кредитную модель оценки финансового состояния предприятия. Отличие заключается в том, что в модели классификация предприятия происодит по пяти классам и для этого используются три финансовых коэффициента.

Показатель	1 класс	2 класс	3 класс	4 класс	5 класс
Рентабельность совокупного капитала, %	30 и выше(50 баллов)	29.9-20(49.9-35 баллов)	19.9-10(34.9-20 баллов)	9.9-1(19.9-5 баллов)	Меньше 1(0 баллов)
Коэффициент текущей ликвидности	2 и больше(30 баллов)	1.99-1.7(29.9-20 баллов)	1.69-1.4(19.9-10 баллов)	1.39-1.1(9.9-1)	1 и ниже(0 баллов)
Коэффициент финансовой независимости	0.7 и больше(20 баллов)	0.69-0.45(19.9-10 баллов)	0.44-0.3(9.9-5 баллов)	0.29-0.2(4.9-1 баллов)	Меньше 0.2(0 баллов)
Границы классов	100 баллов	99-65	64-35	34-6	0 баллов
1 класс>100 баллов	Предприятие с хорошей финансовой прочностью
2 класс65-99 баллов	Предприятие имеет небольшой риск невозврата долгов
3 класс35-64 баллов	Проблемное предприятие
4 класс6-34 баллов	Предприятие имеет высокий риск банкротства. Кредиторы рискую потерять вложенные средства
5 класс0 баллов	Предприятие несостоятельно

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

Коэффициент финансовой независимости = коэффициент автономии.

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

Рентабельность совокупного капитала

Коэффициент текущей ликвидности

Коэффициент финансовой независимости

Резюме

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

Спасибо за внимание! Удачи!
Автор: Жданов Василий Юрьевич, к.э.н.

20.10.2020 | Ренат Алимбеков г. Алматы Казахстан | 0

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

Моделирование кредитных рисков

Моделирование кредитных рисков в банковский сектор пришло из ботаники. В статистике идеи классификации популяции на группы были разработаны Фишером в 1936 г. на примере растений. Этот тот самый знаменитый пример Ирисов Фишера. Он часто используется для иллюстрации работы статистических алгоритмов.

Таблица -1. Пример скоркарты 40-x годов.

Параметр	Критерий оценки
Возраст	0,1 балла за каждый год свыше 20 лет (максимум — 0,30)
Пол	женский (0,40), мужской (0)
Срок проживания в регионе	0,042 за каждый год (максимально — 0,42)
Профессия	0,55 за профессию с низким риском, 0 за профессию с высоким риском, 0,16 — другие профессии
Работа	0,21 на предприятиях общественной отрасли, 0 — другие
Срок занятости	0,059 за каждый год работы на данном предприятии
Финансовые показатели	0,45 за наличие банковского счета, 0,35 за наличие недвижимости, 0,19 — за наличие полиса по страхованию

Современные подходы к моделированию

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

С интерпретацией логистической регрессии немного сложнее. Для этих целей и разрабатывается скоринговая карта.

Жизненный цикл разработки скоринговых карт

Сбор, очистка и предобработка данных
Монотонный WOE bining признаков
Ручной WOE bining признаков при необходимости
Отбор признаков по Information Value
Построение логистической регрессии
Построение скоркарты

Монотонный WOE binning признаков

Теперь необходимо написать WOE binning для числовых и категориальных переменных.

WOE считается для каждого признака!

Плюсы монотонного WOE binningа признаков:

Упрощает интерпретацию
Обрабатывает отсутствующие значения
Выявляет сложные нелинейные связи
Преобразование основано на логарифмическом распределении
Упрощает обработку выбросов
Нет необходимости в dummy variables
Можно устанавливать монотонную зависимость (либо увеличение, либо уменьшение) между независимой и зависимой переменной

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

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

Отбор признаков по Information Value

Information Value (IV) измеряет предсказательную силу признаков. Считается для каждого признака.

Значения Information Value (IV) для определения cutoff по отбору признаков:

План отбора признаков по IV:

Определить Cutoff для IV и корреляции
Можно сразу исключить признаки с низким Information Value, а затем и по cutoff
Осуществить проверку на корреляцию. Из двух коррелирующих признаков нужно исключить тот, у которого IV меньше

Построение логистической регрессии

Строим логистическую регрессию и оцениваем метрики на кросс-валидации и тестовой выборке. Смотрим на коэффициент ROC AUC или Gini.

Построение скоркарты — масштабирование баллов

Скорбалл считается по следующей формуле:

Score = (β×WoE+ α/n)×Factor + Offset

β — коэффициент логистической регрессии признака

α — свободный член

WoE — Weight of Evidence признака

n — количество признаков, включенных в модель

Factor, Offset — параметры масштабирования. Множитель и смещение.

Множитель и смещение считаются так:

Factor = pdo/Ln(2) Offset = B — (Factor × ln(Odds))

pdo — количество баллов, удваивающее шансы

B — значение на шкале баллов, в которой соотношение шансов составляет С:1

Например: Если скоринговая карта имеет базовые коэффициенты 50 : 1 в 600 баллах, а pdo из 20 (вероятность удвоения каждые 20 баллов), то множитель и смещение будут: Factor = 20/Ln(2) = 28.85 Offset = 600- 28.85 × Ln (50) = 487.14

Получаем следующие скорбаллы:

А3 от 0 до 1.5 : 20 баллов
А3 от 1.54 до 5.04 : 23 балла
А3 от 5.085 до 28 : 32 балла

Таким образом скорбаллы считаются и для остальных признаков и составляется итоговая скоркарта. Дополнительный плюс этого подхода, что потом реализовать готовую скоркарту можно на любом языке программирования, в любой системе используя конструкцию if else.

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