Can you please post formulas
Excel Activity: Issues in Capital Budgeting 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 $12 million at Year Oto 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 11% of the year's projected sales, for example, NWC- 11%(Sales). The servers would sell for $25,000 per unit, and Webmasters believes that variable costs would amount to $18,000 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company's nonvariable costs would be $1 million at Year 1 and would increase with inflation The server project would have a life of 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 11% 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 15%. 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-97c102.xlsx G B 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 $12,000,000 6 Networking capital as of next year's sales 11% 7 Units sold per your 1.000 8 Sales price per unit Year 1 $25,000 9 Variable cost per unit (ex. dopr. Year 1 $18,000 10 Nonvanable costs (excl. depr.) Year 1 $1.000.000 11 Inflation rate in sales price and costs 3% 12 Market value of equipment, Year 4 $800,000 13 Tax rate 25% 14 WACC 11% 15 16 17 Intermediate Calculations 0 18 Unit sales 19 Sales price per unit 20 Variable cost per unit (excl. der 21 Nonvariable costs (excl. depr) 22 Sales revenues 23 Required level of net woning capital 24 Basis for depreciation 25 Annuni depreciation rate (MACRS) 26 Annual depreciation expense 27 Ending book value 29 sec Years 2 3 20.00% 32.00% 19 20% - 11.52% G C 26 Salvage value 29 Proft (or loss) on sale of equipment 30 Tax on profit for loss) due to sale of equipment 31 Net cash fow due to sale of equipment 332 33 Years 34 Cash Flow Forecast 0 35 Sales revenus 35 Variable costs (excl. dopr.) 37 Nonvariable costs (excepir 39 Depreciation 30 Operating income before taxes 40 Taxes on operating income 41 Net operating profit after taxes 42 Aod back depreciation 43 Equipment purchases 44 Cash flow due to change in NWO 45 Net cash flow due to sale of equipment 46. Project's net cash flows 47 48 NPV WNIA 49 RR WNA 50 51 Years 52 2 53 Net Cashow 54 Cumule net cash flow 55 Part of year required for payback 50 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 nas Balai V. Shoot + G H Sale price Year Base NPV 3:25.000 LA ANA INA NA INA NA NA NA INA C - Conducting sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit and ber of units Deviation 1 from Sale price Year 11 Base Deviation NPV Base Case from $25.000 209 Base Case 10 -20% 0% -10% 10% ON 37 20% 10% 36 204 Deviation Variable costs per unit Year 1 20 trom Deviation Base 1 NPV Base Case from 310000 204 Base Cove 3 -10 205 -0% 0% 0% 75 10 76 20% 10% 20% 77 TO Deviation Number of 79 from SD NPV from Base Case 5.000 Base Case 87 -20% 20% -10% -10% ok 14 ON 10% 104 20 2013 MT Consucting the project sensitivity graphe Deviation Vith Variables et Different Deviations from Base 09 from Sales Price Variable Cost per 00 Base Case UN Number of Units Sold -20% 50 59 $0 02 -10% $0 50 30 0% 50 10 10 54 SO 30 30 20% sal SO Variable costs per un Yeart Base NV 318.000 NA INA NA INA INA ANIA ANIA NA INA Number of units old Base NPV NA ENIA NA ANA ENA DLA ANA ANA NA 285335X2 . H NPV Form ENA NA WA 19 10 37 10 19.c. Conducting a scenario analysis 20 Unit Sales Price 25 licenario Variable Cost Probability Sales Year 1 22 per United 25% 0 30 2 Base Cave SON 0 90 124 Worm Case 25% 0 $0 yo 25 120 Expected NPV ONIA 127 Standard Devation UNA 126 Coeficient of Variation DNIA 129 130 d. the project appears to be more or les risky than an average project, finding its risk-adjusted NPV, IRR, and payback 132 CV targe offres average-krot 0.7 ta 12 133 Low WACC TX 154 WACC 11% 135 Hohtak WAOC 15% IST Risk and WACO 130 Risk-adusid NPV 130 Poskad usted RR 10 skaduned reguler payback period (years) 141 DIA NA NA NA Excel Activity: Issues in Capital Budgeting 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 $12 million at Year Oto 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 11% of the year's projected sales, for example, NWC- 11%(Sales). The servers would sell for $25,000 per unit, and Webmasters believes that variable costs would amount to $18,000 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company's nonvariable costs would be $1 million at Year 1 and would increase with inflation The server project would have a life of 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 11% 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 15%. 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-97c102.xlsx G B 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 $12,000,000 6 Networking capital as of next year's sales 11% 7 Units sold per your 1.000 8 Sales price per unit Year 1 $25,000 9 Variable cost per unit (ex. dopr. Year 1 $18,000 10 Nonvanable costs (excl. depr.) Year 1 $1.000.000 11 Inflation rate in sales price and costs 3% 12 Market value of equipment, Year 4 $800,000 13 Tax rate 25% 14 WACC 11% 15 16 17 Intermediate Calculations 0 18 Unit sales 19 Sales price per unit 20 Variable cost per unit (excl. der 21 Nonvariable costs (excl. depr) 22 Sales revenues 23 Required level of net woning capital 24 Basis for depreciation 25 Annuni depreciation rate (MACRS) 26 Annual depreciation expense 27 Ending book value 29 sec Years 2 3 20.00% 32.00% 19 20% - 11.52% G C 26 Salvage value 29 Proft (or loss) on sale of equipment 30 Tax on profit for loss) due to sale of equipment 31 Net cash fow due to sale of equipment 332 33 Years 34 Cash Flow Forecast 0 35 Sales revenus 35 Variable costs (excl. dopr.) 37 Nonvariable costs (excepir 39 Depreciation 30 Operating income before taxes 40 Taxes on operating income 41 Net operating profit after taxes 42 Aod back depreciation 43 Equipment purchases 44 Cash flow due to change in NWO 45 Net cash flow due to sale of equipment 46. Project's net cash flows 47 48 NPV WNIA 49 RR WNA 50 51 Years 52 2 53 Net Cashow 54 Cumule net cash flow 55 Part of year required for payback 50 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 nas Balai V. Shoot + G H Sale price Year Base NPV 3:25.000 LA ANA INA NA INA NA NA NA INA C - Conducting sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit and ber of units Deviation 1 from Sale price Year 11 Base Deviation NPV Base Case from $25.000 209 Base Case 10 -20% 0% -10% 10% ON 37 20% 10% 36 204 Deviation Variable costs per unit Year 1 20 trom Deviation Base 1 NPV Base Case from 310000 204 Base Cove 3 -10 205 -0% 0% 0% 75 10 76 20% 10% 20% 77 TO Deviation Number of 79 from SD NPV from Base Case 5.000 Base Case 87 -20% 20% -10% -10% ok 14 ON 10% 104 20 2013 MT Consucting the project sensitivity graphe Deviation Vith Variables et Different Deviations from Base 09 from Sales Price Variable Cost per 00 Base Case UN Number of Units Sold -20% 50 59 $0 02 -10% $0 50 30 0% 50 10 10 54 SO 30 30 20% sal SO Variable costs per un Yeart Base NV 318.000 NA INA NA INA INA ANIA ANIA NA INA Number of units old Base NPV NA ENIA NA ANA ENA DLA ANA ANA NA 285335X2 . H NPV Form ENA NA WA 19 10 37 10 19.c. Conducting a scenario analysis 20 Unit Sales Price 25 licenario Variable Cost Probability Sales Year 1 22 per United 25% 0 30 2 Base Cave SON 0 90 124 Worm Case 25% 0 $0 yo 25 120 Expected NPV ONIA 127 Standard Devation UNA 126 Coeficient of Variation DNIA 129 130 d. the project appears to be more or les risky than an average project, finding its risk-adjusted NPV, IRR, and payback 132 CV targe offres average-krot 0.7 ta 12 133 Low WACC TX 154 WACC 11% 135 Hohtak WAOC 15% IST Risk and WACO 130 Risk-adusid NPV 130 Poskad usted RR 10 skaduned reguler payback period (years) 141 DIA NA NA NA
