For this assignment you will convert financial statements into common size and percentage change format, and perform a ratio analysis. You should begin with the spreadsheet used for Chapter 2's Build-a-model assignment. If you did not complete Chapter 2 assignment, you must use the attached excel file to begin. The following steps should be completed:

1) Build a sheet displaying the 2015 and 2016 balance sheet and income statement in common size format. You will also include a column for the industry composite information.

2) Build a sheet displaying the 2016 balance sheet and income statement in percentage change format.

3) Build a sheet to compute the ratios presented in the chapter for the years presented. The ratios should be grouped according to the five categories presented in the textbook. Your spreadsheet will also include a section for the industry average.

4) Conduct a financial ratio analysis. This will include comparing the ratios over the two years and comparing to the industry average. Your analysis will entail rating the performance and a justification for each rating.

The assignments requires the creation of three separate spreadsheets with each appropriately named. All of the spreadsheets should be directly linked to the original financial data on the financial statements sheet. The only data that is acceptable for manual enter is the industry composite and industry average information which is provided in the chapter excel tool kit spreadsheet.

Formulas and equations should display each component of the formula: numerator and denominator; including the name and amount. For example Current Ratio = Current Assets / Current Liabilities. You will display in separate cells, the equation and the formula using the amount for current assets and current liabilities, along with the result for the current ratio. The cells which contain the numerator and denominator must be used in the calculations for the result. Although not required, you can display each factor included in the numerator and denominator. For example, current assets consists of cash, short-term investments, account receivables, and inventory. Unlike the instructions for Chapter 2, you are not required to display each account separately. However, I highly recommend being as detailed as possible if you are not familiar with the components of the equations.

Columns and rows should be clearly labeled. You should have a column for each year and the industry. For the ratio analysis, each ratio should have a performance rating, such a strong, weak, fair, poor, etc., and provide a justification for each rating.

Points will be deducted if all requirements are not met. Show formula's used in excel as well as answers.

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%

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