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

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).