Financial Modelling #6: Discounted Cash Flow Financial Modelling for Dangote Cement part 1

, , No Comments
In this practice, we are going to make from scratch a DCF financial model for Dangote Cement. (First, I must  confess that this will be a little difficult to grasp without the practice file or video explanation. I intend to create a video explanation).

Below is the Income Statement (Profit and Loss Statement) of Dangote Cement for the years 2010 to 2015

P&L source















NGN in thousands

2010
Act
2011
Act
2012
Act
2013
Act
2014
Act
2015
Act
Revenue from sales and services

          202,566
          241,406
          298,454
      386,177
      391,639
      491,725
Other revenue

                       - 
                       - 
                       - 
                   - 
                   - 
                   - 
Revenue

          202,566
          241,406
          298,454
      386,177
      391,639
      491,725








Cost of Goods Sold

           (84,917)
           (97,708)
         (118,304)
     (142,517)
     (143,058)
     (201,808)
Selling General & Admin Exp.

                   (13)
             (9,821)
             (6,380)
       (14,072)
       (25,213)
       (27,468)
R & D Exp.

                       - 
                       - 
                       - 
                   - 
                   - 
                   - 
Other Operating Expense/(Income)

                       - 
                       - 
                       - 
                   - 
                   - 
                   - 








EBITDA

          117,636
          133,877
          173,770
      229,588
      223,368
      262,449








D&A

           (14,220)
           (16,135)
           (27,621)
       (33,706)
       (36,266)
       (54,626)








EBIT

          103,416
          117,742
          146,149
      195,882
      187,102
      207,823








Financial income/expenses

             (2,081)
             (3,963)
           (10,844)
         (6,722)
       (14,902)
       (32,108)
Extraordinary income

                     (0)
               2,238
                  343
           1,601
         12,489
         12,579








EBT

          101,334
          116,017
          135,648
      190,761
      184,689
      188,294








Taxes

               5,271
               5,398
               9,377
         10,437
       (25,187)
         (6,971)








Net Income

          106,605
          121,416
          145,024
      201,198
      159,501
      181,323



And below is the one for the Balance Sheet.

BS source















NGN in thousands

31Dec10
Act
31Dec11
Act
31Dec12
Act
31Dec13
Act
31Dec14
Act
31Dec15
Act
Intangible assets







PP&E

           285,443
           397,711
         478,092
         581,465
         747,794
         917,212
Financial assets

                        0
             62,142
           56,214
         113,657
           99,823
           27,751








Fixed assets

           285,443
           459,853
         534,306
         695,123
         847,617
         944,963








Inventory

             14,865
             14,351
           32,478
           27,667
           42,688
           53,118
Trade receivables

             11,378
               1,926
             3,408
             6,670
             4,223
             6,234
Other assets

             69,077
             27,518
           43,584
           44,464
           69,600
           65,836








Cash and equivalents

             21,277
             22,836
           44,425
           70,502
           20,593
           40,792
Non fixed assets

           116,597
             66,631
         123,895
         149,303
         137,104
         165,980
























Total Assets

           402,040
           526,483
         658,201
         844,425
         984,721
     1,110,943

NGN in thousands

31Dec10
Act
31Dec11
Act
31Dec12
Act
31Dec13
Act
31Dec14
Act
31Dec15
Act
Share Capital & Minority Interest
           50,176
             61,599
              61,061
         58,856
         57,988
         47,592
Reserves

                     - 
                       - 
                        - 
                   - 
                   - 
                   - 
Retained earnings

         161,334
          229,246
            345,665
      496,456
      537,751
      620,501
Comprehensive Inc. and Other
                     - 
                 (474)
               (2,190)
         (5,218)
         (3,853)
       (23,373)
Total Equity

         211,509
          290,371
            404,536
      550,093
      591,886
      644,720








Trade payable

             5,163
               5,824
              18,279
         23,433
         34,535
         44,044
Short term loans

           37,637
             34,963
              51,697
         56,289
      110,640
         47,275
Current deferred taxes
             3,197
               4,282
                2,505
              566
           2,481
           1,289
Other Current liabilities
           43,863
             70,758
              63,550
         84,478
         85,292
      108,090








Long term loans

           98,251
          116,766
            112,462
      124,850
      131,942
      208,329
Other Financial liabilities
             2,419
               3,519
                5,172
           4,715
         27,944
         57,196
Total Liabilities

         190,531
          236,113
            253,664
      294,332
      392,834
      466,223








Total Liabilities & Equity
         402,040
          526,483
            658,201
      844,425
      984,721
   1,110,943



Those two statements (Income Statement and Balance Sheet) form the basis of the creation of the DCF model. The Cash Flow Statement is not needed because we are going to extract out the Free Cash Flow from those two reports for the future/lifetime of the company and that is what we need to value the company.
Once those to statements have been extracted from the company’s historical financial statement (preferable over past 4 to 6 years), there are just three stages to preparing the actual DCF model.

DCF Modelling Stage One: Forecast Assumptions

The first stage deals with creating the forecast scenarios, usually three to cater for an optimistic case, a neutral/base case and a worst case. Then based on knowledge of the business/company and information from the management on future strategy, you can make future projections for the key lines in the Income Statement (Profit and Loss Statement) and the Balance Sheet.

Below is the Profit and Loss Statement Assumptions I used for Dangote Cement. For Revenues and Cost of Goods Sold the base case is a moving average of the last five years, the optimistic case is the base case + 5% and the worst case is the base case – 5%. While for the Operating Expenses and Depreciation & Amortization, the base case is again the moving average of the last five years while the optimistic case is the base case + 1% and the worst case is the base case – 1% (these are actually in the opposite of the signs as the expenses are originally in negative percentages).


Below is the Balance Sheet assumptions. The average days it takes for the company to receive payment for buyers after sales is recorded/invoiced (known as Days Receivables) is computed and the last actual is projected into the forecast years. Similar calculations are done for Days Inventory (average number of days the company uses to turn inventory into actual sales) and Days Payables (the average number of days the company takes to pay its suppliers).


0 comments:

Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.

Thanks!