Machine Learning in Ms. Excel

Explain credit risk in banking and how a credit officer uses scoring models to estimate the probability of default for mortgages, credit cards, and other loans.

Explore the data overview for client loans, distinguishing continuous and categorical variables, cleaning data, and preparing it for regression analyses and machine learning algorithms.

Learn how to combine data sets from different sources using Excel's look up (vlookup) function to achieve exact matches by client I.D., enabling accurate arrears indicators in banking data.

Learn to create and encode dummy variables in Excel for credit risk models, including gender and location (urban/rural), enabling regression analyses for probability of default.

This lecture explains how to create dummy variables in Excel to quantify credit risk and estimate default probability for regression analyses, including gender and urban area encoding using if formulas.

Demonstrate creating relationship status dummy variables in Excel, encoding married, single, divorced, and widowed as zeros and ones for machine learning and credit risk analysis.

Learn to code the dependent variable for loan default by selecting a days-in-arrears threshold and a binary indicator, where zero denotes default and one denotes non-default.

Compute the weight of evidence for gender in credit risk using Excel pivot tables, comparing male and female good and bad borrowers to enhance modeling.

Compute weight of evidence for urban versus rural residence using good and bad borrower percentages, compare their predictive power, and apply it to credit scoring and modeling.

Apply weight of evidence to educational level categories, combining elementary, high school, other schooling, and university degrees using pivot tables to prepare dummy variables for regression and machine learning.

Compute the weight of evidence for work sector and residence, sort by impact, and visualize as a line graph to select reference variables for regression analyses and machine learning models.

Apply weight of evidence to relationship status to compare loan repayment patterns, noting married individuals show higher weight, guiding regression analyses with married as the reference category.

Construct the weight of evidence for loan grades, normalize to column totals, and use a, b, and c as regression inputs with d or lower as reference.

Code age in years into interval indicators in Excel, using a reference group (18 to 27) and IF formulas to create 0/1 dummies for regression analyses.

Learn to code loan maturity into a binary variable in Excel by converting mixed text and numbers, using text-to-columns, and applying an if formula for 1–12 months.

Analyze weight of evidence for age intervals from 18 to 70, compute WoE per group, fix data issues, and use four age brackets as regression dummies.

calculate weight of evidence for continuous maturity data by grouping months into year-based categories, compare good and bad borrower proportions, and select reference variables for regression.

Select categorical and continuous variables using weight of evidence to prepare data for regression analyses in Excel. Then apply logistic regression with the default indicator as the dependent variable.

Code the grade categories as dummy variables for regression analyses, using grades as the reference and 1/0 indicators to compare repayment history for A/B/C versus E/F.

correct the regression setup by selecting the three-year maturity as reference, removing the six-year maturity from training dataset, and using veto cup functions to align continuous-variable data with the model.

Learn how to run logistic regression in Excel, select input ranges and the dependent variable, adjust iterations, and interpret the output and validation results.

Explore how real statistics outputs from logistic regression inform coefficient interpretation, significance, and scoring for credit risk, including variable selection, score card creation, and model validation.

Learn how to apply logistic regression coefficients from the train set to a test dataset, compute probabilities, build a confusion matrix, and evaluate model accuracy.