Financial Statement Analysis for Tata Motors

Document Sample
Financial Statement Analysis for Tata Motors Powered By Docstoc
					                                 PRELIMINARY STUFF AND INPUTS
Objective       This spreadsheet allows you to compute the optimal capital structure for a non-financial
                 service firm. If you have a financial service firm use capstrfin.xls
Before you start Open preferences in excel, go into calculation options and put a check in the iteration box.
                 If it is already checked, leave it as is.
Inputs           The inputs are primarily in the input sheet. If your company has operating leases,
                 use the operating lease worksheet to enter your lease or rental commitments.
Units            Enter all numbers in the same units (000s, millions or even billions)
Income inputs The key income inputs are EBITDA, depreciation and amortization and interest expenses.
                 Enter the most updated numbers you have for each (even if they are 12-month trailing
                 numbers). If the most recent period for which you have data has an operating income that
                 is abnormal, either because of extraordinary losses/gains or some other occurrence, use
                 an average operating income over the last few years.
                 From the statement of cash flows, also enter the capital spending from the recent period.
                 P.S: If you have negative operating income and you expect to continue having negative
                 operating income, your optimal debt ratio will be zero.
Balance Sheet Enter the book value of all interest-bearing debt. If you have a market value enter that
                 number. Alternatively, input the average maturity of the debt and I will estimate the
                 market value of debt.
Market Data      Enter the current stock price, the current long-term government bond rate, the risk
                 premium you would like to use to estimate your cost of equity and the current rating for
                 your firm. If you do not have a rating, there is an option for you at the very bottom of
                 the spreadsheet to compute a synthetic rating.
Tax Rate         Enter a marginal tax rate, if you can estimate it. Otherwise, use the effective tax rate.
Default Spreads This spreadsheet has interest coverage ratios, ratings and default spreads built into it in
                 the worksheet. This spreadsheet treats the imputed interest expense on operating leases as part of the
                 interest expense when computing the interest coverage ratio. You can choose between ratings for large firms
                 (firms with market capitalizations that exceed $ 5 billion is a simple cut off but you can deviate from it)
                 a more conservatve for small or risky firms. If you want, you can change the interest
                 coverage ratios and ratings in these tables.
                                       READING THE OUTPUT
Summary          The summary provides a picture of your firm's current cost of capital and debt ratio, and
                 compares it to your firm's optimal debt ratio and the cost of capital at that level. It then
                 uses the savings from the change in cost of capital to compute how much your firm value
                 will change:
                  - with constant savings: as the present value of a perpetuity
                  - with a growth rate in the savings in perpetuity
                 The firm value change, divided by the number of shares, yields a price change
Details          The details of the calculation at each debt ratio are below the summary.

References
Corporate Finance: Theory and Practice, Chapter 18
Applied Corporate Finance: Chapter 8
tings for large firms
deviate from it)
Question
Q1: What do I do excel says there are circular references?
Q2: My spreadsheet has gone crazy. I get errors all over.
What did I do wrong?

Q3: I am entering the inputs for my company but the
optimal numbers do not seem to change from the
originals
Q4: I am getting an optimal debt ratio of 0%. This can't
be right. Can it?


Q5: My cost of capital at my optimal debt ratio is higher
than the current cost of capital. I thought it was supposed
to be lower.
             Answer
             Go into preferences, choose calculation options and make sure the iteration box has a check in it.
             I am sorry to say this, but you probably just made an input error. While you might have
             fixed it, the iterations in the spreadsheet make it very sensitive and the errors will not
             go away. The only fix (Sorry, sorry…) is to copy the inputs into a fresh version of the spreadsheet.
             You probably forgot to check the iteration box (see Q1)
change from the

              Sure. If your operating income is either negative or very low, relative to your firm value,
              you can end up at an optimal debt ratio of 0%. For instance, if you have EBIT of 100 on a
              firm value of 10000, a 10% debt ratio would probably push you into a C rating and give
              you a very high cost of capital.
              Generally, you are right. However, I would suggest that you look at three factors:
               - If your optimal is just slightly higher or lower than your current debt ratio, it is possible that you
              are closer to the optimal than the stated optimal. Let me explain. Assume that you are at a 24% debt ratio
              and the optimal comes out to 30%. The true optimal is really somewhere around 30% since
              I am constrained to work in 10% increments of the debt ratio. If the true optimal were
              26%, your current debt ratio of 24% is closer to the optimal.
               - Rating Differences: One of the costs of rating a company based only on the interest
              coverage ratio is that the rating might be very different from the actual rating. Thus, your
              current cost of capital is based upon your current rating, and the optimal is based upon
              the synthetic ratings, and the two don't match, the current and the optimal cost of capital
              can be mismatched. You can get around this by switching to a synthetic rating for computing
              the current cost of capital (in the input sheet).
               - Existing debt at low rates: I assume in the spreadsheet that existing debt gets refinanced at
              the new pre-tax cost of debt at each debt ratio. Consequently, if you have a lot of old debt on
              your books at much lower rates, the interest expense that I report will be much higher than
              your actual interest expense. This, in turn, can affect your interest coverage ratio and rating.
              This, too, you can fix by locking in debt at current rates in the input sheet.
Inputs
Please enter the name of the company you are analyzing:                             Tata Motors
Financial Information

Earnings before interest, taxes and depreciation (EBITDA)                           $26,228.00
Depreciation and Amortization:                                                      $8,701.00
Capital Spending:                                                                   $40,291.00
Interest expense on debt:                                                           $6,737.00
Tax rate on ordinary income:                                                          33.99%
Current Rating on debt (if available):                                                  B+
Interest rate based upon rating:                                                      12.25%
Market Information

Number of shares outstanding:                                                         413.05
Market price per share:                                                              $780.50
Beta of the stock:                                                                     1.20
Lambda of the stock                                                                    0.80
Book value of debt:                                                             $            131,657.00
Can you estimate the market value of the outstanding debt?                              No
If so, enter the market value of debt:
Do you want me to try and estimate market value of debt?                               Yes
If yes, enter the average maturity of outstanding debt?                                3.00
Do you have any operating leases?                                                       No
General Market Data

Current long-term (LT) government bond rate:                                          5.00%               (in percent)
Mature Market Premioum                                                                4.50%               (in percent)
Country Risk Premium (for use with lambda)                                            4.50%
Country default spread (for cost of debt)                                             3.00%

General Data

Which spread/ratio table would you like to use for your analysis?                       2
Do you want to assume that existing debt is refinanced at the 'new' rate?               No                (Yes or No)
Do you want the firm's current rating to be adjusted to the synthetic rating?          Yes                (Yes or No)
                                                                 Operating Lease Converter
Operating lease expenses are really financial expenses, and should be treated as such. Accounting standards allow th
be treated as operating expenses. This program will convert commitments to make operating leases into debt and
adjust the operating income accordingly, by adding back the imputed interest expense on this debt.

Inputs
Operating lease expense in current year =                            $36.90
Operating Lease Commitments (From footnote to financials)
     Year        Commitment ! Year 1 is next year, ….
       1        $      28.40
       2        $      21.40
       3        $      16.30
       4        $        9.90
       5        $      16.90
 6 and beyond $        16.90

Pre-tax Cost of Debt =             9.25%      ! If you do not have a cost of debt, use the attached ratings estimator

From the current financial statements, enter the following
Reported Operating Income (EBIT) =                 $17,527.00 ! This is the EBIT reported in the current income statement
Reported Interest Expenses =                        $6,737.00
Output
Number of years embedded in yr 6 estimate =            1      ! I use the average lease expense over the first five years
                                                              to estimate the number of years of expenses in yr 6
Converting Operating Leases into debt
     Year       Commitment Present Value
             1 $       28.40          $26.00
             2 $       21.40          $17.93
             3 $       16.30          $12.50
             4 $        9.90            $6.95
             5 $       16.90          $10.86
6 and beyond $         16.90            $9.94 ! Commitment beyond year 6 converted into an annuity for ten years
Debt Value of leases =          $       84.17

Restated Financials
Operating Income with Operating leases reclassified as debt =                  $ 17,534.79
Interest expenses with Operating leases classified as debt =                   $ 6,744.79
nverter


pense on this debt.




ed ratings estimator


 current income statement


over the first five years
f expenses in yr 6




nnuity for ten years
Inputs for synthetic rating estimation
Enter the type of firm =                      2 (Enter 1 if large manufacturing firm, 2 if smaller or riskier firm, 3 if financial service firm)
Enter current Earnings before interest and taxes (EBIT) =                          $17,527.00 (Add back only long term interest expense fo
Enter current interest expenses =                                                   $6,737.00 (Use only long term interest expense for fina
Enter current long term government bond rate =                                           5.00%
Output
Interest coverage ratio =                            2.60
Estimated Bond Rating =                               B+
Estimated Default Spread =                          4.25%
Estimated Cost of Debt =                            9.25%

For large or stable firms
 If interest coverage ratio is
       >              ≤ to         Rating is       Spread is
   -100000         0.199999           D             15.00%
       0.2         0.649999            C            12.00%
      0.65         0.799999           CC            10.00%
       0.8         1.249999          CCC             8.50%
      1.25         1.499999           B-             5.50%
       1.5         1.749999            B             5.25%
      1.75         1.999999           B+             4.25%
        2         2.2499999          BB              4.00%
      2.25          2.49999          BB+             3.50%
       2.5         2.999999          BBB             2.00%
        3          4.249999           A-             1.50%
      4.25         5.499999           A              1.25%
       5.5         6.499999           A+             1.00%
       6.5         8.499999          AA              0.75%
      8.50          100000          AAA              0.50%


For smaller and riskier firms
 If interest coverage ratio is
 greater than         ≤ to         Rating is       Spread is
   -100000         0.499999           D             15.00%
       0.5         0.799999           C             12.00%
       0.8         1.249999          CC             10.00%
      1.25         1.499999         CCC              8.50%
       1.5         1.999999           B-             5.50%
        2          2.499999           B              5.25%
       2.5         2.999999          B+              4.25%
        3          3.499999          BB              4.00%
       3.5        3.9999999         BB+              3.50%
        4          4.499999         BBB              2.00%
       4.5         5.999999           A-             1.50%
        6          7.499999           A              1.25%
       7.5         9.499999          A+              1.00%
       9.5        12.499999          AA              0.75%
      12.5          100000          AAA              0.50%
or riskier firm, 3 if financial service firm)
Add back only long term interest expense for financial firms)
Use only long term interest expense for financial firms)
                                                                             CAPITAL STRUCTURE                                                                                             17


                                                   Tata Motors
Capital Structure                         Financial Market                        Income Statement
Current MV of Equity =         $322,388   Current Beta for Stock =    1.20        Current EBITDA =             $26,228
                               $109,198
Market Value of interest-bearing debt =   Current Bond Rating =        B+         Current Depreciation =        $8,701
# of Shares Outstanding =       413.053             Summary of Inputs             Current Tax Rate =            33.99%
                                   $0
Debt Value of Operating leases (if any)                               Rate
                                          Long Term Government Bond5.00%=         Current Capital Spending=    $40,291
Risk Premium =                   4.50%    Pre-tax cost of debt =     12.25%       Current Interest Expense =    $6,737

                                             RESULTS FROM ANALYSIS
                                                     Current Optimal                Change
                             D/(D+E) Ratio =         25.30%   10.00%                -15.30%
                                                                                                            Implied Growth Rate Calculation
                             Beta for the Stock =           1.2         1.05          -0.15                 Value of Firm =$431,586
                             Lambda for the stock =        0.80         0.70
                             Cost of Equity =             14.00%       12.89%        -1.11%                 Current WACC = 12.50%
                                                                                                                           =
                                                                                                            Current FCFF######### ! I am ignoring working capital
                             AT Interest Rate on Debt =    8.09%        5.94%        -2.15%                              NA
                                                                                                            Implied Growth Rate =
                                                                                                            If this number is >Riskfree rate, I use the riskfree rate as a perpetual growth rate.
                             WACC                        12.50%        12.20%        -0.30%
                             Implied Growth Rate =        5.00%
Assumes constant saving      Firm Value (no growth) = $431,586        $442,348      $10,762
Assumes perpeutal growth                               $431,586
                             Firm Value (Perpetual Growth) =          $450,734      $19,149
                             Value/share (No Growth) = $780.50         $806.56       $26.06
                                                        $780.50
                             Value/share (Perpetual Growth) =          $826.86       $46.36

We use the following default spreads in our analysis. Change them in the input sheet if necessary:     Ratings comparison at current debt ratio
                              Rating       Coverage gt     and lt    Spread                      Current Interest coverage ratio =          2.60
                              AAA                  12.5      100000        0.50%                 Rating based upon coverage =                B+
                              AA                     9.5 12.499999         0.75%                 Interest rate based upon coverage =       12.25%
                              A+                     7.5   9.499999        1.00%                 Current rating for company =                B+
                              A                        6   7.499999        1.25%                 Current interest rate on debt =           12.25%
                              A-                     4.5   5.999999        1.50%
                              BBB                      4   4.499999        2.00%
                              BB                       3   3.499999        4.00%
                              B+                     2.5   2.999999        4.25%
                              B                        2   2.499999        5.25%
                              B-                     1.5   1.999999        5.50%
                              CCC                  1.25    1.499999        8.50%
                              CC                     0.8   1.249999       10.00%
                              C                      0.5   0.799999       12.00%
                           CAPITAL STRUCTURE   18


D   -100000   0.499999   15.00%
                                                                      CAPITAL STRUCTURE                                                  19


Current beta=       1.20                 Current Equity=         $322,388         Current Depreciation=          $8,701
Current Debt=     $109,198               Current EBITDA=          $26,228    Current Interest rate (Company)=    12.25%
Tax rate=          33.99%                Current Rating=            B+            Current T.Bond rate=            5.00%

                                        WORKSHEET FOR ESTIMATING RATINGS/INTEREST RATES
D/(D+E)             0.00%     10.00%       20.00%  30.00%   40.00%     50.00%   60.00%    70.00%                   80.00%      90.00%
D/E                 0.00%     11.11%       25.00%  42.86%   66.67%    100.00%  150.00%   233.33%                  400.00%     900.00%
$ Debt               $0      $43,159      $86,317 $129,476 $172,634  $215,793 $258,952  $302,110                 $345,269    $388,427
Beta                 0.98      1.05         1.14    1.26     1.42       1.75     2.24      3.00                     4.52        9.07
Lambda               0.65      0.70         0.76    0.84     0.95       1.17     1.49      2.00                     3.01        6.04
Cost of Equity     12.36%     12.89%       13.57%  14.44%   15.65%     18.15%   21.79%    27.52%                   38.91%      73.00%

EBITDA            $26,228    $26,228      $26,228      $26,228   $26,228     $26,228     $26,228      $26,228     $26,228     $26,228
Depreciation       $8,701     $8,701       $8,701       $8,701    $8,701      $8,701      $8,701       $8,701      $8,701      $8,701
EBIT              $17,527    $17,527      $17,527      $17,527   $17,527     $17,527     $17,527      $17,527     $17,527     $17,527
Interest             $0       $2,208       $4,417       $9,474   $18,156     $28,056     $41,180      $51,107     $61,033     $70,960
Taxable Income    $17,527    $15,319      $13,110       $8,053    ($629)    ($10,529)   ($23,653)    ($33,580)   ($43,506)   ($53,433)
Tax                $5,957     $5,207       $4,456       $2,737    ($214)     ($3,579)    ($8,040)    ($11,414)   ($14,788)   ($18,162)
Net Income        $11,570    $10,112       $8,654       $5,315    ($415)     ($6,950)   ($15,614)    ($22,166)   ($28,718)   ($35,271)
(+)Deprec'n        $8,701     $8,701       $8,701       $8,701    $8,701      $8,701      $8,701       $8,701      $8,701      $8,701
Funds from Op.    $20,271    $18,813      $17,355      $14,016    $8,286      $1,751     ($6,913)    ($13,465)   ($20,017)   ($26,570)

Pre-tax Int. cov       ∞        7.94        3.97         1.85     0.97      0.62          0.43         0.34         0.29        0.25
Funds/Debt             ∞        0.44        0.20         0.11     0.05      0.01          -0.03        -0.04        -0.06       -0.07
Likely Rating        AAA         A+         BB+           B-       CC         C             D            D            D           D
Pre-tax cost of debt 8.50%     9.00%       11.50%      13.50%    18.00%    20.00%        23.00%       23.00%      23.00%      23.00%
Eff. Tax Rate       33.99%    33.99%       33.99%      33.99%    32.81%    21.23%        14.47%       11.66%       9.76%       8.40%
                                                     COST OF CAPITAL CALCULATIONS
D/(D+E)             0.00%     10.00%       20.00%      30.00%    40.00%    50.00%         60.00%      70.00%       80.00%      90.00%
D/E                 0.00%     11.11%       25.00%      42.86%    66.67%   100.00%        150.00%     233.33%      400.00%     900.00%
$ Debt               $0      $43,159      $86,317     $129,476  $172,634 $215,793       $258,952    $302,110     $345,269    $388,427
Cost of equity     12.36%     12.89%       13.57%      14.44%    15.65%    18.15%         21.79%      27.52%       38.91%      73.00%
Cost of debt        5.61%      5.94%        7.59%       8.91%    12.09%    15.75%         19.67%      20.32%       20.75%      21.07%
Cost of Capital    12.36%     12.20%       12.37%      12.78%    14.23%    16.95%         20.52%      22.48%       24.39%      26.26%
                $
Value (no growth) 436,766    $442,348     $436,123    $422,296   $379,297   $318,353    $262,977    $240,071     $221,301    $205,486
                                                                         CAPITAL STRUCTURE                                                 20


                  $440,722
Value (perpetual growth)     $450,734   $439,581      $415,561      $346,933   $262,949        $197,512   $172,970   $153,836   $138,352


                                        Interest cov Interest cov   RATING     Interest rate
                                            Low          High
                                          -100000      0.499999        D         23.00%
                                             0.5       0.799999         C        20.00%
                                             0.8       1.249999        CC        18.00%
                                            1.25       1.499999       CCC        16.50%
                                             1.5       1.999999        B-        13.50%
                                              2        2.499999         B        13.25%
                                             2.5       2.999999        B+        12.25%
                                              3        3.499999        BB        12.00%
                                             3.5      3.9999999       BB+        11.50%
                                              4        4.499999       BBB        10.00%
                                             4.5       5.999999        A-         9.50%
                                              6        7.499999        A          9.25%
                                             7.5       9.499999        A+         9.00%
                                             9.5      12.499999        AA         8.75%
                                            12.5        100000        AAA         8.50%
                             CAPITAL STRUCTURE   21




s a perpetual growth rate.
                                        Chart - Cost of Equity



                               Cost of Equity and Beta: Debt Ratios

10.00                                                                              80.00%


 9.00
                                                                                   70.00%

 8.00
                                                                                   60.00%
 7.00

                                                                                   50.00%
 6.00


 5.00                                                                              40.00%


 4.00
                                                                                   30.00%

 3.00                                                                                       Beta
                                                                                   20.00%   Cost of Equity

 2.00

                                                                                   10.00%
 1.00


 0.00                                                                              0.00%
        0%   10%   20%   30%    40%       50%        60%         70%   80%   90%




                                                Page 22
Debt Ratio   Beta   Cost of Equity Bond RatingInterest rate on debt   Tax Rate Cost of Debt (after-tax)   WACC
   0%        0.98     12.36%          AAA            8.50%            33.99%            5.61%             12.36%
  10%        1.05     12.89%           A+            9.00%            33.99%            5.94%             12.20%
  20%        1.14     13.57%          BB+           11.50%            33.99%            7.59%             12.37%
  30%        1.26     14.44%           B-           13.50%            33.99%            8.91%             12.78%
  40%        1.42     15.65%           CC           18.00%            32.81%           12.09%             14.23%
  50%        1.75     18.15%            C           20.00%            21.23%           15.75%             16.95%
  60%        2.24     21.79%           D            23.00%            14.47%           19.67%             20.52%
  70%        3.00     27.52%           D            23.00%            11.66%           20.32%             22.48%
  80%        4.52     38.91%           D            23.00%             9.76%           20.75%             24.39%
  90%        9.07     73.00%           D            23.00%             8.40%           21.07%             26.26%
Firm Value (G)
 INR 440,722
 INR 450,734
 INR 439,581
 INR 415,561
 INR 346,933
 INR 262,949
 INR 197,512
 INR 172,970
 INR 153,836
 INR 138,352

				
DOCUMENT INFO
Description: Financial Statement Analysis for Tata Motors document sample