Financial Statement Analysis Tools

Course
: F0242-Lab Modeling Keuangan

Effective Period
: September 2015
Financial Statement Analysis Tools

Session 4
Acknowledgement
If you are using PowerPoint from textbook, please
use this slide to give information about the source
of the slides.
Example:
These slides have been adapted from:
Timothy R. Mayes, Todd M. Shank. (2012). Financial
Analysis with Microsoft Excel. 05. CENGA. ISBN:
9781111826246.
Chapter 4
Chapter 4
Financial Statement Analysis Tools
Objectives:
1.Describe the purpose of financial ratios and who
uses them
2.Define the five major categories of ratios
(liquidity, efficiency, leverage, coverage, and
profitability)
3.Calculate the common ratios for any firm by using
income statement and balance sheet data
4. Use financial ratios to access a firms past

performance, identify its current problems,
and suggest strategies for dealing with these
problems
5. Calculate the economic profit earned by a firm
Exhibit 4-1 Ratio Worksheet for EPI
Liquidity Ratio
(i) Current Ratio
Exhibit 4-1 shows the beginning of Ratio worksheet
1.Current Ratio for 2009 in B5 with formula:
=Balance Sheet !B8/Balance Sheet ! B17
Current Ratio will be 2.39. Copy the formula to C5.
(ii) Quick Ratio
We Calculate EPIs 2009 quick ratio in B6 with formula:
=(Balance Sheet !B8-Balance Sheet!B7)/BalanceSheet!B17
Copy this formula to C6 reveals that the 2008 quick ratio was 0.85
Efficiency Ratio
(i) Inventory Turnover Ratio (See p. 103)
1. To calculate the inventory turnover ratio for EPI
enter the formula in B7 :
=Income Statement !B6/Balance Sheet!B7
2. Copy this formula to C8
This ratio in 2008 was 4 times and in 2009 is 3.89
Efficiency Ratio
(ii) Account receivable Turnover Ratio (p.104)
1. In cell B9: =Income Statement!B5/Balance Sheet!B6
The result is 9.58
2. Copy this formula to C9 to get result for 2008
(iii) Average Collection Period
1. In B10 =Balance Sheet!B6/(Income Statement!
B5/360)
2. Copy this to C10 to find 2008=36.84 days which was
slightly better than 2009
Efficiency Ratio
(iv) Fixed Asset Turnover Ratio (p .105)

1.In B11 enter the formula:
=Income Statement!B5/Balance Sheet!B11
You will get 10.67 times for 2009
2. Copy this to C11 to get 2008 Ratio
Exhbit 4-2 EPIs financial ratio
Leverage Ratios
(i)The Total Debt Ratio (p.108)
=Balance Sheet!B19/Balance Sheet!B12
2. The Result for 2009 is 58.45% which is higher than
54.81%
(ii)The Long Term Debt Ratio (p.108)
=Balance Sheet!B18/Balance Sheet!B12
Copy this formula to C15
Leverage Ratio
(iv) The long Term Debt to Total Capitalization Ratio
(p.108, equation 4-10)
1.In B16 enter: =Balance Sheet!B18/(Balance Sheet!
B18+Balance Sheet!B20+Balance Sheet!B21)
In 2008 this ratio was only 32.76%
(v) The Debt to Equity Ratio (p. 109 equation 4-11)
In B17 =Balance Sheet!B19/Balance Sheet!B22
Copy to C17 to get 2008 ratio = 1.21 times
Leverage Ratio
(vi) Long Term debt to Equity Ratio
1.In B18 is :=Balance Sheet !B18/Balance Sheet!B22
Copy this formula to C18, ration for 2008 : 48.73%
Your worksheet should look like Exhibit 4-3
Exhibit 4-3 EPIs financial ratios with the

leverage ratio
Coverage Ratios
(i) The Times Interest Earned Ratio (p.16)
Equation (4-16)
Times Interest Earned =
EBIT
Interest Expense
1. In B20 enter formula:
=Income Statement! B11/Income Statement! B12
2. Copy the formula to C20, See the ratio will be
declined from 2008
Coverage Ratios
(ii) The Cash Coverage Ratio
Cash Coverage Ratio = EBIT + Noncash Expenses
Interest Expense
See equation (4-17) p.112
1. In cell B21 enter formula:
=(Income Statement! B11+Income Statement!B10)/Income Statement!B12
2008 ratio is 3.65
Profitability Ratios
(i) The Gross Profit Margin ..equation (4-18) p.113
= Gross Profit
Sales
=Income Statement!B7/Income Statement!B5
2. Copy to C23, you will see that profit margin will
be declined from 16.55% in 2008.
(ii) The Operating Profit Margin..equation (4-19)
=
Net Operating Income
Sales
The result will be 3.89%
(iii) The Net Profit Margin.equation (4-20)
= Net Profit Margin
Sales
See the result wol be 1.15% (p.114)
(iv) Return on Total Assets.equation (4-21)
= Net Income
Total Assets
=Income Statement B15/Balance Sheet!B12
(v) Return on Equity..equation (4-22) p.115

= Net Income
Total Equity
In B27 enter :
=Income Statement!B15/ Balance Sheet!B22
Copy to C27 reveals that this ratio has declined
from 13.25% in 2008
(vi) Return on Common Equityequation (4-29) p.115
= Net Income Available to Common
Common Equity
(vii) Du Pont Analysis.equation (4-29) p.119
ROE = Net Profit Margin x Total Asser Turnover
1 Total Debt Ratio
In B30 enter the formula:
=(B25*B12)/(1-B14)
Analysis of EPIs Profitability
Du Pont analysis of the firms ROE has shown us
that it could be improved by any of the
following:
(1)Increasing the net profit margin
(2)Increasing the total asset turnover
(3)Increasing the amount of debt relative to equity
Exhibit 4-4 Completed Ratio Worksheet for EPI
Financial Distress Prediction

The Original Z-Score Model
Z = 1.2X1 + 1.4X2 + 3.3X3 +0.6X4 +X5
..equation (4-30)
Z-scores:
Z < 1.81: Bankruptcy predicted within one year
1.81 < Z < 2.675:
Financial distress, possible bankruptcy
Z > 2.675: No Financial Distress predicted

1. In A31 enter the label :
Market Value of Equity and in B31 enter 884,400
2. The market value of the equity
In A32 Enter: Z-Score
In B32 Enter :
=1.2*(Balance Sheet!B8 Balance Sheet!B17)/Balance
Sheet!B12+1.4*(Balance Sheet!B21/Balance Sheet!
B12)+3.3*(Income Statement!B11/Balance Sheet!
B12) + 0.6*(B31/Balance Sheet!B19)+(Income
Statement!B19)+(Income Statement!B5 / Balance
Sheet !B12)

The Z-score Model for Private Firms
Z = 0.717X1 + 0.847X2 + 3.107X3 + 0.420X4 +0.998X5
Z < 1.21
Bankruptcy predicted within one year
1.23 < Z < 2.90
Financial distress, possible bankcruptcy
Z > 2.90
No financial distress predicted
Using Financial Ratio
Trend Analysis
Comparing to Industry Averages
Company Goals and Debt Covenants
Automating Ratio Analysis
Exhibit 4-6 EPIs Ratio with automatic

analysis
Exhibit 4-7
Economic Profit Calculation for EPI
Economics Profit Measures of Performance

Economic Profit
= NOPAT After tax cost operating capital
Exhibit 4-8
EPIs completed economic profit worksheet
THANK YOU

Financial Statement Analysis Tools

Uploaded by

Copyright:

Available Formats

Financial Statement Analysis Tools

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Financial Statement Analysis Tools

Uploaded by

Copyright:

Available Formats

Course

: F0242-Lab Modeling Keuangan

Financial Statement Analysis Tools

4. Use financial ratios to access a firms past

Exhibit 4-1 Ratio Worksheet for EPI

(iv) Fixed Asset Turnover Ratio (p .105)

Exhbit 4-2 EPIs financial ratio

Exhibit 4-3 EPIs financial ratios with the

2008 ratio is 3.65

(v) Return on Equity..equation (4-22) p.115

Exhibit 4-4 Completed Ratio Worksheet for EPI

Financial Distress Prediction

Financial Distress Prediction

Financial Distress Prediction

Bankruptcy predicted within one year

1.23 < Z < 2.90

Financial distress, possible bankcruptcy

No financial distress predicted

Using Financial Ratio

Exhibit 4-6 EPIs Ratio with automatic

Economics Profit Measures of Performance

You might also like