Question

If I could get some help with this question in excel that would be awesome

4. Optimizing Cash Flows (20 points) It is now January 1 of 2021 and the following five projects (see below) are competing for a company's investment dollars Cash Flow Per Dollar Invested Proiect beg of year 2021 2022 2023 2024 upper limit 1.00 +0.30 +1.00 1.00 +1.10 1.00 1.00 +0.30 +1.00 $500,000 1.00 +1.40 $750,000 +1.75 None $500,000 None The cash received from any of these projects may be reinvested in others available at the time. In addition, the company can obtain 6 percent via short-term savings accounts for any money not invested in a given year. Specifically, each dollar put in the savings account at the bank at the beginning of a year will give $1.06 at the beginning of the next year (in time for immediate reinvestment if desired). For example, if you put $100 in the savings account at the bank at the beginning of 2021, you would receive $106 at the beginning of year 2022. Of course, you also have opportunities to put money in the savings account at the bank at the beginning of 2022 and 2023 The positive and negative numbers indicate the cash flows associated with that project The -1.00 in each investment column indicates the year in which you are allowed to invest in that project. For example, Project E does not begin until 2023, so you cannot invest in it in 2021 or 2022 The company has $1 million to invest and wishes to maximize the amount of money it can accumulate from its investments by January 1, 2024. (a) State the decisions to be made, the objective and the constraints in words using <-, >- or- in the constraints. (Handwrite answer, 5 points) (b) Define your decisions algebraically as decision variables. Then, using your objective and constraints in part (a), formulate the problem as an algebraic linear optimization model. (Handwrite answer, 5 points) points) answer via Blackboard. Handwrite your optimal solution, 5 points) (c) Construct an EXCEL model for this problem (Submit answer via Blackboard, 5 (d) Using your EXCEL model and Solver, determine an optimal solution. (Submit

Answer 1