Calculate the annual cash flows and NPV for the following proposed project. Determine whether the company should consider the project. HINT: if EBIT is negative, the company receives a tax credit.

Second HINT: Remember to do all calculations within Excel. The NPV function in Excel is really a PV function; therefore, users must add the negative cash outflow outside of the function to arrive at the correct answer. For example: NPV (arguments are correct inside function) +initial cash outflow. The cash outflow is negative; be careful not to subtract that value since that would actually add the cash outflow.

PROJECT DESCRIPTION:

The Bell Tune Company makes musical recording devices. The company currently sells several analog recording devices and is considering the introduction of a new and more technologically advanced digital recording device, the TuneXL, to fill out and modernize its product line.

Production of the new TuneXL recording device will require the purchase of new equipment. The new equipment will cost $510,000 to buy, $18,000 for shipping, and $34,000 for installation. The equipment will be depreciated straight line to zero over the projects five-year life, at the end of which the equipment is expected to be scrapped for $85,000. The new equipment will require an initial increase in working capital of $27,000.

The Bell Tune Company estimates the sales of the new TuneXL will be $185,000 in the first year of the project. After the new TuneXL catches on the sales are expected to be $360,000 annually for the second through the fifth year of the TuneXL project. Variable costs for producing the TuneXL are expected to be 20 percent of sales and fixed costs are expected to be $40,000 annually during each year of the project.

An independent consultant has determined that if Bell Tune Company introduces the new TuneXL device, it can expect a decrease of $20,000 in net sales (sales less variable costs) of its existing analog recording devices in the first year of the TuneXL project. Net sales for the analog products would be $50,000 less than currently existing sales annually in the second through fifth years of the TuneXL project.

In gathering information about the new automated machine, the chief operating officer and the marketing director have traveled to another state to view a demonstration of the TuneXL production equipment, incurring travel costs of $5,650.

The cost of capital is 5.56%. The firms tax rate is 35%.

This is what I have got so far. I only know the depreciation because someone told me. What formula should've been used to calculate the depreciation, I don't see this number in the problem? Also, I need help figuring out the formulas for the last two columns (PVF 5.56% and Present Value)

Year Revenue cost &Expenses Depreciation PBT Tax e 35% Net Income Depreciation Cash flows from operations working Capital Investment Total cash flows PVF5.56% Present value o (27.000) IS62,000) (589,000) $97,000 $112,400 (24,400) (8,540) (15,860) $112,400 1 $185,000 96,540 96,540.00 $162,000 $112,400 85,600 29,960 55,640 $112,400 3 $360,000 $162,000 $112,400 85,600 29,960 55,640 $112,400 168,040.00 168,040 $360,000 $162,000 $112,400 85,600 29,960 55,640 $112,400 168,040 R$ 168,040.00 4 5 $360,000 $162,000 $112,400 85,600 29,960 55,640 $112,400 168,040 27,000 55,250 S 250,290.00 Net Present Value