Question

Can you do in excel please and show Formulas

The Air Marshal Co. has recently completed a $10,000,000 two-year marketing study.  Based on the results of this study, Air Marshal has estimated that 800 units of its new security electro-optical human scanning hardware, known as "Marshal Dillon," could be sold annually over the next 12 years, at a price of $110,000 the first year with an estimated 2% annual rise from inflation in years 2-6.  The sales price is expected to drop to $90,000 in year 7 due to increasing competition with 2% annual increase for year 8-12.  Variable costs per unit are $45,000 with an estimated 4% annual rise from inflation in years 2-12 and incremental cash fixed costs total $15 million per year all 12 years.   

Start-up costs include $120 million to build production facilities and an additional $10,000,000 for shipping and installation costs, $25 million for land, and net operating working capital is projected to be 12% of next year sakes. The production facility will be depreciated on a straight-line basis to a value of zero over the twelve-year life of the project.  At the end of the project's life, the facilities (including the land) will be sold for an estimated $25 million.  The value of the land is not expected to change during this time period.

Finally, start up would also entail one-time tax-deductible cash expenses of $5 million at year zero.  Air Marshal is an ongoing, profitable business and pays taxes at a 32% rate.  Air Marshal has a 10% opportunity cost of capital for projects such as this one.  

Be sure to answer to the following questions and express your numbers in millions of dollars where appropriate.   If your annual income should be a loss, assume that tax could be saved from other profitable parts of the company.

1. Please estimate the cash flows for all 12 years and provide a detailed statement of projected cash flows, as was done in the sample quiz solution.
2. Calculate the following for the project:  NPV, IRR, and MIRR.
3. Should Air Marshal proceed with the Marshal Dillon project?

Answer 1

Selling price inflation	2%
Variable Cost Inflation	4%

Cost of production facility	120
Shipping & Installation cost	10
Amount to be depreciated	130
Useful life	12
Depreciation per year	10.833

Tax rate

32%

(All figures are in millions)

Income statement	1	2	3	4	5	6	7	8	9	10	11	12
#units sold	800	800	800	800	800	800	800	800	800	800	800	800
Selling price per unit	0.1100	0.1122	0.1144	0.1167	0.1191	0.1214	0.0900	0.0918	0.0936	0.0955	0.0974	0.0994
Variable Cost per unit	0.0450	0.0468	0.0487	0.0506	0.0526	0.0547	0.0569	0.0592	0.0616	0.0640	0.0666	0.0693
Revenue	88.00	89.76	91.56	93.39	95.25	97.16	72.00	73.44	74.91	76.41	77.94	79.49
Variable Costs	36.00	37.44	38.94	40.50	42.11	43.80	45.55	47.37	49.27	51.24	53.29	55.42
Incremental fixed costs	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00
Depreciation	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83
Operating profit	26.17	26.49	26.78	27.06	27.31	27.53	0.62	0.23	-0.19	-0.67	-1.19	-1.76
Tax	8.37	8.48	8.57	8.66	8.74	8.81	0.20	0.07	-0.06	-0.21	-0.38	-0.56
Profit after tax	17.79	18.01	18.21	18.40	18.57	18.72	0.42	0.16	-0.13	-0.45	-0.81	-1.20

Initial Capital Investment = Cost of production facility + Shipping & Installation cost + Cost of land + After tax start-up cost

Cost of production facility	120
Shipping & Installation cost	10
Cost of land	25
Before tax Start-up cost	5
After tax Start-up cost	3.4
Initial Capital Investment	158.4

Investment in Working Capital in Year 0 = 12%*Revenue of Year 1 = $10,560,000

Tax rate	32%
Opportunity cost of capital	10%
Working capital (% of next year's sales)	12%

(All figures are in millions)

Cash flow analysis	0	1	2	3	4	5	6	7	8	9	10	11	12
1. Initial Cash Flow
Capital Investment	-158.40
Working Capital	-10.56	-10.77	-10.99	-11.21	-11.43	-11.66	-8.64	-8.81	-8.99	-9.17	-9.35	-9.54	0.00
2. Operating Cash Flows
Revenue		88.00	89.76	91.56	93.39	95.25	97.16	72.00	73.44	74.91	76.41	77.94	79.49
Variable Costs		36.00	37.44	38.94	40.50	42.11	43.80	45.55	47.37	49.27	51.24	53.29	55.42
Incremental fixed costs		15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00	15.00
Depreciation		10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83	10.83
Operating profit		26.17	26.49	26.78	27.06	27.31	27.53	0.62	0.23	-0.19	-0.67	-1.19	-1.76
Tax		8.37	8.48	8.57	8.66	8.74	8.81	0.20	0.07	-0.06	-0.21	-0.38	-0.56
Profit after tax		17.79	18.01	18.21	18.40	18.57	18.72	0.42	0.16	-0.13	-0.45	-0.81	-1.20
Increase in Working Capital		0.21	0.22	0.22	0.22	0.23	-3.02	0.17	0.18	0.18	0.18	0.19	-9.54
Cash flow (PAT + Dep - Increase in Working Capital)		28.42	28.63	28.83	29.01	29.17	32.57	11.08	10.82	10.52	10.20	9.84	19.18
3. Terminal Cash flow
After tax salvage value													17.00
Net Cash flow (1+2+3)	-168.96	28.42	28.63	28.83	29.01	29.17	32.57	11.08	10.82	10.52	10.20	9.84	36.18
NPV	-7.3972
IRR	8.96%
MIRR	9.59%

NPV of the project = -$7.4 million

(3) Since NPV<0 & IRR/MIRR < Opportunity cost of capital, Air Marshal should not proceed with the Marshal Dillon project.