Question

A manufacturer is considering two investment programs to supply a new laptop. Market research anticipates rapid market growth: sales are expected to be 300, 000 the first year, 600,000 the second, and 900,000 the third. However, the company recognizes that actual sales may differ by plus or minus 50%.

The company has two plans to produce 900,000 units:

• Plan A: Build a single plant that could produce 900,000 units. Construction would cost $900 million and be finished in a year. The company would net $2,000 per computer sold, and the incremental manufacturing costs beyond the capital cost of the plant is expected to be $1,280 per computer.

• Plan B: Build three 300,000-unit plants, one each year, to match expected annual demand. The capital expenditure for each small plant is $300 million. The smaller plant has a unit manufacturing cost of $1,500. This plan gives the company the flexibility not to build successive plants if the demand falls short in the first or second 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 high as 1.35 million or as low as 450 thousand units.

• Plan B is less efficient. Also, if the demand grows faster than expected, it cannot take advantage of it.

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.

Part 1: Based on the forecast demand expectation without variability, set up a spreadsheet to calculate the net present values (NPVs) for Plan A, and Plan B with an inflexible expansion plan (build one plant each year regardless of market demand). Based on this first analysis, which plan is better? See end of exercise for assumptions you should use for the NPV analysis.

Part 2: Now consider the effect of uncertainty in discount rate for small and big plant. Use “Data Table” in What-if Analysis to estimate profitability of plan A over plan B in a one-way (only change discount rate of one plant at a time) and two-way (change discount rate of both plants simultaneously). Draw a chart to illustrate the changes. Also use “Conditional Formatting” to color code when the sign of the difference of profitability of plans changes.

Part 3: Now consider the effect of uncertainty for variable costs of both plants and run another two-way sensitivity analysis using “Data Table” function. Draw a chart to illustrate the changes. Also use “Conditional Formatting” to color code when the sign of the difference of profitability of plans changes.

Part 4: Now consider the effect of uncertain market demand. Assuming that the market forecast is evenly distributed over the range, simulate the performance of Plan A, and Plan B without the flexibility. Use a two-way sensitivity analysis for 1^st year demand vs 2^nd years demand and another two-way sensitivity analysis for 2^nd year demand vs 3^rd year demand using “Data Table” function.

Note: In future assignments, I will ask you to conduct more sophisticated analysis on this same problem.

Assumptions for NPV analysis:

• Use a discount rate of 9% for Plan A, and 8% for Plan B.
• No salvage value for Plan A at year 3. Salvage value for Plan B is $300 million at year 3.
• No corporate overhead or selling costs allocated to the projects.
• For simplicity, assume that a new laptop line will replace the product in the 4^th year so that there will be no sales in year 4 and beyond.

USE EXCEL TO SOLVE THE QUESTION

Answer 1

Asnwere of Part 1:
NPV is Net present Value which is the difference between the Cash Inflow and Cash Outflow of the project .

The cashinflow in the both the plans start from year 1.  

Plan A
$2000 profit on each computer sold
$1280 incremental cost of each computer produced
	Cash Inflow	Cash Outflow	No. of Units Manufactured	Net cashflow
Year 0		-900000000		-900000000
Year 1	600000000	-384000000	300000	216000000
Year 2	1200000000	-768000000	600000	432000000
Year 3	1800000000	-1152000000	900000	648000000
				$148,757,605

Plan B
$1500 per unit cost of manufacturing
$2000 selling price of a computer
	Cash Inflow	Cash Outflow	No. of Units Manufactured	Net cashflow
Year 0		-300000000		-300000000
Year 1	600000000	750000000	300000	-150000000
Year 2	1200000000	1200000000	600000	0
Year 3	1800000000	1350000000	900000	750000000
				$144,893,789

The Net cash flow of Year 3 includes $300mn of Salvage value.

Since, NPV of Plan A is higher than the NPV of Plan B, Plan A is a better option.