4.2 DELINQUENT Accouurs AT GE CAPITAL E Capital is in the business of providing loans to small to medium companies. However, GE Capital's top management believes the company is losing signicant amounts of cash from delinquent accounts, and they have assigned john Barlow, a senior nancial manager, to analyze the problem. After some research,john nds 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 (I -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 connct options: it can phone the com- pany; 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 min- utes 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 $ | 0,000. Therefore, for example, if a 2-due account remains 2-due by next month. this means that one month of payment. $|0,000, has been received. However, if this 2-due ter, and 30% of all l-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 mondas to maximize the expected collection revenue received during this period. As John's Excel-sawy junior assistant. you have been asked to do the following: I. You should develop an Excel optimization model to nd the optimal strategy. This strategy should specify the number of each type of ac count (O-due, l-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 strat- egy than from a strategy of no contacts at all. 2. John wonders whether it would be worth in- creasing 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 account becomes 0-due by next month, this means that 3 months of payments, $30,000, have been re- ceived. However, if this account becomes 3-due by next month, no payment is received. On the basis of thousands of accounts, delin- quency movement matrices (DMMs) have been estimated. These are listed in the le C04_02.xlsx. For example, the topleft 60% entry in the rst DMM indicates that 60% of all l-due accounts that receive a letter become 0due. The IO% and 30% values in this same row indicate that |0% of all [-due accounts remain l-due after receiving a let- 2 l 6 Chapter 4 Linear Programming Models Ill 0 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 specied exactly how he wants you to analyze this, but he is again hoping that you can provide some useful insights