Case 4.2 Delinquent Accounts at GE Capital

GE Capital is in the business of providing loans to small to medium companies. However, GE Capitals top management believes the company is losing significant amounts of cash from delinquent accounts, and they have assigned John Barlow, a senior financial manager, to analyze the problem. After some research, John finds that GE Capital currently has 50,000 accounts. Of these, 40,000 are paid up (the company refers to these as 0-due), 4000 are one month overdue (1-due), 4000 are two months overdue (2-due), and 2000 are 3 months overdue (3-due). After an account is more than three months overdue, it is written off as bad debt. Each month an account is overdue, GE Capital has three contact options: it can phone the company; it can send a letter to the company; or it can make no contact. Although phone calls and letters are fairly routine, they do take some time. It takes approximately 20 minutes for a letter and 40 minutes for a phone call. (Much of this time is for the contact person to assemble information on the account.) Each month, 5000 hours are available from the employees who make the contacts. GE Capitals policy is to never contact companies with 0-due accounts. John has discovered that the average amount of a monthly payment is $10,000. Therefore, for example, if a 2-due account remains 2-due by next month, this means that one month of payment, $10,000, has been received. However, if this 2-due account becomes 0-due by next month, this means that 3 months of payments, $30,000, have been received. However, if this account becomes 3-due by next month, no payment is received. On the basis of thousands of accounts, delinquency movement matrices (DMMs) have been estimated. These are listed in the file C04_02.xlsx. For example, the top-left 60% entry in the first DMM indicates that 60% of all 1-due accounts that receive a letter become 0-due. The 10% and 30% values in this same row indicate that 10% of all 1-due accounts remain 1-due after receiving a letter, and 30% of all 1-due accounts become 2-due after receiving a letter. The bottom row of each DMM simply means that bad debt remains bad debt; it is never recovered. Johns goal is to allocate his contact employees over the next four months to maximize the expected collection revenue received during this period. As Johns Excel-savvy junior assistant, you have been asked to do the following:

1. You should develop an Excel optimization model to find the optimal strategy. This strategy should specify the number of each type of account (0-due, 1-due, 2-due, 3- due) that should receive each contact type (letter, phone call, no contact) in each month. You should also describe the optimal strategy in a brief memo, and this memo should mention how much more revenue can be obtained with this optimal strategy than from a strategy of no contacts at all.

GM Capital
DMM if account receives a letter
From\To	0-due	1-due	2-due	3-due	Bad debt
1-due	60%	10%	30%	0%	0%
2-due	15%	15%	40%	30%	0%
3-due	0%	0%	20%	60%	20%
Bad debt	0%	0%	0%	0%	100%
DMM if account receives a phone call
From\To	0-due	1-due	2-due	3-due	Bad debt
1-due	70%	20%	10%	0%	0%
2-due	30%	25%	30%	15%	0%
3-due	0%	0%	50%	40%	10%
Bad debt	0%	0%	0%	0%	100%
DMM for do-nothing action
From\To	0-due	1-due	2-due	3-due	Bad debt
0-due	90%	10%	0%	0%	0%
1-due	20%	25%	55%	0%	0%
2-due	10%	10%	20%	60%	0%
3-due	0%	0%	10%	40%	50%
Bad debt	0%	0%	0%	0%	100%