Question

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 100,000 the first year, 200,000 the second, 300,000 the third and 400,000 the fourth year. However, the company recognizes that actual sales may differ by 15%.

The net profit per item sold is $750. The company has three plans to produce the units:

Plan A. Build a single plant today that could produce up to 400,000 units. Construction would cost $360 million (in year 0). The M&O cost of the plant is $200,000 and increases by $700,000 per year.

Plan B. Build two plants, one in year 0 and one in year 2, which can produce up to 200,000 units. The cost to build each plant is $190 million. The M&O per plant is $90,000 per year and increases by $6,000 per year.

Plan C. Build four 100,000 unit plants, one each year (beginning in year 0 and ending in year 3), in an effort to match expected annual demand. The cost to build each plant is $90 million. The M&O per plant is $40,000 per year and increases by $5,000 per year.

Construction for all plants are completed within a year. There are pros and cons of each plan. For example, Plan A involves a large amount of excess capacity in the earlier years until market demand grows; and there is always a chance that demand falls short of expectations. For example, demand in year 4 might be as low as 360,000 or as high as 440,000 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.

1. Using only the expected demands each year, set up a spreadsheet to calculate the net present worth for Plan A, B and C. Which plan is better? (Assume demand is exactly 100,000 in year 1, 200,000 in year 2, 300,000 in year 3 and 400,000 in year 4).

2. Since demand is not exact, you decide to generate 20 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. For each random scenario, what is the best option?

3. Comment on the general trends. Which plan is best and why? Given the uncertainity in demand, how would go about recommending which facility should be used? Use an interest rate of 9%. For simplicity, assume that a new technology will replace the current product in the 5th year so that there will be no sales in year 5 and beyond.

Answer 1

Demand in Year 1 (Y1) = 100000

Y2 = 200000

Y3 = 300000

Y4 = 400000 and in Y5, a new technology will replace the work

difference of actual sales may be 15% every year.

In question 1,

let us calculate net present worth for all the plans.

Plan A:

1 plant to be made. Cost of construction(CC) in Y0 = $360 ML with capacity to produce 400000 units. M & O 200000 and increases by $7000000 per annum (p.a.)

Let us analyse this plan: We are making only 1 plant. CC is $360 ML and Capacity to produce is 400000 already. If we see the demand , it increases by 100000 every year. We will not be able to utilise the plant to its maximum capacity. And M & O cost will be as high as 200000 and increases by 700000 p.a.

Plan B:

2 plants to be made, each in 1 year. Capacity to produce 200000 units and CC = $ 190 ML per plant. M & O = $ 90000 per plant and increases by $ 6000 p.a.

Let us analyse this plan: We are making 2 plants , 1 in each year , this will suit the demand schedule as well. capacity to produce is 200000 units. We can utilise the plants to its maximum untill Y2. As in Y3 and Y4, the demand will increase and this plant will not be able to meet the demand. M & O cost will be $90000 and increase by 6000 p.a.

Plan C:

4 plants to be made with capacity to produce 100000 units, 1 plant per year. CC = $90 ML per plant. M & O = $ 40000 and increases by $ 5000 p.a.

Let us analyse this plan: We are making 1 plant each year with capacity of 10000 units which matches our demand schedule. We can utilise these plants to its maximum to match with the demand schedule. Total CC = $ 360 ML .

Here is the excel:

Sales Demand	Quantity
Y1	100000
Y2	200000
Y3	300000
Y4	400000
				Yearwise cost
				Y1	Y2	Y3	Y4	Total
Plan 1
Plant to construct	1			1	0	0	0
CC	$ 360 ML			$ 360 ML	0	0	0	360000000
Capacity	400000		Demand	100000	200000	300000	400000
M&O per year	200000			200000	900000	160000	250000	1510000
increases by	700000		Total cost					361510000
Plan 2				Y1	Y2	Y3	Y4	Total
Plant to construct	2			1	1	0	0	2
CC	$190 ML			$190 ML	$190 ML			380000000
Capacity	200000		Demand	100000	200000	300000	400000
M&O per year	90000		M&O P1	90000	96000	102000	108000	396000
increases by	6000		M&O P2	0	96000	102000	108000	306000
			M&O					702000
			Total cost					380702000
Plan 3				Y1	Y2	Y3	Y4	Total
Plant to construct	4			1	1	1	1
CC	$ 90 ML			$ 90 ML	$ 90 ML	$ 90 ML	$ 90 ML	360000000
Capacity	400000		Demand	100000	200000	300000	400000
M&O per year	40000		M&O P1	40000	45000	50000	55000	190000
increases by	5000		M&O P2	0	45000	50000	55000	150000
			M&O P3	0	0	50000	55000	105000
			M&O P4	0	0	0	55000	55000
			M&O					500000
			Total cost					360500000

Plan C looks most feasible option among the 3 plans.

3. Looking at the 3 plans, If manufacturer decides to go with plan 1 with compound interest of 9%, total amount he has to pay at the end of the project will be $446347232.13, for plan 2 amount will be $470043107.98 and for plan 3, $445100210.74 ; So it is better that the manufacturer goes ahead with plan C. As for the demand, is properly distributed and capacity of the plants is fully utilised at the lowest possible cost. For maximum profit, plan C is the best option.