Use snesitivity report from Excel solver. Showing steps in excel.

4. Adirondack Savings Bank (ASB) has $1 million in new funds that must be allocated to home loans, personal loans, and automobile loans. The annual rates of return for the three types of loans are 7% for home loans, 12% for personal loans, and 9% for automobile loans. The bank's planning committee has decided that at least 40% of the new funds must be allocated to home loans. In addition, the planning committee has specified that the amount allocated to personal loans cannot exceed 60% of the amount allocated to automobile loans. a. How much should be allocated to each type of loan? b. If the interest rate on home loans increased to 9%, would the amount allocated to each type of loan change? c. Suppose the total amount of new funds available was increased by $10,000. What effect would this have on the total annual return? d. Assume that ASB has the original $1 million in new funds available. Interpret the dual price for the constraint corresponding to the requirement at least 40% of the new funds must be allocated to home loans. e. Assume that ASB has the original $1 million in new funds available and that the planning committee has agreed to relax the requirement at least 40% of the new funds must be allocated to home loans by 1%. How much would the annual return change