Number Clear Analyze 19 Ser Filter Select fa Styles Data Cells Editing Analysis Sens B 1 D E F 2 G H Year o K 3 L 4 5 6 7 Year 1 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $1,333,333.33 $106,666.67 $36,266.67 $1,403,733.33 Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCE Change NWC Net Capital Spending CFFA Year 2 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $888,888.89 $551,111.11 $187,377.78 $1,252,622.22 Year 3 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $592,592.59 $847,407.41 $288,118.52 $1,151,881.48 Year 4 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $395,061.73 $1,044,938.27 $355,279.01 $1,084,720.99 8 Year 5 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $395,061.73 $1,044,938.27 $355,279.01 $1,084,720.99 Year 6 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $395,061.73 $1,044,938.27 $355,279.01 $1,084,720.99 9 10 11 12 13 Informat: $8,000,000.00 Company 65,000/Units Sold 6 Project Life $30.00 Price per u $4,000,000.00 Machinery $120,000.00 Machinery 6 Depreciable $160,000.00 Salvage Valu $500,000.00 Increase in In $50,000.00 Increase in Ca $6.00 Marginal Cost 14.00% Cost of Capita 34% Marginal Tax R ($550,000.00) ($4,000,000.00) ($4,550,000.00) 14 $1,403,733.33 $1,252,622.22 $1,151,881.48 $1,084,720.99 15 $1,084,720.99 $1,084,720.99 PV (CFFA) ($4,550,000.00) $1,231,345.03 $963,852.13 16 17 $777,487.19 $642,241.90 $563,370.09 $494,184.29 IRR NPV 18 Decision 15.03% 19 Tax Rate 20 21 22 23 24 25 26 27 Units Sold 55000 60000 65000 70000 75000 80000 85000 90000 95000 Taxable income $0 $50,000 $50,000 $75,000 $75,000 $100,000 $100,000 $335,000 $335,000 $10,000,000 $10,000,000 $15,000,000 $15,000,000 $18,333,333 $18,333,333 Above $18,333,333 15% 25% 34% 39% 34% 35% 38% 35% 28 29 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. 30 31 32 33 34 35 Retirement Problem NPV Ready Type here to search O 2. Reformat Row 2 (cells C2 to 12) so that the numbers display the text "Year" instead of "Period." For example, there is currently a zero in cell C2, but we see "Period 0" displayed and not just 0. Change this so it displays as "Year 0" (this formatting trick allows us to use the numbers in calculations, but we can also use labels so we know what the numbers represent). (3 points) 3. Calculate the net capital spending for each period in Row 12 and calculate the change in net working capital for each period in Row 11. (5 points) 4 Calculate the operating cash flow for each period in Row 10. a. Using relative and/or absolute cell references, use the VDB function in Row 7 to calculate depreciation. Note the function should use the accelerated (double-declining) depreciation for each year but switch to straight-line when its depreciation is larger. If done and linked correctly, when I copy the cell in Column D into Column I, the cell in Column / will return the correct answer for depreciation. (3 points) b. 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) c Make sure to fill in rows 3 to 9, and then calculate the OCF for each period in Row 10. (2 points) 5. a. Evaluate the project Calculate the cash flow from assets in Row 13. (2 points) b. Using relative and/or absolute cell references, use the PV function to calculate the present value of CFFA for each year in Row 15. If done correctly, I will be able to copy cell C15 and paste it into cells D15:15 and those cells will display the correct present value. (3 points) Use the NPV function in D18 to calculate the net present value of the project. If done correctly, it will give the same value as summing cells C15 to 115. (3 points) Use the IRR function in C18 to calculate the internal rate of return on the project. (3 points) In E18, use an IF statement to return the text "Accept" or "Reject" depending on the NPV calculation. (2 points) C. d. e. 6 a. Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19). Define a data table using Excel's What-if Analysis located in the data tab. (6 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. b 7. Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excel's What-if Analysis. Use the following value ranges for the best and worst cases: (6 points) 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 a 8. Using the Scenario Manager, create a scenario summary. (4 points) Number Clear Analyze 19 Ser Filter Select fa Styles Data Cells Editing Analysis Sens B 1 D E F 2 G H Year o K 3 L 4 5 6 7 Year 1 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $1,333,333.33 $106,666.67 $36,266.67 $1,403,733.33 Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCE Change NWC Net Capital Spending CFFA Year 2 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $888,888.89 $551,111.11 $187,377.78 $1,252,622.22 Year 3 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $592,592.59 $847,407.41 $288,118.52 $1,151,881.48 Year 4 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $395,061.73 $1,044,938.27 $355,279.01 $1,084,720.99 8 Year 5 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $395,061.73 $1,044,938.27 $355,279.01 $1,084,720.99 Year 6 $1,950,000.00 $390,000.00 $120,000.00 $1,440,000.00 $395,061.73 $1,044,938.27 $355,279.01 $1,084,720.99 9 10 11 12 13 Informat: $8,000,000.00 Company 65,000/Units Sold 6 Project Life $30.00 Price per u $4,000,000.00 Machinery $120,000.00 Machinery 6 Depreciable $160,000.00 Salvage Valu $500,000.00 Increase in In $50,000.00 Increase in Ca $6.00 Marginal Cost 14.00% Cost of Capita 34% Marginal Tax R ($550,000.00) ($4,000,000.00) ($4,550,000.00) 14 $1,403,733.33 $1,252,622.22 $1,151,881.48 $1,084,720.99 15 $1,084,720.99 $1,084,720.99 PV (CFFA) ($4,550,000.00) $1,231,345.03 $963,852.13 16 17 $777,487.19 $642,241.90 $563,370.09 $494,184.29 IRR NPV 18 Decision 15.03% 19 Tax Rate 20 21 22 23 24 25 26 27 Units Sold 55000 60000 65000 70000 75000 80000 85000 90000 95000 Taxable income $0 $50,000 $50,000 $75,000 $75,000 $100,000 $100,000 $335,000 $335,000 $10,000,000 $10,000,000 $15,000,000 $15,000,000 $18,333,333 $18,333,333 Above $18,333,333 15% 25% 34% 39% 34% 35% 38% 35% 28 29 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. 30 31 32 33 34 35 Retirement Problem NPV Ready Type here to search O 2. Reformat Row 2 (cells C2 to 12) so that the numbers display the text "Year" instead of "Period." For example, there is currently a zero in cell C2, but we see "Period 0" displayed and not just 0. Change this so it displays as "Year 0" (this formatting trick allows us to use the numbers in calculations, but we can also use labels so we know what the numbers represent). (3 points) 3. Calculate the net capital spending for each period in Row 12 and calculate the change in net working capital for each period in Row 11. (5 points) 4 Calculate the operating cash flow for each period in Row 10. a. Using relative and/or absolute cell references, use the VDB function in Row 7 to calculate depreciation. Note the function should use the accelerated (double-declining) depreciation for each year but switch to straight-line when its depreciation is larger. If done and linked correctly, when I copy the cell in Column D into Column I, the cell in Column / will return the correct answer for depreciation. (3 points) b. 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) c Make sure to fill in rows 3 to 9, and then calculate the OCF for each period in Row 10. (2 points) 5. a. Evaluate the project Calculate the cash flow from assets in Row 13. (2 points) b. Using relative and/or absolute cell references, use the PV function to calculate the present value of CFFA for each year in Row 15. If done correctly, I will be able to copy cell C15 and paste it into cells D15:15 and those cells will display the correct present value. (3 points) Use the NPV function in D18 to calculate the net present value of the project. If done correctly, it will give the same value as summing cells C15 to 115. (3 points) Use the IRR function in C18 to calculate the internal rate of return on the project. (3 points) In E18, use an IF statement to return the text "Accept" or "Reject" depending on the NPV calculation. (2 points) C. d. e. 6 a. Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 19). Define a data table using Excel's What-if Analysis located in the data tab. (6 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. b 7. Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excel's What-if Analysis. Use the following value ranges for the best and worst cases: (6 points) 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 a 8. Using the Scenario Manager, create a scenario summary. (4 points)