Use excel

24. Morton Financial must decide on the percentage of available funds to commit to each of two investments, referred to as A and B, over the next four periods. The following table shows the amount of new funds available for each of the four periods, as well as the cash expenditure required for each investment (negative values) or the cash income from the investment (positive values). The data shown (in thousands of dollars) reflect the amount of expenditure or income if 100% of the funds available in any period are invested in either A or B. For example, if Morton decides to invest 100% of the funds available in any period in investment A, it will incur cash expenditures of $1000 in period 1, $800 in period 2, $200 in period 3, and income of $200 in period 4. Note, however, if Morton made the decision to invest 80% in investment A, the cash expenditures or income would be 80% of the values shown. Investment Period Period A B 1 2 3 4 New Investment Funds Available 1500 400 500 100 - 1000 -800 -200 200 - 800 - 500 -300 300 The amount of funds available in any period is the sum of the new investment funds for the period, the new loan funds, the savings from the previous period, the cash income from investment A, and the cash income from investment B. The funds available in any period can be used to pay the loan and interest from the previous period, placed in savings, used to pay the cash expenditures for investment A, or used to pay the cash expenditures for investment B. L(t) Assume an interest rate of 10% per period for savings and an interest rate of 18% per period on borrowed funds. Let S(t) = the savings for period t 1 = the new loan funds for period t Then, in any period t, the savings income from the previous period is 1.1S(t 1), and the loan and interest expenditure from the previous period is 1.18L(t 1). At the end of period 4, investment A is expected to have a cash value of $3200 (assum- ing a 100% investment in A), and investment B is expected to have a cash value of $2500 (assuming a 100% investment in B). Additional income and expenses at the end of period 4 will be income from savings in period 4 less the repayment of the period 4 loan plus interest. Suppose that the decision variables are defined as x1 = the proportion of investment A undertaken x2 = the proportion of investment B undertaken For example, if x = 0.5, $500 would be invested in investment A during the first period, and all remaining cash flows and ending investment A values would be multiplied by 0.5. The same holds for investment B. The model must include constraints xi = 1 and x2 = 1 to make sure that no more than 100% of the investments can be undertaken. If no more than $200 can be borrowed in any period, determine the proportions of investments A and B and the amount of savings and borrowing in each period that will maximize the cash value for the firm at the end of the four periods. 24. Morton Financial must decide on the percentage of available funds to commit to each of two investments, referred to as A and B, over the next four periods. The following table shows the amount of new funds available for each of the four periods, as well as the cash expenditure required for each investment (negative values) or the cash income from the investment (positive values). The data shown (in thousands of dollars) reflect the amount of expenditure or income if 100% of the funds available in any period are invested in either A or B. For example, if Morton decides to invest 100% of the funds available in any period in investment A, it will incur cash expenditures of $1000 in period 1, $800 in period 2, $200 in period 3, and income of $200 in period 4. Note, however, if Morton made the decision to invest 80% in investment A, the cash expenditures or income would be 80% of the values shown. Investment Period Period A B 1 2 3 4 New Investment Funds Available 1500 400 500 100 - 1000 -800 -200 200 - 800 - 500 -300 300 The amount of funds available in any period is the sum of the new investment funds for the period, the new loan funds, the savings from the previous period, the cash income from investment A, and the cash income from investment B. The funds available in any period can be used to pay the loan and interest from the previous period, placed in savings, used to pay the cash expenditures for investment A, or used to pay the cash expenditures for investment B. L(t) Assume an interest rate of 10% per period for savings and an interest rate of 18% per period on borrowed funds. Let S(t) = the savings for period t 1 = the new loan funds for period t Then, in any period t, the savings income from the previous period is 1.1S(t 1), and the loan and interest expenditure from the previous period is 1.18L(t 1). At the end of period 4, investment A is expected to have a cash value of $3200 (assum- ing a 100% investment in A), and investment B is expected to have a cash value of $2500 (assuming a 100% investment in B). Additional income and expenses at the end of period 4 will be income from savings in period 4 less the repayment of the period 4 loan plus interest. Suppose that the decision variables are defined as x1 = the proportion of investment A undertaken x2 = the proportion of investment B undertaken For example, if x = 0.5, $500 would be invested in investment A during the first period, and all remaining cash flows and ending investment A values would be multiplied by 0.5. The same holds for investment B. The model must include constraints xi = 1 and x2 = 1 to make sure that no more than 100% of the investments can be undertaken. If no more than $200 can be borrowed in any period, determine the proportions of investments A and B and the amount of savings and borrowing in each period that will maximize the cash value for the firm at the end of the four periods