Question #1 is already solved and the solution is at the end of this page. Please respond #2 and #3

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: 1. You should develop an Excel optimization model to find the optimal strategy. This strategy should specify the number of each type of account (O-due, 1-due, 2-due, 3-due) that should receive each contact type (letter, phone call, no contact) in each month. What is an optimal strategy? how much more revenue can be obtained with this optimal strategy than from a strategy of no contacts at all? 2. Would be worth increasing the contact hours available each month, either by hiring new contact employees or by reallocating current employees? How much these extra hours would be worth? 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 accounts 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. Is that insight correct? 1 GM Capital 3 DMM if account receives a letter From To 0-due 1-due 2-due 3-due Bad debt 5 1-due 6 2-due 7 3-due 8 Bad debt 60% 15% 0% 0% 10% 15% 0% 0% 0% 0% 30% 60% 0% 30% 40% 40% 20% 20% 100% 10 DMM if account receives a phone cal 11 FromTo 0-de 1-due 2-due 3-due Bad debt 12 1-due 70% 30% 0% 20% 25% 10% 30% 50% 0% 13 2-due 15% 40% 0% 14 3-due 15 Bad debt 16 17 DMM for do-nothing action 18 From To 0-due 1-due 2-due 3-due Bad debt 19 0-due 20 1-due 21 2-due 22 3-due 23 Bad debt 24 0% 0% 10% 100% 0% 0% 0% o 90% 20% 10% 0% 10% 25% 10% 0% 0% 55% 20% 10% 60% 4096 0% 0% 0% 50% 100% From the solver solution we obtain the following solution No of Accounts Send letter Phone call Do nothing 0-due 40 1-due 4 4 2-due 4 4 3-due 2 Bad debt o Total 4 4 Maximum Total payment received is $1194 million 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: 1. You should develop an Excel optimization model to find the optimal strategy. This strategy should specify the number of each type of account (O-due, 1-due, 2-due, 3-due) that should receive each contact type (letter, phone call, no contact) in each month. What is an optimal strategy? how much more revenue can be obtained with this optimal strategy than from a strategy of no contacts at all? 2. Would be worth increasing the contact hours available each month, either by hiring new contact employees or by reallocating current employees? How much these extra hours would be worth? 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 accounts 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. Is that insight correct? 1 GM Capital 3 DMM if account receives a letter From To 0-due 1-due 2-due 3-due Bad debt 5 1-due 6 2-due 7 3-due 8 Bad debt 60% 15% 0% 0% 10% 15% 0% 0% 0% 0% 30% 60% 0% 30% 40% 40% 20% 20% 100% 10 DMM if account receives a phone cal 11 FromTo 0-de 1-due 2-due 3-due Bad debt 12 1-due 70% 30% 0% 20% 25% 10% 30% 50% 0% 13 2-due 15% 40% 0% 14 3-due 15 Bad debt 16 17 DMM for do-nothing action 18 From To 0-due 1-due 2-due 3-due Bad debt 19 0-due 20 1-due 21 2-due 22 3-due 23 Bad debt 24 0% 0% 10% 100% 0% 0% 0% o 90% 20% 10% 0% 10% 25% 10% 0% 0% 55% 20% 10% 60% 4096 0% 0% 0% 50% 100% From the solver solution we obtain the following solution No of Accounts Send letter Phone call Do nothing 0-due 40 1-due 4 4 2-due 4 4 3-due 2 Bad debt o Total 4 4 Maximum Total payment received is $1194 million