Modeling Case Study - Cash Flow Statement Build
- 05:37
Build a cash flow statement from a balance sheet and an income statement, using a checklist to track the items you have included. Excel shortcuts and tips to make the process faster and easier.
Transcript
So the first thing I'm going to do is pull in net income and you could take it from the income statement. I prefer to take it from the base calculation because you are reinforcing, you are taking the change in the balance sheet items. But that's a stylistic point. So I've pulled in net income, then I'm gonna pull in depreciation from the base calculation. And again because it's an asset assets have an inverse relationship to cashflow. So if something makes an asset go down, cash goes up, which is why I'm flipping the sign for depreciation, then I'm taking the change in accounts receivable. So I'll take the prior year minus the current year. Now normally I would always do an operating working capital calculation rather than take the individual changes. But that's been done really for training purposes. So you reinforce this issue of taking the change in all the balance sheet items. Change in inventories. Again, this is an asset so you always want to take the prior year minus the current year and that will give you the accurate change. What I haven't done, I've been a bit naughty, I haven't taken the checklist. Let me go ahead and do that. So I've included net income, which means I've included the operational item related to equity. So I'll tick that off. I've included depreciation, so I include the O next to net PP&E I've also included my accounts receivable and inventories and what else have we included? That's it. And then I'm going to do the change in other current assets. Now I'm gonna be a bit sneaky here and I'll do control D because that should be in the same layout. Let me just check that. Yes it is. That was a control left square bracket F five enter will jump you back. And then accounts parable, I can't copy down because there are many other items in between. But I'm gonna take the current year because it's a liability minus the prior year. Okay? Remember liabilities have a direct relationship to cashflow. So this year minus last year, assets have an inverse relationship to cashflow. So it's last year minus this year, and then that's my cashflow from operations. Now it's made easy because I've got my labels here, but when you're doing this from scratch, you won't have the label. So I'm gonna tick off the O there. And I'm just going to check there are no other O's. Have I included other current assets? Yes. So there are no other O's, which means I finished the operating section of the cashflow statement. The two I's I've got is the CapEx related to net PP&E and the financial assets. So I'm gonna go down and pull in CapEx number from the base calculation and because it's an asset I need to flip the sign. It's quite easy to forget to do that. But just think assets haven't inverse relationship to cash flow and then the change in financial assets. I'm going to go up and I'll take the Prior year's financial assets minus the current years financial assets. And you've got a slight change in that number. I'm going to go up and I'll tick off the I next to net PP&E and I'll tick off the I next to financial assets. So I've done all the asset side of the balance sheet now because I've finished the operating and investing section, now I'm ready to do the financing section and I'm going to pull in dividends from the base calculation. And because liabilities and equity have a direct relationship to cash flow, I can just take the number. I don't need to change the sign. So I've pulled in dividends and then the change in long-term debt. And I'll take the current year minus the prior year. And then I'm going to go up and tick off those Fs, include that I'm included dividends. Is there anything left on the balance sheet? No, everything's got an X next to it, which means I should have included everything. I'll sum up the financing activities. And then what I want to do is I want to do a little base calculation. So the first thing I would always do is I'm gonna anchor this. I'm going to take the cash number in the historical year minus the revolver number in the historical year. And just bear in mind that often companies can have both cash and revolver in the historical year because this is just a snapshot at the year end. And sometimes companies will still have a revolver even though they may have a positive cash balance at year end. So the beginning cash balance net, a revolver is equal to ending balance of last year. And then the net cash flow, I'm going to do a sum function and I'm gonna show you something a little fun here. I'll do the cash flow from operations, then I'm gonna hold the control key down and I'll click on the cash flow from investing and financing. And if I'm holding the control key just puts a comma next to the references. And that's super helpful. You are summing lots of items together. And then I'll sum up and be careful notice when I did alt equals it went left rather than up. Very easy to make mistakes that way. And we have 775 now that should be the difference on the balance sheet and it is. So that's how you know you've got it correct.