Financial ratios in Excel

A webinar for Executive MBA (EMBA) students by Chris Menard. Hosted by The University of Georgia Terry College of Business on 01/28/2020

Chris Menard

Hi, I'm Chris Menard and I'll be your instructor

This webinar will be a 45-60 minute webinar, focusing on using financial ratios in Excel. We will first take a balance sheet and perform financial ratios on the balance sheet. The ratios will inclue the current ratio, debt ratio, working capital, assets-to-equity ratio, and debt-to-equity ratio. We will then analyze an income statement and use financial ratios on the income statement. They will include: gross margin, net profit margin, return on equity, and price-earnings ratios.

I'm a member of the Microsoft Creator team, and my public speaking client list includes The Georgia Society of CPAs for over 10 years, the Continuing Education Center for the University of Georgia since 2009, the Executive MBA students at the University of Georgia since 2010, and many others. In my spare time, when I’m not keeping up to date with the latest MS Office 365 features, I provide free support and create video tutorials on a variety of software. My YouTube channel is approaching 500 training videos and many of my training videos are on Microsoft's YouTube Channel.

 

01/07/2020 Webinar files and resources

2019_Annual_Report Microsoft - pages for webinar (Size: 155.5KB)
Graphic 1 - A simple balance sheet (Size: 33.1KB)
Graphic 2 - Debits and credits (Size: 22.2KB)
Ratios_balance-sheet_income statement (Size: 16.7KB)
t accounts financial analysis UGA Webinar 20200107 (Size: 71.3KB)

Watch the recording here for free!

You can watch the recording of my webinar here for free here or on my YouTube channel.

Subscribe to my newsletter below if you'd like to be notified when I post new videos or when I organize new webinars.

Some videos you might find helpful for this webinar:

Horizontal or Trend Analysis in Excel

Horizontal analysis of financial statements involves comparison of a line item over two or more accounting periods. The period can be months, quarters, or years. This method of analysis is also known as trend analysis.

Watch on YouTube

Vertical Analysis of an Income Statement

A vertical analysis is used to show the relative sizes of the different accounts on a financial statement.

Watch on YouTube

The Rule of 72 demonstrated in Excel

The rule of 72 finds the number of years to double your money at a given interest rate. Doing the math in your head is easy. Take 72 and divide by the interest rate.

Watch on YouTube

Loan Amortization in Excel

Use Excel to create a loan amortization schedule. This is a table that shows each periodic payment on an amortizing loan (typically a mortgage or car loan).

Watch on YouTube

Financial Ratios related to the Balance Sheet

Financial ratio and what it tells you How to calculate it

Working capital (WC)
Positive working capital generally indicates that a company is able to pay off its short-term liabilities almost immediately. The higher this number, the better. Working capital ties into the Current Ratio. Notice both use Current Assets and Current Liabilities.

 Current Assets – Current Liabilities 

Current Ratio
The current ratio is a liquidity ratio that measures whether a firm has enough resources to meet its short-term obligations. This ratio, like a lot of ratios, will vary by industry. For example, the food and beverage industry may have ratios different from the pharmaceutical industry.

 Current Assets / Current Liabilities 

Quick Ratio
The quick ratio differs from the current ratio in that some current assets are excluded from the quick ratio. Inventory, which is usually a significant current asset is excluded. Prepaid expenses are also excluded. Inventory may not be able to be converted into quickly into cash.

 Cash and cash equivalents + marketable securities + accounts receivable / Current Liabilities

Debt Ratio
The debt ratio measures the extent of a company’s leverage. The debt ratio is the proportion of a company’s assets that are financed by debt. This ratio tells you what percentage of the assets are owned to creditors. For this ratio, the lower the number the better.

 Total Liabilities / Total Assets 

Debt-To-Equity Ratio
The ratio is used to evaluate a company's leverage. It is a measure of the degree to which a company is financing its operations through debt versus wholly-owned funds. If total debt is $18,000 and total equit is $10,000, that means for every $1 owned by the shareholders, it owes $1.80 to creditors. For this ratio, the lower the number the better.

 Total Liabilities / Owners’ Equity 

 

Financial Ratios related to the Income Statement

Financial ratio and what it tells you How to calculate it

Gross Margin
Gross Profit is net sales - COGS. Example is $500k - $200K = $300K. Net sales are $500k, COGS is $200K, and Gross Profit is $300K. To find the Gross Margin, you take the Gross Profit and divide by net sales. 

= Gross Profit / Net Sales

= $300,000 / $500,000 = 60.0%
Profit Margin (after tax)
The percentage of net income divided by net sales or revenue. It is the bottom line margin.

= Net Income after Tax / Net Sales

= $50,000 / $500,000 = 10.0%
Earnings Per Share (EPS)
Profits divided by outstanding shares of common stock. The computation requires the deduction of preferred dividends from the net income if a corporation has preferred stock. This is an important indicator of a company's profitability. 

= Net Income after Tax / Weighted Average Number of Common Shares Outstanding

= $60,000 / 100,000 = $0.60
Times Interest Earned
Indicates a company's ability to meet the interest payments on its debt.

= Earnings for the Year before Interest and Income Tax Expense / Interest Expense for the Year

= $40,000 / $12,000 = 3.3
Return on Stockholders' Equity (after tax)
Reveals the percentage of profit after income taxes that the corporation earned on its average common stockholders' balances during the year. If a corporation has preferred stock, the preferred dividends must be deducted from the net income.

= Net Income for the Year after Taxes / Average Stockholders' Equity during the Year

= $23,000 / $278,000 (a computed average) = 8.3%

 

Financial Ratios related to the Balance Sheet and Income Statement 

Financial ratio and what it tells you How to calculate it

Accounts Receivable Turnover
The number of times per year that the accounts receivables turn over. Keep in mind that the result is an average, since credit sales and accounts receivable are likely to fluctuate during the year. It is important to use the average balance of accounts receivable during the year.

= Net Credit Sales for the Year / Average Accounts Receivable for the Year

= $500,000 / $42,000 = 11.9
Days' Sales in Accounts Receivable
The average number of days that it took to collect the average amount of accounts receivable during the year. This statistic is only as good as the Accounts Receivable Turnover figure.

= 365 days in Year / Accounts Receivable Turnover in Year

= 365 days / 11.9

= 30.67 days
Inventory Turnover
The number of times per year that Inventory turns over. Keep in mind that the result is an average, since sales and inventory levels are likely to fluctuate during the year. Since inventory is at cost (not sales value), it is important to use the Cost of Goods Sold. Also be sure to use the average balance of inventory during the year.

= Cost of Goods Sold for the Year / Average Inventory for the Year

= $380,000 / $30,000 (a computed average) = 12.67
Days' Sales in Inventory
The average number of days that it took to sell the average inventory during the year. This statistic is only as good as the Inventory Turnover figure.

= 365 days in Year / Inventory Turnover in Year

= 365 days / 12.67 = 28.81