Sheet 2 NPV Worksheet (50 points total) Garcia and Martinez manufacture widgets and currently have $11 million in taxable income The company is considering an expansion, and theyve asked you to evaluate the project The expansion requires the firm to produce 85,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget Garcia and Martinez estimate they will need an additional $4,000,000 worth of machinery The machinery costs $120,000 a year to operate and maintain The machinerys depreciable life is 6 years, and the company expects to salvage the machinery for $160,000 at the end of year 6 If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the projects life Similarly, the company will immediately add $75,000 to their cash balance at start up and then again in year 1 and maintain that higher cash balance over the projects life The investments in cash and inventory will be recovered when the project is completed The marginal cost of producing a widget is $6 00 and the cost of capital is 12 Calculate the projects NPV by linking to the named variables in Column K Enter the relevant values for the variables that you use in solving this problem in Column K Name each cell in Column K that contains a variable Use the following names Income, Quantity, Life, Price, Machinery, MaintenanceCost, DepLife, Salvage, Inventory, WACC, and TaxRate (e g define the name of cell K4 to be Quantity) When linking cells or writing functions, use these names to reference the cells in your calculations This makes it easier for others to know what the functions are doing (5 points) Calculate revenues, variable costs, and fixed costs Use that information to calculate EBITDA in Row 6 (4 points) Calculate operating cash flow for each period in Row 10 Use the VDB function in Row 7 to calculate depreciation (use named cells where appropriate) Note the function should use the default accelerated (double declining) depreciation for each year and switch to straight line when its depreciation is larger If done correctly, depreciation in Year 1 is $1,333,333 33 and in Year 6 depreciation is $395,061 73 (3 points) Use the XLOOKUP function on the provided tax table to calculate the marginal tax rate in cell K15 Use that marginal tax rate to calculate taxes in each year in Row 9 (3 points) Make sure to calculate EBIT in row 8, and then calculate the OCF for each period in Row 10 If done correctly, OCF in Year 1 will be $1,714 666 67 and in Year 6 it will be $1,386,271 60 (2 points) Calculate the change in net working capital for each period in Row 11 Dont forget, youll need to know the machinerys book value at salvage to get the taxes owed (5 points) Calculate the net capital spending for each period in Row 12 (5 points) Evaluate the project (8 points) a Calculate the cash flow from assets in Row 13 b Use the NPV function in D18 to calculate the net present value of the project (correct answer is about $1 9 million) c Use the IRR function in C18 to calculate the internal rate of return on the project d In E18, use an IF statement to return the text Accept or Reject depending on the NPV calculation Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19) Define a data table using Excels What If Analysis located in the data tab (5 points) The table should include the IRR, NPV, and Decision for each quantity i e for each quantity Excel will calculate these three items in the table which will extend from C19 to E27 Use conditional formatting (found in the Home tab) to highlight the NPV in red if the NPV is negative, and green if it is positive Use a conditional format to highlight the IRR in red if the IRR is greater than the cost of capital, and in green if it is less than the cost of capital Define a base case, best case and worst case scenario using the Scenario Manager in Excels What If Analysis Name them Base Case, Best Case, and Worst Case Use the following value ranges for the best and worst cases (6 points) a Per unit price is plus minus $5 b Quantity sold is plus minus 15,000 units c Marginal cost of producing a widget is plus minus $1 50 Using the Scenario Manager, create a scenario summary (this will create a new worksheet titled Scenario Summary do NOT change the name or make a PivotTable) When you submit your file, be sure that the base case is displayed in the worksheet (4 points) 1 K15 A 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 x fx C B Revenues Variable Costs Fixed Costs Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA Units Sold 65000 70000 75000 80000 85000 90000 95000 100000 105000 Period 0 IRR D Period 1 NPV E Period 2 Decision F Period 3 G Period 4 Taxable Income $0 H Period 5 Period 6 Tax Rate $50,000 15 $75,000 25 $100,000 34 $50,000 $75,000 $100,000 $335,000 $335,000 $10,000,000 $15,000,000 $18,333,333 Above $18,333,333 39 34 35 38 $10,000,000 $15,000,000 $18,333,333 35 Note Corporate taxes are now flat at 21 I have kept the old corporate tax table in this example to illustrate the XLOOKUP function In addition, personal taxes are still based on similar tables, as are state corporate taxes J K L M N Information Variables Company's Other Taxable Income Units Sold Project Life (years) Price per unit Machinery Machinery maintenance costs Depreciable Life Salvage Value Increase in Inventory Increase in Cash Balance Marginal Cost (per unit) Cost of Capital Marginal Tax Rate 1 K15 A 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 x fx C B Revenues Variable Costs Fixed Costs Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA Units Sold 65000 70000 75000 80000 85000 90000 95000 100000 105000 Period 0 IRR D Period 1 NPV E Period 2 Decision F Period 3 G Period 4 Taxable Income $0 H Period 5 Period 6 Tax Rate $50,000 15 $75,000 25 $100,000 34 $50,000 $75,000 $100,000 $335,000 $335,000 $10,000,000 $15,000,000 $18,333,333 Above $18,333,333 39 34 35 38 $10,000,000 $15,000,000 $18,333,333 35 Note Corporate taxes are now flat at 21 I have kept the old corporate tax table in this example to illustrate the XLOOKUP function In addition, personal taxes are still based on similar tables, as are state corporate taxes J K L M N Information Variables Company's Other Taxable Income Units Sold Project Life (years) Price per unit Machinery Machinery maintenance costs Depreciable Life Salvage Value Increase in Inventory Increase in Cash Balance Marginal Cost (per unit) Cost of Capital Marginal Tax Rate

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 06, 2024

Sheet 2 - NPV Worksheet (50 points total) Garcia and Martinez manufacture widgets and currently have $11 million in taxable income. The company is considering

Sheet 2 - NPV Worksheet (50 points total)

Garcia and Martinez manufacture widgets and currently have $11 million in taxable income. The company is considering an expansion, and theyve asked you to evaluate the project. The expansion requires the firm to produce 85,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget. Garcia and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $120,000 a year to operate and maintain. The machinerys depreciable life is 6-years, and the company expects to salvage the machinery for $160,000 at the end of year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the projects life. Similarly, the company will immediately add $75,000 to their cash balance at start-up and then again in year 1 and maintain that higher cash balance over the projects life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 12%. Calculate the projects NPV by linking to the named variables in Column K.

Enter the relevant values for the variables that you use in solving this problem in Column K. Name each cell in Column K that contains a variable. Use the following names: Income, Quantity, Life, Price, Machinery, MaintenanceCost, DepLife, Salvage, Inventory, WACC, and TaxRate (e.g. define the name of cell K4 to be Quantity). When linking cells or writing functions, use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. (5 points)
Calculate revenues, variable costs, and fixed costs. Use that information to calculate EBITDA in Row 6. (4 points)
Calculate operating cash flow for each period in Row 10.
1. Use the VDB function in Row 7 to calculate depreciation (use named cells where appropriate). Note the function
  
  should use the default accelerated (double-declining) depreciation for each year and switch to straight-line when its depreciation is larger. If done correctly, depreciation in Year 1 is $1,333,333.33 and in Year 6 depreciation is $395,061.73. (3 points)
2. Use the XLOOKUP function on the provided tax table to calculate the marginal tax rate in cell K15. Use that marginal tax rate to calculate taxes in each year in Row 9. (3 points)
3. Make sure to calculate EBIT in row 8, and then calculate the OCF for each period in Row 10. If done correctly, OCF in Year 1 will be $1,714.666.67 and in Year 6 it will be $1,386,271.60. (2 points)
Calculate the change in net working capital for each period in Row 11. Dont forget, youll need to know the machinerys book value at salvage to get the taxes owed. (5 points)
Calculate the net capital spending for each period in Row 12. (5 points)
Evaluate the project. (8 points) a. Calculate the cash flow from assets in Row 13. b. Use the NPV function in D18 to calculate the net present value of the project (correct answer is about $1.9 million). c. Use the IRR function in C18 to calculate the internal rate of return on the project. d. In E18, use an IF statement to return the text Accept or Reject depending on the NPV calculation.
Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19). Define a data table using Excels What-If Analysis located in the data tab. (5 points)
1. The table should include the IRR, NPV, and Decision for each quantity; i.e. for each quantity Excel will calculate these three items in the table which will extend from C19 to E27.
2. Use conditional formatting (found in the Home tab) to highlight the NPV in red if the NPV is negative, and green if it is positive. Use a conditional format to highlight the IRR in red if the IRR is greater than the cost of capital, and in green if it is less than the cost of capital.
Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excels What-If Analysis. Name them Base Case, Best Case, and Worst Case. Use the following value ranges for the best and worst cases: (6 points)

a. Per unit price is plus/minus $5 b. Quantity sold is plus/minus 15,000 units c. Marginal cost of producing a widget is plus/minus $1.50
Using the Scenario Manager, create a scenario summary (this will create a new worksheet titled Scenario Summary; do NOT change the name or make a PivotTable). When you submit your file, be sure that the base-case is displayed in the worksheet. (4 points)

image text in transcribed

1 K15 A 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 x fx C B Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA Units Sold 65000 70000 75000 80000 85000 90000 95000 100000 105000 Period 0 IRR D Period 1 NPV E Period 2 Decision F Period 3 G Period 4 Taxable Income $0 H Period 5 Period 6 Tax Rate $50,000 15% $75,000 25% $100,000 34% $50,000 $75,000 $100,000 $335,000 $335,000 $10,000,000 $15,000,000 $18,333,333 Above $18,333,333 39% 34% 35% 38% $10,000,000 $15,000,000 $18,333,333 35% Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes. J K L M N Information/Variables Company's Other Taxable Income Units Sold Project Life (years) Price per unit Machinery Machinery maintenance costs Depreciable Life Salvage Value Increase in Inventory Increase in Cash Balance Marginal Cost (per unit) Cost of Capital Marginal Tax Rate 1 K15 A 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 x fx C B Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA Units Sold 65000 70000 75000 80000 85000 90000 95000 100000 105000 Period 0 IRR D Period 1 NPV E Period 2 Decision F Period 3 G Period 4 Taxable Income $0 H Period 5 Period 6 Tax Rate $50,000 15% $75,000 25% $100,000 34% $50,000 $75,000 $100,000 $335,000 $335,000 $10,000,000 $15,000,000 $18,333,333 Above $18,333,333 39% 34% 35% 38% $10,000,000 $15,000,000 $18,333,333 35% Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes. J K L M N Information/Variables Company's Other Taxable Income Units Sold Project Life (years) Price per unit Machinery Machinery maintenance costs Depreciable Life Salvage Value Increase in Inventory Increase in Cash Balance Marginal Cost (per unit) Cost of Capital Marginal Tax Rate