Problem:
Create an Excel spreadsheet for a production plant that the company will lease for 5 years at US$1,500,000 per year; it will cost the firm US$4,000,000 in capital (straight-line depreciation, 5 year life) in year 0; it will cost the firm an additional US$150,000 per year after the new production plant is brought online for other expenses; and it will generate an incremental revenue of US$3,500,000 per year. Use a 40% tax rate, a 10% cost of capital, and a 12% re-investment rate. Assume the company will use cash flow to finance the project.
Discuss how the project would fair under hurdle rate scenarios of 10%, 15%, and 20% (based on MIRR).
Complete the memorandum and spreadsheet.
(in thousands $US)
|
2001
|
2002
|
2003
|
|
Net Sales
|
210,000
|
227,000
|
250,000
|
|
Cost of Goods Sold
|
106.000
|
111.000
|
120.000
|
|
Gross Profit
|
104,000
|
116,000
|
130,000
|
|
Depreciation
|
6,000
|
6,500
|
7,000
|
|
Selling Expenses
|
35,700
|
39,725
|
45,000
|
|
General and Administrative
|
25,000
|
28,000
|
29,000
|
|
Total Operating Expenses
|
66,700
|
74,225
|
81,000
|
|
Operating Income
|
37,300
|
41,775
|
49,000
|
|
Interest Expense
|
30
|
33
|
35
|
|
Income Before Taxes
|
37,270
|
41,742
|
48,965
|
|
Income Taxes
|
14,908
|
16,697
|
19,586
|
|
Net Income
|
22,392
|
25,078
|
29,414
|