20.Calculating Project Cash Flows and NPV. Pappy\'s Potato has come up with a ne
ID: 2795296 • Letter: 2
Question
20.Calculating Project Cash Flows and NPV. Pappy's Potato has come up with a new product, the Potato Pet (they are freeze-dried to last longer). Pappy's paid $120,000 for a marketing survey to determine the viability of the product. It is felt that Potato Pet will generate sales of $815,000 per year. The fixed costs associated with this will be $196,000 per year, and variable costs will amount to 20 percent of sales. The equipment necessary for production of the Potato Pet will cost $865,000 and will be depreciated in a straight-line manner for the 4 years of the product life (as with all fads, it is felt the sales will end quickly). This is the only initial cost for the production. Pappy's has a tax rate of 40 percent and a required return of 13 percent. Calculate the payback period, NPV, and IRR. I have the question answered but I need to know how I would do it in excel. Please assist.
Explanation / Answer
Marketing Survey costs are sunk costs and are not included in the Cash Flow analysis
Sales $815,000
Fixed Costs $196,000
Variable Costs $163,000 [0.20x815,000 =163,000]
Depreciation $216,250 [865,000/4 =216,250]
EBIT $239,750 [Sales - Fixed - Variable - Depreciation]
Annual Free Cash Flow = EBITx(1-T) + Depreciation
=$239,750 + 216,250
=$456,000
Calculations in Excel
a.) Payback period = 1 + (865,000 - 456,000)/456,000 = 1 + 0.8969 =1.8969 years
b.) NPV of the Project =NPV(0.13,456000,456000,456000,456000) - 865000 =$491,359
c.) IRR of the Project = Rate at which NPV is Zero
-865000 + 456000x{(1-(1+IRR)-4)/IRR} =0
-865 + 456x{(1-(1+IRR)-4)/IRR} =0
Solving for IRR by using the Goal Seek function in Excel
IRR = 0.38312
Hence, the IRR for the project is 38.31%
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.