# Financial Ratio Analysis with Excel Model

VIEWS: 17,262 PAGES: 17

• pg 1
```									FINANCIAL RATIOS ANALYSIS

Working with financial information can be overwhelming if information is not dissected and positioned
in an easy to use metric measurement which gauges the performance of the company. Financial ratios
provide the users with metric performance measurements to understand the business trend over and
create a comparison standard to the competitors and the industry. Using financial ratios for comparison
along the industry and among different firms necessitate understanding the ration calculations; in some
instances the computation method has to be harmonized to arrive at fair judgment on firm performance
in comparison to the industry. The application and interpretation will be same among different firms,
but ratios computations are influenced by how the inputs from the financial statements are determined.
A ratio might have been calculated using average amount of the balance sheet item while the same ratio
for a different firm might have calculated using the end of the year balance sheet amount.

A liquidity ratio as the name indicates measures how well the company position is placed to meet the
coming up obligations and without impending operations. The ratio is measured in coverage times and it
is calculated as current assets divided by current liabilities, the higher the coverage time the favorable
the ratio and the more liquid the company. A low coverage rate instigate rising a red flag, because this
might be an indication the company will be running into trouble of sustaining the operations and paying
the due liabilities. There are different degrees of liquid ratios used to gage the company liquidity, quick
ratio and cash ratios have a high degree as they both exclude inventories as part of current assets. A cash
ratio is more liquid because it only includes cash and cash equivalents as part of the current assets.

Profitability and investments ratios are used to measure the company performance and the company
efficiency in utilizing the resources to build wealth for shareholders. The ratios are benchmarked with
the industry and other competitors to identify the under performing, average or over performing firm.
The comparison allows the investors to make informed decisions about the firm.

Operating performance ratios looks at how well a company utilizes the assets to generate revenues; they
test management efficiency in utilizing the firm assets to generate revenues. The debt ratios analysis the
load risk faced by the company and its share holders, the higher the debt ratio to the assets the higher the
risk of the company going into bankruptcy. The ratios helps the investors asses the risk exposure they
face by being part of the company.

The developed excel template requires keying in balance sheet items, the main investors ratios are
automatically calculated after keying in the necessary information. The template is designed to help the
user perform trend analysis over a period of 5 years and identify the firm performance. The excel
template is also applicable in calculating different ratios for comparison among different firms.
FINANCIAL RATIOS AND TREND ANALYSIS
RATIO ANALYSIS FOR
THE YEAR
2009

2009            2008            2007            2006
INCOME
Net Sales (Revenues)                       \$ 10,000.00     \$ 10,000.00     \$ 10,000.00     \$ 10,000.00
Cost Of Sales                              \$ 1,000.00      \$ 1,000.00      \$ 1,000.00      \$ 1,000.00
Gross Profit                               \$ 9,000.00      \$ 9,000.00      \$ 9,000.00      \$ 9,000.00
Expenses                                   \$ 2,000.00      \$ 2,000.00      \$ 2,000.00      \$ 2,000.00
Earnings Before Interest & Taxes           \$ 7,000.00      \$ 7,000.00      \$ 7,000.00      \$ 7,000.00
Interest                                   \$ 1,000.00      \$ 1,000.00      \$ 1,000.00      \$ 1,000.00
Earnings Before Taxation                   \$ 6,000.00      \$ 6,000.00      \$ 6,000.00      \$ 6,000.00
Tax                                        \$    500.00     \$    500.00     \$    500.00     \$    500.00
Net Income After Tax                       \$ 5,500.00      \$ 5,500.00      \$ 5,500.00      \$ 5,500.00
Cash Dividends Paid Out                    \$ 3,000.00      \$ 3,000.00      \$ 3,000.00      \$ 3,000.00
Retained Earnings                          \$ 2,500.00      \$ 2,500.00      \$ 2,500.00      \$ 2,500.00

CASH FLOW STATEMENT
Net Cash/ (Used by) Operating Activities   \$   3,000.00    \$   3,000.00    \$   3,000.00    \$   3,000.00
Net Cash/ (Used by) Investing Activities   \$     500.00    \$     500.00    \$     500.00    \$     500.00
Net Cash/ (Used by) Financing Activities   \$    (700.00)   \$    (700.00)   \$    (700.00)   \$    (700.00)
Net Increase or Decrease In Cash Held      \$   2,800.00    \$   2,800.00    \$   2,800.00    \$   2,800.00
Cash at beginning of the year              \$   1,000.00    \$   1,000.00    \$   1,000.00    \$   1,000.00
Cash at the end of the year                \$   3,800.00    \$   3,800.00    \$   3,800.00    \$   3,800.00

CURRENT ASSETS
Cash At Bank                               \$ 1,000.00      \$ 1,000.00      \$ 1,000.00      \$ 1,000.00
Inventory                                  \$ 1,000.00      \$ 1,000.00      \$ 1,000.00      \$ 1,000.00
Accounts Receivable                        \$ 1,000.00      \$ 1,000.00      \$ 1,000.00      \$ 1,000.00
Prepaid Expenses
others

TOTAL CURRENT ASSETS                       \$ 3,000.00      \$ 3,000.00      \$ 3,000.00      \$ 3,000.00

FIXED ASSETS
Machinery & Equipment                      \$ 2,000.00      \$ 2,000.00      \$ 2,000.00      \$ 2,000.00
Furniture & Fixtures
Leasehold Improvements
Real Estate/Buildings
Investments

TOTAL FIXED ASSETS                         \$ 2,000.00      \$ 2,000.00      \$ 2,000.00      \$ 2,000.00

Other Assets
Specify
Specify

Total Other Assets               \$      -      \$      -      \$      -      \$      -

TOTAL ASSETS                     \$ 5,000.00    \$ 5,000.00    \$ 5,000.00    \$ 5,000.00

CURRENT LIABILITIES
Accounts payable                 \$ 1,000.00    \$ 1,000.00    \$ 1,000.00    \$ 1,000.00
Accrued Accounts
Tax payable
Debt payable with the year       \$   100.00    \$   100.00    \$   100.00    \$   100.00
Notes payable with the year
Others

TOTAL CURRENT LIABILITIES        \$ 1,100.00    \$ 1,100.00    \$ 1,100.00    \$ 1,100.00

LONG TERM LIABILITIES
Long term debts                  \$   200.00    \$   200.00    \$   200.00    \$   200.00
Lease hold
others

TOTAL LONG TERM LIABILITIES      \$   200.00    \$   200.00    \$   200.00    \$   200.00

EQUITY
Ordinary Shares                  \$ 2,000.00    \$ 2,000.00    \$ 2,000.00    \$ 2,000.00
Retained Earnings
Ordinary Shares holders Equity   \$ 2,000.00    \$ 2,000.00    \$ 2,000.00    \$ 2,000.00

Preferred Shares                 \$   100.00    \$   100.00    \$   100.00    \$   100.00

TOTAL EQUITY                     \$ 4,100.00    \$ 4,100.00    \$ 4,100.00    \$ 4,100.00
Earnings Per Share               \$     5.00    \$     5.00    \$     5.00    \$     5.00
Dividend Per Common Share        \$     2.00    \$     2.00    \$     2.00    \$     2.00
Stock Price Per Share            \$    17.00    \$    17.00    \$    17.00    \$    17.00
Average Number Of Employees            5000          5000          5000          5000

COMMON SHARES OUTSTANDING             1,000         1,000         1,000         1,000

Share Holders Equity Per Share   \$     2.00 \$        2.00 \$        2.00 \$        2.00
Earning Per Share Growth Rate         4.00%         4.00%         4.00%         4.00%

ENTERPRISE VALUE

Capitalization                   \$ 17,000.00   \$ 17,000.00   \$ 17,000.00   \$ 17,000.00
Debt                             \$    200.00   \$    200.00   \$    200.00   \$    200.00
Minority Interest
Total Capitalization             \$ 17,200.00   \$ 17,200.00   \$ 17,200.00   \$ 17,200.00
Less/Cash and Cash Equivalent   \$ 1,000.00    \$ 1,000.00    \$ 1,000.00    \$ 1,000.00
Enterprise Value                \$ 16,200.00   \$ 16,200.00   \$ 16,200.00   \$ 16,200.00
AND TREND ANALYSIS

2005

\$ 10,000.00
\$ 1,000.00
\$ 9,000.00
\$ 2,000.00
\$ 7,000.00
\$ 1,000.00
\$ 6,000.00
\$    500.00
\$ 5,500.00
\$ 3,000.00
\$ 2,500.00

\$   3,000.00
\$     500.00
\$    (700.00)
\$   2,800.00
\$   1,000.00
\$   3,800.00

\$ 1,000.00
\$ 1,000.00
\$ 1,000.00

\$ 3,000.00

\$ 2,000.00

\$ 2,000.00
\$      -

\$ 5,000.00

\$ 1,000.00

\$   100.00

\$ 1,100.00

\$   200.00

\$   200.00

\$ 2,000.00

\$ 2,000.00

\$   100.00

\$ 4,100.00
\$     5.00
\$     2.00
\$    17.00
5000

1,000

\$     2.00
4.00%

\$ 17,000.00
\$    200.00

\$ 17,200.00
\$ 1,000.00
\$ 16,200.00
Liquidity Measurement Ratios

2009    2008    2007

Current Assets             2.73    2.73    2.73
Current Ratios               =        Current Liabilities

Quick Ratio                  = Cash & Equivalents+Short term
Investments+Accounts receivable    1.82    1.82    1.82
Current Liabilities

Cash Ratio                   = Cash+Equivalents+Invested Funds    0.91    0.91    0.91
Current Liabilities

Cash Conversion Cycle        =          DIO+DSO-DPO              36.50   36.50   36.50

Profitability Indicators Ratios

Gross Profit Margin          =           Gross Profit             1.11    1.11    1.11
Net Sales (Revenues)

Net Profit Margin            =            Net Income              0.55    0.55    0.55
Net Sales (Revenues)

Pretax Profit Margin         =            Net Income              0.60    0.60    0.60
Net Sales (Revenues)

Effective Tax Rate                  Income Tax Expenses           0.08    0.08    0.08
Pretax Income

Return On Assets             =           Net Income               2.75    2.75    2.75
Total Assets

Return On Equity                         Net Income               1.34    1.34    1.34
Shareholders Equity

Return On Capital Employed   =           Net Income               0.42    0.42    0.42
Capital Employed
2006    2005

2.73    2.73

1.82    1.82

0.91    0.91

36.50   36.50

1.11    1.11

0.55    0.55

0.60    0.60

0.08    0.08

2.75    2.75

1.34    1.34

0.42    0.42
Debt Ratios
2009

Debt Equity Ratio                           =             Total Liabilities              0.32
Shareholders' Equity

Capitalization Ratio                        =             Long Term Debt                 0.05
Long Term Debt + Share Holders Equity

Interest Coverage Ratio                     =      Earnings Before Interest & Taxes      7.00
Interest Expenses

Cash Flow To Debt Ratio                     =            Operating Cash flow            10.00
Total Debt

Operating Performance Ratios

Fixed Assets Turnover Ratio                 =                 Revenue                    5.00
Property, Plant & Equipment

Sales Revenue Per Employee                                   Revenue                     2.00
Number of Employees (Average)

Cash Flow Indicators Ratios

Operating Cash Flow to Sales Ratio          =           Operating Cash Flow              0.30
Net Sales( Revenues

Free Cash flow/ Operating Cash flow Ratio   =              Free Cash flow                0.83
Operating Cash flow

Capital Expenditure Coverage                =           Operating Cash Flow              6.00
Capital Expenditure
Dividends Coverage      =      Operating Cash Flow      1.00
Cash Dividends

Dividend Payout Ratio   =   Dividend Per Common share   0.40
Earnings Per Share
2010    2011    2012    2013

0.32    0.32    0.32    0.32

0.05    0.05    0.05    0.05

7.00    7.00    7.00    7.00

10.00   10.00   10.00   10.00

5.00    5.00    5.00    5.00

2.00    2.00    2.00    2.00

0.30    0.30    0.30    0.30

0.83    0.83    0.83    0.83

6.00    6.00    6.00    6.00
1.00   1.00   1.00   1.00

0.40   0.40   0.40   0.40
2009

Investments valuation Ratios
2009    2008

Price /Book Value Ratio           =         Stock Price Per Share        8.50    8.50
Shareholders Equity Per Share

Price/ Cash Flow Ratio            =         Stock Price Per Share        5.67    5.67
Operating Cash Flow Per Share

Price/ Earnings Ratio             =         Stock Price Per Share        3.40    3.40
Earnings Per Share

Price/ Earnings to Growth Ratio   =        Price Earnings (P/E) Ratio   85.00   85.00
Earnings Per Share (EPS) Growth

Price/Sales Ratio                 =          Stock Price Per Share       1.70    1.70
Net Sales (Revenue) Per Share

Dividend Yield                    =       Annual Dividend Per Share      0.12    0.12
Stock Price Per Share

Enterprise Value Multiple         =           Enterprise Value           0.43    0.43
EBITDA
2007    2006    2005

8.50    8.50    8.50

5.67    5.67    5.67

3.40    3.40    3.40

85.00   85.00   85.00

1.70    1.70    1.70

0.12    0.12    0.12

0.43    0.43    0.43
```
To top