Please find the question below and complete the three worksheets titled -'Ratio&CF', 'CommonsizeV', and 'CommonsizeH'

(find out the

values/fill out the 'green' cells using cell reference and formula). Accordingly, please answer the following 10 (TEN) questions in the spaces
provided (max 100 words for each).

This google sheet is autosaved. Once you are done, please turn in/submit by deadline.
By turning in this assignment you attest that you have not consulted anyone/taken help from anyone while completing it.

Case: Ravi Bose, an NSU business graduate with few years' experience as an equities analyst, was recently brought in as assistant to the
chairman of the board of Microdrive Industries, a manufacturer of computer components. During 2016, Microdrive had doubled its plant
capacity, opened new sales offices outside its home territory, and launched an expensive advertising campaign. Ravi was assigned to
evaluate the impact of the changes in in 2016 & 2017. In fact, a large loss occurred in 2016, rather than the expected profit. As a result, its
managers, directors, and investors are concerned about the firm’s survival. Ravi must prepare an analysis of where the company is now, what
it must do to improve its financial health, and what actions to take. Your assignment is to help him answer the following questions, using the
recent financial information shown below. Please provide clear explanations (maximum 100 words for each the following 10 questions)

What effect did the expansion have on sales and net income? What effect did the expansion have on the asset side of the balance
Q1 sheet? What effect did it have on liabilities and equity?
Due to the expansion within the company, the net sales increased by 170% in between 2015-16 and by 205% in 2016-17 while net
income decreased by 108.16% and 288.29% respectively. Because of the purchase of PP&E along with new office, the total assets
increased by 196.53% in 2015-16 and by 239.44% in 2016-17. Accordingly, total liabilities and equity also increased by 196.53% and by
239.44% within the periods 2015-16 and 2016-17 respectively. Initially after the expansion Microdrive incurred a loss of $95136 in the
year 2016 but managed to make a profit of $253584 in the year 2017 so which proves that the company made a significant amount of
improvement in the year 2017. Compared to 2016 the assets of the balance showed an increased amount, it increased from $2,886,592
to $3516952. The total liabilities and equity also increaed from 2016 to 2017.

Q2 What do you conclude from the statement of cash flows?

In the conclusion, I can say that the cash flow statement shows the decrease of the cash and cash equivalents used in operating
activities in 2017 which is 1,70,536 compare to 2016.which was 5,03,936. It also shows a huge decline in Net cash used by investing
activities 2017 which is 68,682 compare to 2016 which was 6,83,350. Then is again shows huge decline in Net cash provided by
financing activities in 2017 (2,45,936) than 2016 (11,85,568). Finally, Cash and securities at end of the year has increased in 2017
(14,000) than 2016 (7,282). So, we can interpret that the cash and securities at the end of the year have increased from $7282 to
$14000 so this can have posiitive effect on Microdrive
Q3 What can you say about the company’s liquidity position in 2015, 2016, and 2017?
 Day by day its decreasing . Its show poor performance of management.Here industry average for current ratio and quick ratio is 2.7 and
1.But here we can see current ratio 2015,2016,2017 was 2.33,1.46, 2.58 and the industry average was 2.7 which is poor than industry.
Quick ratio was below 0.85, 0.50, & 0.93 and the industry average 1. Here 2017 ratio is little bit good than industry average. The liquidity
position of the company is not in a good state as for the three years both current and quick ratio is lagging behind the industry average
which can be a concern for Microdrive.
Q4 How does Microdrive's asset management performance, esp. the utilization of assets stack up against that of other firms in its industry?
Compared to other firms where the industry average is 2.1, Microdive has had better turnover of assets to sales in 2015 at 2.34, which
decreased to 2.02 on 2016 and further decreased to 2.00 on 2017. The asset management performance of Mircrodrive shows both
positive sign and negative according to the given ratios. Like the total asset turnover was performing well initially meeting the obligations
of the industry average but again in the year 2017 it failed to meet the requirements of 2017. The same applied for fixed asset turnover
could not meet the industry average. If the utilization of assets gets stacked up the performance of asset management is expected to get
worse for Microdrive in the future. The higher the asset turnover ratio, the more efficient a company is at generating revenue from its
assets. Conversely, if a company has a low asset turnover ratio, it indicates it is not efficiently using its assets to generate sales. In 2016
the ROA is -3.30% and year 2017 the ROA is 7.21 % these ROA is below the industry average. A very less percentage ROA indicates
an inefficient use of company assets financial ratio analysis does not consider inside factors.

Q5 How does Microdrive compare with the industry with respect to financial leverage? What can you conclude from these ratios?
Compared to the industry, the company had leveraged on assets more than debt on the years 2015 & 2017; but on 2016, we can see
the company increasing its debt. Similar insight can be found from debt-to-equity ratio where we can see a sudden rise on the year 2016
because of increasing dependency over debt on that year.
Microdrive can compare the financial leverage by looking at the debt ratio, debt -equity ratio and times interest earned ratio. Debt ratio
shows that it is a good state as it is lower than the industry average for the three years.

Q6 Comment on the profitability ratios of Microdrive compared to the industry average. Also, comment on the trend that Microdrive followed.
 The company has performed poorly on the years 2015 and 2016 when compared to the industry average of 3.6. On the year 2016, the
company incurred a loss which can be seen from the profitability ratios. The profitability ratios showed a bad performance for Microdrive.
LIke for profit margin in the year 2016 Microdrive failed to meet the expectation as it incurred a loss and as a result could not meet the
expectations of industry average. and same applies for 2015 it failed to meet industry average which is a bad sign for the company, in
the year 2017 it also did not manage to meet the expectations of industry average which is not a good sign for the company. Here
industry average is 8.2% but microdrive profit margin is 7.14 % in 2017 which is higest between these 3 years which indicates poor than
industry average. On the other hand 2017 profit margin is 3.60% and profit margin also 3.60% thats show a good indication. Industry
average of gross profit margin 20% but microwave has 18% in 2017 which is higest between these 3 years which is poor. For operating
profit margin Microdrive failed to meet the industry average for three years which is a bad sign for the company. Industry average of
return on assests and return on equity are 9% & 17.90% but microdrive has return on assests 7.21% in 2017 & 13.25% in 2015 which
are higest between these 3 years but still which is poor than industry average. For both return on assets and return on equity it failed to
meet the industry average which is again a bad sign. The same applies fror Gross Profit for three years it failed to meet industry average
which is a bad sign. All these concerning factors must be taken into account.
Q7 Please refer to the 'market value ratios'. Do these ratios indicate that investors are expected to have a high or low opinion of the company?

The EPS and price-to-earnings ratio of the company are below the industry average of $2.50 and 16.2 respectively except the year,
2016, when both the ratios went negative. Whereas the price-to-cash ratio is lower than the industry average of 7.6 in the years 2015,
2016 and 2017 which are 0. The book value per share was higher than the industry average of $7.50 only in the year 2017. All these
ratios show that the company could maintain less value to its prospective and current investors; a reflection of this lesser value can be
seen from Market-to-book ratio which shows that the companies stock has been less than the industry average of 2.9 but more than 1
meaning the stock were over-valued and investors doesn’t see any value on investing in the stocks.
Q8 What happened to Microdrive’s MVA and EVA? Should Microdrive management be concerned about the trend in MVA and EVA? Explain.

A positive MVA means that the company has added value or created wealth. However, in case this value comes out to be negative then
it indicates that the company has eroded the investment value of the shareholders. And the market value of the company has come
down. However, Microdrive’s indicates really good sign as it increase year by year. In 2015-2017 MVA increases 186232, 42368.00 and
1065348 respectively. Which means that Microdrive has added value or created wealth every year which will attract the investors.
Microdrive management should not be concerned about the trend in MVA.
EVA is the profit that a company makes after reducing the cost of financing. However, Microdrive's EVA is not showing good sign over
the three year as its EVA was negative in 2016 which was -215299.20. However, in 2015 & 2017 the EVA was positive which were
11600 & 31032 respectively. Microdrive management should be concerned about the trend in EVA as it was negative in 2016.
Q9 Please refer to the common size analysis (vertical & horizontal). What do these analyses tell you about Microdrive?
 Vertical analysis translates figures in financial statements to percentages of a base figure, which has a value of 100%. Using
percentages can make the data easier to visualize and understand. It shows a larger percentage of expenses 82.44% than the industry
average which is 80% in 2017,which is not good. Depreciation and amortization 1.71% is lower than the industry average which is 2%
and it is good. Total operating expenses 92.86% is larger than the industry average which is 91 % and it is not good. Net income
available to common stockholders is below than the industry average in all three years and it is negative in 2016 which is alarming.
In horizontal analysis, we can compare figures from one time period to figures from a base time period to get an overview of changes
over time. Analyzing financial trends over periods or years can help us track how a company's financial state has changed. Microdrive's
the net sales increased by 170% in between 2015-16 and by 205% in 2016-17 while net income decreased by 108.16% and 288.29%
respectively, the total assets increased by 196.53% in 2015-16 and by 239.44% in 2016-17. Accordingly, total liabilities and equity also
increased by 196.53% and by 239.44% within the periods 2015-16 and 2016-17 respectively.
Q10 What are some potential problems and limitations of financial ratio analysis?

Financial ratios are based on accounting figures and are primarily based on assumptions, manipulation, etc. Also, they do not consider
external factors. Hence, they are not reliable.
Ratio analysis does not Benchmark to Industry Leaders' Ratios, Not Industry Averages,Companies' Balance Sheets Are Distorted By
Inflation,
Ratio Analysis Just Gives You Numbers, Not Causation Factors,Different Divisions May Need Comparison to Different Industry
Averages.Its also does not measure the human component of a firm.
One of the limitation of financial ratio analysis is that it focuses on the historic performance not on the current performance. The ratio
analysis will not take into account of the external factors like recession and inflation. The ratio analysis will not focus on the human
element of the firm. The ratio analysis can be used to compare it with firms which are of same size and type. It may be difficult to make
comparison it with other business/comanies as they won't be willing to share the information.
 MicroDrive Inc's December 31 Balance Sheets Ratio Analysis

Assets 2015 2016 2017 Liquidity Ratios

Cash and cash equivalents $ 9,000 $ 7,282 $ 14,000 Current Ratio
Short-term investments $ 48,600 $ 20,000 $ 71,632 Quick Ratio
Accounts Receivable $ 351,200 $ 632,160 $ 878,000 Asset Management Ratios
Inventories $ 715,200 $ 1,287,360 $ 1,716,480 Inventory Turnover
Total current assets $ 1,124,000 $ 1,946,802 $ 2,680,112 Receivables Turnover
Gross fixed asets $ 491,000 $ 1,202,950 $ 1,220,000 Payable Turnover
Less: Accumulated
depreciation $ 146,200 $ 263,160 $ 383,160 Inventory Days

Receivable Days (Days Sales

Net plant and equipment $ 344,800 $ 939,790 $ 836,840 Outstanding)

Total assets $ 1,468,800 $ 2,886,592 $ 3,516,952 Payable Days

Liabilities and equity Operating Cycle
Accounts payable $ 145,600 $ 324,000 $ 359,800 Net Operating Cycle
Notes payable $ 200,000 $ 720,000 $ 300,000 Fixed Assets Turnover
Accruals $ 136,000 $ 284,960 $ 380,000 Total Assets Turnover
Total current liabilities $ 481,600 $ 1,328,960 $ 1,039,800 Debt Management Ratios
Long-term bonds $ 323,432 $ 1,000,000 $ 500,000 Debt Ratio
Total liabilities $ 805,032 $ 2,328,960 $ 1,539,800 Debt-to-equity ratio
Common stock $ 460,000 $ 460,000 $ 1,680,936 Marker Debt Ratio
Retained Earnings $ 203,768 $ 97,632 $ 296,216 Liabilities-to-asset ratio
Total common equity $ 663,768 $ 557,632 $ 1,977,152 Times-interest-earned ratio
Total liabilities and equity $ 1,468,800 $ 2,886,592 $ 3,516,952 EBITDA coverage ratio
Profitability Ratios
MicroDrive Inc's December 31 Income Statements Profit Margin
2015 2016 2017 Operating Profit Margin
Net Sales $ 3,432,000 $ 5,834,400 $ 7,035,600 Gross Profit Margin

Expenses/COGS excluding
depr. and amort. $ 2,864,000 $ 4,980,000 $ 5,800,000 Return on Assets
Depreciation and
Amortization $ 18,900 $ 116,960 $ 120,000 Return on Equity
Other operating expenses $ 340,000 $ 720,000 $ 612,960 Market Value Ratios
Total operating expenses $ 3,222,900 $ 5,816,960 $ 6,532,960 Earnings per share
EBIT $ 209,100 $ 17,440 $ 502,640 Price-to-earnings ratio
Interest Expense $ 62,500 $ 176,000 $ 80,000 Cash flow per share
EBT $ 146,600 $ (158,560) $ 422,640 Price-to-cash flow ratio
Taxes $ 58,640 $ (63,424) $ 169,056 Book Value per share
Net Income available to
common stockholders $ 87,960 $ (95,136) $ 253,584 Market-to-book ratio

Other Data NOPAT

Common dividend $ 22,000 $ 11,000 $ 55,000 Op current assets
Year-end Stock Price $ 9 $ 6.00 $ 12.17 Op current liabilities

# of common shares
outstanding 100,000 100,000 250,000 Net operating working capital
EPS $ 0.88 $ (0.9514) $ 1.0143 Op long term assets
DPS $ 28.00 $ 0.11 $ 0.22 Total net operating capital
Tax rate 40% 40% 40% ROIC
WACC 10% 10% 10%
Lease Payment $ 40,000 $ 40,000 $ 40,000 Market value of equity
Total Debt $ 523,432 $ 1,720,000 $ 800,000 Book value of equity
Market cap 850 600 3042.5 MVA
Market to Book Ratio
EVA
Net investment in operating
capital
FCF=
 2016
2017
(Industry
2015 2016 2017 Average) Operating activities
2.33 1.46 2.58 2.7 Net Income -95136.00
0.85 0.50 0.93 1 Non cash adjustment
Depreciation & Amortization 116960.00
4.03 3.96 3.45 6.1 WC adjustment
9.77 9.23 8.01 10.2 Change in Accounts Receivables 280960.00
19.80 15.73 16.45 15.1 Change in Inventories 572160.00

90.55 92.19 105.83 59.8 Change in Accounts Payable 178400.00

37.35 39.55 45.55 35.8 Change in Accruals 148960.00

18.43 23.20 22.18 24.2 Net cash provided (used) by operating activities -503936.00
127.90 131.74 151.38 95.6 Investing activities
109.47 108.54 129.20 71.4 Cash used to acquire Fixed Assets -711950.00
9.95 6.21 8.41 9.5 Change in short‐term investments 28600.00
2.34 2.02 2.00 2.1 Net cash provided (used) by investing activities -683350.00
Financing activities
0.36 0.60 0.23 0.32 Change in notes payable 520000.00
0.79 3.08 0.40 0.50 Change in bonds 676568.00
0.998 1.00 0.996 0.19 Change in common stocks 0.00
55% 81% 44% 50% Payment of common and preferred dividends -11000.00
3.35 0.10 6.28 6.2 Net cash provided (used) by financing activities ###
2.61 0.81 5.52 8.1 Net cash provied (used) -1718.00
Cash and securities at beginning of the year 9000.00
2.56% -1.63% 3.60% 3.60% Cash and securities at end of the year 7282.00
6.09% 0.30% 7.14% 8.20%
17% 15% 18% 20%

5.99% -3.30% 7.21% 9%

13.25% -17.06% 12.83% 17.90%

$0.88 -$0.95 $1.01 $2.50

9.66 -6.31 12.00 16.2
$7.95 $27.49 $8.14 $1.50
0.00 0.00 0.00 7.6
$6.64 $5.58 $7.91 $7.50

1.28 1.08 1.54 2.9

125460.00 10464.00 301584.00

1075400.00 1926802.00 2608480.00
281,600.00 608960.00 739800.00

793800.00 1317842.00 1868680.00

344800.00 939790.00 836840.00
1138600.00 2257632.00 2705520.00
11.02% 0.46% 11.15%

850000.00 600000.00 3042500.00

663768.00 557632.00 1977152.00
186232.00 42368.00 1065348.00
1.28 1.08 1.54
11600.00 -215299.20 31032.00

1119032.00 447888.00
### -146304.00
 2017

253584.00

120000.00

245840.00
429120.00

35800.00

95040.00

###

-17050.00
-51632.00
-68682.00

###
###
###
-55000.00
245936.00
6718.00
7282.00
14000.00
 MicroDrive Inc's December 31 Balance Sheets MicroDrive Inc's December 31 Balan
Assets 2015 2016 2017 Assets
Cash and cash equivalents $ 9,000 $ 7,282 $ 14,000 Cash and cash equivalents
Short-term investments $ 48,600 $ 20,000 $ 71,632 Short-term investments
Accounts Receivable $ 351,200 $ 632,160 $ 878,000 Accounts Receivable
Inventories $ 715,200 $ 1,287,360 $ 1,716,480 Inventories
Total current assets $ 1,124,000 $ 1,946,802 $ 2,680,112 Total current assets
Gross fixed asets $ 491,000 $ 1,202,950 $ 1,220,000 Gross fixed asets
Less: Accumulated depreciation $ 146,200 $ 263,160 $ 383,160 Less: Accumulated depreciation
Net plant and equipment $ 344,800 $ 939,790 $ 836,840 Net plant and equipment
Total assets $ 1,468,800 $ 2,886,592 $ 3,516,952 Total assets
Liabilities and equity Liabilities and equity
Accounts payable $ 145,600 $ 324,000 $ 359,800 Accounts payable
Notes payable $ 200,000 $ 720,000 $ 300,000 Notes payable
Accruals $ 136,000 $ 284,960 $ 380,000 Accruals
Total current liabilities $ 481,600 $ 1,328,960 $ 1,039,800 Total current liabilities
Long-term bonds $ 323,432 $ 1,000,000 $ 500,000 Long-term bonds
Total liabilities $ 805,032 $ 2,328,960 $ 1,539,800 Total liabilities
Common stock $ 460,000 $ 460,000 $ 1,680,936 Common stock
Retained Earnings $ 203,768 $ 97,632 $ 296,216 Retained Earnings
Total common equity $ 663,768 $ 557,632 $ 1,977,152 Total common equity
Total liabilities and equity $ 1,468,800 $ 2,886,592 $ 3,516,952 Total liabilities and equity

MicroDrive Inc's December 31 Income Statements MicroDrive Inc's December 31 Income

2015 2016 2017
Net Sales $ 3,432,000 $ 5,834,400 $ 7,035,600 Net Sales

Expenses/COGS excluding depr. Expenses/COGS excluding depr.

and amort. $ 2,864,000 $ 4,980,000 $ 5,800,000 and amort.
Depreciation and Amortization $ 18,900 $ 116,960 $ 120,000 Depreciation and Amortization
Other operating expenses $ 340,000 $ 720,000 $ 612,960 Other operating expenses
Total operating expenses $ 3,222,900 $ 5,816,960 $ 6,532,960 Total operating expenses
EBIT $ 209,100 $ 17,440 $ 502,640 EBIT
Interest Expense $ 62,500 $ 176,000 $ 80,000 Interest Expense
EBT $ 146,600 $ (158,560) $ 422,640 EBT
Taxes $ 58,640 $ (63,424) $ 169,056 Taxes
Net Income available to common Net Income available to
stockholders $ 87,960 $ (95,136) $ 253,584 common stockholders
 Industry
roDrive Inc's December 31 Balance Sheets composite
2015 2016 2017 2017
0.61% 0.25% 0.40% 1%
3.31% 0.69% 2.04% 3%
23.91% 21.90% 24.96% 20%
48.69% 44.60% 48.81% 45%
76.53% 67.44% 76.21% 75%
33.43% 41.67% 34.69% 30%
9.95% 9.12% 10.89% 9%
23.47% 32.56% 23.79% 20%
100.00% 100.00% 100.00%

9.91% 11.22% 10.23% 10%

13.62% 24.94% 8.53% 10%
9.26% 9.87% 10.80% 8%
32.79% 46.04% 29.57% 30%
22.02% 34.64% 14.22% 20%
54.81% 80.68% 43.78% 40%
31.32% 15.94% 47.80% 40%
13.87% 3.38% 8.42% 10%
45.19% 19.32% 56.22% 68%
100.00% 100.00% 100.00%

Industry
Drive Inc's December 31 Income Statements composite
2015 2016 2017 2017
100.00% 100.00% 100.00%

83.45% 85.36% 82.44% 80%

0.55% 2.00% 1.71% 2%
9.91% 12.34% 8.71% 9%
93.91% 99.70% 92.86% 91%
6.09% 0.30% 7.14% 8%
1.82% 3.02% 1.14% 1%
4.27% -2.72% 6.01% 5%
1.71% -1.09% 2.40% 2%

2.56% -1.63% 3.60% 4%

 MicroDrive Inc's December 31 Balance Sheets
Assets 2015 2016 2017
Cash and cash equivalents $ 9,000 $ 7,282 $ 14,000
Short-term investments $ 48,600 $ 20,000 $ 71,632
Accounts Receivable $ 351,200 $ 632,160 $ 878,000
Inventories $ 715,200 $ 1,287,360 $ 1,716,480
Total current assets $ 1,124,000 $ 1,946,802 $ 2,680,112
Gross fixed asets $ 491,000 $ 1,202,950 $ 1,220,000
Less: Accumulated depreciation $ 146,200 $ 263,160 $ 383,160
Net plant and equipment $ 344,800 $ 939,790 $ 836,840
Total assets $ 1,468,800 $ 2,886,592 $ 3,516,952
Liabilities and equity
Accounts payable $ 145,600 $ 324,000 $ 359,800
Notes payable $ 200,000 $ 720,000 $ 300,000
Accruals $ 136,000 $ 284,960 $ 380,000
Total current liabilities $ 481,600 $ 1,328,960 $ 1,039,800
Long-term bonds $ 323,432 $ 1,000,000 $ 500,000
Total liabilities $ 805,032 $ 2,328,960 $ 1,539,800
Common stock (100,000 shares) $ 460,000 $ 460,000 $ 1,680,936
Retained Earnings $ 203,768 $ 97,632 $ 296,216
Total common equity $ 663,768 $ 557,632 $ 1,977,152
Total liabilities and equity $ 1,468,800 $ 2,886,592 $ 3,516,952

MicroDrive Inc's December 31 Income Statements

2015 2016 2017
Net Sales $ 3,432,000 $ 5,834,400 $ 7,035,600
Expenses/COGS excluding depr. and am$ 2,864,000 $ 4,980,000 $ 5,800,000
Depreciation and Amortization $ 18,900 $ 116,960 $ 120,000
Other operating expenses $ 340,000 $ 720,000 $ 612,960
Total operating expenses $ 3,222,900 $ 5,816,960 $ 6,532,960
EBIT $ 209,100 $ 17,440 $ 502,640
Interest Expense $ 62,500 $ 176,000 $ 80,000
EBT $ 146,600 $ (158,560) $ 422,640
Taxes $ 58,640 $ (63,424) $ 169,056
Net Income available to common stockh$ 87,960 $ (95,136) $ 253,584
 MicroDrive Inc's December 31 Balance Sheets
Assets 2015 2016 2017
Cash and cash equivalents 100.0% 80.91% ###
Short-term investments 100.0% 41.15% ###
Accounts Receivable 100.0% 180.00% ###
Inventories 100.0% 180.00% ###
Total current assets 100.0% 173.20% ###
Gross fixed asets 100.0% 245.00% ###
Less: Accumulated depreciation 100.0% 180.00% ###
Net plant and equipment 100.0% 272.56% ###
Total assets 100.0% 196.53% ###
Liabilities and equity
Accounts payable 100.0% 222.53% ###
Notes payable 100.0% 360.00% ###
Accruals 100.0% 209.53% ###
Total current liabilities 100.0% 275.95% ###
Long-term bonds 100.0% 309.18% ###
Total liabilities 100.0% 289.30% ###
Common stock (100,000 shares) 100.0% 100.00% ###
Retained Earnings 100.0% 47.91% ###
Total common equity 100.0% 84.01% ###
Total liabilities and equity 100.0% 196.53% ###

MicroDrive Inc's December 31 Income Statements

2015 2016 2017
Net Sales 100.0% 170.00% ###
Expenses/COGS excluding depr. and amort. 100.0% 173.88% ###
Depreciation and Amortization 100.0% 618.84% ###
Other operating expenses 100.0% 211.76% ###
Total operating expenses 100.0% 180.49% ###
EBIT 100.0% 8.34% ###
Interest Expense 100.0% 281.60% ###
EBT 100.0% ### ###
Taxes 100.0% ### ###
Net Income available to common stockholders 100.0% ### ###