Excel and report

Set up the timeline and computation of the free cash flow in five columns.

(Cost of Revenue + SG&A + R&D)/Total Revenue

You should assume that this ratio will hold for this project as well. You do not need to break out the individual components of operating costs in your forecast. Simply forecast the total of Cost of Revenue+SG&A+R&D for each year.

• Determine the IRR of the project and the NPV of the project at a cost of capital of 12% using the Excel functions.
• Build a Data Table in Excel to show how the NPV changes for a range of discount rates (4% to 20% with increments of 2%) and for a range of first-year revenues ($1.4 billion to $3 billion).Remember that your Excel document must not be hard-coded for the Data Table to work.
• Write a one-page (1.5 spaced 12 pt font) report on your recommendation for this project.This should be a professional email to Mr. Swan on the profitability of the new mobile chips.Set up
  the timeline and computation of the free cash flow in five columns.

  a. Assume
  that the project’s profitability will be similar to Intel’s existing projects
  in 2018 and estimate costs each year by using the 2018 ratio of
  non-depreciation costs to revenue:

  (Cost of Revenue
  + SG&A + R&D)/Total Revenue

  You
  should assume that this ratio will hold for this project as well. You do not
  need to break out the individual components of operating costs in your
  forecast. Simply forecast the total of Cost of Revenue+SG&A+R&D for
  each year.

  b. Determine
  the annual depreciation by assuming Intel depreciates these assets by the
  straight-line method over a 5-year life.

  c. Determine
  Intel’s tax rate as in 2018.

  d. Calculate
  the net working capital required each year by assuming that the level of NWC
  will be a constant percentage of the project’s sales. Use Intel’s 2018 to
  estimate the required percentage. (Use only accounts receivable, accounts
  payable, and inventory to measure working capital. Other components of current
  assets and liabilities are harder to interpret and are not necessarily
  reflective of the project’s required NWC—e.g., Intel’s cash holdings.)

  e. To
  determine the free cash flow, calculate the additional capital
  investment and the change in net
  working capital each year. Note that in
  part 2d, you estimated NWC for the project, not change in NWC.

  2.
  Determine the IRR of the
  project and the NPV of the project at a cost of capital of 12% using the Excel
  functions.

  3.
  Build a Data Table in
  Excel to show how the NPV changes for a range of discount rates (4% to 20% with
  increments of 2%) and for a range of first-year revenues ($1.4 billion to $3
  billion). Remember that your Excel
  document must not be hard-coded for the Data Table to work.

  4.
  Write a one-page (1.5
  spaced 12 pt font) report on your recommendation for this project. This should be a professional email to Mr.
  Swan on the profitability of the new mobile chips.

• The first paragraph should state your recommendation and report the most important numbers, including the base case NPV and IRR and the NPV with different inputs from step 4.
  The second paragraph should discuss what you did for this analysis.Include the details on estimating costs, tax rates, and NWC.You should say to “see my attached Excel document for calculations.”The third paragraph should conclude with a summary and an offer to answer any questions.