Need help with parts a through d.

Scenario Summary Current Values Base Best Worst Changing Cells: $D$29 1,000 1,000 1,200 800 $D$30 $24.00 $24.00 $28.80 $19.20 $D$31 $17.50 $17.50 $14.00 $21.00 Result Cells: $D$79 $3,463 $3,463 $25,434 ($11,991) $D$80 21.09% 21.09% 76.96% #NUMI $D$81 16.99% 16.99% 43.42% -41.39% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Vebmasters.com has developed a poverful new server that would be used for corporations Internet activities. It would cost $10 million to buy the equipment necessary to manufacture the server, and it would require net working capital equal to 10% of sales. The servers vould sell for $24,000 per unit, and Vebmasters believes that variable costs would amount to $17,500 per unit. After the first year, the sales price and variable costs would increase at the inflation rate of 3%. The company's fized costs would be $1 million per year, and would increase with inflation. It would take one year to buy the required equipment and set up operations, and the server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. estimated market value of the equipment at the end of the project's 4-year life is $500,000. Vebmasters' federal-plus-state tax rate is 40. Its cost of capital is 10% for average risk projects, defined as projects with a coefficient of variation for NPY between 0.8 and 1.2. Low risk projects are evaluated with a VACC of 8%, and high risk projects at 137. a. Develop a spreadsheet model and use it to find the project's NPY. IRR. and payback.. Part 1. Input Data (in thousands of dollars) Key Outp NPY - IRR = MIRR = $0 0.0% 0.0% Equipment cost Net Operating VC/sales First year sales (in units) Sales price per unit Variable cost per unit Fized costs $10.000 10% 1.000 $24.00 $17.50 $1,000 Market value of equipment in t Tax rate YACC Inflation $500 40% 10% 3.0% Part 2. bepreciation and Amortization Year Initial Cos Equipment Depr'n Rate 1 20.0% 2 32.07 3 19.07 4 12.07 Part 3. Net Salvage Values, in Year + Equipment Part 4. Projected Net Cash Flows (Time line of annual cash flows) Years Investment Puilags ar Time Zero: 0 1 N 3 + Operating Cash Flows over the Project's Life: Terminal Year Cast Flors- Required level of net operating working capital Required investment in NOVC Terminal Year Cast Flors Net salvage value Net Cash Flow (Time line of cash floys) Part 5. Key Output Net Present Value (at 10%) IRR MIRR Payback (See calculation below) b. Noy conduct a sensitivity analysis to determine the sensitivity of NPY 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. Include a graph in your Part 6. Evaluating Risk: Sensitivity Analysis 1. Sensitivity of NPY to Changes in Inputs. Here we use an Excel Data Table to find NPY different unit sales, holding other thing constant. VACC NPY Deviatiot YEAR UNIT SALE from Units NPY Base Cas Sold -20% 800 -10% 900 1,000 107 1.100 202 1,200 z Deviatio: from Base Casel -20% -10% 0% 10% 20% 0% VACC 8.0% 9.0% 10.07 11.0% 12.02 Deviatic VARIABLE COSTS from Variable NPY ase Cas Costs -20% $14.00 -10% $15.75 0% $17.50 10% $19.25 20% $21.00 z Deviatior! from Base Case -20% -10% 0% 10% 20% SALES PRICE Sales NPY Price $19.20 $21.60 $24.00 $26.40 $28.80 Deviatid FIXED COSTS from Fized NPY ase Cas Costs -20% $800 -10% $900 0% $1,000 10% $1,100 20% $1.200 Deviation from Base Cas -20% -10% 0% 10% 20% NPY at Different Deviations from Base Sales Variable Fized Price Cost/Unit Units Sold Cost VACC $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Range 0 0 0 0 0 c. Nor conduct a scenario analysis. Assume that there is a 25% 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 25% probability of worst case conditions. Part 7. Evaluating Risk: Scenario Analysis Squared Deviation Times NPY Probability Scenario Sales Probability Price Unit Sales Variable Costs Best Case Base Case Vorst Case 25% 50% 25% $24.00 1,000 $17.50 Expected NPY - sum, prob times NPY Standard Deviation - Sq Root of column H sum Coefficient of Variation - Std Dev / Expected NPY d. If the project appears to be more or less risky than an average project, find its risk- adjusted NPY, IRR. and payback. Vith the high CY, we must re-evaluate the project using a higher VACC, 13%. That results in: sk adjusted NPY - IRR = Payback - Scenario Summary Current Values Base Best Worst Changing Cells: $D$29 1,000 1,000 1,200 800 $D$30 $24.00 $24.00 $28.80 $19.20 $D$31 $17.50 $17.50 $14.00 $21.00 Result Cells: $D$79 $3,463 $3,463 $25,434 ($11,991) $D$80 21.09% 21.09% 76.96% #NUMI $D$81 16.99% 16.99% 43.42% -41.39% Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Vebmasters.com has developed a poverful new server that would be used for corporations Internet activities. It would cost $10 million to buy the equipment necessary to manufacture the server, and it would require net working capital equal to 10% of sales. The servers vould sell for $24,000 per unit, and Vebmasters believes that variable costs would amount to $17,500 per unit. After the first year, the sales price and variable costs would increase at the inflation rate of 3%. The company's fized costs would be $1 million per year, and would increase with inflation. It would take one year to buy the required equipment and set up operations, and the server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. estimated market value of the equipment at the end of the project's 4-year life is $500,000. Vebmasters' federal-plus-state tax rate is 40. Its cost of capital is 10% for average risk projects, defined as projects with a coefficient of variation for NPY between 0.8 and 1.2. Low risk projects are evaluated with a VACC of 8%, and high risk projects at 137. a. Develop a spreadsheet model and use it to find the project's NPY. IRR. and payback.. Part 1. Input Data (in thousands of dollars) Key Outp NPY - IRR = MIRR = $0 0.0% 0.0% Equipment cost Net Operating VC/sales First year sales (in units) Sales price per unit Variable cost per unit Fized costs $10.000 10% 1.000 $24.00 $17.50 $1,000 Market value of equipment in t Tax rate YACC Inflation $500 40% 10% 3.0% Part 2. bepreciation and Amortization Year Initial Cos Equipment Depr'n Rate 1 20.0% 2 32.07 3 19.07 4 12.07 Part 3. Net Salvage Values, in Year + Equipment Part 4. Projected Net Cash Flows (Time line of annual cash flows) Years Investment Puilags ar Time Zero: 0 1 N 3 + Operating Cash Flows over the Project's Life: Terminal Year Cast Flors- Required level of net operating working capital Required investment in NOVC Terminal Year Cast Flors Net salvage value Net Cash Flow (Time line of cash floys) Part 5. Key Output Net Present Value (at 10%) IRR MIRR Payback (See calculation below) b. Noy conduct a sensitivity analysis to determine the sensitivity of NPY 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. Include a graph in your Part 6. Evaluating Risk: Sensitivity Analysis 1. Sensitivity of NPY to Changes in Inputs. Here we use an Excel Data Table to find NPY different unit sales, holding other thing constant. VACC NPY Deviatiot YEAR UNIT SALE from Units NPY Base Cas Sold -20% 800 -10% 900 1,000 107 1.100 202 1,200 z Deviatio: from Base Casel -20% -10% 0% 10% 20% 0% VACC 8.0% 9.0% 10.07 11.0% 12.02 Deviatic VARIABLE COSTS from Variable NPY ase Cas Costs -20% $14.00 -10% $15.75 0% $17.50 10% $19.25 20% $21.00 z Deviatior! from Base Case -20% -10% 0% 10% 20% SALES PRICE Sales NPY Price $19.20 $21.60 $24.00 $26.40 $28.80 Deviatid FIXED COSTS from Fized NPY ase Cas Costs -20% $800 -10% $900 0% $1,000 10% $1,100 20% $1.200 Deviation from Base Cas -20% -10% 0% 10% 20% NPY at Different Deviations from Base Sales Variable Fized Price Cost/Unit Units Sold Cost VACC $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Range 0 0 0 0 0 c. Nor conduct a scenario analysis. Assume that there is a 25% 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 25% probability of worst case conditions. Part 7. Evaluating Risk: Scenario Analysis Squared Deviation Times NPY Probability Scenario Sales Probability Price Unit Sales Variable Costs Best Case Base Case Vorst Case 25% 50% 25% $24.00 1,000 $17.50 Expected NPY - sum, prob times NPY Standard Deviation - Sq Root of column H sum Coefficient of Variation - Std Dev / Expected NPY d. If the project appears to be more or less risky than an average project, find its risk- adjusted NPY, IRR. and payback. Vith the high CY, we must re-evaluate the project using a higher VACC, 13%. That results in: sk adjusted NPY - IRR = Payback