comparable analysis

Tuleenhb
COMPARABLEEXCELSAMPLE.xlsx
Home>Business & Finance homework help>comparable analysis

Sheet1

almaraei sadafco nadec almaraei sadafco nadec
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Equity value book value 10,764.2 11,630.9 12,618.2 13,456.8 849.01 921.53 948.01 1,092.22 1,152.4 1,258.9 1,364.5 1,425.2 13,456.8 1,092.22 1,425.2
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Enterprise Value 40,174.5 55,537.7 56,743.3 65,999.8 2,988.72 3,856.90 3,781.59 2,737.6 3,725.4 4,182.2 3,760.9 65,999.8 3,781.59 3,760.9
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Sales or revenue 11,219.2 12,605.6 13,794.6 14,698.7 1,549.02 1,552.82 1,806.88 1,982.76 1,928.1 2,072.4 2,328.6 2,296.5 14,698.7 1,982.76 2,296.5
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Gross profit 3,962.7 4,535.0 5,283.3 5,833.6 475.03 493.14 552.28 708.64 704.7 775.0 942.6 959.0 5,833.6 708.64 959.0
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EBITDA 3,127.2 3,404.0 3,849.9 4,336.8 240.91 226.94 357.83 333.6 375.5 440.9 507.1 4,336.8 357.83 507.1
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EBIT 1,796.6 1,950.1 2,262.1 2,541.9 183.95 155.59 275.66 130.2 147.8 175.3 200.0 2,541.9 275.66 200.0
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Net income 1,502.2 1,674.3 1,915.7 2,080.5 164.26 171.11 140.95 260.21 100.2 107.2 141.3 100.2 2,080.5 260.21 100.2
profitability ratio FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Gross profit margin 35.32 35.98 38.30 39.69 30.67 31.76 30.57 35.74 36.55 37.40 40.48 41.76 39.69 35.74 41.76
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EBITDA Margin 27.87 27.00 27.91 29.50 14.30 15.51 12.56 18.05 17.30 18.12 18.93 22.08 29.50 18.05 22.08
sales 11,219.2 12,605.6 13,794.6 14,698.7 sales 1,552.8 1,806.9 1,982.8 sales 1,928.1 2,072.4 2,328.6 2,296.5 14,698.7 1,982.8 2,296.5
EBIT 184.0 155.6 275.7 EBIT 130.2 147.8 175.3 200.0 275.7 200.0
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EBIT margin 16.01% 15.47% 16.40% 17.29% 0.12 0.09 0.14 6.75% 7.13% 7.53% 8.71% 17.29% 0.14 8.71%
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Profit margin ratio ,Net income margin (net income/sales) 13.39 13.28 13.89 14.15 10.60 11.02 7.80 13.12 5.20 5.17 6.07 4.36 14.15 13.12 4.36
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
ROIC(EBIT/net debt+equity) 9.24 9.07 9.95 10.31 21.21 20.47 16.17 26.28 5.82 5.53 5.42 5.86 10.31 26.28 5.86
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
ROE 16.98 15.92 16.70 16.06 20.13 19.33 15.08 25.51 8.96 8.89 10.77 7.18 16.06 25.51 7.18
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
ROA 7.04 7.11 7.47 7.38 14.94 15.25 11.99 20.02 3.58 3.35 3.90 2.56 7.38 20.02 2.56
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
ROC 8.97 8.85 8.98 9.92 15.25 6.04 5.45 6.06 5.13 9.92 5.13
3.50 4.00 4.00
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Implied dividend yield 1.59 1.30 1.28 1.26 3.14 2.90 2.82 -63.8 -39.6 41.6 33.9 1.26 2.82 33.9
leverage or credit profile FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
leverage(Net debt to EBITDA) 2.58 2.57 2.40 2.49 -0.58 -0.48 -0.29 -0.69 2.77 4.21 3.88 3.41 2.49 -0.69 3.41
EPS Diluted before Abnormal 4.32 11.46 20.21 3.75 EPS Diluted before Abnormal 8.14 4.32 -24.32 104.68 EPS Diluted before Abnormal 5.03 6.92 22.66 -10.93 3.75 104.68 -10.93
Dividend per Share 20.00 0.00 15.00 4.35 Dividend per Share 0.00 0.00 0.00 14.29 Dividend per Share — — 10.00 — 4.35 14.29 —
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Debt to total capitalization 21.00 1.00 16.00 5.35 1.00 1.00 1.00 15.29 ERROR:#VALUE! ERROR:#VALUE! 0.69 ERROR:#VALUE! 5.35 15.29 ERROR:#VALUE!
Coverage ratio FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
interest coverage ratio( EBIT/interest expense) 8.01 9.38 9.85 7.23 — — 144.46 — 3.63 3.94 3.84 2.97 7.23 — 2.97
Turnover ratio FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Inventory turnover(COGS/inventory) 2.98 3.04 3.04 2.95 3.92 3.51 3.80 3.47 3.19 2.81 2.84 2.48 2.95 3.47 2.48
revenue 11219.182 12605.565 13794.616 14698.662 Revenues 1,549.0 1,552.8 1,806.9 1,982.8 1,928.1 2,072.4 2,328.6 2,296.5 14698.662 1,982.8 2,296.5
assets 23171.372 23948.915 27371.035 29022.74 assets 1,102.7 1,141.3 1,209.1 1,390.5 2,998.6 3,428.8 3,825.7 4,001.4 29022.74 1,390.5 4,001.4
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Total asset turnover 0.48 0.53 0.50 0.51 1.40 1.36 1.49 1.43 0.64 0.60 0.61 0.57 0.51 0.57
17.40 16.52 14.76 13.08 15.97 0.25 16.36 9.73 11.61 7.48 12.36 -1.38 13.08 9.73 -1.38
fixed assets 22.98 20.82 18.90 15.00 30.92 17.82 18.92 -2.48 12.94 5.68 23.74 5.29 15.00 -2.48 5.29
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Fixed asset turnover 0.7574751454 0.7933369788 0.7807418769 0.8715802945 0.52 0.01 0.86 -3.93 0.90 1.32 0.52 0.26- 0.8715802945 -3.93 0.26-
Liquidity ratio FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Cash Ratio 0.49 0.20 0.42 0.15 0.74 0.84 0.38 1.25 0.07 0.07 0.09 0.04 0.15 1.25 0.04
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Current Ratio 1.44 1.21 1.28 1.11 3.91 4.66 3.50 4.14 1.58 0.84 0.92 0.75 1.11 4.14 0.75
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Quick Ratio 0.66 0.38 0.59 0.34 2.00 2.11 1.28 2.08 0.64 0.36 0.24 0.17 0.34 2.08 0.17
current assets 5348.245 4911.572 6154.987 5304.86 Current Assets 680.0 643.4 617.0 813.1 843.8 929.8 995.6 1,000.6 5304.86 813.1 1,000.6
current liabilities 3720.463 4042.934 4806.636 4793.32 Current Liabilities 173.9 138.1 176.1 196.5 1,007.4 1,103.1 1,083.9 1,328.7 4793.32 196.5 1,328.7
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Net working capital ratio(current asset/current liabilities) 1.44 1.21 1.28 1.11 3.91 4.66 3.50 4.14 84% 84% 92% 75% 1.11 4.14 75%
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Debt ratio (total debt / total asset) 42.57 39.91 41.59 40.03 0.00 0.00 0.00 0.00 32.38 48.39 47.38 44.76 40.03 0.00 44.76
Trading multiples
Equity value multiple FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Price to earning ratio P/E 20.84 27.30 24.36 26.96 18.13 27.83 15.43 18.08 20.01 19.22 20.31 26.96 15.43 20.31
net income 164.3 171.1 141.0 260.2 260.2
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
Equity value to net income 7.17 6.95 6.59 6.47 5.17 5.39 6.73 4.20 11.50 11.74 9.66 14.22 6.47 4.20 14.22
Enterprise value multiple FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EV/EBITDA 12.85 16.32 14.74 15.22 12.41 17.00 10.57 8.21 9.92 9.49 7.42 15.22 10.57 7.42
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EV/EBIT 22.36 28.48 25.08 25.97 16.25 24.79 13.72 21.03 25.21 23.85 18.80 25.97 13.72 18.80
FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2013 FY 2014 FY 2015 FY 2016 FY 2016 FY 2016 FY 2016
EV/Sales 3.58 4.41 4.11 4.49 1.92 2.13 1.91 1.42 1.80 1.80 1.64 4.49 1.91 1.64

a1

FY 2016 FY 2016 FY 2016 39.688048000000002 35.739856000000003 41.758201999999997

a2

FY 2016 FY 2016 FY 2016 29.504460999999999 18.047080000000001 22.081188000000001

a3

FY 2016 FY 2016 FY 2016 0.17293247507834389 0.13902713585681403 8.7098659291303407E-2

a4

FY 2016 FY 2016 FY 2016 14.154248000000001 13.123749999999999 4.36266

a5

FY 2016 FY 2016 FY 2016 10.314211 26.278665 5.8635760000000001

a6

FY 2016 FY 2016 FY 2016 16.060317000000001 25.508253 7.1827540000000001

a7

FY 2016 FY 2016 FY 2016 7.3784210000000003 20.019642000000001 2.5600510000000001

Sheet1 (2)

Almarai Sadafco Nadec
Almarai Sadafco Nadec
Equity value book value 13,456.8 1,092.22 1,425.2
Almarai Sadafco Nadec
Enterprise Value 65,999.8 3,781.59 3,760.9
Almarai Sadafco Nadec GP margin
Sales or revenue 14,698.7 1,982.76 2,296.5 0.396880478 0.3573985608 0.4175820189
cost
Almarai Sadafco Nadec 8,865.1 1,274.13 1,337.52
Gross profit 5,833.6 708.64 959.0
Almarai Sadafco Nadec
EBITDA 4,336.8 357.83 507.1
FY 2016 FY 2016 FY 2016
EBIT 2,541.9 275.66 200.0
FY 2016 FY 2016 FY 2016
Net income 2,080.5 260.21 100.2
profitability ratio Almarai Sadafco Nadec
Gross profit margin 39.69 35.74 41.76
Almarai Sadafco Nadec
EBITDA Margin 29.50 18.05 22.08
sales 14,698.7 1,982.8 2,296.5
275.7 200.0
Almarai Sadafco Nadec
EBIT margin 17.29% 0.14 8.71%
Almarai Sadafco Nadec
Profit margin ratio ,Net income margin (net income/sales) 14.15 13.12 4.36
Almarai Sadafco Nadec
ROIC(EBIT/net debt+equity) 10.31 26.28 5.86
Almarai Sadafco Nadec
ROE 16.06 25.51 7.18
Almarai Sadafco Nadec
ROA 7.38 20.02 2.56
FY 2016 FY 2016 FY 2016
ROC 9.92 5.13
4.00
FY 2016 FY 2016 FY 2016
Implied dividend yield 1.26 2.82 33.9
leverage or credit profile FY 2016 FY 2016 FY 2016
leverage(Net debt to EBITDA) 2.49 -0.69 3.41
EPS Diluted before Abnormal 3.75 104.68 -10.93
Dividend per Share 4.35 14.29 —
FY 2016 FY 2016 FY 2016
Debt to total capitalization 5.35 15.29 ERROR:#VALUE!
Coverage ratio FY 2016 FY 2016 FY 2016
interest coverage ratio( EBIT/interest expense) 7.23 — 2.97
Turnover ratio FY 2016 FY 2016 FY 2016
Inventory turnover(COGS/inventory) 2.95 3.47 2.48
revenue 14698.662 1,982.8 2,296.5
assets 29022.74 1,390.5 4,001.4
FY 2016 FY 2016 FY 2016
Total asset turnover 0.51 0.57
13.08 9.73 -1.38
fixed assets 15.00 -2.48 5.29
FY 2016 FY 2016 FY 2016
Fixed asset turnover 0.8715802945 -3.93 0.26-
Liquidity ratio FY 2016 FY 2016 FY 2016
Cash Ratio 0.15 1.25 0.04
FY 2016 FY 2016 FY 2016
Current Ratio 1.11 4.14 0.75
FY 2016 FY 2016 FY 2016
Quick Ratio 0.34 2.08 0.17
current assets 5304.86 813.1 1,000.6
current liabilities 4793.32 196.5 1,328.7
FY 2016 FY 2016 FY 2016
Net working capital ratio(current asset/current liabilities) 1.11 4.14 75%
FY 2016 FY 2016 FY 2016
Debt ratio (total debt / total asset) 40.03 0.00 44.76
Trading multiples
Equity value multiple FY 2016 FY 2016 FY 2016
Price to earning ratio P/E 26.96 15.43 20.31
260.2
FY 2016 FY 2016 FY 2016
Equity value to net income 6.47 4.20 14.22
Enterprise value multiple FY 2016 FY 2016 FY 2016
EV/EBITDA 15.22 10.57 7.42
FY 2016 FY 2016 FY 2016
EV/EBIT 25.97 13.72 18.80
FY 2016 FY 2016 FY 2016
EV/Sales 4.49 1.91 1.64

Gross Profit Margin 2016

Almarai

39.688048000000002 Sadafco

35.739856000000003 Nadec

41.758201999999997

EBITDA Margin

Almarai

29.504460999999999 Sadafco

18.047080000000001 Nadec

22.081188000000001

EBIT Margin

Almarai

0.17293247507834389 Sadafco

0.13902713585681403 Nadec

8.7098659291303407E-2

Profit Margin

Almarai

14.154248000000001 Sadafco

13.123749999999999 Nadec

4.36266

ROIC

Almarai

10.314211 Sadafco

26.278665 Nadec

5.8635760000000001

ROE

Almarai

16.060317000000001 Sadafco

25.508253 Nadec

7.1827540000000001

ROA

Almarai

7.3784210000000003 Sadafco

20.019642000000001 Nadec

2.5600510000000001

Sheet2

FY 2013 FY 2014 FY 2015 FY 2016
Equity value book value 10,764.2 11,630.9 12,618.2 13,456.8
FY 2013 FY 2014 FY 2015 FY 2016
Enterprise Value 40,174.5 55,537.7 56,743.3 65,999.8
FY 2013 FY 2014 FY 2015 FY 2016
Sales or revenue 11,219.2 12,605.6 13,794.6 14,698.7
FY 2013 FY 2014 FY 2015 FY 2016
Gross profit 3,962.7 4,535.0 5,283.3 5,833.6
FY 2013 FY 2014 FY 2015 FY 2016
EBITDA 3,127.2 3,404.0 3,849.9 4,336.8
FY 2013 FY 2014 FY 2015 FY 2016
EBIT 1,542.4 1,755.0 1,864.4 2,157.1
FY 2013 FY 2014 FY 2015 FY 2016
Net income 1,502.2 1,674.3 1,915.7 2,080.5
profitability ratio FY 2013 FY 2014 FY 2015 FY 2016
Gross profit margin 35.32 35.98 38.30 39.69
FY 2013 FY 2014 FY 2015 FY 2016
EBITDA Margin 27.87 27.00 27.91 29.50
FY 2013 FY 2014 FY 2015 FY 2016
EBIT margin 16.01% 15.47% 16.40% 17.29% calculated
FY 2013 FY 2014 FY 2015 FY 2016
Profit margin ratio ,Net income margin (net income/sales) 13.39 13.28 13.89 14.15
FY 2013 FY 2014 FY 2015 FY 2016
ROIC(EBIT/net debt+equity) 9.24 9.07 9.95 10.31
FY 2013 FY 2014 FY 2015 FY 2016
ROE 16.98 15.92 16.70 16.06
FY 2013 FY 2014 FY 2015 FY 2016
ROA 7.04 7.11 7.47 7.38
FY 2013 FY 2014 FY 2015 FY 2016
ROC 8.97 8.85 8.98 9.92
FY 2013 FY 2014 FY 2015 FY 2016
Implied dividend yield 1.59 1.30 1.28 1.26
leverage or credit profile FY 2013 FY 2014 FY 2015 FY 2016
leverage(Net debt to EBITDA) 2.58 2.57 2.40 2.49
FY 2013 FY 2014 FY 2015 FY 2016
Debt to total capitalization 21.00 1.00 16.00 5.35 calculated
Coverage ratio FY 2013 FY 2014 FY 2015 FY 2016
interest coverage ratio( EBIT/interest expense) 8.01 9.38 9.85 7.23
Turnover ratio FY 2013 FY 2014 FY 2015 FY 2016
Inventory turnover(COGS/inventory) 2.98 3.04 3.04 2.95
FY 2013 FY 2014 FY 2015 FY 2016
Total asset turnover 0.48 0.53 0.50 0.51 calculated
FY 2013 FY 2014 FY 2015 FY 2016
Fixed asset turnover 0.76 0.79 0.78 0.87 calculated
Liquidity ratio FY 2013 FY 2014 FY 2015 FY 2016
Cash Ratio 0.49 0.20 0.42 0.15
FY 2013 FY 2014 FY 2015 FY 2016
Current Ratio 1.44 1.21 1.28 1.11
FY 2013 FY 2014 FY 2015 FY 2016
Quick Ratio 0.66 0.38 0.59 0.34
FY 2013 FY 2014 FY 2015 FY 2016
Net working capital ratio(current asset/current liabilities) 1.44 1.21 1.28 1.11 calculated
FY 2013 FY 2014 FY 2015 FY 2016
Debt ratio (total debt / total asset) 42.57 39.91 41.59 40.03
Trading multiples
Equity value multiple FY 2013 FY 2014 FY 2015 FY 2016
Price to earning ratio P/E 20.84 27.30 24.36 26.96
FY 2013 FY 2014 FY 2015 FY 2016
Equity value to net income
Enterprise value multiple FY 2013 FY 2014 FY 2015 FY 2016
EV/EBITDA 12.85 16.32 14.74 15.22
FY 2013 FY 2014 FY 2015 FY 2016
EV/EBIT 22.36 28.48 25.08 25.97
FY 2013 FY 2014 FY 2015 FY 2016
EV/Sales 3.58 4.41 4.11 4.49