Question

Can you please post the excel answers and post the (SHOW FORMULAS) button as well so I can see how you solved the question

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?
4. Can you please post the excel answers and post the (SHOW FORMULAS) button as well so I can see how you solved the question

Answer 1

Particulars	Amount
Production Facility	120
Shipping and Installation	10
Land	25
Working Cap	10.56
Tax Benefit	-1.6
Total Cost	163.96

Year	0	1	2	3	4	5	6	7	8	9	10	11	12
Sales		1,10,000	1,12,200	1,14,444	1,16,733	1,19,068	1,21,449	90,000	91,800	93,636	95,509	97,419	99,367
			2%	2%	2%	2%	2%		2%	2%	2%	2%	2%
Variable Cost		45000	46,800	48,672	50,619	52,644	54,749	56,939	59,217	61,586	64,049	66,611	69,275
			4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%
Operational Profit		65,000	65,400	65,772	66,114	66,424	66,700	33,061	32,583	32,050	31,460	30,808	30,092
No of Units		800	800	800	800	800	800	800	800	800	800	800	800
Operational Profit		52	52	53	53	53	53	26	26	26	25	25	24
Fixed Cost		15	15	15	15	15	15	15	15	15	15	15	15
Depreciation		11	11	11	11	11	11	11	11	11	11	11	11
Profit		26	26	27	27	27	28	1	0	-0	-1	-1	-2
Tax		8	8	9	9	9	9	0	0	-0	-0	-0	-1
PAT		18	18	18	18	19	19	0	0	-0	-0	-1	-1
CF		29	29	29	29	29	30	11	11	11	10	10	10
Investment	-163.96												25
Net Cashflow	-163.96	29	29	29	29	29	30	11	11	11	10	10	35
NPV	₹ 160.58
IRR	9.5%
MIRR	9.81%

It sh