A manufacturer is considering two investment programs to supply a new transportation related technology Market research anticipates rapid market growth sales are expected to be 120,000 the first year, 240,000 the second, and 360,000 the third However, the company recognizes that actual sales may differ by15 The net profit per item sold is $500 The company has two plans to produce the units Plan A Build a single plant today that could produce up to 360,000 units Construction would cost $290 million Plan B Build three 120,000 unit plants, one each year (beginning in year 0), in an effort to match expected annual demand The capital expenditure for each small plant is $100 million Construction for all plants are completed within a year Note that both plans have drawbacks Plan A involves a large amount of excess capacity in the first two years until market demand grows and there is always a chance that demand falls short of expectations Thus the demand in year 3 might be as low as 300,750 or as high as 400,250 units The CFO asks you to prepare spreadsheets to analyze this decision As the company will want to carry out extensive sensitivity analyses on the spreadsheet, all the input variables must be set in an input sheet, so that the rest of the spreadsheet will be an automated black box that generates the required results Using only the expected demands each year, set up a spreadsheet to calculate the net present worth for Plan A, and Plan B Which plan is better (Assume demand is exactly 120,000 in year 1, 240,000 in year 2 and 360,000 in year 3) Since demand is not exact, you decide to generate 15 random scenarios for the demand Use the RANDBETWEEN function in excel and then copy and paste the values so that they do not change as you manipulate the sheet The lower unit of RANDBETWEEN should be the minimum estimate for units sold that year and the upper unit the higher estimate for units sold Calculate the present worth for each plan Assume that for Plan B all three plants will be built regardless of actual demand For each random scenario, what is the best option Project1 of 1

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 11, 2024

A manufacturer is considering two investment programs to supply a new transportation-related technology. Market research anticipates rapid market growth: sales are expected to be 120,000

A manufacturer is considering two investment programs to supply a new transportation-related technology. Market research anticipates rapid market growth: sales are expected to be 120,000 the first year, 240,000 the second, and 360,000 the third. However, the company recognizes that actual sales may differ by15%. The net profit per item sold is $500.

The company has two plans to produce the units:

Plan A. Build a single plant today that could produce up to 360,000 units. Construction would cost $290 million.
Plan B. Build three 120,000 unit plants, one each year (beginning in year 0), in an effort to match expected annual demand. The capital expenditure for each small plant is $100 million.
Construction for all plants are completed within a year. Note that both plans have drawbacks. Plan A involves a large amount of excess capacity in the first two years until market demand grows; and there is always a chance that demand falls short of expectations. Thus the demand in year 3 might be as low as 300,750 or as high as 400,250 units.
The CFO asks you to prepare spreadsheets to analyze this decision. As the company will want to carry out extensive sensitivity analyses on the spreadsheet, all the input variables must be set in an input sheet, so that the rest of the spreadsheet will be an automated black-box that generates the required results.

Using only the expected demands each year, set up a spreadsheet to calculate the net present worth for Plan A, and Plan B. Which plan is better? (Assume demand is exactly 120,000 in year 1, 240,000 in year 2 and 360,000 in year 3).
Since demand is not exact, you decide to generate 15 random scenarios for the demand. Use the RANDBETWEEN function in excel and then copy and paste the values so that they do not change as you manipulate the sheet. The lower unit of RANDBETWEEN should be the minimum estimate for units sold that year and the upper unit the higher estimate for units sold. Calculate the present worth for each plan. Assume that for Plan B all three plants will be built regardless of actual demand. For each random scenario, what is the best option

Project1 of 1