Im beginning to learn three statement financial modelling and I’m having difficulty getting the cash flow statement to tie to the plugging figure for cash on the balance sheet.
Hoping a fresh set of eyes might spot something I’ve missed
https:// drive.google
.com/file/d/14AGjufTgHwVUzYF7fG0IGBENoagyThd6/view?usp=sharing
I wanna help you but I can’t see the image, can you uploaded imgur or other image sharing sites, it much more convenient as the image would be displayed on the forum.
Here is the corrected link:
You’re making it complicated for yourself, that’s why you’re finding it hard to fix. For instance, you forgot the intangible capex, as far as I can tell, on the CFS.
As opposed to us fixing your sheet for you, where you would learn nothing, I suggest you do the following. These steps will make you see more clearly where the error(s) comes from:
Have the years/columns on all sheets lined up (2017 on col C, 2018 on col D etc, say)
That way, you can tell straight away from looking at formulas if one of the links is off. It also reduces errors from links.
use good formatting: inputs in blue, formulas in black. Personally, I also like to put links from different sheets in green. This will make the architecture of the model stand out and save you time.
avoid big chunks of formulas in one cell, such as in row 14 of CFS. Keep formulas ultra simple. Instead, use one row per item, with a subtotal line, in the same order as the lines of the BS so a single formula, copied through, will get you the result. You can always group the extra lines so they dont interfere with the presentation/ big picture.
keep the sheets focussed. Your CFS is doing two tasks: its calculating the change in cash but its also bridging to NOPAT and back. This creates room for mistakes. You’re better off computing the cashflow on CFS starting from net income, so you can check line by line that each part of the IS is reflected properly on the CFS. You can then have a bridge to NOPAT down below that links from the main CFS to make each task separate and clear.
use consistent labelling. For instance you’re showing an ‘other investment’ line on line 17 of CFS but that’s really the equity investments of the BS line 14. This error in labelling probably made you miss the other non current asset line of the BS that you’ve put alongside NWC on the CFS but really belongs with other LT items such as capex. Meaning capex doesnt include all LT assets. Likewise, its ‘operating income’ on IS and ‘EBIT’ on CFS, creates room for errors, such as not factoring in one-off costs fully.
avoid modelling at different scales. So your BS and CFS have a single line for capex and PPE. But you’ve got a whole mechanics on the PPE sheet to split it in parts in order to derive depreciation. Its not consistent, and creates the room for mistakes as you sum up and divide the aggregates between sheets. Besides, from a modelling standpoint it doesnt achieve much. Depreciation only impacts cash through tax, and because the link between PPE and D is mechanical, it adds no real new information.
Similarly, you’re showing details of one-off and equity items on the IS (lines 14,15) but no corresponding lines on the BS or CFS, so you can’t be sure these flow through properly.
Once all these are fixed, here is what you can do to spot mistakes. You force growth/revenue at a massive number, like 10000% say. This will create outliers on all rows that are related to revenue. You can then see if anything stands out that shouldnt. After revenue, you can do this for capex and cash interest to check other mechanics.
All of this will help with the mechanics of the model. However, even with the mechanics fixed, my sense is there is an implicit bias in your model. This is because as operating drivers, you’re using an average of historical KPIs line by line. Although this might sound reasonable at first glance, it creates a forecast that is not internally consistent, ie you’re not modelling a scenario.
As a result, you’re forecasting a reduction of RoA at constant capex and operating margin. In other words, the return on capital is going down, as more PPE is required to maintain margin. This introduces a negative bias to your cashflow, so you’re likely modelling some destruction in value that is artificial. On the other hand, you’re modelling a sharp decrease in financial leverage, so you’re likely underestimating credit risk.
In real life, management would change opex/capex to increase ROCE. Indeed, the 6pts gross margin increase leading to 2019 is clearly what’s driven the performance historically. Its not driven by PPE, as you’d expect for a mature business such as this. You need to take a view on the path of this KPI to price the equity and debt properly.
As it happens, I’ve looked at Altria fairly closely a long time ago. Back then, the whole investment thesis hinged on the mix between EM and DM. You’d want to look at volumes between the two, along with operating leverage on a fairly steady production base, and see to what extent growth in EM can offset declines in DM. This then drives long term growth. Manifestly as well the leverage increase in 2019 was not neutral as well. Also, there may be a big exposure to LatAm, and so high inflation currency may be an issue.
As a last comment, be careful with mechanical computations of beta. Because there is a sharp change in leverage over the period, that the share price reflected (ie a transfer of value from debt to equity), the beta estimate is skewed. You need to run an estimate before the leverage hike, and after, then de-lever the beta, to get a better estimate. You’d expect cigarettes to be low beta, but 0.56 feels too low, especially at that leverage. Indeed, Altria should have higher beta than comps.
Good luck