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

Year	0	1	2	3	4	5	6	7	8	9	10	11	12
Revenue	-	8,80,00,000	8,97,60,000	9,15,55,200	9,33,86,304	9,52,54,030	9,71,59,111	7,20,00,000	7,34,40,000	7,49,08,800	7,64,06,976	7,79,35,116	7,94,93,818
Annual Fixed Cost	-	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)	(1,50,00,000)
Variable Cost	-	(3,60,00,000)	(3,74,40,000)	(3,89,37,600)	(4,04,95,104)	(4,21,14,908)	(4,37,99,504)	(4,55,51,485)	(4,73,73,544)	(4,92,68,486)	(5,12,39,225)	(5,32,88,794)	(5,54,20,346)
One Time Cash Expense	(50,00,000)	-	-	-	-	-	-	-	-	-	-	-	-
Profits before depreciation	(50,00,000)	3,70,00,000	3,73,20,000	3,76,17,600	3,78,91,200	3,81,39,122	3,83,59,606	1,14,48,515	1,10,66,456	1,06,40,314	1,01,67,751	96,46,321	90,73,472
Taxation	-	(1,18,40,000)	(1,19,42,400)	(1,20,37,632)	(1,21,25,184)	(1,22,04,519)	(1,22,75,074)	(36,63,525)	(35,41,266)	(34,04,901)	(32,53,680)	(30,86,823)	(29,03,511)
Cash Flows from above (a)	(50,00,000)	2,51,60,000	2,53,77,600	2,55,79,968	2,57,66,016	2,59,34,603	2,60,84,532	77,84,990	75,25,190	72,35,414	69,14,071	65,59,498	61,69,961
Depreciation		1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000	1,00,00,000
Tax Savings in Depreciation (b)		32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000	32,00,000
Start-up Costs	(12,00,00,000)
Shipping and Installation Cost	(1,00,00,000)
Land	(2,50,00,000)
Initial Outlay (c )	(15,50,00,000)
Annual Working Capital	(1,05,60,000)	(1,07,71,200)	(1,09,86,624)	(1,12,06,356)	(1,14,30,484)	(1,16,59,093)	(86,40,000)	(88,12,800)	(89,89,056)	(91,68,837)	(93,52,214)	(95,39,258)
Increase in Working Capital (d)	(1,05,60,000)	(2,11,200)	(2,15,424)	(2,19,732)	(2,24,127)	(2,28,610)	30,19,093	(1,72,800)	(1,76,256)	(1,79,781)	(1,83,377)	(1,87,044)
Recovery at the end of 12 years (e)	-	-	-	-	-	-	-	-	-	-	-	-	2,50,00,000
Net Cash Flows from Operations [a+b+c+d+e]	ekkarill92 Next > < Previous Related Solutions Can you please post the excel answers and post the (SHOW FORMULAS) button as well so... 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... Can you please post the excel answers and post the (SHOW FORMULAS) button as well so... 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... Can you please post the excel answers and post the (SHOW FORMULAS) button as well so... 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... Can you please post the excel answers and post the (SHOW FORMULAS) button as well so... 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... Can you please post the excel answers and post the (SHOW FORMULAS) button as well so... 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... Can you please post the excel answers and post the (SHOW FORMULAS) button as well so... 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... Can you put this in excel and show the formulas of how you're getting the answers... Can you put this in excel and show the formulas of how you're getting the answers please! Hi-Tek Manufacturing Inc. makes two types of industrial component parts—the B300 and the T500. An absorption costing income statement for the most recent period is shown below: Hi-Tek Manufacturing Inc. Income Statement Sales $ 1,718,000 Cost of goods sold 1,220,369 Gross margin 497,631 Selling and administrative expenses 650,000 Net operating loss $ (152,369) Hi-Tek produced and sold 60,300 units of B300 at a... Can someone do this in Excel and show me the formulas as well. The management of... Can someone do this in Excel and show me the formulas as well. The management of Tri-County Air Taxi, Inc., is considering the replacement of an old machine used in its helicopter repair facility. It is fully depreciated but it can be used by the corporation through 20x5. If management decides to replace the old machine, James Transportation Company has offered to purchase it for $69,000 on the replacement date. The old machine would have no salvage value in 20x5.... Please show work and answers with the excel formulas According to the Sleep Foundation, the average... Please show work and answers with the excel formulas According to the Sleep Foundation, the average night's sleep is 6.5 hours. Assume the standard deviation is 0.5 hours and thatthe probability distribution is normal. A. What is the probability that a randomly selected person sleeps more than 8 hours? B).What is the probability that a randomly selected person sleeps 6 hours or less? C).  Doctors suggest getting between 7 and 9 hours of sleep each night? What percentage of the population... Use excel and show both the numbers and the formulas so I can learn how to... Use excel and show both the numbers and the formulas so I can learn how to do it If I want to invest $1200 a year for 20 years and then stop investing for 10 years, to pay for my slacker child to get a doctorate, how much will I need to invest a year in my final 10 years of work to get my $2,000,000 to make me really happy at 12% interest? ADVERTISEMENT Subjects Accounting Advanced Math Anatomy and Physiology Biology Chemistry Civil Engineering Computer Science Economics Electrical Engineering Finance History Math Mechanical Engineering Operations Management Physics Psychology Statistics and Probability Nursing Other ADVERTISEMENT Latest Questions Invoice Class - Create a class called Invoice that a hardware store might use to represent... Selected comparative financial statements of Korbin Company follow: KORBIN COMPANY Comparative Income Statements For Years Ended... Stage IV of Psychosocial Development, inside of Eric Erickson’s “Childhood in Society,” is slightly related to... identify the SWOT analysis, key competitor analysis, and current needs analysis of Bellagio resort and casino... Before you read or reread this section in your text, make a list of all of... Suppose Congress decides to increase government spending and taxes by equal amounts. Use the IS-LM AD-SRAS-LRAS... A long horizontal wire carries 26.0 A of current due north. The Earth's field 21.0 cm... ADVERTISEMENT About @ 2020 WizEdu