ABC needs a rolling-horizon planning model for determining the optimal means of financing successive multi-year research and development (R&D) portfolios (involving internal projects, joint ventures, etc.). Each year, ABC commits to funding a new (five-year) R&D portfolio by making a combination of cash set aside and investments to fully fund that portfolio over the years involved.

Required Decision Support Assistance

ABC desires a working model (developed in Excel) for ongoing use in its annual budgeting cycle. Also, ABC expects the model to be used to develop an optimal solution/plan for the 2023 R&D investment portfolio (for years 2023 through 2027), and also expects the model to be validated before use.

Decision Scenario Details 1. Typically, ABC is planning its R&D portfolio five years into the future and, as of now, planning is underway for the time horizon of 2023 through 2027.

2. The total dollar amount for each five-year R&D portfolio is usually about $5M.

3. optimal means requires determining what parts of the overall dollar amount needing to be set aside to fund successive years new R&D projects should be, from year to year, either held as cash or invested in two or more of the following four investment alternatives (with expected details as of the end of 2022 shown):

The A money market fund, with an interest rate of 0.75% per year;

Intermediate-term B bonds, with $5,000 face value, a 78% discount rate, a 3% coupon rate, and 3-years maturity;

Short-term C bonds, also of $5,000 face value, with a 92% discount rate, a 5% coupon rate, and 1-year maturity; and

Short-term D bonds, also of $5,000 face value, with an 89% discount rate, a 4% coupon rate, and 2-years maturity. [NOTE: The four investment alternatives have been utilized over multiple years, are considered reliable, and are the only alternatives ABC wishes to consider for use. ABCs conservative financial practices require that money be invested in at least two investments at any point in time over the five-year planning horizon.]

4. ABCs CFO predicts that all details of the four investment alternatives will hold steady over the next five years, except that the pricing of all three bonds will change yearly, increasing each year by 5% of the difference between 100% and their respective discount rates [e.g., for 2024 the discount rate for B bonds will be 78%+(5%)(100%-78%)=79.1%]. 5. The R&D portfolio for 2023-2027 will require expenditures as follow:

2023: $400,000

2024: $900,000

2025: $1,700,000

2026: $1,400,000

2027: $600,000

The cash set aside and investments needed to fully fund all these expenditures will all occur as the 2022 year ends that is, at the start of 2023. Page 2 of 2 6. Assume the interest earned on, and proceeds from, all investments are available for immediate re-investment (for example, if Investment C is made at the start of 2023 the proceeds and interest will be available for re-investment at the start of 2024). 7. ABCs financial management policies call for all expenditures to be treated as if occurring at the beginnings of the associated years.

Solution to include variables, constraints and excel solver solution