(in Excel) Capital Budgeting: Fancy Dust Collectors, Inc. is considering a project to build a new factory to produce luxury snow globes. They estimate the initial cost for the factory and equipment to be $500,000 (this is at t=0), which will be depreciated (to zero) on a straight-line basis over eight years. The equipment and factory will have no salvage value. They expect the project to require a working capital investment of $100,000, which is paid at t = 0 and returned to the firm as a cash inflow at t = 8. They estimate that they will sell 65,000 snow globes in the first year. The opportunity cost of capital (the discount rate) is 15%. The tax rate is 35%. The following estimates apply to each year during the project’s 8-year life:
Annual fixed costs are estimated at $95,000 throughout the life of the project.
A luxury snow globe costs $17 to manufacture, and they will be sold for $21 each.
They estimate that sales of snow globes will increase by 3.0% per year for the life of the project.
Calculate the net income (earnings) from the project for each year of the project.
Calculate the Operating Cash Flow (OCF) for each year of the project.
Calculate the Cash Flow From Assets (CFFA) for each year of the project.
What is the project’s NPV? Based on NPV, should the firm accept the project?
What is the project’s IRR (use the Excel IRR function)? Based on IRR, should the firm accept the project?
Keeping all of the other assumptions the same, what is the minimum number of snow globes the firm can sell in the first year for this project to be accepted? In other words, how many snow globes must be sold in the first year so the project has an NPV of zero? (HINT: In the “Data” tab, Click on the “What If Analysis” icon and choose “Goal Seek”. Then “Set Cell” should be the NPV formula cell, “To Value” should be “0” and “By Changing Cell” should be the cell with the initial sales level of 65,000.) HINT: after using the solver, copy the value as the answer to this part of the question. Then re-input the 65,000 in the first year sales to change the values back to the original amounts used above.