Part 1

Instructions

In this assignment, you will prepare a report that includes the following: (1) financial information for a company, (2) calculations for a set of financial ratios, and (3) a pro forma balance sheet and income statement with the above data.

Part 1

Collect the following financial information from NIKE Inc. for the fiscal years 2017, 2018 and 2019 from their website (here) or SEC Edgar (here) and use it to populate the sheet below in Excel.

(MILLIONS OF DOLLARS)201720182019

Balance sheet

Cash and Cash Equivalents

Accounts Receivables

Inventories

Total Current Assets

Property, Plant and Equipment, Net

Total Assets

Total Current Liabilities

Long-term debt

Total Liabilities

Total Stockholders' Equity

Income Statement

Sales

Cost of Goods Sold

Gross Profit

Interest Expense

Net Income

Cash Flow Statement

Cash provided (used) by Operating Activities

Cash provided (used) by Investing Activities

Cash provided (used) by Financing Activities

Part 2

Calculate the following financial ratios for the fiscal years 2018 and 2019 using the financial data collected above:

ROA

ROE

Profit Margin

Asset Turnover

Current Ratio

Leverage Ratio

Part 3

Based on the financial data collected in Part 1, as well as the accounting information in the following table, complete the task that follows.

(MILLIONS OF DOLLARS)201720182019

Short-term debt6175

Other liabilities6,2477,918

Depreciation expense269277288

Operating expense (excl depreciation)10,74413,69213,108

An analyst attempts to determine the value of NIKE Inc. at the end of fiscal year 2019. The first step of the analysis is to construct forecasted financial statements over the next three fiscal years on a pro forma basis. After careful examination of the company information, the analyst makes the following assumptions:

Sales growth will be -1% from 2019 to 2020 but will revert to the normal level of 8% per annum from 2020-2021 onwards.

Gross profit will remain at the normal 45% of total sales over the next three years.

Depreciation expenses will be 6% of net balance of property, plant, and equipment at fiscal year-end.

Operating expenses will account for 40% of total sales in the fiscal years 2019-2020 but will fall to 32% from 2020-2021 onwards.

Interest expenses will be constant at $50 million per year.

The company will normally maintain cash and cash equivalent balance of 11% of total sales. However, the expected downturn over the fiscal year 2019-2020 will force the company to drain its cash balance, so this ratio will drop temporarily to 6% over the year.

Accounts receivable turnover will be 7 for the fiscal year 2019-2020 but will revert to the normal level of 9 from 2020-2021 onwards.

Inventory turnover will be constant at 3.85 over the next 3 years, where inventory turnover is defined as cost of goods sold divided by inventories.

On average, a $1 net balance of property, plant and equipment is required to support $8 of sales, and $1 worth of other current assets are required to support $18 of sales.

On average, the company borrows short-term debts equivalent to 0.2% of sales, and each dollar of sales transaction typically leads to the incurrence of 20 cents of other liabilities.

Long-term debt will be frozen at its 2019 fiscal year amount.

Task

Complete the pro forma balance sheet and income statement for NIKE over the next three fiscal years in the spreadsheet below, using the assumptions listed above.

(MILLIONS OF DOLLARS)202020212022

Balance sheet

Cash and Cash Equivalents

Accounts Receivables

Inventories

Other current Assets

Property, Plant and Equipment, Net

Short-term debt

Long-term debt

Other liabilities

Total Stockholders' Equity

Income Statement

Sales

Cost of Goods Sold

Gross Profit

Depreciation expense

Operating expense (excl. depreciation)

Interest Expense

Net Income

Professional analysts consensus earnings per share forecast for Nike Inc. for the year 2020-2021 is $2.21. Compare your earnings forecast with the analysts consensus and discuss the key factors that make the forecast produced in Task A different from this consensus. Do you think the assumptions underlying your forecasts in Task A are more reasonable than the analysts? Explain why. Note the number of shares outstanding at the date of forecast is about 1,618,400,000.

Discuss whether you think the statistical forecasting methods introduced in Week 3 can outperform the pro forma forecasting in this case. Explain why.