PLEASE solve this problem using EXCEL and insert screenshots step by step for solver part.

1. The Bank of Elkins is allocating a maximum of $200,000 for personal and car loans during the next month. The bank charges 14% for personal loans and 12% for car loans. Both types of loans are repaid at the end of a 1- year period. Experience shows that about 3% of personal loans and 2% of car loans are never repaid, both as principal and interest. The bank usually allocates at least twice as much to car loans as to personal loans. Determine the optimal allocation of funds between the two loans and the net rate of return on all the loans. b. Determine the optimality range for the ratio of car to personal interest rates that will keep solution in (a) unchanged. Suppose that the percentages of unrepaid personal and car loans are changed to 4% and 3%, respectively; use the result in (b) to show if this change will affect the optimum solution in (a)