1. You should develop an Excel optimization model to find the optimal strategy. This strategy should specify the number of each type of ac-count (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.

2. John wonders whether it would be worth increasing the contact hours available each month, either by hiring new contact employees or by reallocating current employees. Before he makes detailed decisions on this, he wants you to discover how much these extra hours would be worth. He does not provide you with explicit directions for this part of the assignment, but he expects you to provide useful insights.

3. Still wondering about the labor hour issue, John suspects that the current times required for the contacts, 20 minutes for a letter and 40 minutes for a phone call, are excessive and that they could be decreased by making data about ac-counts available to the contact people in a more timely fashion. In fact, he even suspects that by doing this, some of the currently allocated 5000 hours per month could be allocated to other more productive activities. Again, he has not specified exactly how he wants you to analyze this, but he is again hoping that you can provide some useful insights.

GE Capital is in the business of providing loans to small to medium companies. However, GE Capital’s 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 ac-count.) Each month, 5000 hours are available from the employees who make the contacts. GE Capital’s 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.

John’s goal is to allocate his contact employees over the next four months to maximize the expected collection revenue received during this period. As John’s Excel-savvy junior assistant, you have been asked to do the following.