Description
A spreadsheet showing the financial analysis of various automobile companies
Group Number :
1
Sector : S.No.
Automobile Companies
1 MARUTI SUZUKI INDIA LTD. 2 TVS MOTORS 3 ASHOK LEYLAND 4 BAJAJ 5 HERO HONDA 6 EICHER 7 HINDUTAN MOTORS 8 TATA MOTORS 9 FORCE MOTORS 10 M&M rf = 7.83% risk free premium
CoE = Market risk premium* Eq. Beta + Risk free premium Capital Asset pricing Model
Ownership Pattern Promoters 54.21% 57.4% 38.60% Financial Institutions 39.14 % 22.03% 44.21% Retail Investors 6.64% 18.86% 15.83% Pension Funds Mutual Funds 2.69% 1.71% 1.36%
29.2% 47.23% rm - rf = 6% - market risk premium
9.55%
2.40%
eta + Risk free premium
2004 Sales EBIT PAT Degree of Financial Leverage Degree of Operating Leverage Combined Leverage Interest Loans Equity Debt/Equity Cost of Debt Cost of Equity WACC No. of Share Share Value Economic Value Added Market Value Added rf = 7.83% rm - rf = 6% 43.4 311.9 3,591.20 0.11 9.740301379 12.19 11.99424253 288900000 400 101.09272 7964.8 9,104.40 813.2 621.82
Beta value = 0.7057 2005 10,923.80 1340.9 860.1 0.590518055 3.247228604 1.91754712 36 307.6 4,378.80 0.08 8.192457737 13.2 12.87132127 288900000 650 335.4284 14399.7
2006 12,015.90 1770.4 1,197.07 1.223138152 3.203893789 3.918804728 20.4 71.7 5,452.60 0.04 19.91631799 11.8 11.90534185 288900000 932 581.5932 21472.88
2007 14,696.30 2317.4 1,535.29 0.914458104 1.385072866 1.266591107 37.6 630.8 6,853.90 0.06 4.172479391 12.11 11.44103692 288900000 1000 765.85271 22036.1
DFL = %c in PAT / % change in EBIT DOL = %c in EBIT / % change in sales
Company Name NOC Year End Automobiles - passenger cars Equity Gross Blk Sales
Full Year (Rs Cr
8
3352.76
24484.56
42809.85
Daewoo Motors Ford India Hind.Motors Honda Siel Cars Hyundai Motor I Maestro Motors
200203 200903 200903 200903 200903 199803
511.18 1,097.00 161.17 360 812.54 3.48
4,153.06 1,667.81 481.25 1,893.34 7,560.52 7.98
221.78 2,119.68 626.65 3,534.92 15,522.55 2.2
Maruti Suzuki Pal-Peugeot
200903 199609
144.46 262.93
8,720.60 0
20,530.10 251.97
25,000.00 2008 17,891.60 2562.6 1,669.71 0.827473333 0.486648997 0.402689068 59.6 900.2 8,415.40 0.1 4.634525661 12.04 11.32438233 288900000 525 738.88584 6751.85 2009 20,000.00 20,530.10 1726.8 1,072.63 1.096402282 -2.211635878 2.424842625 51 698.9 9,344.90 0.09 5.108026899 12.06 11.57624544 288900000 1460 46.06506 32834.5 15,000.00 10,000.00 5,000.00 0.00 1 2 3 4 5 6 Series1 Series2 Series3
35000 30000 25000 20000 15000 10000 5000 0 1 2 3 4 5 6 Series1 Series2
Full Year (Rs Cr.) NP NP Var% Div% 2010 Sales 451.71 --BMW India Pvt. Ltd. --MercedesBenz India Pvt Ltd --Volkswagen India Pvt Ltd --Nissan Motor India Pvt Ltd --Hind. Motors --Mahindra & Mahindra --Tata Motors 339 222 53 2009 Sales % Change
-314.72 -118.22 -95.82 -194.8 195.56 -4.12
-59 -133 -76 0 -18 -15
0 0 0 0 15 0
395 589 23 762 537 23607
231 111 3 544 1108 15788
71 431 667 40 -52 50
1,072.63 -88.8
-36 0
70 0
--Maruti Udyog --Gen Motors India --Ford India --Honda Siel Cars --Hyundai Motor I --Fiat Auto India --Toyota Kirloskar
85572 9498 3001 6156 54616 2335 877
70915 3775 2599 5476 38251 1309 534
21 152 15 12 43 78 64
Beta value = 0.8362 2004 Sales EBIT PAT Degree of Financial Leverage Degree of Operating Leverage Combined Leverage Interest Loans Equity Debt/Equity Cost of Debt Cost of Equity WACC No. of Share Share Value Economic Value Added Market Value Added 10.3 119.01 574.95 0.24 2,820.21 224.83 138.01
2005 2,875.91 208.78 110.45 2.797356912 -3.614490014 10.11101862 8.33 186.84 678.83 0.24 3.120852066 12.84 10.74228886 237543557 100 53.153228 1509.76557
2006 3,234.96 189.44 103.74 0.655826896 -0.741972717 0.486605664 20.99 385.04 766.12 0.4 3.815967172 12.84 9.821641475 237543557 86 19.545192 891.7145902
2007 3,854.96 125.9 60.16
2008 3,219.50 46.84 3.03
1.252464592 1.512256001 -1.750059244 3.809454033 2.191887237 5.760869723 35.05 633.56 809.27 11.47 666.34 821.58
0.65 0.8 3.872561399 1.204940421 12.84 12.84 8.902314756 7.629433841 237543557 75 -40.315268 338.7466775 237543557 25 -80.731872 -894.061108
237543557 70 157.381 1087.854899
Company Name Year End Automobile sMotorcycles / Mopeds Equity Gross Blk Sales
Full Year (Rs Cr.) NP
94.46 200803 200903 200906 200903 200903 10 39.94 10.37 10.4 23.75
4788.51 0.99 2,516.27 305.75 100.14 1,865.36
16168.9 0 12,315.24 64.31 118.43 3,670.92
1176.84 -0.72 1,180.63 -37.11 4.56 29.48
Escorts Automot Hero Honda Motor Kinetic Engg. Majestic Auto TVS Motor Co.
5,000.00 2009 3,670.92 95.71 29.48 8.366765467 7.441030516 62.25735717 64.61 905.98 810.16 0.96 4.992052805 12.84 8.696932884 237543557 76 -82.254544 89.1910332 2000 1500 1000 Series1 500 0 1 -500 -1000 2 3 4 5 6 Series2 4,000.00 3,000.00 2,000.00 1,000.00 0.00 1 2 3 4 5 6 Series1 Series2 Series3
Year (Rs Cr.) Div%
0 1,000.00 0 0 70
Beta value = 1.044 2004 Sales EBIT PAT Degree of Financial Leverage Degree of Operating Leverage Combined Leverage Interest Loans Equity Debt/Equity Cost of Debt Cost of Equity WACC No. of Share Share Value Economic Value Added Market Value Added 59.41 498.9 1,051.80 0.24 8.335738625 14.09 12.23870639 1330338317 23.3 239.3643797 2047.888279 3,440.54 345.87 196.61 2005 4,247.65 385.41 250.42 2.394052212 0.487323836 1.166678708 30.4 880.41 1,167.87 0.24 2.41705569 14.19 9.129647949 1330338317 34.2 268.2708343 2501.477044 2006 5,329.81 492.95 302.47 0.744911483 1.095227401 0.815847468 40.65 691.93 1,412.45 0.4 4.112410215 15.06 11.4603812 1330338317 41.1 342.8677687 3363.310483 2007 7,320.37 633.35 425.77 1.431253407 0.762606832 1.091483626 28.84 640.4 1,894.57 0.65 3.152404747 15.1 12.08172365 1330338317 49.1 441.1397949 3996.991136
2008 7,935.48 714.46 450.76 0.458311307 1.524090905 0.698508095 76.31 887.5 2,148.98 0.8 6.018816901 14.2 11.8088102 1330338317 16.45 497.7343983 -848.0734685
2009 6,126.01 368.77 178.79 1.247000805 2.121928332 2.646046338 160.32 1,961.98 3,473.90 0.96 5.719935983 14.09 11.06898074 1330338317 50.2 255.946242 1242.418351
9,000.00 8,000.00 7,000.00 6,000.00 5,000.00 4,000.00 3,000.00 2,000.00 1,000.00 0.00 1 4000 3000 2000 1000 0 1 -1000 2 3 4 5 6 2 3 4 5 6
Series1 Series2 Series3
3 2.5 2 Series1 Series2 1.5 1 0.5 0 1 2 3
Combined Leverage
Series1
3
4
5
2005 Sales EBIT PAT DoF DOL 7649.51 744.56 512.67 1.142 1.377
2006
2007
2008
2009
9273.09 11231.99 12894.94 14713.03 1126.74 1457.81 1494.95 1170.59 857.1 1068.39 1103.37 836.78 1.3088 3.1653 0.838 1.39 1.16482 0.46 1% 8% 2% 1.285 0.172 1.113 -1.5388
Combined Leverage 1.572534 4.142745 Debt/Equity 0.53 Cost of Debt 2.00% Cost of Equity 8% WACC 3% 0.31 2.13% 8% 3%
0.22102 1.712684 0.6 2.37% 8% 3% 0.77 2.31% 8% 3%
16000 14000 12000 10000 Int 8000 6000 Debt 4000 2000 0 2005 2006 2007 2008 2009 cost interest #VALUE!
1,052.62 883.39 1,636.00 2,587.06 4,052.76 134.12 93.884 4052.76 0.023165 87.59 61.313 2587.06 19.8 13.86 1636 26.96 18.872 883.39 30.24 21.168 1052.62 0.02011 Sales EBIT PAT
0.0237 0.008472 0.021363
doc_799524401.xls
A spreadsheet showing the financial analysis of various automobile companies
Group Number :
1
Sector : S.No.
Automobile Companies
1 MARUTI SUZUKI INDIA LTD. 2 TVS MOTORS 3 ASHOK LEYLAND 4 BAJAJ 5 HERO HONDA 6 EICHER 7 HINDUTAN MOTORS 8 TATA MOTORS 9 FORCE MOTORS 10 M&M rf = 7.83% risk free premium
CoE = Market risk premium* Eq. Beta + Risk free premium Capital Asset pricing Model
Ownership Pattern Promoters 54.21% 57.4% 38.60% Financial Institutions 39.14 % 22.03% 44.21% Retail Investors 6.64% 18.86% 15.83% Pension Funds Mutual Funds 2.69% 1.71% 1.36%
29.2% 47.23% rm - rf = 6% - market risk premium
9.55%
2.40%
eta + Risk free premium
2004 Sales EBIT PAT Degree of Financial Leverage Degree of Operating Leverage Combined Leverage Interest Loans Equity Debt/Equity Cost of Debt Cost of Equity WACC No. of Share Share Value Economic Value Added Market Value Added rf = 7.83% rm - rf = 6% 43.4 311.9 3,591.20 0.11 9.740301379 12.19 11.99424253 288900000 400 101.09272 7964.8 9,104.40 813.2 621.82
Beta value = 0.7057 2005 10,923.80 1340.9 860.1 0.590518055 3.247228604 1.91754712 36 307.6 4,378.80 0.08 8.192457737 13.2 12.87132127 288900000 650 335.4284 14399.7
2006 12,015.90 1770.4 1,197.07 1.223138152 3.203893789 3.918804728 20.4 71.7 5,452.60 0.04 19.91631799 11.8 11.90534185 288900000 932 581.5932 21472.88
2007 14,696.30 2317.4 1,535.29 0.914458104 1.385072866 1.266591107 37.6 630.8 6,853.90 0.06 4.172479391 12.11 11.44103692 288900000 1000 765.85271 22036.1
DFL = %c in PAT / % change in EBIT DOL = %c in EBIT / % change in sales
Company Name NOC Year End Automobiles - passenger cars Equity Gross Blk Sales
Full Year (Rs Cr
8
3352.76
24484.56
42809.85
Daewoo Motors Ford India Hind.Motors Honda Siel Cars Hyundai Motor I Maestro Motors
200203 200903 200903 200903 200903 199803
511.18 1,097.00 161.17 360 812.54 3.48
4,153.06 1,667.81 481.25 1,893.34 7,560.52 7.98
221.78 2,119.68 626.65 3,534.92 15,522.55 2.2
Maruti Suzuki Pal-Peugeot
200903 199609
144.46 262.93
8,720.60 0
20,530.10 251.97
25,000.00 2008 17,891.60 2562.6 1,669.71 0.827473333 0.486648997 0.402689068 59.6 900.2 8,415.40 0.1 4.634525661 12.04 11.32438233 288900000 525 738.88584 6751.85 2009 20,000.00 20,530.10 1726.8 1,072.63 1.096402282 -2.211635878 2.424842625 51 698.9 9,344.90 0.09 5.108026899 12.06 11.57624544 288900000 1460 46.06506 32834.5 15,000.00 10,000.00 5,000.00 0.00 1 2 3 4 5 6 Series1 Series2 Series3
35000 30000 25000 20000 15000 10000 5000 0 1 2 3 4 5 6 Series1 Series2
Full Year (Rs Cr.) NP NP Var% Div% 2010 Sales 451.71 --BMW India Pvt. Ltd. --MercedesBenz India Pvt Ltd --Volkswagen India Pvt Ltd --Nissan Motor India Pvt Ltd --Hind. Motors --Mahindra & Mahindra --Tata Motors 339 222 53 2009 Sales % Change
-314.72 -118.22 -95.82 -194.8 195.56 -4.12
-59 -133 -76 0 -18 -15
0 0 0 0 15 0
395 589 23 762 537 23607
231 111 3 544 1108 15788
71 431 667 40 -52 50
1,072.63 -88.8
-36 0
70 0
--Maruti Udyog --Gen Motors India --Ford India --Honda Siel Cars --Hyundai Motor I --Fiat Auto India --Toyota Kirloskar
85572 9498 3001 6156 54616 2335 877
70915 3775 2599 5476 38251 1309 534
21 152 15 12 43 78 64
Beta value = 0.8362 2004 Sales EBIT PAT Degree of Financial Leverage Degree of Operating Leverage Combined Leverage Interest Loans Equity Debt/Equity Cost of Debt Cost of Equity WACC No. of Share Share Value Economic Value Added Market Value Added 10.3 119.01 574.95 0.24 2,820.21 224.83 138.01
2005 2,875.91 208.78 110.45 2.797356912 -3.614490014 10.11101862 8.33 186.84 678.83 0.24 3.120852066 12.84 10.74228886 237543557 100 53.153228 1509.76557
2006 3,234.96 189.44 103.74 0.655826896 -0.741972717 0.486605664 20.99 385.04 766.12 0.4 3.815967172 12.84 9.821641475 237543557 86 19.545192 891.7145902
2007 3,854.96 125.9 60.16
2008 3,219.50 46.84 3.03
1.252464592 1.512256001 -1.750059244 3.809454033 2.191887237 5.760869723 35.05 633.56 809.27 11.47 666.34 821.58
0.65 0.8 3.872561399 1.204940421 12.84 12.84 8.902314756 7.629433841 237543557 75 -40.315268 338.7466775 237543557 25 -80.731872 -894.061108
237543557 70 157.381 1087.854899
Company Name Year End Automobile sMotorcycles / Mopeds Equity Gross Blk Sales
Full Year (Rs Cr.) NP
94.46 200803 200903 200906 200903 200903 10 39.94 10.37 10.4 23.75
4788.51 0.99 2,516.27 305.75 100.14 1,865.36
16168.9 0 12,315.24 64.31 118.43 3,670.92
1176.84 -0.72 1,180.63 -37.11 4.56 29.48
Escorts Automot Hero Honda Motor Kinetic Engg. Majestic Auto TVS Motor Co.
5,000.00 2009 3,670.92 95.71 29.48 8.366765467 7.441030516 62.25735717 64.61 905.98 810.16 0.96 4.992052805 12.84 8.696932884 237543557 76 -82.254544 89.1910332 2000 1500 1000 Series1 500 0 1 -500 -1000 2 3 4 5 6 Series2 4,000.00 3,000.00 2,000.00 1,000.00 0.00 1 2 3 4 5 6 Series1 Series2 Series3
Year (Rs Cr.) Div%
0 1,000.00 0 0 70
Beta value = 1.044 2004 Sales EBIT PAT Degree of Financial Leverage Degree of Operating Leverage Combined Leverage Interest Loans Equity Debt/Equity Cost of Debt Cost of Equity WACC No. of Share Share Value Economic Value Added Market Value Added 59.41 498.9 1,051.80 0.24 8.335738625 14.09 12.23870639 1330338317 23.3 239.3643797 2047.888279 3,440.54 345.87 196.61 2005 4,247.65 385.41 250.42 2.394052212 0.487323836 1.166678708 30.4 880.41 1,167.87 0.24 2.41705569 14.19 9.129647949 1330338317 34.2 268.2708343 2501.477044 2006 5,329.81 492.95 302.47 0.744911483 1.095227401 0.815847468 40.65 691.93 1,412.45 0.4 4.112410215 15.06 11.4603812 1330338317 41.1 342.8677687 3363.310483 2007 7,320.37 633.35 425.77 1.431253407 0.762606832 1.091483626 28.84 640.4 1,894.57 0.65 3.152404747 15.1 12.08172365 1330338317 49.1 441.1397949 3996.991136
2008 7,935.48 714.46 450.76 0.458311307 1.524090905 0.698508095 76.31 887.5 2,148.98 0.8 6.018816901 14.2 11.8088102 1330338317 16.45 497.7343983 -848.0734685
2009 6,126.01 368.77 178.79 1.247000805 2.121928332 2.646046338 160.32 1,961.98 3,473.90 0.96 5.719935983 14.09 11.06898074 1330338317 50.2 255.946242 1242.418351
9,000.00 8,000.00 7,000.00 6,000.00 5,000.00 4,000.00 3,000.00 2,000.00 1,000.00 0.00 1 4000 3000 2000 1000 0 1 -1000 2 3 4 5 6 2 3 4 5 6
Series1 Series2 Series3
3 2.5 2 Series1 Series2 1.5 1 0.5 0 1 2 3
Combined Leverage
Series1
3
4
5
2005 Sales EBIT PAT DoF DOL 7649.51 744.56 512.67 1.142 1.377
2006
2007
2008
2009
9273.09 11231.99 12894.94 14713.03 1126.74 1457.81 1494.95 1170.59 857.1 1068.39 1103.37 836.78 1.3088 3.1653 0.838 1.39 1.16482 0.46 1% 8% 2% 1.285 0.172 1.113 -1.5388
Combined Leverage 1.572534 4.142745 Debt/Equity 0.53 Cost of Debt 2.00% Cost of Equity 8% WACC 3% 0.31 2.13% 8% 3%
0.22102 1.712684 0.6 2.37% 8% 3% 0.77 2.31% 8% 3%
16000 14000 12000 10000 Int 8000 6000 Debt 4000 2000 0 2005 2006 2007 2008 2009 cost interest #VALUE!
1,052.62 883.39 1,636.00 2,587.06 4,052.76 134.12 93.884 4052.76 0.023165 87.59 61.313 2587.06 19.8 13.86 1636 26.96 18.872 883.39 30.24 21.168 1052.62 0.02011 Sales EBIT PAT
0.0237 0.008472 0.021363
doc_799524401.xls