Excel - Ratio Analysis


Ratio Analysis  

Ratio analysis is a way of comparing various aspects of a business’s finances as a way of testing such things as a business’s efficiency, liquidity, profitability and solvency.  Ratio analysis is more meaningful when it is compared to similar companies in the same industry.  A publication often used by corporate financial officers, managers, investors and creditors to compare ratios of like businesses is the Dunn & Bradstreet publication Industry Norms and Key Business Ratios.

Move your cursor over any of the following colored blocks to view the ratio formula.
Acid-Test (Quick Ratio) Cash + Accts Receiv + Short-term Investments
Current Liabilities
Current Ratio Current Assets
Current Liabilities
Average Collection Period Accounts Receivable
(Net Sales / 365)
Debt Ratio Total Liabilities
Total Assets
Debt to Equity Total Liabilities
Stockholders Equity
Gross Profit Margin Gross Profit
Net Sales
Return On Equity Net Earnings
Stockholders Equity
Earnings per Common Share Net Earnings
Average Common Share Outstanding
Price to Earnings Market Price of Common Stock
Earnings per Share
Market Capitalization
Shares Outstanding * Stock Price
Net Profit Margin Net Income
Net Sales
Return on Investment Net Earnings
Total Assets

Liquidity Ratios

Current Position Analysis uses Liquidity Ratios  such as Working Capital, Current Ratio and  Acid-test Ratio.  Liquidity ratios measure the  ability of a company to meets its currently maturing debts determined from the balance sheet.  Liquidity ratios are especially of interest to short-term creditors.

Working Capital and Current Ratio

Working Capital is the amount left over when taking a businesses current assets minus its current liabilities at a particular point in time.  This is a measure of a company to meet its current obligations.  Although Working Capital is a good measurement for a company making intraperiod comparisons it has little value when comparing companies of different sizes or when comparing it to other industries.

A lending institution would look carefully at a business's Current Ratio which compares a company's current assets to current liabilities.   The Current Ratio is also known as working capital ratio or bankers' ratio. The general rule is that a Current Ratio should be at least 2:1.   Current ratio is  a more meaningful indication of a company's solvency to a lending corporation than a company with a large Working Capital.

Working Captial = Current Assets - Current Liabilities
Current Ratio = Current Assets / Current Liabilities

Display of Working Capital and Current Ratio

Acid Test Ratio

formula for acid ratio

The Acid Test Ratio also known as the Quick Ratio is a stronger measure of liquidity because it only uses cash and those items that can be quickly converted to cash from the Current Assets whereas the Current Ratio could include items that would be difficult to convert to cash.

Accounts Receivable Analysis also known as Asset Management Ratios

Average Collection Period

The Average Collection Period is the number of days it takes a business to convert its Account Receceivables into cash.  Another words this would be the average number of days between the date a credit sales is made and the day it is collected.

Number of Days = Accounts Receivable / (Net Sales / 365)

In order for a company to determine how well they are doing they can compare their Average Collection Period to their company's credit policy.  If the company's credit policy is that accounts are payable within 30 days and their Average Collection Period is 50 days then the company will have to review their credit policy or do a study to determine why payments are being made on time.

A company can compare its current Average Collection Period with previous years to determine if customers are taking longer to pay back on their accounts or if they are paying them back quicker.  Of course the smaller the number of days the better because that means less time that the company's funds will be tied up in receivables and will now be available for other uses.

Since a company's Accounts Receiveable may vary widely throughout the year because of increases in seasonal sales it is usually best to determine the Average Collection Period over an annual period.

Average Collection Period computation

Leverage Ratios also called Debt Ratios

A company can improve its profitability by wisely taking on debt to make improvements to its business but it must be carefult not to take on too much debt.  The Debt Ratio and Debt to Equity Ratio identify companies that are highly leveraged and therefore a higher risk for investors.  Two groups have claims against a company; its creditors and its stockholders.  Since creditors have first claim to a company's assets stockholder's are of course concerned how much debt is owed to them.

Debt Ratio

Debt Ratio = Total Debt / Total Assets

Compares a company's total debt to its total assets

Debt to Equity Ratio

Debt to Equity  =   Total Liabilities /  Stockholders Equity

If a company has total liabilies of $150,000 and a stockholder's equity of $100,000 this would mean that the company has a debt of $1.50 for every dollar of equity it has. 

Investment Valuation Ratios

Price to Earnings Ratio

Formula for Price to Earnings ratio

 The Price to Earnings ratio is also well known as the P/E ratio.  It is the most well-known investment ratio.  It compares the current price of a company's shares to the amount of earnings it generates.  The purpose of this ratio is to give users a quick idea of how much they are paying for each $1 of earnings.  Investors often look at the P/E ratio to judge the companies future earnings.  The higher the P/E ratio usually means that investors expect the company will have higher future earnings.


Profitability Indicator Ratios

By looking at the Profitability Indicator Ratios one can see if the company is making money, if its profitability is on an uphill or downhill swing and how profitable it is compared to its competitors.

Gross Profit Margin

formula for Gross Profit Margin

Net Profit Margin

formula for Net Profit Margin

Return on Investment

formula for Return on Investment

Return on Equity

Rate of Return on Stockholders' Equity

Formula for Return on Equity ratio

The Return on Equity ratio tells common shareholders how well the company has done with the amount they have invested.


Market Capitization

Market Capitalization

 Market Capitalization = Shares Outstanding * Stock Price

Market Capitalization is the total dollar market value of all of a company's outstanding shares.
A company that has 9000 shares outstanding, with each share having a market value of $100, would have a market capitalization of $900,000 (1,000 x $100 per share).
The Investment world categorizes companies by their size using the term "market cap"
There are three categories of company sizes Large Cap, Mid Cap, and Small Cap.  There is some descrepency in the cutoff value for what is a Large Cap, Mid Cap and Small Cap.
Large Cap companies are usually those who have a market capitalization that is greater than 10 billion.  Mid Cap companies have 2 to 10 billion and Small Cap are below 2 billion.

The worksheet below shows the Ratio Analysis along with the Income Statement and Balance Sheet for Professor Office for 2014. The worksheet below that shows the same thing except it shows the Ratio formumlas rather than the result of those formulas.

Ratio Analysis for Financial Statements


The following image displays all the formulas used in the Ratio Analysis.

Ratio Analysis showing Formulas