The Northampton County Development Corporation (NCDC) plans to take part in three possible development projects to...

     

Transcribed Image Text:

The Northampton County Development Corporation (NCDC) plans to take part in three possible development projects to be launched in 2022. Each project requires a significant investment over the next few years, and then would be sold upon completion. The estimated cash flows (selling prices and costs, in millions of dollars) associated with each project are shown in the table below. Project B Project C Year 2 3 4 5 6 Project A 4 -6 -6 24 0 0 -8 -8 -4 -4 30 0 -10 -7 -7 -5 -3 44 NCDC has $10 million cash reserves available at the end of 2021; they also expect to receive $6 million per year from other currently ongoing projects by the end of years 2022 through 2027. The corresponding cash amounts will be available for investments in the following years for projects A, B, and C. We assume that NCDC can participate in each project according to a continuous decision scale (ranging from 0% to 100% participation). If NCDC participates at less than 100% in a certain project, then all cash flow needs associated with that project are reduced proportionally. NCDC plans to finance all project participation from its own resources, without receiving bank loans. NCDC policy requires ending each year with a cash balance of at least $1 million. Your task is to develop a spreadsheet model to analyze and solve the problem. 2.1 What are the decision options? What constraints need to be considered? What should the objective be? 2.2 For testing purposes, assume that NCDC finances Project A at 100% and Project C at 50%. Calculate by hand what the ending cash position would be at the end of year 2022 and year 2023. 2.3 Build a spreadsheet model using the appropriate model template, first for years 2022 and 2023, and then test the model, by entering another tentative data set of your choice. 2.4 Develop and solve the full-scale model. Summarize your recommendation for NCDC in a paragraph. The Northampton County Development Corporation (NCDC) plans to take part in three possible development projects to be launched in 2022. Each project requires a significant investment over the next few years, and then would be sold upon completion. The estimated cash flows (selling prices and costs, in millions of dollars) associated with each project are shown in the table below. Project B Project C Year 2 3 4 5 6 Project A 4 -6 -6 24 0 0 -8 -8 -4 -4 30 0 -10 -7 -7 -5 -3 44 NCDC has $10 million cash reserves available at the end of 2021; they also expect to receive $6 million per year from other currently ongoing projects by the end of years 2022 through 2027. The corresponding cash amounts will be available for investments in the following years for projects A, B, and C. We assume that NCDC can participate in each project according to a continuous decision scale (ranging from 0% to 100% participation). If NCDC participates at less than 100% in a certain project, then all cash flow needs associated with that project are reduced proportionally. NCDC plans to finance all project participation from its own resources, without receiving bank loans. NCDC policy requires ending each year with a cash balance of at least $1 million. Your task is to develop a spreadsheet model to analyze and solve the problem. 2.1 What are the decision options? What constraints need to be considered? What should the objective be? 2.2 For testing purposes, assume that NCDC finances Project A at 100% and Project C at 50%. Calculate by hand what the ending cash position would be at the end of year 2022 and year 2023. 2.3 Build a spreadsheet model using the appropriate model template, first for years 2022 and 2023, and then test the model, by entering another tentative data set of your choice. 2.4 Develop and solve the full-scale model. Summarize your recommendation for NCDC in a paragraph.

Answer rating: 100% (QA)

21 Decision options constraints and objective Decision options NCDC can choose to participate in each project at any percentage from 0 to 100 Constraints NCDC must end each year with a cash balance of ... View the full answer