M1.Q3:

The Widget Corporation is considering manufacturing a special type of widget. The chief financial analyst of the company presented the following calculation for the special widget:

• R&D: $200,000 annually today and in each of the next three years (years 0,,3)
• Production and Sales: Can only begin year 4, after an appropriate machine is purchased.
• Expected life span of project: 10 years from purchase of the machine in year 3.
• Investment in machinery: $250,000 (at t=3); expected life span of the machine 10 years; the machine will produce in years 4, 5, 13. The machine is depreciated to 0 using straight line depreciation over the 10 years.
• Expected annual sales: 5,000 widgets at $150 per widget
• Fixed Costs: $300,000 annually
• Variable Costs: $50 per widget.

The Widgets discount rate is 12%, the corporate rate is 35%. Assume that at the end of the project (i.e. after year 13) the new technology for producing widgets will have been superseded by other technologies and therefore the machine has no value.

1. What is the NPV/IRR of this project? Should you accept the project? (use an IF function to identify an ACCEPT/REJECT decision)

2. How sensitive is your decision to accept/reject to the discount rate and the price per widget assumptions? Provide a two-way sensitivity analysis (i.e. data table) that shows how your decision varies with these two assumptions (hint: your data table should say ACCEPT or REJECT based on various values for these two inputs; use conditional formatting to make all ACCEPT cells green and all REJECT cells red)

3. Assume you only keep machine for 7 years after you purchase it, at which point you sell it for $100,000. What is your decision under these conditions? (ACCEPT/REJECT)

Discount rate Corporate tax rate THE WIDGET CORPORATION 12% 35% Annual R&D, years 0-3 200,000 Machine 250,000 Year purchased Cost Life span Depreciation, years 4-14 10 #N/A Expected annual sales (widget units) Price per widget Annual fixed cost Variable cost per widget 5,000 150 300,000 50 2 3 8 9 10 12 13 01-1: Year Widget Sales Annual fixed cost Annual variable cost R&D Expense Depreciation Profit before taxes Taxes Profit after taxes Add back depreciation Capital Expenditures (machine) Free cash flow NPV IRR ACCEPT PROJECT? #N/A #N/A #N/A 01-2: 100 110 120 130 Price per Widget 140 150 160 170 180 190 69 8% 10% 12% 14% 16% 18% Discount Rate 20% 22% 24% 26% 28% 30% 32% 4 5 6 7 8 9 10 01-3: Machine Salvage value at the end of year 10 (7 years after purchase) - see exam word document: Year 0 1 2 3 Widget Sales Annual fixed cost Annual variable cost R&D Expense Depreciation Profit before taxes Taxes Profit after taxes Add back depreciation Capital Expenditures (machine) Free cash flow NPV IRR ACCEPT PROJECT? #N/A #N/A #N/A Discount rate Corporate tax rate THE WIDGET CORPORATION 12% 35% Annual R&D, years 0-3 200,000 Machine 250,000 Year purchased Cost Life span Depreciation, years 4-14 10 #N/A Expected annual sales (widget units) Price per widget Annual fixed cost Variable cost per widget 5,000 150 300,000 50 2 3 8 9 10 12 13 01-1: Year Widget Sales Annual fixed cost Annual variable cost R&D Expense Depreciation Profit before taxes Taxes Profit after taxes Add back depreciation Capital Expenditures (machine) Free cash flow NPV IRR ACCEPT PROJECT? #N/A #N/A #N/A 01-2: 100 110 120 130 Price per Widget 140 150 160 170 180 190 69 8% 10% 12% 14% 16% 18% Discount Rate 20% 22% 24% 26% 28% 30% 32% 4 5 6 7 8 9 10 01-3: Machine Salvage value at the end of year 10 (7 years after purchase) - see exam word document: Year 0 1 2 3 Widget Sales Annual fixed cost Annual variable cost R&D Expense Depreciation Profit before taxes Taxes Profit after taxes Add back depreciation Capital Expenditures (machine) Free cash flow NPV IRR ACCEPT PROJECT? #N/A #N/A #N/A