Financial Ratio Analysis with Excel Model

Document Sample
Financial Ratio Analysis with Excel Model Powered By Docstoc
					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
				
DOCUMENT INFO
Description: A company can use this document to calculate various financial ratios. This spreadsheet allows the user to input the financial data. The spreadsheet then calculates liquidity measurement ratios, profitability indicator ratios, debt ratios, operating performance ratios, cash flow indicator ratios, and investment valuation ratios. Financial ratios are used to perform quantitative analysis; they show the statistical relationships between pieces of financial data. Such ratios can be used to evaluate past, current, and projected performance of a company, or they can be used to compare two different companies.
This document is also part of a package Financial Models Bundle 7 Documents Included