Excel Capital Budgeting Assignment

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. This new capacity will be needed to meet the demand from a large national auto parts chain.

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. The plant and equipment will cost $2,300,000 today and will be depreciated for tax purposes on a straight line basis to a value of zero over 10 years.

They expect to be able to sell 10,000 sets 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 years sales.

The opportunity cost of capital for Williams Corp. is 10% and their marginal income tax rate is 35%.

Calculate the NPV and IRR of the project. Should Williams invest in the new plant?

What would be the new NPV and IRR if the units sold for sets were to grow @ 4 percent per year?

What is the economic break-even number of units to sell? What is the economic break-even price?

Complete a sensitivity analysis by changing price per unit (up to $110 and down to $130) and COGS as a % of sales (down to 59% and up to 61%) and show the effect on NPV.

Put the answers to a) through d)

***Please answer with the equations for excel, not just the numbers***

11 14 17 21. 24 28 31 37 SIMPE Project NPV Tempate Assumptions 0.0% Tax Rate Unit Sales Price per Unit Cost of sales m Sales Growth Ann Operating Expenses NOWC as a 5% of Sales Cost of Sales Gross Profit Operating Expenses Depreciation me Tax NOPAT (Unlevered Net income) Add Back Depreciation subtract increase in Nowcl S p./Dispositions Subtract Capital Free Cash Flow Discount Factor FCF Present Value NPN RNUM Required Nowc S Change in NowC Cash Flow Impact of NOWC Depreciation Capital Expenditure S Depreciation Expense oYNet Book value 1.00000 1.00000 100