CI Market Dashboard

We have put together a collection of market indicators and track them to help you gauge the direction of the market.


CI Analysis Worksheets

Interactive analysis templates covering DuPont analysis (return on equity), valuing stocks the Warren Buffett way, and more to come.


CI Blog Feed

TipRanks Launches Website
posted 2 days ago by Jaclyn McClellan

CI Editor Speaking on East Coast
posted 6 days ago by Wayne Thorp

Market Dashboard: One New Neutral, Bearish Signal This Week
posted 6 days ago by Wayne Thorp

Bullish Sentiment Sees Biggest 2-Week Decline in Over a Year
posted 10 days ago by Wayne Thorp

Apple Unveils New Apple Watch and Two New iPhones
posted 12 days ago by Jaclyn McClellan



Computerized Investing > July 19, 2014

CI’s Financial Ratio Analysis Spreadsheet

PRINT | | | | COMMENTS (10) | A A   Reset

by Jaclyn McClellan

For most investors, examining a company’s financial statements front to back is not only time-consuming, but overwhelming. Financial ratios seek to minimize this tedious task by allowing investors to work through financial statement figures in an organized fashion and draw conclusions based on how certain figures relate to one another.

Stock investors are primarily looking for future growth in cash flow and earnings. Fundamental investors examine variables that might significantly impact a firm’s financial structure, sales, earnings production and dividend policy. By looking through a company’s financial ratios, investors can piece together an estimate about the company’s overall “health” to determine if it is an investment worth undertaking. CI offers a financial ratio analysis spreadsheet that aids in collecting the needed data and calculating the ratios. Five basic financial ratio categories are addressed in this spreadsheet: liquidity, efficiency, leverage, coverage and profitability. Note that the spreadsheet will not work correctly for evaluating financial companies.

The spreadsheet was created in Excel 2013 (Office 365). If you do not have this version of Excel, the spreadsheet may not open correctly. Support for Office 2003 has ended and we recommend users of Office 2003 update their software. Another option is to install an Office Compatibility Pack, which will allow you to open 2013, 2010 and 2007 Office System documents in Office 2003, Office XP, or Office 2000.

The Spreadsheet

The CI Ratio Analysis Spreadsheet contains six different tabs: Ratio Analysis, Financials, Charts, Income Statement, Balance Sheet and Cash Flow Statement. The Income Statement, Balance Sheet and Cash Flow Statement tabs require user input by copying and pasting data from Morningstar.com. The rest of the tabs are populated accordingly.

The Financials tab is a condensed version of the financial statement data that is imported from Morningstar.com. This page is also used for some of the calculations on the Charts tab. Nothing on the Financials tab requires manual input. The dates for each period across the top of the spreadsheet are pulled from the Income Statement tab, so they will automatically correspond to the five years of annual data you input from Morningstar. The “Year Over Year Change” section of the Financials tab calculates rate of change using the formula below. For example, the revenue growth calculation in cell C29 of the Financials tab uses the formula:

=IF(C3>0 & B3>0,((C3/B3)-1), "na")

The “IF” part of this formula ensures that the two figures used in the computation are positive, since Excel can’t correctly compute a growth figure if there are negatives or zero. When zero or negative figures are present in the equation, the cell will display an “na.” This way investors aren’t viewing distorted figures. The charts corresponding to these calculations will also not be meaningful if “na” values are returned here.

Importing From Morningstar

Once you choose a company you want to evaluate, visit Morningstar.com. In the quote search box at the top (Figure 1), type in the company’s ticker and click “quote.” This brings you to the company’s summary page. Next, click the Financials tab, located on the row of choices in between Key Ratios and Valuation (Figure 2). The Income Statement subtab is opened by default. Check the drop-down boxes at the top of the data and make sure the statement type box says annual, the period box shows 5 years, the “show report dates” box says ascending, and the dollar sign is highlighted for the view. Click the Export button located to the far right of these options above the first row of data.

If you use Google Chrome, you will see an Income Statement.csv file link appear at the bottom left-hand corner of your browser (Figure 3). Click on this file to download the spreadsheet into Excel click the downloaded spreadsheet. (Some users may have to make sure that pop-ups are allowed.)

Mozilla Firefox browser users will need to click export, then “save.” Then once the spreadsheet has downloaded, click the download button (down arrow) next to the bookmark buttons at the top-right of the browser. Click on the IncomeStatement.csv filename to open the file and complete the process above.

Internet Explorer users should click Export, then “open.” The spreadsheet should open into Excel and the process above can be completed.

Select all from this spreadsheet and copy and paste it into the Income Statement tab of the CI Ratio Analysis Spreadsheet. Make sure that you start in cell “A1” of the tab to paste the data (Figure 4).

Go back to the Morningstar.com page and select the Balance Sheet subtab under Financials to load the balance sheet data into the current page. Export this page to Excel and paste it into the Balance Sheet tab in the CI Ratio Analysis Spreadsheet in the same manner as above, depending on your browser. Finally, select the Cash Flow Statement subtab in Morningstar.com and export and paste this data sheet into the Cash Flow Statement tab of the CI Ratio Analysis Spreadsheet. Note: There will be two rows titled Free Cash Flow in your import, one of them with data and one without. You will need to delete the blank Free Cash Flow row since there are no values associated with it. This deletion is necessary in order for the Financials tab to pull the correct values from the correct cells.

After you have completed importing all three financial statements, the data should be ready to go. On the Ratio Analysis tab, you will see a summary of each financial statement ratio pulled directly from the data you have input. There are five different types of ratios analyzed: liquidity ratios, efficiency ratios, leverage ratios, coverage ratios and profitability ratios.

The Ratio Analysis Tab

There is a section at the top of the Ratio Analysis tab for you to input the company’s stock ticker, company name, industry and sector as well as date of analysis for your own personal records. Five years of ratios are calculated on this page as well as a three-year averages and five-year averages. All of the ratios are explained below. Please note that you may see different values for these ratios if you compare them at different sources. Some sources may use average asset values in calculations and others may make proprietary adjustments to the reported income statement figures.

Liquidity Ratios

Liquidity ratios examine how easily the firm could meet its short-term obligations (Figure 5).

Current ratio = current assets ÷ current liabilities

The current ratio compares the level of the most liquid assets (current assets) against that of the shortest maturity liabilities (current liabilities). A high current ratio indicates a high level of liquidity and less risk of financial trouble. A ratio that is too high may point to unnecessary investment in current assets, failure to collect receivables, or a bloated inventory, which all negatively affect earnings. A ratio that is too low implies illiquidity and the potential inability to meet current liabilities and handle random shocks, such as labor strikes, that may temporarily reduce the inflow of cash.

For the current ratio calculation, we divide total current assets by total current liabilities. In this example, for year 2009 total current assets in cell B13 on the Balance Sheet tab is divided by total current liabilities in cell B34 on the Balance Sheet tab. This formula is used in cell C9 is:

=IFERROR((VLOOKUP("total current assets", 'Balance Sheet'!$A:$F, 2, FALSE)/(VLOOKUP("total current liabilities", 'Balance Sheet'!$A:$F, 2, FALSE))), "na")

Excel would show ERROR in the cell if current liabilities are negative, since you cannot divide by a negative. The “IFERROR” function instructs Excel to replace the ERROR with “na” if current liabilities are negative. The “VLOOKUP” function instructs Excel to search through the first column of a range of cells and return a value from any cell on the same row of the range. In this example, we want Excel to search through the information on the Balance Sheet tab to find the words “total current assets” in the first column of the worksheet of the range A through F. The formula returns the value that is contained in the second column of the range and on the same row as the lookup value “total current assets.” Next, the formula uses the same process to identify the value in the second column associated with “total current liabilities” for a given column, then divides the two.

The word “FALSE” in the formula lets Excel know that we are looking for an exact word match. Excel must pull the figure associated with “total current assets” and nothing else. For each year that the Ratio Analysis tab completes the ratios, Excel will pull a number from a different column. For the example above to fill cell C9, we want data for “Y5” or 2009, so we instruct Excel to search through the second column (the “2” in the formula). When we search for data corresponding to “Y4” or 2010, the formula changes to:

=IFERROR((VLOOKUP("total current assets", 'Balance Sheet'!$A:$F, 3, FALSE)/(VLOOKUP("total current liabilities", 'Balance Sheet'!$A:$F, 3, FALSE))), "na")

You can see that the “2” in the formula is now changed to a “3.” This is why it is important to paste source data into the CI Ratio Analysis spreadsheet starting in cell A1. When the information is copied correctly, the formula identifies the correct columns and the correct data will be pulled into the ratio calculations.

Quick ratio (acid test) = current assets – inventory ÷ current liabilities

The quick ratio is similar to the current ratio, but it is a more conservative measure. It subtracts inventory from the current assets side of the comparison because inventory may not always be quickly converted into cash or may have to be greatly marked down in price before it can be converted into cash. In cell C10 you will see the formula:

=IFERROR((VLOOKUP("total current assets", 'Balance Sheet'!$A:$F, 2, FALSE)-VLOOKUP("Inventories", 'Balance Sheet'!$A:$F, 2, FALSE))/VLOOKUP("total current liabilities", 'Balance Sheet'!$A:$F, 2, FALSE), "na")

Again, we used the IFERROR formula so that Excel will return “na” instead of an error if total current liabilities is negative, because the resulting figure will not be meaningful. The VLOOKUP function finds total current assets on the Balance Sheet tab, subtracts inventories and then divides by total current liabilities.

Efficiency Ratios

No matter what type of business a company is in, it relies on assets to perform its operations. Efficiency ratios measure how effectively the company utilizes these assets, as well as how well it manages its liabilities (Figure 6).

Inventory turnover = cost of revenue ÷ inventory

The inventory turnover ratio uses cost of revenue (cost of goods sold) as opposed to revenues because cost of goods sold and inventory are both recorded at cost. Inventory turnover approximates the number of times inventory is used up and replenished during the year. A higher ratio indicates that inventory does not languish in warehouses or on the shelves. Inventory turnover is very industry-specific. For example, supermarket chains have a higher turnover ratio than jewelry store chains. In cell C12, you will see the formula:

=IFERROR((VLOOKUP("cost of revenue", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("inventories", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

We use the “IFERROR” function to let Excel know that if inventories are zero, then the formula should return “na” instead of an error value. The VLOOKUP function searches through the Income Statement tab to find a value for “cost of revenue” then divides that figure by “inventories” located in the Balance Sheet tab.

Accounts receivable turnover = revenue ÷ accounts receivable

Accounts receivable turnover measures the effectiveness of the firm’s credit policies and helps to indicate the level of investment in receivables needed to maintain the firm’s level of sales. The receivable turnover tells us how many times each period the company collects (turns into cash) its accounts receivable. The higher the turnover, the shorter the time between the typical sale and cash collection. A decreasing figure over time is a red flag. Seasonality may affect the ratio if the period ends at a time of year when accounts receivable are normally high. The calculation in cell C13 looks like:

=IFERROR((VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("receivables", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR formula tells Excel to insert “na” if receivables are negative or any other figure that could distort the calculation. The VLOOKUP function searches through columns A through F on the Income Statement tab to find a value for revenue in the second column, and divides that by a receivables value found on the Balance Sheet tab in the second column.

Average collection period = 365 ÷ accounts receivable turnover

The average collection period converts the accounts receivable turnover ratio into the more intuitive unit of number of days. The ratio calculates the average number of days receivables are outstanding before they are collected. Note that a very high number is a red flag indicating that it takes the company a long time to collect debt from its customers. A very low number may point to a credit policy that is too restrictive, leading to lost sales opportunities. Meaningful industry comparisons and an understanding of credit sales policy of the firm are critical when examining these figures. The number 365 in the formula below represents the number of days in a typical calendar year. Sometimes 360 is used. The formula in cell C14 is:

=IFERROR(365/C13, "na")

The IFERROR function in this formula tells Excel to display “na” if 365 divided by accounts receivable turnover displays an error. Otherwise, the function calculates the formula and displays the numerical answer.

Fixed asset turnover = revenue ÷ net plant, property and equipment

The fixed asset turnover ratio measures a company’s ability to generate revenue from fixed-asset investments, specifically plant, property and equipment (net of depreciation). A higher fixed asset turnover ratio shows that the company has been more effective in using the investment in fixed assets to generate revenues. The ratio is often used as a measure in manufacturing industries, where major investments are made in plant, property and equipment to help increase output. When companies make these large investments, prudent investors watch this ratio in following years to see how effective the investment in the fixed asset was. The formula in cell C15 is:

=IFERROR((VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("net property, plant and equipment", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR calculation in this formula requires that Excel display “na” if either of the inputs are not valid for the fixed asset turnover calculation. For example, if net property, plant and equipment was negative or unavailable an “na” will be returned. The VLOOKUP function then searches through the Income Statement tab and finds a revenue figure in the second column, because cell C15 finds “Y5,” and then divides that figure by the net property, plant and equipment figure in the second column on the Balance Sheet tab.

Total asset turnover = revenue ÷ total assets

Total asset turnover measures how well the company’s assets have generated sales. Industries differ dramatically in asset turnover, so comparison to firms in similar industries is crucial. A ratio that is too high relative to other firms may indicate insufficient assets for future growth and sales generation, while an asset turnover figure that is too low points to redundant or low productivity assets. The formula in cell C16 is:

=IFERROR((VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("total assets", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR function prevents users from receiving an incorrect figure due to an error or an invalid figure for either total assets or revenue. The VLOOKUP figure finds the revenue figure in the second column on the Income Statement tab then divides that by the total assets figure found in the second column on the Balance Sheet tab.

Leverage Ratios

Leverage ratios help investors get an idea of the company’s method of financing or to measure its ability to meet financial obligations (Figure 7).

Total debt ratio (total liabilities to assets) = total liabilities÷ total assets

The debt to total assets ratio measures the percentage of assets financed by all forms of debt. The higher the percentage and the greater the potential variability of earnings, the greater the potential for default. Yet prudent use of debt can boost return on equity. You will see the below formula in cell C18:

=IFERROR((VLOOKUP("total liabilities", 'Balance Sheet'!$A:$F, 2, FALSE)/VLOOKUP("total assets", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR function tells Excel to show “na” if total assets are zero, because you can’t divide by zero or a negative figure. The VLOOKUP function searches through the Balance Sheet tab and finds the figure associated with total liabilities in the second column, then divides that figure by total assets, located on the Balance Sheet tab in the second column.

Long-term debt ratio = long term debt ÷ total assets

The long-term debt ratio represents the percentage of a corporation’s assets that are financed with loans and financial obligations lasting more than one year. The ratio provides a general measure of the financial position of a company, including its ability to meet financial requirements for outstanding loans. A year-over-year decrease in this metric would suggest the company is progressively becoming less dependent on debt to grow their business. We use the formula below in cell C19:

=IFERROR((VLOOKUP("long-term debt", 'Balance Sheet'!$A:$F, 2, FALSE)/VLOOKUP("total assets", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR function tells Excel to display “na” if total assets is zero or a non-meaningful figure. The VLOOKUP function searches through the Balance Sheet tab in the second column to find the figure associated with long-term debt, and divides that figure by total assets located on the Balance Sheet tab in the second column.

Total debt to equity = total liabilities ÷ shareholders’ equity

The debt-to-equity ratio indicates what proportion of equity and debt the company is using to finance its assets. Lower debt-to-equity ratios are favorable because they indicate less risk. A higher debt-to-equity ratio is unfavorable because it indicates that the business relies more on external lenders, thus it carries higher risk. A debt-to-equity ratio of 100% means that half of the assets of a business are financed by debts and half by shareholders’ equity. A value higher than 100% means that more assets are financed by debt than those financed by shareholders’ money, and vice versa. You will see the formula below in cell C20:

=IFERROR((VLOOKUP("total liabilities", 'Balance Sheet'!$A:$F, 2, FALSE)/VLOOKUP("total stockholders' equity", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR function ensures that Excel provides the user with a meaningful function. If it can’t, the result will be an “na.” The VLOOKUP function searches through the Balance Sheet tab to find a value for total liabilities located in the second column of the sheet, then divides that by the total stockholders’ equity value located in the second column of the Balance Sheet tab. Remember, the second column is being used in this specific example to show that the Ratio Analysis page is using figures from the corresponding year to calculate the ratio. If you move a column to the right, you will see a “3” as opposed to a “2” in the formula so that the next year’s ratios are pulling data from the correct corresponding-year columns on the financial statement tabs.

Long-term debt to equity = long-term debt ÷ total stockholders’ equity

The ratio of long-term debt to equity expresses the relationship between long-term capital contributions of creditors to contributions by owners (investors). The ratio shows the degree of protection provided by the owners for the long-term creditors. A company with a high ratio of long-term debt to equity is considered to be highly leveraged, or more risky. Typically investors should compare this figure to that of the industry average to see where the company fares in terms of leverage and risk. The formula used in cell C21 is:

=IFERROR((VLOOKUP("long-term debt", 'Balance Sheet'!$A:$F, 2, FALSE)/VLOOKUP("total stockholders' equity", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR function tells Excel to display “na” if the calculation returns a non-meaningful value. The VLOOKUP function searches for a long-term debt figure in the second column of the Balance Sheet tab and divides that by the total stockholders’ equity figure found in the second column of the Balance Sheet tab.

Equity multiplier =total assets ÷ total stockholders’ equity

The equity multiplier is a measurement of a company’s financial leverage. Companies finance the purchase of assets through either debt or equity. A high equity multiplier indicates that a larger portion of asset financing is being done through debt. The formula in cell D22 is:

=IFERROR((VLOOKUP("total assets", 'Balance Sheet'!$A:$F, 2, FALSE)/VLOOKUP("total stockholders' equity", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR functions instructs Excel to show “na” in the cell if the ratio provides a nonsensical figure. The VLOOKUP function searches through the imported information on the Balance Sheet tab to find a value in the second column associated with total assets and divides that by the total stockholders’ equity figure, also on the Balance Sheet tab.

Coverage Ratios

Coverage ratios also assist investors in finding out if a company can meet its financial obligations. These ratios look to see how much of the figure in the numerator the company has to fund paying down the figure in the denominator. Typically, a higher ratio is desired (Figure 8).

Times interest earned (interest coverage ratio) = operating income (EBIT) ÷ interest expense

Times interest earned, or the interest coverage ratio, is the traditional measure of a company’s ability to meet its interest payments. It is calculated by dividing operating income (also known as earnings before interest and taxes, or EBIT) by the interest expense. Times interest earned indicates how well a company is able to generate earnings to pay interest. The larger and more stable the ratio, the less the risk of default. Interest on debt obligations must be paid, regardless of company cash flow. Failure to do so results in default if the lender will not restructure the debt obligations. The formula in cell C24 is:

=IFERROR((VLOOKUP("operating income", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("interest expense", 'Income Statement'!$A:$F, 2, FALSE)), "na")

The IFERROR function requires that Excel returns “na” if the times interest earned is a nonsensical figure. The VLOOKUP function searches through the Income Statement tab, in the second column, to find a figure associated with operating income. Then it divides this figure by interest expense, located in the second column of the Income Statement tab.

Cash coverage ratio = operating income (EBIT) + non-cash expenses (depreciation) ÷ interest expense

The cash coverage ratio is used for determining the amount of cash available to pay for a borrower’s interest expense. A general rule of thumb is that the ratio should be greater than 1.0 in order to show that the company can sufficiently pay interest expense. Other non-cash items may need to be subtracted from the numerator, such as reserves for sales allowances, product returns, bad debts or inventory obsolescence. The formula in cell C25 is:

=IFERROR(((VLOOKUP("operating income",'Income Statement'!$A:$F,2,FALSE)+VLOOKUP("depreciation & amortization",'Cash Flow Statement'!$A:$F,2,FALSE))/VLOOKUP("interest expense",'Income Statement'!$A:$F,2,FALSE)), "na")

The IFERROR function ensures that Excel displays “na” if the ratio calculation returns a nonsensical number. For example, some companies may not display interest expense on their income statement, or have a zero value due to tax implications. If you divide a figure by zero you will receive an error value. The VLOOKUP functions find the needed values on their respective tabs in the second column of each financial statement spreadsheet since in this example we are looking for “Y5”, or 2009 figures, which are located in column 2.

Profitability Ratios

Profitability ratios assess the ability of a company to generate earnings in relation to expenses and other relevant costs incurred during a specific period of time. Industry comparisons are critical for all profitability ratios. Margins vary from industry to industry. A high margin relative to an industry norm may point to a company with a competitive advantage over its competitors (Figure 9).

Gross profit margin = (revenue – cost of revenue) ÷ revenue

Gross profit margin reflects the firm’s basic pricing decisions and material costs. The greater the margin and the more stable the margin over time, the greater the company’s expected profitability. Trends should be closely followed because they generally signal changes in market competition. Gross profit margin is computed in cell C27 using the formula:

=IFERROR((VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE)-VLOOKUP("cost of revenue", 'Income Statement'!$A:$F, 2, FALSE))/VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE), "na")

The IFERROR function instructs Excel to show “na” if the ratio returns a nonsensical figure. The VLOOKUP function searches through the Income Statement tab and finds the value associated with revenue in the second column, and subtracts the cost of revenue, which is often called “cost of goods sold.” The formula then divides this figure by revenue to arrive at gross profit margin.

Operating profit margin = operating income (EBIT) ÷ revenue

Operating profit margin examines the relationship between sales and management-controllable costs before interest, taxes and non-operational expenses. As with profit margin, one is looking for a high, stable margin. The formula used in cell C28 is:

=IFERROR((VLOOKUP("operating income", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE)), "na")

The IFERROR function is used in case the VLOOKUP function returns a nonsensical figure. If this happens, then the cell will display “na.” The VLOOKUP function searches the Income Statement tab to find operating income and revenue figures in the second column, then divides operating income by revenue.

Net profit margin = net income ÷ revenue

Net profit margin is the “bottom line” margin frequently quoted for companies. It indicates how well management has been able to turn revenues into earnings available for shareholders. The formula in cell C29 is:

=IFERROR((VLOOKUP("net income", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("revenue", 'Income Statement'!$A:$F, 2, FALSE)), "na")

We used the IFERROR function to ensure that only a non-error value is returned. The VLOOKUP function searches through the figures in the second column on the Income Statement tab to find net income and revenue. Then it divides net income by revenue to arrive at the net profit margin.

Return on assets = net income ÷ total assets

Return on assets allows investors to examine how effectively the company is converting the money it has to invest into net income. A high return implies the assets are productive and well-managed. The formula displayed in cell C30 is:

=IFERROR((VLOOKUP("net income", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("total assets", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The formula uses the IFERROR function to make sure that there is no error in the calculation. An example would be if the company didn’t report a total assets figure on their income statement. If there were a blank, the formula wouldn’t be able to correctly divide net income by total assets to arrive at return on assets. If an error is detected, the cell will display “na.” The VLOOKUP function finds net income in the second column of the Income Statement tab and divides it by total assets located in the second column of the Balance Sheet tab.

Return on equity = net income available to common shareholders ÷ total stockholders’ equity

Net income is for the fiscal year and should be the figure before dividends paid to common stockholders but after dividends paid to preferred stockholders because shareholders’ equity does not include preferred shares. In the imported income statement data from Morningstar, the net income figure used is called “net income available to common shareholders.”

Return on stockholders’ equity considers the financial structure of the firm and its impact on earnings. It indicates how much the stockholders earned for their investment in the company. The level of debt (financial leverage) on the balance sheet has a large impact on this ratio. Debt magnifies the impact of earnings on return on equity during both good and bad years. When large differences between return on total assets and return on equity exist, an investor should closely examine the liquidity and financial risk ratios. The return on equity formula used in cell C31 is:

=IFERROR((VLOOKUP("net income available to common shareholders", 'Income Statement'!$A:$F, 2, FALSE)/VLOOKUP("total stockholders' equity", 'Balance Sheet'!$A:$F, 2, FALSE)), "na")

The IFERROR function is used to ensure that Excel will returns “na” if the ratio can’t be computed correctly. The VLOOKUP function is used to find net income available to common shareholders in the second column of the Income Statement tab, and then divide that figure by total stockholders’ equity located in the second column of the Balance Sheet tab.

The Charts Tab

The Charts tab presents time series analyses for the financial statement data that you have imported from Morningstar.com. For example, the first chart titled “Income Statement Results” provides bar charts comparing net income to revenue over the last five years of financial statement information (Figure 10). The profit margin figure for each year is displayed as a line on this chart so users can see how profit margin compared to revenue and net income over the five-year period. The scale on the left is determined by the bar chart figures for revenue and net income. The scale on the right is determined by the profit margin figures on the Financials tab.

The time series along the bottom of each chart will automatically calculate based on the data you input from Morningstar.

Charts referencing ratios that are “na” in the Ratio Analysis tab will be rendered non-meaningful. The series function used to construct the charts cannot chart an “na” value.

Conclusion

The CI Ratio Analysis Spreadsheet gives investors an easy way to analyze a variety of financial ratios for a particular company based on reported figures from financial statements.

Although financial ratios are available on other websites, investors may not be able to determine the source of the data or verify how the ratios are calculated. These factors are crucial when making judgments about a firm and making comparisons against industry averages and other similar firms.

The CI Ratio Analysis Spreadsheet seeks to eliminate some of these difficulties by allowing investors to import complete financial statement information from one source (Morningstar.com) into a spreadsheet that will automatically derive the financial ratios and visual charts based on the ratios.

Click here to download the CI Ratio Analysis Spreadsheet.


Discussion

Douglas Gurak from NY posted 2 months ago:

This looks like a great contribution but it appears to have some "bugs". I downloaded data for Tenneco and all worked fine in the sense that the downloaded data had the same structure as for Caterpillar which was the company whose data was present in the downloaded spreadsheet. Then I downloaded the Morningstar data for Apple and saw that the number of rows did not match either the original company (CAT) or TEN. This was so for income, balance and cash flow data. For example for Cash Flow the Morningstar data has 41 total rows, but only 35 for Apple. This variability throws off the formula addresses. Is there some was to force Morningstar to always include all possible rows?


Ramnarine Persaud from ON posted 2 months ago:

This is a fantastic spreeadsheet/tool that could save a tremendous amount of time and effort. It must have taken a lot of work and energy to put this together. Please let us know when the bugs are fixed.


Wayne Thorp from IL posted 2 months ago:

@Douglas and @ Ramnarine,

Are you running Office 365? This spreadsheet makes use of formulas that will function no matter what. The formulas look for the line item NAME, not specific cells. So, for example, it doesn't matter what cell "current assets" are in. As long as the formula finds the line item, it will correctly calculate the ratios. BUT, you need the latest version of Excel, or have the compatibility pack installed, which Jackie mentioned in the article.


David Goldgewert from NY posted about 1 month ago:

Does the compatibility pack have to be installed if I am running Office Home & Business 2010?


Bob Gervasoni from NJ posted about 1 month ago:

Is this spreadsheet compatible with Open Office 4.1.0? When I open the file with Open Office the Formulas in the Cells on the RATIO tab are changed to Open Office format, but the cells all have #NAME, instead of a numeric. I have tried different combination changes in the formula but still no data. Any help would be appreciated.


Wayne Thorp from IL posted about 1 month ago:

@David G.,

If you are not running Office 365/Excel 2013 you will need the compatibility pack.


Wayne Thorp from IL posted about 1 month ago:

@Bob G.,

The spreadsheet was made using Excel 2013/Office 365. I doubt that OpenOffice has all of the functions required to make use of all the features/formulas of the spreadsheet.


Kenneth Metz from CO posted about 1 month ago:

FYI. The downloaded spreadsheet works OK in Excel 2010 on my PC without the compatibility pack. I verified that for those stocks (CAT, AAPL and TEN) there were mentioned in earlier posts.

I've also made a small modification to show the name and symbol for the company in the table and chart titles. For example, the text "Ratio Analysis" is replaced with the formula:

="Ratio Analysis: " & MID('Income Statement'!A1,1,FIND(")",'Income Statement'!A1,1))


bgs from GA posted 17 days ago:

Will the spreadsheet work properly using Excel 2013?


Jay Lagree from DE posted 3 days ago:

Looks to be a very worthwhile spreadsheet and fun to use. I downloaded it.

Won't work on my IPad.
Won't work on my Mac Desktop with Apache Calc.
Won't work on my old PC laptop running "7" and Excel 2007 (no service pac).

This spreadsheet is not rocket science. If I were 16 years old with a couple of hours to spare, I'd write an app for my IPad.

I spend 85% of my computer time, nowadays, on my tablet. Trading, banking, email, online commerce, travel reservations, you name it, all done on my IPad.

The search continues.


You need to log in as a registered AAII user before commenting.
Create an account

Log In