Name

Industry
Market Cap (Cr)
Exchange

Sr. No.

Competitor

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Arvind Remedies
Pharmaceuticals
318.15
BSE

Current Market Price

Current EPS
Current Sales/share
Current BV/share

Price

EPS

PE ratio

100

10

10

Average

10

Price Based on PE ratio

Price Based on PS ratio
Price Based on PBV ratio
Result

100
96
100
Reasonable

200
10
8
10

Current Market Price

Current EPS
Current Sales/share
Current BV/share

20
25
20

Current PE ratio
Current PS ratio
Current PBV ratio

Sales, Cr. Shares, Cr Sales / Share PS ratio BV / share PBV Ratio

100

12

8.3

12

12

10

10

10

1. Click File > Make a copy.. to get your own version of this spreadsheet

aapl

Ticker

Company info
Name
Industry
Market Cap (Cr)
Exchange

Analyze US listed
AAPL

2. Click Log in twice: first

Arvind Remedies
Pharmaceuticals
$318
BSE

Score

75

Intrinsic value estimates

$272

out of 100

P/E Valuation
Fundamentals
Return on Equity
Debt/Equity
Conservative PEG ratio
P/E ratio
Net margin
Bookvalue per share
Growth rate next 5yr
FCF yield
Asset turnover change
Current ratio
Altman Z Score
Accrual profit
Defensive profit
Enterprising profit

Judgement
Good
Good
Good
Good
Good
Good
Relatively low
Low
Good
Bad
Bad
Reasonable
Positive & increasing
Bad

Debt Repayment Period

Good

Creation date:
8/31/2014 10:33:00

Points Weight
9.09
5
9.09
5
1.82
1
3.64
2
5.45
3
9.09
5
0.00
1
0.00
1
7.27
4
0.00
5
0.00
3
5.45
3
5.45
3
0.00
4
18.18

10
55

Price info
Average estimate
The estimate is
Current share price
Undervalued
Max purchase price

Tutorials:
Support:

timates

##### #####
DCF Valuation

ROE Valuation

$143,818
Reasonable
$47.00
305895%
$88,928.00

http://www.valuespreadsheet.com/tutorials
support@valuespreadsheet.com

Financial statements
Income statement
In Millions of USD (except for per share items)
Revenue
Other Revenue, Total
Total Revenue
Cost of Revenue, Total
Gross Profit
Selling/General/Admin. Expenses, Total
Research & Development
Depreciation/Amortization
Interest Expense(Income) - Net Operating
Unusual Expense (Income)
Other Operating Expenses, Total
Total Operating Expense
Operating Income
Interest Income(Expense), Net Non-Operating
Gain (Loss) on Sale of Assets
Other, Net
Income Before Tax
Income After Tax
Minority Interest
Equity In Affiliates
Net Income Before Extra. Items
Accounting Change
Discontinued Operations
Extraordinary Item
Net Income
Preferred Dividends
Income Available to Common Excl. Extra Items
Income Available to Common Incl. Extra Items
Basic Weighted Average Shares
Basic EPS Excluding Extraordinary Items
Basic EPS Including Extraordinary Items
Dilution Adjustment
Diluted Weighted Average Shares
Diluted EPS Excluding Extraordinary Items
Diluted EPS Including Extraordinary Items

52 weeks ending
2013-09-28
911.11
0.47
911.57
702.51
209.06
0
0
13.9
0
31.66
45.56
163.5
77.1
86.4
58.84
58.84
0.06
58.9
58.9
58.9
4
8.65
8.65
0
4.3498
8.65
-

52 weeks ending
2012-09-29
664.26
0.59
664.85
509.38
155.47
0
0
12.59
0
21.15
33.74
121.73
59.19
62.54
40.62
40.62
0
40.62
40.62
40.62
3
8.42
8.42
2.5639
8.42
-

Dividends per Share - Common Stock Primary Issue

Gross Dividends - Common Stock
Net Income after Stock Based Comp. Expense
Basic EPS after Stock Based Comp. Expense
Diluted EPS after Stock Based Comp. Expense

Depreciation, Supplemental
Total Special Items
Normalized Income Before Taxes
Effect of Special Items on Income Taxes
Income Taxes Ex. Impact of Special Items

Normalized Income After Taxes

Normalized Income Avail to Common
Basic Normalized EPS
Diluted Normalized EPS

8.65

8.42

Balance sheet
52 weeks ending
2011-09-24
436.63
0.39
437.02
351.15
85.87
0
0
3.24
0
17.69
20.93
64.94
27.72
37.22
19.41
19.41
0
19.41
19.41
19.41
27
0.4
0.4
26.8918
0.4
-

52 weeks ending
2010-09-25
362.55
0.34
362.9
311.99
50.91
0
0
2.43
0
9.31
11.74
39.17
16.39
22.78
16.89
16.89
0.02
16.9
16.9
16.9
26
0.35
0.35
26.0865
0.35
-

In Millions of USD (except for per share

items)
Cash & Equivalents
Short Term Investments
Cash and Short Term Investments
Accounts Receivable - Trade, Net
Receivables - Other
Total Receivables, Net
Total Inventory
Prepaid Expenses
Other Current Assets, Total
Total Current Assets
Property/Plant/Equipment, Total - Gross
Accumulated Depreciation, Total
Goodwill, Net
Intangibles, Net
Long Term Investments
Other Long Term Assets, Total
Total Assets
Accounts Payable
Accrued Expenses
Notes Payable/Short Term Debt
Current Port. of LT Debt/Capital Leases
Other Current liabilities, Total
Total Current Liabilities
Long Term Debt
Capital Lease Obligations
Total Long Term Debt
Total Debt
Deferred Income Tax
Minority Interest
Other Liabilities, Total
Total Liabilities
Redeemable Preferred Stock, Total
Preferred Stock - Non Redeemable, Net
Common Stock, Total
Additional Paid-In Capital

Retained Earnings (Accumulated Deficit)

Treasury Stock - Common
Other Equity, Total
Total Equity
Total Liabilities & Shareholders' Equity

Shares Outs - Common Stock Primary Issue

Total Common Shares Outstanding

0.4

0.35

As of 2013-09-28
23.7
165.04
188.74
241.53
0
241.53
121.13
165.04
386.36
277.77
26.92
0
4179
106215
5146
207000
22367
4782
0
16509
43658
16960
16960
16960
16489
6344
83451
19764
-

As of 2012-09-29
21.95
29.69
51.64
169.88
0
169.88
87.72
29.69
279.55
258.07
15.64
0
4224
92122
5478
176064
21175
3283
0
14084
38542
0
0
13847
5465
57854
16422
-

104256
-

101289
-

-191
123549
207000
-

731
118210
176064

37169
-

130
76615
116371
-

6574.46

As of 2010-09-25
7.24
35.3
42.54
106.54
0
106.54
45.71
27.46
159.49
48.02
12.06
0
342
25391
2263
75183
12015
1593
0
7114
20722
0
0
4300
2370
27392
10668
-

62841
-

6294.37

As of 2011-09-24
13.81
31.52
45.33
134.54
0
134.54
57.11
24.54
205.46
57.98
13.22
0
3536
55618
3556
116371
14632
2428
0
10910
27970
0
0
8159
3627
39756
13331
-

171
47791
75183
-

6504.94

6411.79

Cash flow statement

In Millions of USD (except for per
share items)
Net Income/Starting Line
Depreciation/Depletion
Amortization
Deferred Taxes
Non-Cash Items
Changes in Working Capital
Cash from Operating Activities
Capital Expenditures
Other Investing Cash Flow Items, Total
Cash from Investing Activities
Financing Cash Flow Items
Total Cash Dividends Paid
Issuance (Retirement) of Stock, Net
Issuance (Retirement) of Debt, Net
Cash from Financing Activities
Foreign Exchange Effects
Net Change in Cash
Cash Interest Paid, Supplemental
Cash Taxes Paid, Supplemental

52 weeks ending
2013-09-28
37037
6757
1141
2253
6478
53666
-9076
-24698
-33774
-381
-10564
-22330
16896
-16379
3513
9128

52 weeks ending
2012-09-29
41733
3277
4405
1740
-299
50856
-9402
-38825
-48227
125
-2488
665
-1698
931
7682

52 weeks ending
2011-09-24
25922
1814
2868
1168
5757
37529
-7452
-32967
-40419
613
0
831
1444
-1446
3338

52 weeks ending
2010-09-25
14013
1027
1440
903
1212
18595
-2121
-11733
-13854
345
912
1257
5998
2697

Inputs
Conservatism inputs
Desired return on investment
Margin of Safety
Discount rate
Growth decline rate
Year 10 FCF multiplier

20% annualy
25%
9%
5%
12

Valuation inputs
Price
EPS
Dividend Yield
Current ratio
Past 5 years EPS growth per year
Next 5 years EPS growth per year
Sustainable Growth Rate
Shares outstanding (Cr)
Cash and Short Term Investments
Cash from Operating Activities
Capital Expenditures
Enterprise Value

Month

AVG P/E
9/4/2014
9/5/2014
9/6/2014
9/7/2014
9/8/2014
9/9/2014
9/10/2014
9/11/2014
9/12/2014
9/13/2014

54.6
34.4
34.0
39.1
21.2
20.4
18.7
13.8
15.1
12.0

$47.00
$ 10.05
2.13%
0.94
31.00%
21.00%
27.56%
4.35
$24
$13
$0
$17,254.45

NET PROFIT
PRICE/ SALES PRICE/ BOOK
MARGIN (%)
1.8
3.0
3.3
3.3
6.0
9.6
3.5
6.6
10.3
5.7
9.2
14.6
3.2
4.5
14.9
3.9
5.3
19.2
4.0
5.4
21.5
3.3
4.6
23.9
4.0
5.3
26.7
2.6
3.5
21.7

There is a known bug with the dates in the two tables above. It should be read as M/Y/D.

M/Y/D.

BOOK
VALUE/
SHARE
9/4/2014
9/5/2014
9/6/2014
9/7/2014
9/8/2014
9/9/2014
9/10/2014
9/11/2014
9/12/2014
9/13/2014

0.9
1.3
1.7
2.4
3.6
5.0
7.5
11.8
18.0
19.6

DEBT/
EQUITY
NA
NA
NA
NA
NA
NA
NA
NA
NA

RETURN ON EQUITY
(%)
5.9
21.3
22.8
28.5
27.2
31.3
35.3
41.7
42.8
0.1
30.6

There is a known bug with the dates in the two tables above. It should be read as M/Y/D.

M/Y/D.

RETURN ON
ASSETS
(%)
3.7
13.6
13.8
16.4
14.9
18.9
22.8
27.1
28.5
19.3

INTEREST
COVERAGE
NA
NA
NA
NA
NA
NA
NA
NA
NA
369.8

Fundamental Analysis
Variable
Return on Equity
Debt/Equity
Conservative PEG ratio
P/E ratio

Value
30.6
0.14
0.40
4.68

Judgement
Good
Good
Good
Good

Net margin

Stable/Increasing Good

Bookvalue per share

Next 5yr EPS growth rate
FCF yield
Asset turnover change
Current ratio
Altman Z Score
Accrual profit

Stable/Increasing
21.00%
0.08%
16.62%
0.94
-0.24
$8.65

Defensive profit
Enterprising profit
Debt Repayment Period

Value versus Price

Avg intrinsic value estimate
Current price
Undervalued
Max purchase price

Good
Relatively low
Low
Good
Bad
Bad
Reasonable

$7.08 Positive & increasing

0.06% Bad
0.38 Good

$143,817.67 Reasonable
$47.00
305895.05%
$88,928.00
#REF!

nalysis
Comments
Return on Equity is consistently high, company capital invested profitably
Firm not dependent on loans, low interest rate risk
Stock has high potential value
Currently trading at a multiple below the historic median
The company is becoming more efficient and/or is able to increase its prices
The company is growing or maintaining its book value
Growth rate significantly below historic rate, is there something slowing this company down?
The company is either not generating much excess cash, or is heavily investing in growth
The company is becoming more efficient at using its assets to generate revenue
The company is unable to meet its obligations, bad financial health
High risk of bankruptcy within the coming 2 years
Earnings figures seem trustworthy, probably no earnings manipulation
The company is able to self-fund its growth and is growing its yearly free cash flows
The company is not creating much value, and might even be destroying value
There is enough cash being generated to pay of debt in a reasonable amount of time

The three valuation methods yield approximately the same result

Disclaimer: the information in this entire workbook is for information purposes only and is not a professional
investment advice. You are responsible for your own investment decisions, even if they are based on information
gained from this workbook. The information in this workbook does not constitute advice, merely a source of
information which may be used to aid in your decision making. You should not rely on any information in this
workbook to make (or refrain from making) any decision or take (or refrain from making) any action. The author
cannot be held responsible for any loss or damage arising from the use of this workbook.

Graphs
Average P/E
60.0

50.0

Average P/E

40.0

30.0

20.0

10.0

0.0

Price/Book
10.0
9.0
8.0

Price/Book

7.0
6.0
5.0
4.0
3.0
2.0
1.0
0.0

Time >

Debt/Equity
0.2
0.1

0.1

Bookvalue per share

0.1
0.1
0.1
0.1
0.0
0.0
0.0
0.0

Time >

Price/Sales
6.0

5.0

Price/Sales

4.0

3.0

2.0

1.0

0.0

Time >
Net margin (%)

30.0

25.0

Net margin (%)

20.0

15.0

10.0

5.0

0.0

Time >

Book value per share

25.0

Debt/Equity

20.0

15.0

10.0

5.0

0.0

Time >

Return on Assets
35.0
30.0

Return on Assets

25.0
20.0
15.0
10.0
5.0
0.0

Time >
Return on Equity

50.0
45.0
40.0

Return on Equity

35.0
30.0
25.0
20.0
15.0
10.0
5.0
0.0

Time >

P/E valuation
Inputs
Price
EPS
Median historical P/E
Expected growth rate
Margin of Safety
Conservative growth rate
Growth decline rate
Discount rate

47
10.05
20.82
21.00%
0.25
15.75%
0.05
0.09

Calculations

Value in 5 years
Present value

Year EPS*Growth rate

1
$11.63
2
$13.47
3
$15.48
4
$17.68
5
$20.07
$417.81
$271.55

$272

-$3,796

DCF valuation
Inputs
Total cash (mil $)
Free cash flow (mil $)
Shares outstanding (mil)
Expected growth rate
Margin of Safety
Conservative growth rate
Growth decline rate
Discount rate
Valuation last FCF

$23.70
$13.28
4.35
21.00%
25.00%
15.75%
5.00%
9.00%
12

Calculations
Year FCF * Growth rate NPV FCF
1
$ 15.37
2
$ 17.67
3
$ 20.18
4
$ 22.91
5
$ 25.85
6
$ 29.00
7
$ 32.36
8
$ 35.91
9
$ 39.67
10
$ 43.60
Total NPV FCF
Year 10 FCF value
Cash on Hand
Total Debt
Company value:

$ 15.37
$ 16.21
$ 16.99
$ 17.69
$ 18.31
$ 18.85
$ 19.29
$ 19.65
$ 19.91
$ 20.08

$ 182.34
$ 240.92
$ 23.70
$ 16,960.00
-$ 16,513.04

#####

ROE valuation
Inputs
Shareholders equity
Return on Equity (avg)
Shares outstanding
Dividend Yield
Dividend payout ratio
Margin of Safety
Conservative growth rate
Discount rate

$123,549.00
36.34%
4.35
2.13%
9.95%
25.00%
24.54%
9.00%

Calculations (per share)

Year Shareholders equity Dividend
1
$ 35,374.43
2
$ 44,056.40
3
$ 54,869.19
4
$ 68,335.77
5
$ 85,107.45
6
$ 105,995.43
7
$ 132,009.95
8
$ 164,409.23
9
$ 204,760.28
10
$ 255,014.72
Year 10 net income
Required value
NPV required value
NPV dividends
Intrinsic value:

NPV dividends
$ 1.25
$ 1.55
$ 1.93
$ 2.41
$ 3.00
$ 3.73
$ 4.65
$ 5.79
$ 7.21
$ 8.98

$ 92,672.35
$ 1,029,692.78
$ 434,953.36
$ 24.39 '+
$ 434,977.75

$ 1.25
$ 1.42
$ 1.63
$ 1.86
$ 2.12
$ 2.43
$ 2.77
$ 3.17
$ 3.62
$ 4.13

Financial health
Earnings Power
Year
Accrual profit
Defensive profit
Enterprising profit
Debt Repayment Period

2013
$8.65
$7.08
0.06%

2012
$8.42
$6.31
0.05%

2011
$0.40
$4.62
0.05%

2010
$0.35
$2.57
0.05%

0.38

0.00

0.00

0.00

Quality of Profits
Accrual profit

Defensive profit

Enterprising profit

$10.00
$9.00
$8.00
$7.00
$6.00
$5.00
$4.00
$3.00
$2.00
$1.00
$0.00
Learn more about Earnings Power here:
http://www.valuespreadsheet.com/value-investing-blog/earnings-manipulation-detect-value-traps-hewitt-heiserman

-heiserman

Earnings Power chart*

0.07%

Enterprising profits

0.06%
0.05%
0.04%
0.03%
0.02%
0.01%
0.00%
$0.00

$2.00

$4.00

$6.00

$8.00

Defensive profits
*Slightly different from the original Earnings Power Chart

Altman Z Score
Inputs
Working capital
Total assets
Retained earnings
EBIT
Sales
Market value of equity
Total liabilities

-0.24
-$43,271.64
$207,000.00
$39.37
$195.16
$911.57
$318.15
$83,451.00

Learn more about the Altman-Z score here:

http://www.investopedia.com/terms/a/altman.asp