Financial Ratios Model

This model is a perfect tool for diagnosing the overall financial health of the company. Just enter handful of readily available (in company’s financial statements – Mostly in Balance Sheet, and Income Statement) inputs into the model and observe how company’s key financial ratios behave(d) over time.

Model can calculate and graph critical financial ratios over the length of 10 years. The application is designed to focus on most areas of “financial significance” like: Liquidity, Profitability, and Efficiency. Spreadsheet also calculates (separately from above) Earnings Per Share (EPS), and Price-to-Earnings (P/E Multiple) ratios.

Model generates periodic outputs for the following:

1. Liquidity Ratios:

• Current Ratio;

• Quick Ratio.

2. Profitability Ratios:

• Return on Equity (ROE);

• Return on Assets (ROA);

• Gross Profit Margin (GPM);

• Net Profit Margin (NPM).

3. Efficiency Ratios:

• Total Asset Turnover (TAT);

• Fixed Asset Turnover (FAT);

• Average Collection Period (Average # of Days during which Receivables remain outstanding);

• Inventory Days (average # of Days during which inventory stays on “shelf”);

• Accounts Payable Days (average # of Days during which payables balance remains outstanding);

• Cash Conversion Cycle (Average # Days needed to purchase/produce inventory, sell it and collect receivables).

4. Leverage:

• Debt Ratio.

5. Other:

• Earnings Per Share (EPS);

• Price-to-Earnings (P/E) Ratios.

6. DuPont/ROE decomposition table.

Spreadsheet can automatically detect inconsistent inputs used in model and warn users accordingly.

Model can automatically label ratio values as: Poor, Normal, Good, or Excellent. User can influence the labeling process by adjusting the interval cut-off values. For example, user can tell the spreadsheet that all values of current ratio between 1 and 2 are poor, values between 2 and 3 Normal, etc.

Model can support maximum 10 periods. Period length can equal to 1 year, 6 months, 1 quarter, 1 month, or 1 week.

Application not only calculates ratio values for given periods, but also generates charts that help users visualize trends of the above mentioned key financial metrics.

The “Glossary” Tab of the application displays formulas used in ratio calculations.

***NOTE: xlsx format requires MS Office 2007!