Balancing financial model
Hi fellows,
I am modeling an IT-security company currently, and I just don't get it balanced. It would be great if anyone could lend me a hand.
I already tried:
1. Dividing the difference by two and try to find the value (didn't find it)
2. Delete all years after the first forecast year of the balance sheet and look for the problem.
The problem might lie in the wrong concept I learned, but I don't know what it is. I am often uncertain how to handle specific line items and their changes. Some specific questions:
- how do I handle depreciation when it is not a line item in the income statement?
- how do I handle changes in the provisions? If I am right, it should be like: decrease income by the change, increase operating CF, increase balance sheet item
However, I don't know whether the balancing of my model is connected to these questions.
Please see the model below if you would like to get into details.
Hi there, I took a look. It is extremely hard for WSO people to help you out because your Model is in Dutch (I believe it is Dutch?). Otherwise I would just do it for you.
Hope the following is helpful, at least conceptually.
My guess is when you go from Net Income to Operating Cash Flow, you probably didn't do 1 or more of the following 3 things right. What I'm going to say happens to myself many times, so don't worry if you indeed didn't get it/them correctly...
1. You did not add back non-cash items from the income statement (IS) to the operating cash flow (OCF) section or subtract non-cash gains that appeared on the IS.
You mentioned confusion over how to handle D&A since it didn't appear on the IS. The solution for you is to look at the Cash Flow Statement (CFS). You look at the OCF section of the CFS, locate where depreciation is, and then you calculate Depreciation / Revenue as a percentage (%). Using an average Dep % Revenue for the last say 5 years since you have a really long historical period, you get a % for your forecast period. Now, you can calculate depreciation.
Don't forget to adjust your Net PP&E (on the balance sheet, a non-current asset) accordingly. Old PP&E + CapEx - Depreciation = New PP&E.
Sometimes depreciation gets mired into operating expense or some other line items on the IS. However, you always find the numbers in the CFS.
2. You did not implement the motto that "an increase in assets is cash outflow and an increase in liabilities is cash inflow".
It is very important to have a negative number when say your inventory increased year over year because you used cash to buy that inventory. Same reasoning applies to liabilities.
3. Everything on the balance sheet must appear once and ONLY once on the CFS
I'm just regurgitating Brian DeChesare's M&I banking guide here. I've come to realize that this is really helpful advice when building a 3-statement model.
If you've managed to do those 3 things right, and your Model is still not balanced, then check if you left out some line items to be incorporated somewhere.
Again, hope this helps !
Hi iridescent007,
thank you very much! And I am absolutely sorry that I did not translate everything directly. However, now everything is translated (within the financial statements at least).
To the first point: I calculated depreciation via a PP&E and intangibles schedule below the statements under the area "supporting schedules". The reason for my insecurity was that they kind of sink into the operating expenses.
To the second point: Most of my balance sheet items are actually fixed, so there is no change in cash flow. With pension provisions, I was really not sure how to handle them. The problem is that they appear in other comprehensive income, not the main part of the income statement. Thinking back to my accounting classes, I would reduce net income, increase CF, and increase pension provisions' balance sheet item.
Every item in the balance sheet that varies can be seen somewhere below in the supporting schedules. Would you take another look? I would be super grateful! (And I already am)
I'm having a relatively chill Friday so no, I do not mind taking another look.
A couple of issues:
1. I don't know much about pension accounting, but I believe you can treat pension provisions as a liability (others please correct me if I'm wrong here, or elaborate on pension accounting from a banker's perspective, not an accountant's). I believe Aswath Damodaran, that NYU professor, does this. Think about it for a second: you need to pay those money to your elderly. You owe them something. That is a liability. Its increase is then a cash inflow. You owe more money that you pay later, so now you get more cash.
2. Intangible amortization. It's not depreciation. When you calculate this, the formula in general is: Old intangibles balance + purchases of new intangibles - amortization = New intangibles balance. I'd say assume no new purchases of intangibles. You can assume something for amortization. You do not add CapEx here since CapEx is something tangible.
3. Leasing payments: when you calculate ending lease balance, you added interest. Why did you add interest here? I believe you intended to treat lease as a liability on the balance sheet, and your lease payments are essentially interest expenses, which can be classified as a financing cash outflow under IFRS.
4. You might want to assume a cash interest rate for your interest income. Not sure how this affects your balance sheet. My guess is it doesn't.
5. When you calculate equity value, you want to subtract dividends and fx change effects. Also, you want to subtract stock-based compensation (SBC) out of equity if there is any.
Think about it this way: you record dividends and SBC on the CFS. We have said above that everything on the B/S must appear on the CFS. The reverse logic here is if you see something on the CFS, if has its place on the B/S because that is where it comes from. Now, it doesn't need to be directly listed on the B/S. For example, for depreciation, it's within PP&E. You need to count in dividend and all that into your equity line.
Whenever you don't know how to classify something after thinking long and hard, put it into equity above your "Total L & E" line.
6. There are quite a few asset and liability line items. However, when you adjust for OCF, you only accounted for a small portion of them. Make sure you put in things like "other current liabilities", "contract liabilities", "other long term assets", etc. because they all affect your cash flow. This was the same as my last comment's No.3. They need to be on the CFS, otherwise it won't balance.
7. Edit your interest expense formula. You added pension provisions and interest on your finance lease. You can treat finance/capital lease as debt. I would suggest using interest-bearing debt to calculate your beginning and ending debt balance, and then from there get your interest expense. Assuming there is only 1 tranche of debt. If multiple tranches, just add them up. You do not want to calculate interest expense based off other interest expense. You want to include interest-bearing debt (IBD) only.
For pension, assuming we do it as a liability and need to pay interest on it (which means we treat it as an IBD) --- like a monthly pension funding thing in people's retirement account. You record liability, create a pension amortization schedule, and have your ending balance in each period. Your pension interest (amortization) also goes to your income statement along with other depreciation and amortization, and your cash flow statement since this is a non-cash expense.
Okay, I need to do an interview practice: assume $100 pension as a liability and pension goes down by $10. You record a -$10 pre-tax income and assuming a 40% tax rate, your net income goes down by $6. Now, on the CFS, net income is down by $6, but you add back $10 because pension amortization is a non-cash expense. That means your operating cash goes up by $4. Now, on the B/S, pension is down $10 on the liability side, and cash is up by $4, so A + L is down $6. Your net income, which goes into equity, is also down by $6. Your balance sheet balances.
Okay, that's good. if that is wrong accounting, other people please point out.
8. OCF section. I believe you started the normalization with EBT, not net income. Check that again. For U.S. listed companies, people usually start with Net Income. For Hong Kong listed companies, people start with EBT a lot (then you need to account for taxes payable and whatever separately, which IMO is a pain in the ass. It depends on how good your auditor is because that is where bankers get their raw data).
Not sure how that works for EU companies.
9. For turnover ratios, use formulas like Accounts Payable / COGS * 365 = Days of Inventory. Things like that.
I believe that's enough for some more work.
While I don't have iridescent's level of experience, rosenbaum & pearl's IB is also a great resource. I use it in conjunction with aforementioned M&I guide and it's rare that the combo is unable to answer a question I have. I ended up buying the textbook but if you look hard enough you can find a full PDF online; I went pretty deep into google and found it prior to buying the hardcopy.
I'm an Analyst 1 at a shitty bank, so I don't think you are too far behind in terms of experience.
Good luck to your job search and hope you land a spot at a top group.
Had a quick look at your model. Too much detail to go over, but have some recommendations that might help (apologies these are bigger picture design suggestions, but I swear they will help):
1. Have your model flow I/S, C/S, then B/S. When I prep people for interviews with the accounting question, I have them keep the same structure. Why? I/S starts with Rev and goes to NI, C/S goes from NI to change in Cash, B/S goes from Cash to RE. It really helps you keep the flow and not miss anything and helps with point two:
2. Model the balance sheet balances as prior year priod + line item from cash flow statement. If you capture all the C/S statement items correctly (without accidentally reversing signs) you are GUARANTEED to have a balanced B/S. I used to have a paper side work sheet where I would list each individual cash flow item (and Excel line) and mark it off as I included it in the balance sheet. This one rule has saved me HOURS in chasing down random model errors.
Once you follow these rules and build in standard checks (all sources of cash add up to all uses of cash, change in LFCF equals change in net debt, etc). It should be a lot easier to hunt down any errors.
Hope that helps!
Hi Toronto,
Thanks for your reply.
Interesting idea. I heard about it before but saw some problems. First, I thought it was easier to forecast balance sheet items than cash flow. Second, when you predict cash flow items, you are probably more likely to be biased in your prediction, as the CFs directly impact the DCF. But I will definitely give it a try because creating a structure that is easy to edit/error-check seems one of the most critical features to models.
If my understanding is correct, basically, I create one line in the CFS for each line item in the balance sheet that is not fixed and make my forecast down from the CFS to the balance sheet. I see one practical challenge here. If I follow your advice, shouldn't I adjust historical statements to stay consistent? How do you handle this?
Also, you said my model is too detailed. You probably have much less line items. So, would you recommend to reduce my structure to less line items?
Thanks for your help!
Libero quod voluptatem ipsum architecto aperiam deserunt nam. Aut eum accusamus neque perferendis ducimus. Nemo vel et eligendi.
Possimus dolorem similique corrupti nobis aspernatur. Et explicabo qui vitae aut. Qui fugit earum quo expedita sunt porro consequatur. Amet eligendi ducimus reiciendis sint ab assumenda mollitia. Aspernatur eos sed commodi eum doloremque ex.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...