Supposed to use excel to get the answers to this. Don't know what equations to use or even how to set it up to be able to get the answers.

24. A financial manager is managing a cash fund. His investment alternatives available are various certificates of deposit, also known as CDs, as listed in the following table Yield 0.5% 1.75% , 2.3% Investment 1-month CD 3-month CD 6-month CD Availability Beginning of each month Beginning of Months 1, 2, 3,4 Beginning of Month 1 However, he also must ensure that sufficient funds are available to pay company expen- ditures over the next six months. The following table lists the net expenditures (in thou- sands of dollars) that the manager is obligated to cover (cash amounts in parenthesis indicate a net inflow of cash rather than outflow). Month Net Expenditures ($1,000s) $45 2 $25 4 $43 (S15) 6 The cash on hand to invest at the start of month 1 is $200,000 and the minimum cash required to be available at the end of month 6 is $100,000. Develop and solve a linear program that will recommend how to invest to maximize the amount of interest income accrued over the next six months while satisfying all financial commitments (Hint: Investment time starts at the beginning of the month and returns at the end of the month. For example, money invested in a 1-month CD in month 1 will be invested at the beginning of month 1 and returned with interest at the end of month 1. Likewise, money invested in a 3-month CD at the start of month 1 will be returned with interest Assume that Month 1 to Month 6 is referring to the Current Year starting in January. I would like you to take the problem above and make the following alterations 1) Run this analysis for a period of 12 months with the expenditures for July to December as follows: Month July August September October November December Net Expenditures $14 S32 ($1,000) $25 $28 (S 12) $50 2) You can invest in any of the Investment Instruments during any month of the year. For example, the original problem states you can only invest in 3-Month CDs in Months 1 to 4. I am giving you the option to invest in 3 Month CDs in any month. Note: This is subject to one more restriction listed below (item 6) 3) Add one additional investment, 2 Month Government Bonds which pay 3% at maturity. At no time during the year can you have more than $50,000 invested in 2 Month Government Bonds 4) The minimum cash that must be available at year end needs to be $80,000 5) At year end, take into consideration in computing the Total Return the amounts already earned on current investments (but will be received in the future). For example, if you invested 10,000 in 3 Month CD's in November, at year end your Total Return (the value you are Maximizing) needs to include two thirds of the return that will be earned on this investment. 6) At year end, make sure that your investments do not tie up any of your money beyond the end of February. We want everything to be liquid on March 1st of next year. 24. A financial manager is managing a cash fund. His investment alternatives available are various certificates of deposit, also known as CDs, as listed in the following table Yield 0.5% 1.75% , 2.3% Investment 1-month CD 3-month CD 6-month CD Availability Beginning of each month Beginning of Months 1, 2, 3,4 Beginning of Month 1 However, he also must ensure that sufficient funds are available to pay company expen- ditures over the next six months. The following table lists the net expenditures (in thou- sands of dollars) that the manager is obligated to cover (cash amounts in parenthesis indicate a net inflow of cash rather than outflow). Month Net Expenditures ($1,000s) $45 2 $25 4 $43 (S15) 6 The cash on hand to invest at the start of month 1 is $200,000 and the minimum cash required to be available at the end of month 6 is $100,000. Develop and solve a linear program that will recommend how to invest to maximize the amount of interest income accrued over the next six months while satisfying all financial commitments (Hint: Investment time starts at the beginning of the month and returns at the end of the month. For example, money invested in a 1-month CD in month 1 will be invested at the beginning of month 1 and returned with interest at the end of month 1. Likewise, money invested in a 3-month CD at the start of month 1 will be returned with interest Assume that Month 1 to Month 6 is referring to the Current Year starting in January. I would like you to take the problem above and make the following alterations 1) Run this analysis for a period of 12 months with the expenditures for July to December as follows: Month July August September October November December Net Expenditures $14 S32 ($1,000) $25 $28 (S 12) $50 2) You can invest in any of the Investment Instruments during any month of the year. For example, the original problem states you can only invest in 3-Month CDs in Months 1 to 4. I am giving you the option to invest in 3 Month CDs in any month. Note: This is subject to one more restriction listed below (item 6) 3) Add one additional investment, 2 Month Government Bonds which pay 3% at maturity. At no time during the year can you have more than $50,000 invested in 2 Month Government Bonds 4) The minimum cash that must be available at year end needs to be $80,000 5) At year end, take into consideration in computing the Total Return the amounts already earned on current investments (but will be received in the future). For example, if you invested 10,000 in 3 Month CD's in November, at year end your Total Return (the value you are Maximizing) needs to include two thirds of the return that will be earned on this investment. 6) At year end, make sure that your investments do not tie up any of your money beyond the end of February. We want everything to be liquid on March 1st of next year