Logistic Regression: Company Bankruptcy

1. Logistic Regression: Company Bankruptcy

In the Excel file named CompanyBankruptcy.xlsx (downloadable on eLearning from where you access this exam), we collected data on 74 companies that have either gone bankrupt or haven’t. The data set also contains five frequently quoted accounting ratios for each company:

1) WC/TA – working capital to total assets 2) RE/TA – retained earnings to total assets3) EBIT/TA – earnings before interest and taxes to total assets 4) MVE/TA – market value of equity to total assets5) S/TA – sales to total assetsPlease analyze this data set by answering each of the following questions. Write your answers to these questions on the Answer Sheet for this problem (provided separately) and attach the appropriate documentation to the Answer Sheet.

a) Exploratory analysis: (1) Create a pivot table that shows the average of each ratio, broken down by the Yes/No values in the variable “Bankrupt” (Column G in the data file).(2) Use XLMiner to create five box plots with Y-axis showing each of the five ratios and X-axis showing Yes/No of the variable “Bankrupt” (Column G in the data file). (3) Based on these information, does any ratio seem to have an effect on whether a company goes bankrupt? If so, name them?

b) Partition the data set.

c) Use logistics regression to classify companies as bankrupt or not, using all five of the accounting ratios. (1) Report the coefficient for each accounting ratio. (2) Report the Confusion Matrix and Error Report on validation set. (3) Report the lift chart on validation set. (4) Does this model do a good job of classifying? Are any of the ratios insignificant in the model?

d) Experiment with logistics regression that use only two of the accounting ratios. Which pair classifies about as well as in Part c), but with both ratios significant? For that pair,(1) Report the coefficient for each accounting ratio. (2) Report the Confusion Matrix and Error Report on validation set. (3) Report the lift chart on validation set.