Hi,
I am trying to practice my modeling abilities by creating a simplified model for ARII. I am not trying to recreate a copy-cat of the 10k FS, but roll things up into the most essential line items.
I am stuck...I really don't understand why my balance sheet won't balance. Is it related to the changes in working capital or the fact that I am rolling up leased rail cars and PP&E all into cap ex?
I should note that I have added int. expense in as I tried to "zero" any projected changes to the company in order to identify the problem.
| Attachment | Size |
|---|---|
| ARII Financial 67 KB | 67 KB |
Not a full answer since I took literally 30 seconds actually looking at it, but here's what I noticed. I may have more for you later depending on if I get bored later today.
That said even if I do I'm not going to just give you the answer. I'm going to ask leading questions to get you there because it helps you a lot more to figure it out yourself.
-Poke around some of your assumptions and take not of which ones change it and which ones do not. Notice in particular that changing almost every single item in your income assumptions (except revenue components) changes the amount of this imbalance, but no matter what happens the change in the amount is constant from year to year.
This points you towards one specific type of problem. This problem is what I'll be checking for if I go over it more.
Also why the hell do you have a model with a negative cash balance? If you're going to be running a model or a scenario that involves paydown plans of existing debt you need to have some kind of a limit on how low cash will go.
I had a few more minutes (waiting on work here). So yeah, this is kinda a mess.
-Review how the 3 financial statements connect. Where is your Net Income supposed to go and where is it actually going in your model?
-Is it even possible from a GAAP standpoint to have a CFS line item that reduces the value of your cash account but doesn't impact any other IS or BS accounts?
Now here's the big one: When I set ALL assumptions to stay flat, your model balances. Why is that? And why is it that a lot of these imbalances suddenly start happening in Y+2 rather than Y+1?
I've fixed your model with the exception of that one small mistake that I don't really care to fix (this kind of thing is a good exercise for me).
Get back to me with your initial answers to those questions and i'll let you know how warm you are.
Be advised that you're gonna feel kinda dumb when you see them. There's four major mistakes in this model and all were easily preventable by being more careful.
Hi Attack_Chihuahua , really appreciate the help - brand new to all this stuff.
Right now, I'm just trying to get the model to balance - not modeling for a specific paydown in debt scenario.
Apologies ahead of time for answers that still convey I am confused:
a. to roll forward PP&E, I'm taking last year's PPE, adding back capex (which I have held constant as a portion of net income) and D&A.
I'm having trouble finding a line items in my CFS that isn't linked in some way to my balance sheet, excluding net income, but I thought that eventually flows through to cash
I went back and made sure everything in my assumptions section was held flat (and noticed that the YoY assumptions were off)...the balance sheet is till off, albeit by a smaller amount
You're correct on the CFS but that isn't the nature of your mistake. Look at how your working capital changes are impacting cash flows and what's wrong with years 2 onwards.
It doesn't just have to be linked. The changes have to be correctly calculated.
Went back through and made sure I am adding net income into shareholder's equity, and fixed my signs for PPE - balance sheet is still off but not by as much
Yep. Got that. Fix your working capital and I'll drop my file so you can see what I did.
Only looked at it for 10 secs but how is net PP&E going lower if capex > D&A?
Is PP&E not BOP PPE + capex (a negative) - D&A - assets sales? In that case it would be 1,085,061+(-75391)-37,889-0=971,781.
Should I be turning capex into a positive?
Let me ask you this: when you spend money buying assets, do your assets increase or decrease?
hey can you take a look at my model, it won’t balance.
Attack Chihuahua pretty much nailed it but I'll provide some practical fixes that will save you a lot of pain in the future.
Always build out roll-forward schedules for PP&E and SE if it's more than just adding NI. Would also do this for debt too, especially with multiple tranches. This will allow you to very quickly see where your busts are and allow you to fix them.
If it's a simple model such as this one, just do it all on one tab. It's easier to link from statement to statement and makes it easy to audit.
Continue to use +/- numbers. Oddly enough, using +/- is a big reason of why this busted on you; the advantage of +/- is that you shouldn't have to use a - in a formula so you can just sum everything without worrying about sign change.
Good practices in general. I've actually found that in practice, it's often easier for auditing purposes to build an extremely simplified model just to get everything tying together correctly. Once you've done that, THEN you can blow out your model by building more robust forecasting mechanics and re-linking your line items so that they are tied to those forecasts The benefit of this is that all you have to do is change the reference in your simplified model.
Did this with a small services company lately. We got more detailed financials sent over, so rather than blow up my existing financial statement model I dropped all the line items in on another tab complete with drivers (as a % of revenue for all of these numerous little things) and summed them up. I changed my SG&A values so it referred to the summed value. That way it the revamp doesn't change the basic flow of your model but you can still punch in new assumptions for any line items that end up being challenged or changed.
In practice it's also good to have the simplified model because you're going to need it later. NOBODY is going to put a model with 60 frackin SG&A components into a presentation so it helps to have a simplified tab where you can copy-paste the summary number.
Tell her to focus on a stationary object as a reference point
Last but not least
TADA, it balances.
Thank you! I know this seems like nothing for guys with experience, but this is my first time having a 0 show up on that check line and it feels way better than it should!
Autem deleniti voluptas sit facere quo rerum distinctio. Quod debitis corporis et at. Temporibus dolorum sit quia architecto id consequuntur suscipit.
Odio totam quod repudiandae architecto dolorem et. Quasi labore qui similique fugiat. Eos sapiente dolores nam id rerum ipsa. A odit dolorem a in qui inventore eaque. Aliquam fugit tempora esse temporibus voluptatem assumenda.
Quis consequatur nobis voluptatem porro dolorem. Aperiam rerum id at. Officia vitae ullam maiores officiis. Vel aut nam qui harum incidunt molestiae fugiat. Aut omnis tenetur rerum recusandae quam non.
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...