Question

Can you do in excel please

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

Based on the given data, pls find below the calculations:

Assumption: Apart from the data given, have not considered the intial marketing costs of $10Mn, as part of the intial costs, as it is assumed that it might be general marketing study and not exclusively for this new product launch. If that marketing study is exclusively for this new product purpose, then the costs incurred on the same shall need to be considered below as part of Initial costs (Year 0);

Based on the above workings, it is evident that this project is not feasible to invest.

NPV is -ve $ 8 Mn; IRR and MIRR - Both are lower than the discounting factor; Payback period is more than the life of the project; Hence, it is not recommended for this Project;

Computation:

Computation of IRR: This can be computed using formula in Excel = IRR("range of cashflows", discounting factor%);

Computation of IRR: This can be computed using formula in Excel = IRR("range of cashflows", discounting factor%, return on reinvestment%); In this case, with no other data availbale, it is assumed that both the factor%s are 10%;

Computation of Net Present Value (NPV) based on the Discounted Cash flows; The Discounting factor is computed based on the formula: For year 0, the discounting factor is 1; For Year 1, it is computed as = Year 0 factor /(1+discounting factor%) ; Year 2 = Year 1 factor/(1+discounting factor %) and so on;

Next, the cashflows need to be multiplied with the respective years' discounting factor, to arrive at the discounting cash flows;

The total of all the discounted cash flows is equal to its respective Project NPV of the Cash Flows;

Computation of Pay Back Period: Here, the period is computed for each project, based on cumulative discounted cash flows: If the cumulative value is less than or equal to zero, the period is considered as 12 months (it means that the net cumulative cash flow has not yet paid back the initial investment); Once the value turns positive in a particular year, the period for such year is observed at a proportion of actual discounted cash flow to the cumulative CF; This gives the period less than 12 months in such year; Once this is computed, total of all the years is taken and divided by 12, to arrive at the Payback period in no.of years.