10K Analysis -Nike final assignment

SAMPLE MEMO for FINAL Project

to:	MBA 606 Class
from:	Dr. Sean Wright
subject:	Facebook PROJECTIONS Shown on Excel Spreadsheet titled SAMPLE COURSE Project for FACEBoOK
date:	October 6, 2018

The first worksheet tab of the Excel spreadsheet I created is the creation of a common-size Income Statement. A common-size income statement is created by dividing every item on the Income Statement by revenue which is synonymous with net sales. The second worksheet tab of the Excel spreadsheet is a common-size Balance Sheet which is created by dividing every item on the Balance Sheet by total assets. The third worksheet tab of the spreadsheet contains the following required calculations:

1) Sales growth % - I took the (2017 revenue minus the 2016 revenue) and divided by the 2016 revenue to calculate the growth year over year. I used the same formula to calculate the growth in 2016 revenue over 2015 revenue. Facebook’s sales growth for 2017 over 2016 was 47.1% which was down from 54.2% growth in 2016 over 2015. As companies get bigger and bigger like Facebook, it is harder to grow sales by 50% year over year.

2) PP&E (Gross) Growth % - First, I found the gross PP&E in Note 5 to the 2017 10k. Gross PP&E is the Property, Plant, and Equipment before subtracting accumulated depreciation. To calculate this growth rate, I took the (2017 Gross PP&E minus the 2016 Gross PP&E) and divided by the 2016 Gross PP&E. Facebook PP&E (Gross) growth rate was 55.4%. Facebook is a rapidly growing company hence the large growth in PP&E.

3) AR turnover – I took the 2017 revenue and divided by the average Accounts Receivable. Average accounts receivable is found by adding the (2017 AR plus the 2016 AR ) and dividing by 2. Facebook’s AR turnover for 2017 was 8.38 meaning that they collect their average accounts receivable balance over 8 times a year.

4) Inventory turnover – Facebook does not report any inventory on their Balance sheet as they are primarily a service provider. If they had inventory, I would have taken 2017 Cost of Revenue and divided by the average inventory.

5) AP turnover – I took the 2017 Cost of Revenue and divided by the average Accounts Payable. Average accounts payable is found by adding the (2017 AP plus the 2016 AP) and dividing by 2. Facebook’s AP turnover for 2017 was 15.99.

6) Depreciation expense as a % of Gross PP& E- Note 5 to the 2017 10k contains the Depreciation Expense and the Gross PP&E. I divided the Depreciation Expense by the Gross PP&E to calculate this figure. Facebook’s depreciation expense as a % of Gross PP&E was 12.7% in 2017 down from 13.5% in 2016.

The third worksheet tab also contains footnote information for the following three items:

1) Note 8 to the 2017 10k shows that Facebook has no long-term debt.

2) Since Facebook has no long-term debt, they do not have plans to pay down long-term debt.

3) Per Note 12 to the 2017 10k, Facebook’s effective tax rate is 22.6%.

The fourth worksheet tab contains the assumptions which are linked using Excel formulas to the 2018 Forecasted Income Statement and Balance Sheet. The value of the assumptions page is the ability to perform sensitivity analysis.

The first section contains seven value drivers .

1) The primary driver is of course the sales growth %. I calculated 40% sales growth for Facebook in 2018. I decided on this figure as the growth rate dropped by 13% from 2016 to 2017 so I assumed that the growth rate would drop 15% for 2018 which would make the sales growth rate 40%.Due to the law of large numbers and recent privacy concerns with Facebook, I decided growth would decline but not by that much due to the success of Instagram. My Porter Five Forces also views Facebook as having very little direct competition, strong bargaining power with buyers and suppliers, little threat of substitution due to switching costs, and little threat of new entrants due to network externalities.

2) COGS as a % of Sales Revenue – I forecasted 20.1% for this figure. Although this figure had fallen to 13% in 2017 from 16% in 2016, I predicted a 50% increase due to the increased costs for security discussed during the 2nd quarter earnings conference call.

3) S, G, &A as a % of Sales Revenue – I forecasted 24.9% for this figure. Although this figure had fallen to 18% in 2017 from 20% in 2016, I predicted a 40% jump due to increased legal costs related to government investigations in the United States and European Union.

4) Interest Rate as a % of Long-Term Debt – This is not applicable for Facebook since they do not have long-term debt.

5) Tax Rate as a % of Pre-Tax Income – I forecasted 8.6% for this number. Based on President Trump’s tax cut, I decreased the 2017 common income statement percentage by 25%.

6) PP&E (Gross) Growth % - I forecasted 60.9% for 2018. I increased the 2017 figure by 10% due to Facebook’s growth and need for data centers.

7) Pay down of Long-Term Debt – Since Facebook does not have long-term debt, there is nothing to pay down for this item.

The next section contains four other assumptions:

1) Depreciation Expense as a % of Gross PP&E – I forecasted 13.1% which was the average of depreciation expense as a % of Gross PP&E for 2017 and 2016.

2) AR Turnover – I used 8.38 which was their most recent AR turnover.

3) Inventory Turnover – Facebook does not report inventory on their Balance sheet so I did not forecast this number.

4) AP Turnover – I used 15.99 which was Facebook’s AP turnover for 2017.

The fifth worksheet tab contains the 2018 Forecasted Income Statement. Please note that each of the cells contains the formulas I used. I linked to the Assumptions page for the Sales Growth %, COGS as a % of Sales Revenue, SG&A as a % of Sales Revenue, and Provision for Income Taxes. For simplicity, I left some items unchanged from the 2017 Income Statement. The bottom line is I forecast Sales of almost $57 billion and net income at $19 billion.

The sixth worksheet tab contains the 2018 Forecasted Balance Sheet. Please note that each of the cells contains the formulas I used. I linked to the Assumptions page for the Accounts Receivable, Property, Plant, and Equipment, and Accounts Payable values. For simplicity, I left some items unchanged from the 2017 Balance Sheet. To calculate 2018 Retained Earnings, I added 2018 projected Net Income to the 2017 Retained Earnings Balance and subtracted dividends of $0 as Facebook does not pay dividends. The bottom line is I forecast Total Assets of almost $104 billion and total liabilities at $10.5 billion.

References

Facebook 10k. (2017). Retrieved from https://s21.q4cdn.com/399680738/files/doc_financials/annual_reports/FB_AR_2017_FINAL.pdf

Facebook 2nd Quarter Earnings Conference Call (2018). Retrieved from https://www.fool.com/earnings/call-transcripts/2018/07/25/facebook-inc-fb-q2-2018-earnings-conference-call-t.aspx

5

2