See attached file for spreadsheet assignment.
Instructions FIN 357 Excel Case #2 Spring 2021 submit to Canvas before 10pm on 4/5/2021 In this assignment, you will evaluate a potential project for the Whatchamacallit Company. Whatchamacallit Incorporated is considering a new product line, Gizmos. Production will last a total of six years. This year, Gizmos sell for $3 each, and you expect this price to grow at 10% per year. A Gizmo factory will cost $6,000 (today) to build, and it will be online starting next year. You expect the plant to produce 500 Gizmos per year. The fixed cost of operating the factory is $125 per year. The variable cost of production is $1.70 per unit, starting next year when you begin production. Variable costs are expected to decrease at a rate of 15% per year after the first year, as your employees become more efficient. The fixed costs of operating the plant do not change over time. You will depreciate the plant in a straight-line manner to $0 over the six-year project. You expect to sell the plant for $250 at the end of the sixth year. You will need an additional $100 in cash starting today to cover incidental expenses. Once you start producing Gizmos next year, you will need working capital equal to 15% of total revenue. At the end of the sixth year, all working capital will be reabsorbed back into the firm. Thus net working capital in year 0 is $100; net working capital in years 1-6 is 15% of revenue in each year; and don't forget the firm receives a chas-flow at the end of year 6 equal to the final value of NWC. The firm's tax rate is 35%. Assume that the rest of the firm is profitable, so that if there are any losses from this project, they will reduce the firm's total tax burden (in other words, tax cash flow can be negative). Find total revenue, total costs, taxes, and the change in NWC for each of the six years. What are the non-operating cash-flows in year 0 (now) through year 6? Non-operating cash-flows include investment in net working capital and in fixed assets. What are operating cash flows in years 1 through 6? What is the internal rate of return on this project? If your discount rate is 7%, should you undertake the project? A colleague of yours points out that the since the Tax Cuts and Jobs Act of 2018, the correct marginal tax rate is now 21%. At this lower tax rate, does it make sense to undertake the project? Why or why not? Another colleague agrees that the tax rate should be 21%, but he thinks your analysis about cash flows is flawed. He believes that the plant will be worthless at the end of year 6, that fixed costs will actually be $200 per year, but that Gizmo production will grow each year at a 5% rate: 500 Gizmos in the first year, 525 in the second year, etc. He also thinks that the variable cost per Gizmo will only drop by 15% once, after the first year; it's crazy to think that it will drop by 15% a year for six full years. He thinks your other assumptions are OK. What are all the cash flows under these alternative assumptions, what is the new internal rate of return, and should you accept the project if she is right? After you do all your calculations, your boss tells you that in fact the correct discount rate for this project is 5%, not 7%. Now what are your accept/reject decisions under the three different scenarios? Your final output should be: (1) A new sheet where you calculate all cash flows from the project. Highlight in green the cells that report revenue, total (operating) costs, taxes, the change in NWC, operating cash flows, and non-operating cash flows in each year, as well as the internal rate of return for the project. Answer whether, and why, you should undertake the project at a 7% discount rate. (2) An answer to what the internal rate of return is at the tax rate of 21%, and whether you should now undertake the project. You can answer this two ways: either copy your previous sheet, change the tax rate, and answer the questions in the new sheet, or just change the tax rate on the old sheet, find the answers, write them down, and then change the tax rate back to 35% so your answer to #1 is still there. You do not need to report the annual cash-flows under the 21% tax assumption, just the IRR and the accept/reject decision. (3) A new sheet where you redo the analysis using the alternative assumptions about the scrap value of the plant, the new fixed cost, the new production growth rate, and the new variable costs. Highlight in green the cells that report revenue, total costs, taxes, the change in NWC, operating cash flows, and non-operating cash flows in each year, as well as the internal rate of return for the project and your accept/reject decision at a 7% discount rate. (4) In the last sheet, your answer to whether you should or should not undertake the project under the three different scenarios with a 5% discount rate. (5) Bonus question for extra credit: what is the NPV of the project under all six scenarios/discount rates? Report these values next to your accept/reject decision.