Построение логит модели в excel

Обновлено: 05.07.2024

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Модели оценки вероятности банкротства представляют собой логистическое регрессионное уравнение. Отсюда и вытекает название модели логистической регрессии (logit-модели) оценки вероятности банкротства. К сравнению, модели множественного дискриминантного анализа (MDA-модели), куда относится модель американца Э. Альтмана, имеют вид простой регрессии.

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

Ниже представлено сравнение подходов к оценке финансового состояния по logit-моделям и по mda-моделям.

Сравнение подходов к оценке финансовой устойчивости предприятия

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Разработка математического инструментария модели

Э. Альтман, 1968 год

Обозначение интегрального показателя

Деление на «красную», «серую» и «зеленую» зоны

Как строятся logit-модели?

Алгоритм построения logit-моделей оценки вероятности банкротства:

1. Формируем выборку предприятий банкротов (около 20-100 предприятий)
2. Формируем выборку предприятий небанкротов (20-100 предприятий)
3. Рассчитываем финансовые коэффициенты для всех предприятий
4. С помощью инструментария логистической регрессии (можно сделать это в программе SPSS) строится логистическое регрессионное уравнение, которое дает оценку риска банкротства предприятия
5. Проверяется адекватность построенной модели

Модели оценки вероятности банкротства

Ниже приведу наиболее известные модели оценки вероятности банкротства (logit-модели) и комментарии к ним. Рассмотрим несколько моделей зарубежных авторов, а также коснемся и отечественных разработок.

Модель Дж. Ольсона (1980 г.)

Формула расчета

P – вероятность банкротства (значение в интервале от 0 до 1)

Коэффициент

Ln (стр.1600/индекс-дефлятор ВВП)

стр.2400 / стр.2400_{2 года назад}

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

Ln – натуральный логарифм. Индекс дефлятор показывает темп роста ВВП (экономики страны).

К₅ – рентабельность активов (ROA).

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

K₈ – фиктивная переменная, принимающая значение равное 1, если текущая задолженность копании (стр. 1400 + стр. 1500) превышает ее текущие активы (стр. 1600), и значение равное 0, если нет;

К₉ – мера изменения чистого дохода (чистой прибыли) за последние два года.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Модель Джу-Ха, Техонга (2000 г.)

Трехфакторная logit-модель разработана на основе статистических данных 46 Южно-Корейских предприятий, признанными банкротами в 1997- 1998 гг. Оценка вероятности банкротства находится по формуле ниже.

Формула расчета

P – вероятность банкротства (имеет значение в интервале от 0 до 1)

Коэффициент

стр.2110 / стр. 1230

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

Российские модели оценки вероятности банкротства

Рассмотрим две современные модели оценки вероятности банкротства (logit-модели) для российской экономики.

Модель Хайдаршиной (2009 г.)

Русскими экономистами также предлагаются различные модели для оценки вероятности банкротства. Можно выделить 11-факторную logit-модель Г.А. Хайдаршиной, разработанную в 2009 году, для предприятий различных отраслей.

Формула расчета

Коэффициент

стр.1200 / (стр.1520 + стр. 1510+стр. 1550)

Темп роста стр.1300

К₁ – фактор «возраста» компании. Принимает значение 0, при условии, что предприятие было создано более 10 лет назад, и значение 1 – если менее 10 лет.

К₂ – характеристика кредитной истории хозяйственной деятельности компании. В случае, если она является положительной, принимается значение 0, отрицательной – 1.

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

К₆ – ставка рефинансирования ЦБ.

К₇ – характеристика деятельности предприятия с точки зрения ее региональной принадлежности. Принимает значение 0 – при нахождении в Москве или Санкт-Петербурге, и 1 – в других регионах.

К₉ – рентабельность собственного капитала предприятия.

К₁₀ – темп прироста собственного капитала компании.

К₁₁ – темп прироста активов компании.

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

Фактор модели

Сельское хозяйство

Модель Жданова (2012 г.)

В 2012 году была разработана модель для оценки вероятности банкротства промышленных предприятий. Для построения брались предприятия авиационно-промышленного комплекса России. Выборка состояла из 40 предприятий (20 из которых стали банкротами, а 20 остались финансово устойчивыми в период с 2000 по 2010 гг.). В состав модели входит 5 основных финансовых коэффициентов, отобранных с помощью корреляционного анализа. На наш взгляд они наиболее точно описывают финансы предприятия.

Формула расчета

Коэффициент

стр. 1100 / стр. 1200

стр.1200 / (стр.1520+стр. 1510+стр. 1550)

Расшифровка финансовых коэффициентов представлена ниже.

К₁ – коэффициент рентабельности оборотных активов,

К₂ – коэффициент самофинансирования (коэффициент капитализации),

К₃ – коэффициент соотношения мобильных и иммобилизованных активов,

К₄ – коэффициент оборачиваемости активов (находится среднее значение показателя за период),

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

Оценка предприятия по logit-модели

Ниже в таблице представлены интервалы для описания риска банкротства у предприятия.

Интервалы изменения вероятности банкротства предприятия

Очень высокий риск банкротства

Риск банкротства ниже среднего

Очень низкий риск банкротства

Резюме

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

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

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

Основная идея логистической регрессии

В отличие от обычной регрессии, в методе логистической регрессии не производится предсказание значения числовой переменной исходя из выборки исходных значений. Вместо этого, значением функции является вероятность того, что данное исходное значение принадлежит к определенному классу. Для простоты, давайте предположим, что у нас есть только два класса (см. Множественная логистическая регрессия для задач с большим количеством классов) и вероятность, которую мы будем определять, вероятности того, что некоторое значение принадлежит классу "+". И конечно . Таким образом, результат логистической регрессии всегда находится в интервале [0, 1].

Основная идея логистической регрессии заключается в том, что пространство исходных значений может быть разделено линейной границей (т.е. прямой) на две соответствующих классам области. Итак, что же имеется ввиду под линейной границей? В случае двух измерений — это просто прямая линия без изгибов. В случае трех — плоскость, и так далее. Эта граница задается в зависимости от имеющихся исходных данных и обучающего алгоритма. Чтобы все работало, точки исходных данных должны разделяться линейной границей на две вышеупомянутых области. Если точки исходных данных удовлетворяют этому требованию, то их можно назвать линейно разделяемыми. Посмотрите на изображение.

Указанная разделяющая плоскость называется линейным дискриминантом, так как она является линейной с точки зрения своей функции, и позволяет модели производить разделение, дискриминацию точек на различные классы.

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

Но каким образом используется линейная граница в методе логистической регрессии для количественной оценки вероятности принадлежности точек данных к определенному классу?

Как происходит разделение

Во-первых, давайте попробуем понять геометрический подтекст «разделения» исходного пространства на две области. Возьмем для простоты (в отличие от показанного выше 3-мерного графика) две исходные переменные - и , тогда функция, соответствующая границе, примет вид:

Важно отметить, что и и являются исходными переменными, а выходная переменная не является частью исходного пространства в отличие от метода линейной регрессии.

Рассмотрим точку . Подставляя значения и в граничную функцию, получим результат . Теперь, в зависимости от положения следует рассмотреть три варианта:

• лежит в области, ограниченной точками класса "+". Тогда , будет положительной, находясь где-то в пределах (0,). С математической точки зрения, чем больше величина этого значения, тем больше расстояние между точкой и границей. А это означает большую вероятность того, что принадлежит классу "+". Следовательно, будет находиться в пределах (0,5, 1].
• лежит в области, ограниченной точками класса "-". Теперь, будет отрицательной, находясь в пределах (-, 0). Но, как и в случае с положительным значением, чем больше величина выходного значения по модулю, тем больше вероятность, что принадлежит классу "-", и находится в интервале [0, 0.5).
• лежит на самой границе. В этом случае, . Это означает, что модель действительно не может определить, принадлежит ли к классу "+" или к классу "-". И в результате, будет равняться 0,5.

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

Это значит, что необходимо еще одно действие, так как используемая нами граничная функция выдает значения от - до . Далее следует вычислить логарифм , что называется логарифмом отношения шансов. В математическом смысле, имеет пределы от 0 до , а — от - до .

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

• Шаг 1. Вычислить значение граничной функции (или, как вариант, функцию отношения шансов). Для простоты обозначим эту величину .
• Шаг 2. Вычислить отношение шансов: . (так как является логарифмом ).
• Шаг 3. Имея значение , вычислить с помощью простой зависимости.

Получив значение в шаге 1, можно объединить шаги 2 и 3:

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

Как обучается функция

Остался не отвеченным вопрос: «Каким образом обучается граничная функция ?» Математическая основа этого выходит за рамки статьи, но общая идея заключается в следующем:
Рассмотрим функцию , где — точка данных обучающей выборки. В простой форме можно описать так:

если является частью класса "+", (здесь — выходное значение, полученное из модели логистической регрессии). Если является частью класса "-", .

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

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

Логистическая регрессияалгоритм классификации машинного обучения, который используется для прогнозирования вероятности категориальной зависимой переменной В логистической регрессии зависимой переменной является двоичная переменная, которая содержит данные, закодированные как 1 (да, успех и т. Д.) Или 0 (нет, сбой и т. Д.). Другими словами, модель логистической регрессии предсказывает P (Y = 1) как функцию от X.

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

Помня вышеупомянутые предположения, давайте посмотрим на наш набор данных.

Набор данных происходит отUCI хранилище машинного обученияи это связано с прямыми маркетинговыми кампаниями (телефонными звонками) португальского банковского учреждения. Цель классификации - предсказать, будет ли клиент подписываться (1/0) на срочный депозит (переменная y). Набор данных можно загрузить сВот,

Набор данных предоставляет информацию о клиентах банка. Включает 41 188 записей и 21 поле.

1. возраст (числовой)
2. работа: тип работы (категория: «администратор», «рабочий», «предприниматель», «горничная», «менеджмент», «пенсионер», «работающий не по найму», «услуги», «студент», «техник») »,« Безработный »,« неизвестный »)
3. семейное положение: семейное положение (категориальное: «в разводе», «женат», «холост», «неизвестно»)
4. образование (категориальное: «базовый 4 года», «базовый 6 лет», «базовый 9 лет», «старшая школа», «неграмотный», «профессиональный курс», «университетский уровень», «неизвестный»)
5. по умолчанию: имеет кредит по умолчанию? (категориально: «нет», «да», «неизвестно»)
6. жилье: есть ли жилищный кредит? (категориально: «нет», «да», «неизвестно»)
7. кредит: есть личный кредит? (категориально: «нет», «да», «неизвестно»)
8. контакт: тип контактной связи (категориальный: «сотовый», «телефонный»)
9. месяц: последний контактный месяц года (категориальный: «январь», «февраль», «мар»,…, «ноябрь», «декабрь»)
10. day_of_week: последний контактный день недели (категориальный: «понедельник», «вторник», «ср», «чт», «пт»)
11. длительность: длительность последнего контакта в секундах (числовая). Важное примечание: этот атрибут сильно влияет на выходную цель (например, если длительность = 0, тогда y = 'нет'). Продолжительность неизвестна до того, как будет выполнен вызов, также, после окончания вызова, y, очевидно, известен. Таким образом, этот вход должен быть включен только для целей сравнения и должен быть отброшен, если намерение состоит в том, чтобы иметь реалистичную прогнозную модель
12. кампания: количество контактов, выполненных во время этой кампании и для этого клиента (числовое, включая последний контакт)
13. pdays: число дней, прошедших после последнего контакта с клиентом из предыдущей кампании (числовое значение; 999 означает, что с клиентом ранее не связывались)
14. previous: количество контактов, выполненных до этой кампании и для этого клиента (числовое)
15. poutcome: результат предыдущей маркетинговой кампании (категориальный: «провал», «несуществующий», «успех»)
16. emp.var.rate: коэффициент изменения занятости - (числовой)
17. cons.price.idx: индекс потребительских цен - (числовой)
18. cons.conf.idx: индекс доверия потребителей - (числовой)
19. euribor3m: курс euribor 3 месяца - (числовой)
20. nr.employed: количество сотрудников - (числовое)

Прогнозируемая переменная (желаемая цель):

y - подписал ли клиент срочный депозит? (двоичный код: «1» означает «Да», «0» означает «Нет»)

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

Давайте сгруппируем «basic.4y», «basic.9y» и «basic.6y» вместе и назовем их «basic».

После группировки это столбцы:

процент без подписки составляет 88,73458288821988

процент подписки 11.265417111780131

Наши классы несбалансированны, и отношение количества случаев отсутствия подписки к экземплярам подписки составляет 89:11. Прежде чем идти вперед, чтобы сбалансировать классы, давайте сделаем еще несколько исследований.

наблюдения:

• Средний возраст клиентов, которые купили срочный депозит, выше, чем у клиентов, которые этого не сделали.
• Pdays (дни с момента последнего контакта с клиентом) понятно ниже для клиентов, которые его купили. Чем ниже число дней, тем лучше память о последнем звонке и, следовательно, больше шансов на продажу.
• Удивительно, но кампании (количество контактов или звонков, сделанных во время текущей кампании) ниже для клиентов, которые купили срочный депозит

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

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

Семейное положение не кажется сильным предиктором для переменной результата.

Образование кажется хорошим предиктором переменной результата.

День недели не может быть хорошим предсказателем результата.

Месяц может быть хорошим предиктором переменной результата.

Большинство клиентов банка в этом наборе данных находятся в возрасте от 30 до 40 лет.

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

То есть переменные только с двумя значениями, ноль и один.

Наши окончательные столбцы данных будут:

Создав наши учебные данные, я проведу пробную подписку без подписки, используяSMOTE алгоритмСинтетический метод пересчёта меньшинств. На высоком уровне SMOTE:

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

Мы собираемся реализоватьSMOTE в Python,

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

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

RFE помог нам выбрать следующие функции: «euribor3m», «job_blue-collar», «job_housemaid», «marital_unknown», «education_illiterate», «default_no», «default_unknown», «contact_cellular», «contact_telephone», «month_apr» »,« Month_aug »,« month_dec »,« month_jul »,« month_jun »,« month_mar »,« month_may »,« month_nov »,« month_oct »,« poutcome_failure »,« poutcome_success ».

Значения p для большинства переменных меньше 0,05, за исключением четырех переменных, поэтому мы их удалим.

Прогнозирование результатов тестового набора и расчет точности

Точность классификатора логистической регрессии на тестовом наборе: 0,74

[[6124 1542]

[2505 5170]]

Результат говорит нам, что у нас есть6124 + 5170правильные прогнозы и2505 + 1542неверные прогнозы.

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

Напомним, отношение tp / (tp + fn), где tp - количество истинных положительных результатов, а fn - число ложных отрицательных. Напомним, интуитивно способность классификатора найти все положительные образцы.

Оценка F-бета может быть интерпретирована как взвешенное среднее значение гармоники точности и отзыва, где оценка F-бета достигает своего лучшего значения в 1 и худшего результата в 0.

Оценка F-бета взвешивает отзыв больше, чем точность, с коэффициентом бета. бета = 1,0 означает, что отзыв и точность одинаково важны.

Поддержка - это число вхождений каждого класса в y_test.

интерпретация: Из всего набора тестов 74% продвигаемого срочного депозита составляли срочные депозиты, которые понравились клиентам. Из всего набора тестов 74% предпочтительных срочных депозитов клиента были продвинуты.

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

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

Logistic regression is a method that we use to fit a regression model when the response variable is binary.

This tutorial explains how to perform logistic regression in Excel.

Example: Logistic Regression in Excel

Use the following steps to perform logistic regression in Excel for a dataset that shows whether or not college basketball players got drafted into the NBA (draft: 0 = no, 1 = yes) based on their average points, rebounds, and assists in the previous season.

Step 1: Input the data.

First, input the following data:

Step 2: Enter cells for regression coefficients.

Since we have three explanatory variables in the model (pts, rebs, ast), we will create cells for three regression coefficients plus one for the intercept in the model. We will set the values for each of these to 0.001, but we will optimize for them later.

Next, we will have to create a few new columns that we will use to optimize for these regression coefficients including the logit, e logit , probability, and log likelihood.

Step 3: Create values for the logit.

Next, we will create the logit column by using the the following formula:

Step 4: Create values for e logit .

Next, we will create values for e logit by using the following formula:

Step 5: Create values for probability.

Next, we will create values for probability by using the following formula:

Step 6: Create values for log likelihood.

Next, we will create values for log likelihood by using the following formula:

Log likelihood = LN(Probability)

Step 7: Find the sum of the log likelihoods.

Lastly, we will find the sum of the log likelihoods, which is the number we will attempt to maximize to solve for the regression coefficients.

Step 8: Use the Solver to solve for the regression coefficients.

• Click File.
• Click Options.
• Click Add-Ins.
• Click Solver Add-In, then click Go.
• In the new window that pops up, check the box next to Solver Add-In, then click Go.

Once the Solver is installed, go to the Analysis group on the Data tab and click Solver. Enter the following information:

• Set Objective: Choose cell H14 that contains the sum of the log likelihoods.
• By Changing Variable Cells: Choose the cell range B15:B18 that contains the regression coefficients.
• Make Unconstrained Variables Non-Negative: Uncheck this box.
• Select a Solving Method: Choose GRG Nonlinear.

Then click Solve.

The Solver automatically calculates the regression coefficient estimates:

Now these regression coefficients can be used to find the probability that draft = 1.

For example, suppose a player averages 14 points per game, 4 rebounds per game, and 5 assists per game. The probability that this player will get drafted into the NBA can be calculated as:

Since this probability is greater than 0.5, we predict that this player would get drafted into the NBA.

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

  
• Keylight out of memory after effects что это
  
• Как установить gts 450 драйвер
  
• Программа icarefone for whatsapp transfer не видит iphone
  
• Flow программа для андроид
  
• Лучшие программы для здоровья андроид