Please put together similar to the Excel chart above to answer the question.

Axis Corp has invested $250 million in R & D. A marketing firm name JKL has been hired and was paid $175k to analyze the potential market for this new product plus they are holding a refundable $315k (after tax) retainer to do further market research while the firm is in production. If the plant is not built, the JKL firm will refund the retainer. Axis will begin selling the new drug today, the sales price would be $1 per pill, and they could sell 50 million pills in the first year. After the first year of production, they estimate that sales volume will increase by 10% per year for 4 years then level off for the life of the project.

The JKL company also estimates that if Axis began selling the pill today, the sales of, COOL, their currently available pain pill, would be cannibalized and COOLs annual after-tax cash flows would decrease from $150,000,000 to $135,150,000.

AXISs lobbyist warns that it is possible drug benefits will be capped for eligible customers. They estimate that 50% of its customers will be eligible for this benefit and the marketing firm believes todays effective sales price for these customers will be $0.90 per pill.

Due to the possibility of consumer litigation, Axis is required by their existing debt holders to place 1% of sales revenues in a non-interest-bearing trust account. This account is refunded at the end of the project assuming no litigation is pending. Under the existing liability laws, Axiss litigation team believes this drug has a low probability of consumer litigation.

According to the plant manager, variable production costs for this pill are projected at 40% of sales in the first year of production, and 20% for subsequent years. Like other products, Axis headquarters will assess a charge of 1% of sales to cover allocated overhead costs. The engineers estimate that fixed costs will be $15 million per year during production. From experience, you know that though the engineers estimates are normally accurate there is a possibility that they overestimated the costs. Historically, if the cost estimate was too high, the actual costs were 10% lower than predicted.

Total net working capital requirements are $5 million in the first year of production and will double each year for the next 3 years, thereafter NWC will remain constant. Net working capital will be recovered at the end of the project.

The marginal tax rate for Axis is 21%, inflation is predicted to be 3% and similar risk projects have a required rate of return of 11%. All estimates, except those from the JKL firm, are in nominal dollars. You believe the appropriate economic life of this project is that the plant will be in production for 6 years.

Axis plans on financing 20% of the buildings by issuing a 10-year, 5% annual coupon bond. They also believe that the new return on equity will be 12.88%.

Though the finance department believes their current estimate is the most likely required rate of return, they believe it is possible that the required return on the project may increase to 13%.

Axis needs to buy a new production facility (building) for this project. The price of a suitable facility is $35 million. The production facility has an expected salvage value of $10 million in the final year of the project. The building can be purchased today, and it will be ready for production in 2 years (beginning of year 3). During those two years, the plant will have $8 million worth of equipment installed. The payment for that equipment will be made in two equal payments. One payment will be made today and one payment a year from now. You estimate that the salvage value of the equipment will be $2 million. Axis also needs a special storage facility (building) for this project. Axis currently has one available storage unit, which was bought 4 years ago for $10 million. If Axis doesnt use the storage facility for this project, they could sell it for $15 million. You estimate it will be worth $5 million at the end of the project. Axis uses 20-year MACRS for its buildings and 5-year MACRS for its equipment.

Using the FCF method, what is the most likely NPV of this project? Would you accept it?

Analysis: 5 Year MACRS 20 Year MACRS 0.2 0.0375 0.32 0.07219 0.192 0.06677 0.1152 0.06177 0.1152 0.05713 0.0576 0.05285 0.04888 0.04522 0.04462 0 $1.00 10% 3% Production Year Project Year Sales Volume Price Revenue Variable cost 1 2 $50,000,000.00 $55,000,000.00 $1.00 $1.03 1 2 3 4 5 3 4 5 6 7 $60,500,000.00 $66,550,000.00 $73,205,000.00 $73,205,000.00 $73,205,000.00 $1.06 $1.09 $1.13 $1.16 $1.19 $64,184,450.00 $72,720,981.85 $82,392,872.44 $84,864,658.61 $87,410,598.37 -$25,673,780.00 - $14,544,196.37 -$16,478,574.49 -$16,972,931.72 -$17,482,119.67 6 8 $73,205,000.00 $1.23 $90.032,916.32 -$18,006,583.26 40% 20% $(15,000,000.00) Fixed cost -$15,000,000.00 $15,000,000.00 $15,000,000.00 -$15,000,000.00 $15,000,000.00 -$15,000,000.00 $ 8,000,000.00 Depreciation Equipment $ 10,000,000.00 Depreciation Storage $ 20,000,000.00 Depreciation Building -$571,300.00 -$528,500.00 $1,600,000.00 -$488,800.00 $750,000.00 -$2,560,000.00 $1,536,000.00 $921,600.00 -$921,600.00 -$452,200.00 -$446,200.00 $446,100.00 -$446,200.00 $1,443,800.00 $1,335,400.00 $1,235,400.00 $1,142,600.00 -$460,800.00 $446,100.00 -$1,057,000.00 -$8,000,000.00 $3,825,400.00 -$6,964,200.000 Total Cost -$571,300.00 -$528,500.00 $43,512,580.00 $34,000,196.37 $34,796,174.49 $34,576,031.72 $34,992,519.67 -$34.970,483.26 30% EBIT Tax Depreciation Add-Back (Total Depreciation) OCF -$571,300.00 $0.00 $571,300.00 -$528,500.00 $0.00 -$528,500.00 $20,671,870.00 $38,720,785.48 $47,596,697.95 $50,288,626.89 $52,418,078.69 $6,201,561.00 $11,616,235.64 $14,279,009.38 $15,086,588.07 $15,725,423.61 -$2,838,800.00 -$4,456,000.00 -$3,317,600.00 -$2,603,100.00 -$2,510,400.00 $55,062,433.05 $16,518,729.92 -$1,963,900.00 2 $0.00 $0.00 $17,309, 109.00 $31,560,549.84 $36,635,288.56 $37,805,138.82 $39,203,055.09 $40,507,603.14 NWC Changes in NWC $5,000,000.00 $10,000,000.00 $20,000,000.00 $40,000,000.00 $40,000,000.00 -$5,000,000.00 -$5,000,000.00 -$10,000,000.00 - $20,000,000.00 $0.00 $40,000,000.00 $40,000,000.00 Capital Costs Equipment -$4,000,000.00 $4,000,000.00 Storage Building -$20,000,000.00 Cannibalization (CHILL) (150,000,000 - 135,150,000 = $14,850,000 in year 1, plus 3% inflation rate each consecutive year) Opportunity Cost -$15,315,000.00 (Retainer + Storage) FCF -$39,315,000.00 $4,000,000.00 $0.00 $1,400,000.00 $5,352,380.00 $10.910,740.00 -$18,263,626.90 Inflation 3% -$15,754,365.00 $16,226,995.95 $16,713,805.83 -$17,215,220.00 $17,731,676.60 -$3,445,256.00 $10.333,553.89 $9.921.482.74 $589,918.82 $21,471,378.48 $79.907,096.24 0.11 DCF -$39,315,000.00 $3,603,603.60 $0.00 -$2,519,141.49 $6,807,032.02 $5,887,917.11 $315,394.69 $10,341,870.04 $34,673,806.30 NPV $12,588,275.06 Analysis: 5 Year MACRS 20 Year MACRS 0.2 0.0375 0.32 0.07219 0.192 0.06677 0.1152 0.06177 0.1152 0.05713 0.0576 0.05285 0.04888 0.04522 0.04462 0 $1.00 10% 3% Production Year Project Year Sales Volume Price Revenue Variable cost 1 2 $50,000,000.00 $55,000,000.00 $1.00 $1.03 1 2 3 4 5 3 4 5 6 7 $60,500,000.00 $66,550,000.00 $73,205,000.00 $73,205,000.00 $73,205,000.00 $1.06 $1.09 $1.13 $1.16 $1.19 $64,184,450.00 $72,720,981.85 $82,392,872.44 $84,864,658.61 $87,410,598.37 -$25,673,780.00 - $14,544,196.37 -$16,478,574.49 -$16,972,931.72 -$17,482,119.67 6 8 $73,205,000.00 $1.23 $90.032,916.32 -$18,006,583.26 40% 20% $(15,000,000.00) Fixed cost -$15,000,000.00 $15,000,000.00 $15,000,000.00 -$15,000,000.00 $15,000,000.00 -$15,000,000.00 $ 8,000,000.00 Depreciation Equipment $ 10,000,000.00 Depreciation Storage $ 20,000,000.00 Depreciation Building -$571,300.00 -$528,500.00 $1,600,000.00 -$488,800.00 $750,000.00 -$2,560,000.00 $1,536,000.00 $921,600.00 -$921,600.00 -$452,200.00 -$446,200.00 $446,100.00 -$446,200.00 $1,443,800.00 $1,335,400.00 $1,235,400.00 $1,142,600.00 -$460,800.00 $446,100.00 -$1,057,000.00 -$8,000,000.00 $3,825,400.00 -$6,964,200.000 Total Cost -$571,300.00 -$528,500.00 $43,512,580.00 $34,000,196.37 $34,796,174.49 $34,576,031.72 $34,992,519.67 -$34.970,483.26 30% EBIT Tax Depreciation Add-Back (Total Depreciation) OCF -$571,300.00 $0.00 $571,300.00 -$528,500.00 $0.00 -$528,500.00 $20,671,870.00 $38,720,785.48 $47,596,697.95 $50,288,626.89 $52,418,078.69 $6,201,561.00 $11,616,235.64 $14,279,009.38 $15,086,588.07 $15,725,423.61 -$2,838,800.00 -$4,456,000.00 -$3,317,600.00 -$2,603,100.00 -$2,510,400.00 $55,062,433.05 $16,518,729.92 -$1,963,900.00 2 $0.00 $0.00 $17,309, 109.00 $31,560,549.84 $36,635,288.56 $37,805,138.82 $39,203,055.09 $40,507,603.14 NWC Changes in NWC $5,000,000.00 $10,000,000.00 $20,000,000.00 $40,000,000.00 $40,000,000.00 -$5,000,000.00 -$5,000,000.00 -$10,000,000.00 - $20,000,000.00 $0.00 $40,000,000.00 $40,000,000.00 Capital Costs Equipment -$4,000,000.00 $4,000,000.00 Storage Building -$20,000,000.00 Cannibalization (CHILL) (150,000,000 - 135,150,000 = $14,850,000 in year 1, plus 3% inflation rate each consecutive year) Opportunity Cost -$15,315,000.00 (Retainer + Storage) FCF -$39,315,000.00 $4,000,000.00 $0.00 $1,400,000.00 $5,352,380.00 $10.910,740.00 -$18,263,626.90 Inflation 3% -$15,754,365.00 $16,226,995.95 $16,713,805.83 -$17,215,220.00 $17,731,676.60 -$3,445,256.00 $10.333,553.89 $9.921.482.74 $589,918.82 $21,471,378.48 $79.907,096.24 0.11 DCF -$39,315,000.00 $3,603,603.60 $0.00 -$2,519,141.49 $6,807,032.02 $5,887,917.11 $315,394.69 $10,341,870.04 $34,673,806.30 NPV $12,588,275.06