Expert answer:Finance project of Free cash flow( Excel and Repor

Solved by verified expert:The first document That I uploaded is the instruction of this project, which is included an excel sheet part and a word document report, and the second document is an example answer for the excel sheet part, and I want this project looks like the example document but not copy the data.
fi_311_project.docx

copy_of_688d9449ec8e2793.xlsx

Don't use plagiarized sources. Get Your Custom Essay on
Expert answer:Finance project of Free cash flow( Excel and Repor
Just from \$10/Page

Unformatted Attachment Preview

FI 311 Project
Due Monday June 24 at 5:00 PM
You have just been hired by Intel in its finance division. Your first assignment is to
determine the net cash flows and NPV of a proposed new generation of mobile chips.
Capital expenditures to produce the new chips will initially require an investment of
\$1.2 billion (CapEx). The R&D that will be required to finish the chips is \$500 million
this year (operating expense). Any ongoing R&D for upgrades will be covered in the
calculation in 2a below. The product family is expected to have a life of five years. Firstyear revenues for the new chip are expected to be \$2 billion. The chip family’s revenues
are expected to grow by 20% for the second year, and then decrease by 10% for the
third, decrease by 20% for the 4th and finally decrease by 50% for the 5th (final) year of
sales. Your job is to determine the rest of the cash flows associated with this project.
Your boss, Mr. Bob Swan, has indicated that the operating costs and net working capital
requirements are similar to the rest of the company’s products. Since your Mr. Swan
hasn’t been much help, here are some tips to guide your analysis:
balance sheets for the last four fiscal years from Yahoo Finance
(finance.yahoo.com). Enter Intel’s ticker symbol (INTC) and then go to
“Financials.” Click “Annual,” to ensure you’re getting annual, instead of quarterly,
data. Next, copy and paste the income statements and balance sheets into Excel.
2. On a different sheet in Excel, start setting up the DCF for this particular project:
Set up the timeline and computation of the free cash flow in six 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.
3.
4.
5.
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.
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 with increments of \$.2 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.
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.
This will be graded on correctness and professionalism. The Excel document
should be easy for Mr. Swan to read and find the answers. He should also be able
to easily adjust the value drivers (AKA inputs or assumptions) to see their effect.
You can choose a format similar to my “Ch 9 Excel” examples, but please make it
setting up Excel documents for others to use and writing professional reports!
Income Statement
All numbers in thousands
Revenue
12/29/2018
12/30/2017
12/31/2016
12/26/2015
Total Revenue
70,848,000
62,761,000
59,387,000
55,355,000
Cost of Revenue
Gross Profit
27,111,000
43,737,000
23,608,000
39,153,000
22,767,000
36,620,000
20,676,000
34,679,000
13,543,000
13,035,000
12,685,000
12,128,000
6,750,000
7,566,000
8,636,000
7,930,000

-72,000
-72,000
-72,000
-72,000
Operating Expenses
Research Development
Non Recurring
Others
Total Operating Expenses
Operating Income or Loss
47,604,000
23,244,000
44,386,000
18,375,000
44,382,000
15,005,000
40,999,000
14,356,000
73,000
1,977,000
-2,069,000
-144,000
23,244,000
18,375,000
15,005,000
14,356,000
-459,000
-637,000
-725,000
-345,000
23,317,000
20,352,000
12,936,000
14,212,000
Income Tax Expense
2,264,000
10,751,000
2,620,000
2,792,000
Minority Interest
Net Income From Continuing Ops
21,053,000
10,316,000
11,420,000
Income from Continuing Operations
Total Other Income/Expenses Net
Earnings Before Interest and Taxes
Interest Expense
Income Before Tax
9,601,000
Non-recurring Events
Discontinued Operations

Extraordinary Items

Effect Of Accounting Changes
Other Items
Net Income
Net Income
Preferred Stock And Other
Net Income Applicable To
Common Shares

21,053,000

9,601,000

10,316,000

11,420,000

21,053,000
9,601,000
10,316,000
11,420,000
12/29/2018
12/30/2017
12/31/2016
12/26/2015
Cash And Cash Equivalents
3,019,000
3,433,000
5,560,000
15,308,000
Short Term Investments
2,788,000
1,814,000
3,225,000
2,682,000
Net Receivables
7,238,000
5,708,000
5,102,000
5,392,000
Inventory
7,253,000
6,983,000
5,553,000
5,167,000
Other Current Assets
2,646,000
2,807,000
7,754,000
2,448,000
Balance Sheet
All numbers in thousands
Period Ending
Current Assets
Total Current Assets
28,787,000
Long Term Investments
9,430,000
12,291,000
15,322,000
12,374,000
Property Plant and Equipment
48,976,000
41,109,000
36,171,000
31,858,000
Goodwill
24,513,000
24,389,000
14,099,000
11,332,000
Intangible Assets
11,836,000
12,745,000
9,494,000
3,933,000

Other Assets
4,421,000
3,215,000
2,733,000
3,642,000
Deferred Long Term Asset Charges
1,122,000
840,000
907,000
1,051,000
127,963,000
123,249,000
113,327,000
101,459,000
3,824,000
2,928,000
2,475,000
2,063,000
761,000
1,739,000
4,609,000
2,593,000
Other Current Liabilities
1,438,000
3,810,000
4,338,000
2,728,000
Total Current Liabilities
16,626,000
17,421,000
20,302,000
15,646,000
Accumulated Amortization
Total Assets
29,500,000
35,508,000
38,320,000
Current Liabilities
Accounts Payable
Short/Current Long Term Debt
Long Term Debt
25,098,000
25,037,000
20,649,000
20,036,000
Other Liabilities
Deferred Long Term Liability
Charges
Minority Interest
11,676,000
11,772,000
6,150,000
4,692,000

Negative Goodwill
Total Liabilities
53,400,000
54,230,000
47,101,000
40,374,000
Stockholders’ Equity
Misc. Stocks Options Warrants

Redeemable Preferred Stock

Preferred Stock

Common Stock
25,365,000
26,074,000
25,373,000
23,411,000
Retained Earnings
50,172,000
42,083,000
40,747,000
37,614,000
Treasury Stock
-974,000
862,000
106,000
60,000
Capital Surplus

Other Stockholder Equity
-974,000
862,000
106,000
60,000
Total Stockholder Equity
Net Tangible Assets
74,563,000
38,214,000
69,019,000
31,885,000
66,226,000
42,633,000
61,085,000
45,820,000
Intel Data
2018
0. General Info
WACC
tax bracket
revenue change (%) from the previous
year
% of costs to Sales
Initial capital expenditure
depreciation
R&D first year
NWC investment -% of next-yr sales
12.00%
10%
\$
1,200,000
500000
15%
1. FCF from operations
Revenue
Operating costs
depreciation
EBIT
NOPLAT
+ depreciation
FCF from operations
2. FCF from investments
A.
B.
\$
2,000,000.00
N/A
N/A
N/A
N/A
N/A
N/A
(1)Initial capital
i) Capital expenditure
\$
(1,200,000.00)
(2) Net Working Capital
Ending balance
Beginning balance
change in NWC
FCF from investments
\$
\$
\$
\$
301,123.53
(301,123.53)
(1,501,123.53)
FREE CASH FLOWS
\$
(1,501,123.53)
NPV
IRR
\$1,096,624.25
42.23%
what-if analysis
Data table
\$1,096,624.25
4%
6.00%
8.00%
10.00%
12.00%
14.00%
16.00%
18.00%
20.00%
\$
\$
1400000
818,049.52
709,982.32
\$
\$
\$
\$
\$
\$
\$
610,301.21
518,166.21
432,837.79
353,663.09
280,064.28
211,528.62
147,600.05
Intel Data
(USD in thousands)
2019
2020
2022
100.00%
120.00%
90.00%
80.00%
48.50%
66.91%
66.91%
66.91%
20%
20%
20%
20%
15%
15%
15%
15%
\$
\$
\$
\$
\$
\$
\$
\$
2,000,000.00
(969,992.10)
(240,000.00)
790,007.90
(76,707.03)
713,300.87
240,000.00
953,300.87
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
361,348.24
301,123.53
(60,224.71)
(60,224.71)
\$
\$
\$
\$
\$
893,076.16 \$
invest
invest
2021
2,400,000.00
(1,605,826.56)
(240,000.00)
554,173.44
(53,808.32)
500,365.12
240,000.00
740,365.12
325,213.41
361,348.24
36,134.82
36,134.82
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
776,499.94 \$
2,160,000.00
(1,445,243.90)
(240,000.00)
474,756.10
(46,097.17)
428,658.92
240,000.00
668,658.92
260,170.73
325,213.41
65,042.68
65,042.68
\$
\$
\$
\$
\$
\$
\$
\$
1,728,000.00
(1,156,195.12)
(240,000.00)
331,804.88
(32,217.11)
299,587.77
240,000.00
539,587.77
\$
\$
\$
\$
130,085.37
260,170.73
130,085.37
130,085.37
733,701.61 \$
669,673.14
NPV with changes in cost of capital and first year revenues
\$
\$
1600000
1,091,522.08 \$
968,813.87 \$
\$
\$
\$
\$
\$
\$
\$
855,623.84
750,998.90
654,099.94
564,186.15
480,601.83
402,765.13
330,158.52
\$
\$
\$
\$
\$
\$
\$
1800000
1,364,994.64 \$
1,227,645.43 \$
1,100,946.47
983,831.60
875,362.09
774,709.21
681,139.38
594,001.65
512,716.99
\$
\$
\$
\$
\$
\$
\$
2000000
1,638,467.21 \$
1,486,476.98 \$
1,346,269.10
1,216,664.29
1,096,624.25
985,232.27
881,676.93
785,238.17
695,275.46
\$
\$
\$
\$
\$
\$
\$
2200000
1,911,939.77
1,745,308.54
1,591,591.73
1,449,496.99
1,317,886.40
1,195,755.33
1,082,214.48
976,474.69
877,833.94
2023
50.00%
66.91%
20%
\$
\$
\$
\$
\$
\$
\$
\$
864,000.00
(578,097.56)
(240,000.00)
45,902.44
(4,456.97)
41,445.47
240,000.00
281,445.47
\$
\$
\$
130,085.37
130,085.37
130,085.37
\$
411,530.84
Recommendation
The company should invest in the project since
The project will result in gains.
revenues
\$
\$
\$
\$
\$
\$
\$
\$
\$
2400000
2,185,412.34 \$
2,004,140.09 \$
1,836,914.36
1,682,329.68
1,539,148.55
1,406,278.39
1,282,752.03
1,167,711.20
1,060,392.41
\$
\$
\$
\$
\$
\$
\$
2600000
2,458,884.90 \$
2,262,971.65 \$
2,082,236.99
1,915,162.38
1,760,410.71
1,616,801.45
1,483,289.58
1,358,947.72
1,242,950.88
\$
\$
\$
\$
\$
\$
\$
2800000
2,732,357.46 \$
2,521,803.21 \$
2,327,559.62
2,147,995.07
1,981,672.86
1,827,324.51
1,683,827.13
1,550,184.24
1,425,509.35
\$
\$
\$
\$
\$
\$
\$
3000000
3,005,830.03
2,780,634.76
2,572,882.25
2,380,827.77
2,202,935.02
2,037,847.57
1,884,364.68
1,741,420.76
1,608,067.83
hould invest in the project since it has a postive NPV, an IRR greater than the cost of capital
t in gains.

attachment

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
\$26
The price is based on these factors:
Number of pages
Urgency
Basic features
• Free title page and bibliography
• Unlimited revisions
• Plagiarism-free guarantee
• Money-back guarantee
On-demand options
• Writer’s samples
• Part-by-part delivery
• Overnight delivery
• Copies of used sources
Paper format
• 275 words per page
• 12 pt Arial/Times New Roman
• Double line spacing
• Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.