Please use this excel template for the spread sheet, I can seem to figure out ho
ID: 2712636 • Letter: P
Question
Please use this excel template for the spread sheet, I can seem to figure out how to complete the last half.
https://drive.google.com/file/d/0BzawUzMQcadNS3NhVnA3WjZRU0U/view?usp=sharing
it's uploaded on google drive and has the asusmptiosn up top, thanks in advance.
Williams Corp. makes chrome wheels that are sold through mail order and auto supply stores nationally. They are considering the construction of a new manufacturing plant in South Dakota to increase their capacity by 25%. This new capacity will be needed to meet the demand from a large national auto parts chain.
The plant will be able to manufacture 20,000 sets of chrome wheels a year when at full capacity. They will sell the wheels for $120 a set. The COGS (excluding depreciation) is projected to be 60% of sales or $72 per set. They expect that fixed operating expenses (all of which are incremental new expenses) will be $90,000 a year. The land will cost $200,000 today and can not be depreciated. The plant and equipment will cost $2,300,000 today and will be depreciated for tax purposes on a straight line basis (10% per year) to a value of zero over 10 years.
They expect to be able to sell 10,000 sets in year 1 and they expect sales to grow by 4% per year. At the end of ten years they will close the plant and expect to be able to sell it and the land at that time for $1,200,000 before taxes. The project is expected to require an initial investment of $180,000 in Accounts Receivable & Inventory less Accounts Payable and Accrued Expenses or Net Operating Working Capital (NOWC). In subsequent years the year end investment in NOWC is expected to be 15% of next year’s sales.
The opportunity cost of capital for Williams Corp. is 10.2% and their marginal income tax rate is 35%. Calculate the NPV and IRR of the project. Should Williams invest in the new plant?
Explanation / Answer
Answer:
units 10000 10400 10816 11248.64 11698.59 12166.53 12653.19 13159.32 13685.69 14233.12 Year 0 1 2 3 4 5 6 7 8 9 10 Sales 1200000 1248000 1297920 1349837 1403830 1459983 1518383 1579118 1642283 1707974 Less: Cost of sales 720000 748800 778752 809902.1 842298.2 875990.1 911029.7 947470.9 985369.7 1024785 Gross profit 480000 499200 519168 539934.7 561532.1 583993.4 607353.1 631647.3 656913.1 683189.7 Less: Operating expenses 90000 90000 90000 90000 90000 90000 90000 90000 90000 90000 Depreciation 230000 230000 230000 230000 230000 230000 230000 230000 230000 230000 EBIT 160000 179200 199168 219934.7 241532.1 263993.4 287353.1 311647.3 336913.1 363189.7 Income tax 56000 62720 69708.8 76977.15 84536.24 92397.69 100573.6 109076.5 117919.6 127116.4 NOPAT 104000 116480 129459.2 142957.6 156995.9 171595.7 186779.5 202570.7 218993.5 236073.3 Add: Dep 230000 230000 230000 230000 230000 230000 230000 230000 230000 230000 Less: Capital exp./Dispositions 2500000 180000 187200 194688 202475.5 210574.5 218997.5 227757.4 236867.7 246342.4 256196.1 Less: Inc in NOWC 7200 7488 7787.52 8099.021 8422.982 8759.901 9110.297 9474.709 9853.697 Add: Sale of land 780000 Free cash flow -2500000 154000 152080 157283.2 162694.5 168322.3 174175.2 180262.2 186592.7 193176.4 980023.5 Discount factor 1 0.90744 0.823449 0.74723 0.678 0.6153 0.55835 0.506674 0.459777 0.41722 0.3786 PV of free cash flow -2500000 139745.8 125230.1 117526.7 110306.9 103568.7 97250.72 91334.17 85791.03 80597.06 371036.9 NPV -1177612 IRR 0%