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. This is the example in the PowerPoint. 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. 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 and used for the result. 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. There is also a file which contains examples of the sheet created by previous students. The examples are not to be copied and just provided to share some ideas. MicroDrive Inc Statement of Cash Flows for the Year Ending December 31, 2016 Provided (Used) Operating Activities Net Income before preferred dividends Adjustments Noncash adjustments Depreciation and amortization Due to changes in working capital Increase in accounts receivable Increase in inventories Increase in accounts payable Increase in accruals Net cash provided (used) by operating activities Investing Activities Cash used to acquire fixed assets Sale of short-term investments Net cash provided (used) by investing activities Financing Activities Increase in notes payable Increase in bonds outstanding Payment of preferred and common dividends Net cash flow from financing activities Summary Nel change in cash Cash a beginning of year Cash and of year Comparty Name Closing Date Previous Closing Date FINANCIAL STATEMENTS MicroDrive Inc 12/31/16 12/31/15 MicroDrive Inc Balance Sheet as of December 31, 2016 and 2015 in millions of dollars) MicroDrive Inc Income Statement for the Years Ending December 31, 2016 and 2015 (in millions of dollars) 12/31/16 12/31/15 ASSETS Assets Current Assets $ $ Cash and equivalents Short-term investments Accounts Receivable Inventory Tots Ne Sales COGS Other operating expenses EBITDA Depreciation and amortization EBIT (operating income) Less: Interest 2016 2015 5,000 $ 4.780 3,800 $ 3,580 500 S 480 700 $ 720 200 $ 170 500 $ 550 120 s 380 450 152 S 180 228 S 270 S8 2203 262 50 S 170 $ 214 3 1000 ,550 1 $ 1,300 EBT 3 Fixed Assets Net plant and equipment 3 2 .000 $ 1,700 3 Taxes (40%) NET INCOME Less: Preferred Stock Dividends Net Income Available to c/S Less: Common Stock Dividends Addition to Retained Earnings Total assets 3 3 ,550 $ 3.000 S 12/31/16 12/31/15 LIABILITIES & EQUITY Liabilities and Equity Current liabilities: Accounts payable Notes payable Accruals 190 130 200 280 300 780 S $ $ S Common shares (millions) Common stock price Earnings per share (EPS) Dividends per share (DPS) Book value per shares (BVPS) Cash flow per share (CFPS) 1 $ IS $ $ 3 50 50 27.00 $ 40.00 4.40 S 524 1.00 $ 0.98 29.40 $ 20.00 8 .40 $ 8.84 280 Toty 1000 Additional data Sinising fund (millions) Lease payments (millions) $ 20 $20 100 $28 500 Long term bonds Total abilities Preferred stock (1M shares) Common stock (50M shares) Retained Earnings Total Common Equity Total Equity Total Liabilities and Equity 1 200 $ 1,980 $ 100 $ 500S 970 S 1.470 1,570 $ 3.550 $ Tax rate WACC 11.0% 40% 10.5% 1300 1,400 3.000