YOU ARE SUPPOSED TO MAKE THE EXCEL SPREADSHEET......

For this assignment you will use the attached Excel spreadsheet to build a one sheet model applying the five step process to calculate MicroDrive Inc.'s FCF, the uses of FCF, and other performance measures. Your spreadsheet should contain the following sections:

1) FCF: consisting of calculations for NOPAT, NOWC, TOC, NIOC, and FCF.

2) Use of FCF

3) Performance evaluation: ROIC, OP, CR, MVA, and EVA.

FCF= FREE CASH FLOW

NOPAT= NET OPERATING PROFIT AFTER TAXES

NOWC= NET OPERATING WORKING CAPITAL

TOC= TOTAL OPERATING CAPITAL

NIOC= NET INVESTMENT IN OPERATING CAPITAL

ROIC= RETURN OF INVESTED CAPITAL

OP= OPERATING PROFITABLITY RATIO

CR=CAPITAL REQUIREMENT RATIO

MVA= MARKET VALUE ADDED

EVA= ECONOMIC VALUE ADDED

SHOW FORMULAS AND ANSWERS

The model sheet must display each component of the formula. Columns and rows should be clearly labeled showing the equation for each formula. Each factor of the equation should be presented separately. For example the OP Ratio = NOPAT / Sales. When presenting, you will create a cell which shows NOPAT, one that shows Sales, and one for the result. All original numbers used should reference a cell from the financial statements tab. Please view the attached scoring rubrics for the assessment.

	FINANCIAL STATEMENTS
Company Name	MicroDrive Inc
Closing Date	12/31/2016
Previous Closing Date	12/31/2015

MicroDrive Inc
Balance Sheet as of December 31, 2016 and 2015
(in millions of dollars)
	12/31/2016	12/31/2015
	ASSETS
Assets
Current Assets:
Cash and equivalents	$ 50	$ 60
Short-term investments	$ -	$ 40
Accounts Receivable	$ 500	$ 380
Inventory	$ 1,000	$ 820
Total	$ 1,550	$ 1,300
Fixed Assets:
Gross Plant & Equipment	$ 2,200	$ 1,870
Less: Depreciation	$ 200	$ 170
Net plant and equipment	$ 2,000	$ 1,700
Total assets	$ 3,550	$ 3,000
	12/31/2016	12/31/2015
	LIABILITIES & EQUITY
Liabilities and Equity
Current liabilities:
Accounts payable	$ 200	$ 190
Notes payable	$ 280	$ 130
Accruals	$ 300	$ 280
Total	$ 780	$ 600
Long-term bonds	$ 1,200	$ 1,000
Total Liabilites	$ 1,980	$ 1,600
Preferred stock (1M shares)	$ 100	$ 100
Common stock (50M shares)	$ 500	$ 500
Retained Earnings	$ 970	$ 800
Total Common Equity	$ 1,470	$ 1,300
Total Equity	$ 1,570	$ 1,400
Total Liabilities and Equity	$ 3,550	$ 3,000

MicroDrive Inc
Income Statement for the Years Ending December 31, 2016 and 2015
(in millions of dollars)
	2016	2015
Net Sales	$ 5,000	$4,760
COGS	$ 3,800	$3,560
Other operating expenses	$ 500	$ 480
EBITDA	$ 700	$ 720
Depreciation and amortization	$ 200	$ 170
EBIT (operating income)	$ 500	$ 550
Less: Interest	$ 120	$ 100
EBT	$ 380	$ 450
Taxes (40%)	$ 152	$ 180
NET INCOME	$ 228	$ 270
Less: Preferred Stock Dividends	$ 8	$ 8
Net Income Available to C/S	$ 220	$ 262
Less: Common Stock Dividends	$ 50	$ 48
Addition to Retained Earnings	$ 170	$ 214
Per share data
Common shares (millions)	50	50
Common stock price	$ 27.00	$40.00
Earnings per share (EPS)	$ 4.40	$ 5.24
Divdidends per share (DPS)	$ 1.00	$ 0.96
Book value per shares (BVPS)	$ 29.40	$26.00
Cash flow per share (CFPS)	$ 8.40	$ 8.64
Additional data
Sinking fund (millions)	$20	$20
Lease payments (millions)	$28	$28
Tax rate	40%	40%
WACC	11.0%	10.5%

MicroDrive Inc
Statement of Cash Flows for the Year Ending December 31, 2016
		Cash Provided (Used)
Operating Activities
Net Income before preferred dividends		$ 228
Adjustments
Noncash adjustments
Depreciation and amortization		$ 200
Due to changes in working capital
Increase in accounts receivable		$ (120)
Increase in inventories		$ (180)
Increase in accounts payable		$ 10
Increase in accruals		$ 20
Net cash provided (used) by operating activities		$ 158
Investing Activities
Cash used to acquire fixed assets		$ (500)
Sales of short-term investments		$ 40
Net cash provided (used) by investing activities		$ (460)
Financing Activities
Increase in notes payable		$ 150
Increase in bonds outstanding		$ 200
Payment of preferred and common dividends		$ (58)
Net cash flow from financing activities		$ 292
Summary
Net change in cash		$ (10)
Cash at beginning of year		$ 60
Cash at end of year		$ 50