Finance
In presenting solution to this project, you MUST use Excel and show the work.
Please show your work in EXCEL
Superficial Casting Company is considering adding a new line to its product mix. The production line would be set up in unused space in Superficial’s main plant based in Hollywood, California. The machinery’s invoice price is $208,000, another $12,000 in shipping charges is required, and installation costs would amount to $27,000. The machinery has an economic life of 4 years, and Superficial management has obtained a special tax ruling that places the equipment in MACRS 3-year class. The machinery is expected to have a salvage value of $28,000 after 4 years of use.
The new line would generate incremental sales of 1,325 units per year for all 4 years at an incremental cost of $112 per unit in the first year, excluding depreciation. Costs (excluding depreciation) are expected to increase by 2% per year due to inflation after the first year. Each unit can be sold for $219 in the first year. The sales price is expected to rise by 3.75% in each of the next two years, and then to fall by 8% in the fourth year, due to fast diminishing demand.
To handle the new line, the company will require net operating working capital at 11.5% of the sales anticipated at the end of the next period. In other words, the company will need net operating working capital at time 0 of 11.5% of the sales expected at the end of year 1, etc. Net operating working capital is expected to be fully recoverable at the end of year 4, the last year of the product’s life cycle.
The firm’s marginal tax rate is 38.2%, and its cost of capital is 10.75% per year.
Deliverables:
(1) Set up a spreadsheet showing input and output sections next to each other to facilitate ease of sensitivity analysis. Input should include all parameters discussed in the text of the problem. Output should include: NPV, IRR, MIRR, PI, Payback Years and a Decision Box (Invest or Don’t Invest). BE SURE TO include the Discounted Years to Payback, in your output section.
(2) MOST IMPORTANT QUESTION: Show a calculation area separate from the Input/Output area where the parameters used in the calculations are LINKED to the input section, where necessary, and the output in the calculation area is LINKED to the output section. Your logic should be easy to follow. Ideally, MACRS factors, annual depreciation and book value should be shown together on three separate lines. Net Working Capital Requirements should be shown on a line with Changes in Net Working Capital Requirements shown on a line below the Requirements line.
(3) Derive a one way DATA TABLE for one of the variables in the input section versus the NPV of the output section. Graph this table and label the graph following the label standards discussed in class.
(4) Derive a two way DATA TABLE using any two input variables that you like against NPV. Color code the NPV values less than 25% of your best estimate of the NPV. This table should be properly labeled, as well.
(5) All dollar results should be shown in the nearest dollar. All percent values should be shown as xx.xx%. And all years to payback or index values should be shown to two decimal places. Do not round your results until the end of the calculations.
(6) Derive a formula for the Discounted Years to Payback for this case and include the result in the Output Section.
(7) Use a vertical or horizontal table lookup for revenue. Generate several different revenue tables, using different inflation rates. When creating your graph, do it by showing NPV or IRR vs the different table options.