Excel Activity: Issues in Capital Budgeting Question 1 0/10 Excel Activity: Issues in Capital Budgeting Submit Start with the partial model in the file Ch13 P18 Build a Model.xlsx. Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $10 million at Year O to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 13% of the year's projected sales; for example, NwCo = 13%(Sales,). The servers would sell for $27,500 per unit, and Webmasters believes that variable costs would amount to $20,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 5%. The company's nonvariable costs would be $2 million at Year 1 and would increase with inflation. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell 1,000 units per year. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project's 4-year life is $600,000. Webmasters.com's federal-plus-state tax rate is 25%. Its cost of capital is 9% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.7 and 1.3. Low-risk projects are evaluated with a 7% project cost of capital and high-risk projects at 13%. The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Download spreadsheet Ch13 P18 Build a Model-0a73d9.xlsx a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback. Round your answer for the NPV to the nearest dollar and for the IRR and payback to two decimal places. NPV $ % IRR Regular payback period years b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Round your answers to the nearest dollar. Use a minus sign to enter a negative value, if any. % Deviation from NPV with Variables at Different Deviations from Base Sales Drice Variable Cost ner I Init Number of Initc Sold Race Case b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Round your answers to the nearest dollar. Use a minus sign to enter a negative value, if any. % Deviation from Base Case NPV with Variables at Different Deviations from Base Sales Price Variable Cost per Unit Number of Units Sold $ $ -20% $ -10% $ $ $ 0% $ $ 10% $ $ $ 20% $ $ $ Choose the correct graph. A. B. Sensitivity Analysis Sensitivity Analysis $20,000,000 $20,000,000 $15,000,000 $15,000,000 $10,000,000 $10,000,000 $5,000,000 $5,000,000 SO -20% -15% -10% -5% 10% 5% 790 15% 2006 $0 -20% -15% 20% -5% 10% 5% 10% 15% 20% -$5,000,000 $5,000,000 -$10,000,000 -$10,000,000 % Deviation from Base - Sales Price Variable Cost per Unit Number of Units Sold % Deviation from Base - Sales Price Variable Cost per Unit - Number of Units Sold C. D. Sensitivity Analysis Sensitivity Analysis $20,000,000 $20,000,000 $15,000,000 $15,000,000 $10,000,000 $10,000,000 $5,000,000 $5,000,000 SO -20% -15% -1045 10% 5% 10% 15% 20% SO -20% -15% -109 -5% 10% 50% 10% 15% 20% % -$5,000,000 -$5,000,000 -$10,000,000 -$10.000.000 % Deviation from Base - Sales Price Variable Cost per Unit - Number of Units Sold % Deviation from Base - Sales Price Variable Cost per Unit Number of Units Sold The correct graph is C. Now conduct a scenario analysis. Assume that there is a 30% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 30% probability of worst-case conditions, with the variables 20% worse than base, and a 40% probability of base-case conditions. Round your answers for the NPV and standard deviation to the nearest dollar and for the coefficient of variation to two decimal places. Use a minus sign to enter a negative value, if any. Scenario NPV Best Case $ Base Case $ Worst Case $ Expected NPV $ -$10,000,000 -$10,000,000 % Deviation from Base - Sales Price -- Variable Cost per Unit - Number of Units Sold % Deviation from Base - Sales Price -- Variable Cost per Unit - Number of Units Sold The correct graph is c. Now conduct a scenario analysis. Assume that there is a 30% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 30% probability of worst-case conditions, with the variables 20% worse than base, and a 40% probability of base-case conditions. Round your answers for the NPV and standard deviation to the nearest dollar and for the coefficient of variation to two decimal places. Use a minus sign to enter a negative value, if any. Scenario NPV Best Case $ Base Case $ Worst Case $ Expected NPV $ Standard Deviation $ Coefficient of Variation d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. Round your answer for the NPV to the nearest dollar and for the IRR and payback to two decimal places. Use a minus sign to enter a negative value, if any. Risk-adjusted NPV Risk-adjusted IRR Risk-adjusted regular payback period % years Check My Work Reset Problem E F G A B D 1 Issues in Capital Budgeting 2 3 a. Developing a spreadsheet model, and using it to find the project's NPV, IRR, and payback 4 5 Equipment cost, Year 0 $10,000,000 6 Net working capital as % of next year's sales 13% 7 Units sold per year 1,000 8 Sales price per unit, Year 1 $27,500 9 Variable cost per unit (excl. depr.), Year 1 $20,500 10 Nonvariable costs (excl. depr.), Year 1 $2,000,000 11 Inflation rate in sales price and costs 5% 12 Market value of equipment, Year 4 $600,000 13 Tax rate 25% 14 WACC 9% 15 16 17 Intermediate Calculations 0 1 18 Unit sales 19 Sales price per unit 20 Variable cost per unit (excl. depr.) 21 Nonvariable costs (excl. depr.) 22 Sales revenues 23 Required level of net working capital 24 Basis for depreciation 25 Annual depreciation rate (MACRS) 26 Annual depreciation expense 27 Ending book value 28 Salvage value 29 Profit (or loss) on sale of equipment 30 Tax on profit (or loss) due to sale of equipment 31 Net cash flow due to sale of equipment 32 Years 2 3 4 20.00% 32.00% 19.20% 11.52% A D E F G 32 33 Years 34 Cash Flow Forecast 0 1 2 3 4 35 Sales revenue 36 Variable costs (excl. depr.) 37 Nonvariable costs (excl. depr.) 38 Depreciation 39 Operating income before taxes 40 Taxes on operating income 41 Net operating profit after taxes 42 Add back depreciation 43 Equipment purchases 44 Cash flow due to change in NWC 45 Net cash flow due to sale of equipment 46 Project's net cash flows 47 48 NPV #N/A 49 IRR 50 51 Years 52 0 1 2 3 4 53 Net cash flow 54 Cumulative net cash flow 55 Part of year required for payback 56 57 Regular payback period (years) #N/A 58 59 b. Conducting a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold 60 % Deviation Sales price, Year 1 % Deviation Sales price, Year 1 61 Base NPV from Base NPV 62 Base Case $27,500 Base Case $27,500 #N/A 63 -20% -20% #N/A #N/A Sheet1 #N/A from 61 65 A B D 58 59 b. Conducting a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, va 60 % Deviation Sales price, Year 1 from Base NPV 62 Base Case $27,500 63 -20% 64 -10% 0% 66 10% 67 20% 68 69 % Deviation Variable costs per unit, Year 1 70 from Base NPV 71 Base Case $20,500 72 -20% 73 -10% 74 0% 75 10% 76 20% 77 78 % Deviation Number of units sold from Base NPV 80 Base Case 1,000 81 -20% 82 -10% 83 0% 84 10% 85 20% 86 87 Constructing the project's sensitivity graph % Deviation NPV with Variables at Different Deviations from Base 89 from Variable Cost ner Number of Units Sheet1 79 88 89 86 87 Constructing the project's sensitivity graph 88 % Deviation from 90 Base Case 91 -20% 92 -10% 93 0% 94 10% 95 20% NPV with Variables at Different Deviations from Base Variable Cost per Sales Price Number of Units Unit Sold $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 96 97 98 99 00 01 02 03 04 05 06 07 08 09 10 11 12 13 NPV Formulas #N/A #N/A #N/A 119 C. Conducting a scenario analysis 120 Unit Sales Price, Variable Cost 121 Scenario Probability Sales Year 1 per Unit, Year 1 122 Best Case 30% 0 $0 $0 123 Base Case 40% 0 $0 $0 124 Worst Case 30% 0 $0 $0 125 126 Expected NPV #N/A 127 Standard Deviation #N/A 128 Coefficient of Variation #N/A 129 130 d. If the project appears to be more or less risky than an average project, finding its risk-adjusted NPV, IRR, and payback 131 132 CV range of firm's average-risk project: 0.7 to 1.3 133 Low-risk WACC 7% 134 WACC 9% 135 High-risk WACC 13% 136 137 Risk-adjusted WACC #N/A 138 Risk-adjusted NPV #N/A 139 Risk-adjusted IRR #N/A 140 Risk-adjusted regular payback period (years) #N/A 141 142 143 144 145 146 147 Sheet1 + Excel Activity: Issues in Capital Budgeting Question 1 0/10 Excel Activity: Issues in Capital Budgeting Submit Start with the partial model in the file Ch13 P18 Build a Model.xlsx. Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $10 million at Year O to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 13% of the year's projected sales; for example, NwCo = 13%(Sales,). The servers would sell for $27,500 per unit, and Webmasters believes that variable costs would amount to $20,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 5%. The company's nonvariable costs would be $2 million at Year 1 and would increase with inflation. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell 1,000 units per year. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of the equipment at the end of the project's 4-year life is $600,000. Webmasters.com's federal-plus-state tax rate is 25%. Its cost of capital is 9% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.7 and 1.3. Low-risk projects are evaluated with a 7% project cost of capital and high-risk projects at 13%. The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Download spreadsheet Ch13 P18 Build a Model-0a73d9.xlsx a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback. Round your answer for the NPV to the nearest dollar and for the IRR and payback to two decimal places. NPV $ % IRR Regular payback period years b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Round your answers to the nearest dollar. Use a minus sign to enter a negative value, if any. % Deviation from NPV with Variables at Different Deviations from Base Sales Drice Variable Cost ner I Init Number of Initc Sold Race Case b. Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 20% above and below their base-case values. Round your answers to the nearest dollar. Use a minus sign to enter a negative value, if any. % Deviation from Base Case NPV with Variables at Different Deviations from Base Sales Price Variable Cost per Unit Number of Units Sold $ $ -20% $ -10% $ $ $ 0% $ $ 10% $ $ $ 20% $ $ $ Choose the correct graph. A. B. Sensitivity Analysis Sensitivity Analysis $20,000,000 $20,000,000 $15,000,000 $15,000,000 $10,000,000 $10,000,000 $5,000,000 $5,000,000 SO -20% -15% -10% -5% 10% 5% 790 15% 2006 $0 -20% -15% 20% -5% 10% 5% 10% 15% 20% -$5,000,000 $5,000,000 -$10,000,000 -$10,000,000 % Deviation from Base - Sales Price Variable Cost per Unit Number of Units Sold % Deviation from Base - Sales Price Variable Cost per Unit - Number of Units Sold C. D. Sensitivity Analysis Sensitivity Analysis $20,000,000 $20,000,000 $15,000,000 $15,000,000 $10,000,000 $10,000,000 $5,000,000 $5,000,000 SO -20% -15% -1045 10% 5% 10% 15% 20% SO -20% -15% -109 -5% 10% 50% 10% 15% 20% % -$5,000,000 -$5,000,000 -$10,000,000 -$10.000.000 % Deviation from Base - Sales Price Variable Cost per Unit - Number of Units Sold % Deviation from Base - Sales Price Variable Cost per Unit Number of Units Sold The correct graph is C. Now conduct a scenario analysis. Assume that there is a 30% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 30% probability of worst-case conditions, with the variables 20% worse than base, and a 40% probability of base-case conditions. Round your answers for the NPV and standard deviation to the nearest dollar and for the coefficient of variation to two decimal places. Use a minus sign to enter a negative value, if any. Scenario NPV Best Case $ Base Case $ Worst Case $ Expected NPV $ -$10,000,000 -$10,000,000 % Deviation from Base - Sales Price -- Variable Cost per Unit - Number of Units Sold % Deviation from Base - Sales Price -- Variable Cost per Unit - Number of Units Sold The correct graph is c. Now conduct a scenario analysis. Assume that there is a 30% probability that best-case conditions, with each of the variables discussed in Part b being 20% better than its base-case value, will occur. There is a 30% probability of worst-case conditions, with the variables 20% worse than base, and a 40% probability of base-case conditions. Round your answers for the NPV and standard deviation to the nearest dollar and for the coefficient of variation to two decimal places. Use a minus sign to enter a negative value, if any. Scenario NPV Best Case $ Base Case $ Worst Case $ Expected NPV $ Standard Deviation $ Coefficient of Variation d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. Round your answer for the NPV to the nearest dollar and for the IRR and payback to two decimal places. Use a minus sign to enter a negative value, if any. Risk-adjusted NPV Risk-adjusted IRR Risk-adjusted regular payback period % years Check My Work Reset Problem E F G A B D 1 Issues in Capital Budgeting 2 3 a. Developing a spreadsheet model, and using it to find the project's NPV, IRR, and payback 4 5 Equipment cost, Year 0 $10,000,000 6 Net working capital as % of next year's sales 13% 7 Units sold per year 1,000 8 Sales price per unit, Year 1 $27,500 9 Variable cost per unit (excl. depr.), Year 1 $20,500 10 Nonvariable costs (excl. depr.), Year 1 $2,000,000 11 Inflation rate in sales price and costs 5% 12 Market value of equipment, Year 4 $600,000 13 Tax rate 25% 14 WACC 9% 15 16 17 Intermediate Calculations 0 1 18 Unit sales 19 Sales price per unit 20 Variable cost per unit (excl. depr.) 21 Nonvariable costs (excl. depr.) 22 Sales revenues 23 Required level of net working capital 24 Basis for depreciation 25 Annual depreciation rate (MACRS) 26 Annual depreciation expense 27 Ending book value 28 Salvage value 29 Profit (or loss) on sale of equipment 30 Tax on profit (or loss) due to sale of equipment 31 Net cash flow due to sale of equipment 32 Years 2 3 4 20.00% 32.00% 19.20% 11.52% A D E F G 32 33 Years 34 Cash Flow Forecast 0 1 2 3 4 35 Sales revenue 36 Variable costs (excl. depr.) 37 Nonvariable costs (excl. depr.) 38 Depreciation 39 Operating income before taxes 40 Taxes on operating income 41 Net operating profit after taxes 42 Add back depreciation 43 Equipment purchases 44 Cash flow due to change in NWC 45 Net cash flow due to sale of equipment 46 Project's net cash flows 47 48 NPV #N/A 49 IRR 50 51 Years 52 0 1 2 3 4 53 Net cash flow 54 Cumulative net cash flow 55 Part of year required for payback 56 57 Regular payback period (years) #N/A 58 59 b. Conducting a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold 60 % Deviation Sales price, Year 1 % Deviation Sales price, Year 1 61 Base NPV from Base NPV 62 Base Case $27,500 Base Case $27,500 #N/A 63 -20% -20% #N/A #N/A Sheet1 #N/A from 61 65 A B D 58 59 b. Conducting a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, va 60 % Deviation Sales price, Year 1 from Base NPV 62 Base Case $27,500 63 -20% 64 -10% 0% 66 10% 67 20% 68 69 % Deviation Variable costs per unit, Year 1 70 from Base NPV 71 Base Case $20,500 72 -20% 73 -10% 74 0% 75 10% 76 20% 77 78 % Deviation Number of units sold from Base NPV 80 Base Case 1,000 81 -20% 82 -10% 83 0% 84 10% 85 20% 86 87 Constructing the project's sensitivity graph % Deviation NPV with Variables at Different Deviations from Base 89 from Variable Cost ner Number of Units Sheet1 79 88 89 86 87 Constructing the project's sensitivity graph 88 % Deviation from 90 Base Case 91 -20% 92 -10% 93 0% 94 10% 95 20% NPV with Variables at Different Deviations from Base Variable Cost per Sales Price Number of Units Unit Sold $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 96 97 98 99 00 01 02 03 04 05 06 07 08 09 10 11 12 13 NPV Formulas #N/A #N/A #N/A 119 C. Conducting a scenario analysis 120 Unit Sales Price, Variable Cost 121 Scenario Probability Sales Year 1 per Unit, Year 1 122 Best Case 30% 0 $0 $0 123 Base Case 40% 0 $0 $0 124 Worst Case 30% 0 $0 $0 125 126 Expected NPV #N/A 127 Standard Deviation #N/A 128 Coefficient of Variation #N/A 129 130 d. If the project appears to be more or less risky than an average project, finding its risk-adjusted NPV, IRR, and payback 131 132 CV range of firm's average-risk project: 0.7 to 1.3 133 Low-risk WACC 7% 134 WACC 9% 135 High-risk WACC 13% 136 137 Risk-adjusted WACC #N/A 138 Risk-adjusted NPV #N/A 139 Risk-adjusted IRR #N/A 140 Risk-adjusted regular payback period (years) #N/A 141 142 143 144 145 146 147 Sheet1 +
