Problem: Suppose you have a capital budgeting proposal that includes a machine that will cost $420,000 and the project will last 7 years. The machine will be mounted on a truck you currently own. This truck was purchased for $100,000, has a book value of $10,000 and could be sold for $50,000 (net of taxes) should you choose not to accept the capital budgeting proposal. The new machine is expected to be sold at the end of the project for $50,000 (gross of taxes), but will be depreciated completely (use straight-line depreciation). You have spent $100,000 in market research related to this new product. You expect that the project will result in $125,000 in annual revenues and $54,000 in annual expenses (for what it's worth, $9,000 of the $54,000 in expenses is 1/6th allocation of the salary and fringe benefits of a well-paid security guard that currently oversees the manufacturing building). A $30,000 recoverable investment in working capital will be required to get the project started and will remain at that level throughout the life of the project. The applicable marginal tax rate is 30% and the cost of capital is 6%.
Question 1: Find the NPV of this project. (Note: You might wish to use the Excel NPV function, just realize the Excel NPV function assumes the first cash flow in your range is one year in the future. Thus, you need a "two-part" function. I strongly suggest you calculate it "by hand" to double check your answer. Whether you use the NPV function or not, you must use formulas in Excel to calculate your NPV. Trust me -- I will check EVERY SINGLE assignment.)
Question 2: Does your answer change if the annual revenues grow at an annual rate of 5% each year beyond the first year? Repeat question 1 with this new assumption. You must also use formulas to adjust your cash flows!
Question 3: Now, go back to the original question. Assuming all of the other projections do not change, approximately how much revenue (assume it is the same each year) is needed to get a NPV = 0. (An NPV within five dollars plus/minus zero is sufficient. Note, Trial and Error is OK, but the Solver Function under the Data tab works really slick if you can figure it out! HINT: It won't work unless you use formulas throughout your spreadsheet. Also, if you can't find Solver, you might have to add it via "File-Options" within Excel.)
Bonus - For the original problem, please use the Excel spreadsheet function to determine the IRR of the project. For credit, this must be displayed on the page for Question 1 and must use the Excel spreadsheet function.