Balance sheet out of balance (3 statement operating model)
I've been banging my head against the wall for a few hours and can't get this thing to balance. I think it may have something to do with either the income tax receivable/deferred income tax, the operating lease asset or liability, or something like SCB/tax withholding in the CFO/CFF statements.
I had also grouped together some smaller line items into SG&A like pre opening costs and impairments which also show up on the CFO statement so I'm wondering if that could be it.
Should the lease assets and liabilities all be grouped under the appropriate CURRENT label and included in the change in net working capital calculation? The 10-K filing mixes and matches between ST and LT liability and LT asset.
This is for Chipotle by the way. I don't think I can attach a file since it's my first post so I uploaded below.
*PS: you can balance it by changing the "impairments" line item in CFO statement to -30,856 but obviously that doesn't fix the problem.
*PSS: some of the line items jumped around a lot during Covid (FY20) so I tried ignoring those outliers when I could but I wonder if that is also leading to any problems with trying to forecast some of these line items.
Hi tDos, don't worry, the WSO Monkey Bot is here.... I'm hoping one of these links will help find your answer:
More suggestions...
You're welcome.
bump (if this is the wrong sub please move or I can post elsewhere)
I took another stab at it and updated some assumptions but still not getting any closer. I also separated out impairments from SG&A since there is an impairment line item on the CFO statement.
I think I've narrowed down the problem to the deferred income tax provision in the CFO statement, tax withholding for SCB in the CFF statement, and the income tax receivable and deferred income tax liability line items on the balance sheet.
The remaining questions seem to be whether I am including or excluding anything from my Equity formula that should/shouldn't be there.
Any help would be greatly appreciated as I ran into the same balancing problem with Labcorp so I must be doing something wrong fundamentally here.
Updated file is here: https://easyupload.io/bjw3y4
Obviously you may not be able to but if you send a screenshot or more info I or one of us can probably spot it easily
Also curious how often you do 3 statement models in CF?
i'm not in CF - I just chose that because I am trying to career change from commercial real estate to corporate development or strategy. i think i'm too old to go the IB analyst route so shooting for those types of positions on the corporate side.
the operating lease assets & liabilities is throwing me off too. i meant to ask in the previous post -- i think i should be including the change in short term/current operating lease liabilities in my change in working capital calc, but not the change in long term operating lease assets or liabilities, correct?
Your name is in the file, FYI
the file download link self destructs in 30 days so my secret dies with the 4 people that ultimately view the model
If it helps I've included notes on what I think are the potential issues:
1. For cash and equivalents on the balance sheet, do cash from prev. period on B/S + net change in cash from CF statement. Don't just link ending balance at bottom of CF statement
2. The "(+) Impairment, closure costs, and disposals:" cash flow item isn't showing up anywhere in the B/S. Have this decrease Other assets (or whatever the relevant bucket is)
Should work after those two^
He also has deferred income tax liability as $0 on the B/S and $0 on the CFS. Not capturing the change in the B/S value right now
Looks like Excel is different to screenshot above. I'm going off of Excel.
Thanks for taking a look.
1. Changing the cash reference formula had no impact, but it did make me realize I need to subtract Restricted Cash (H106) from the Ending Cash (H168) on my cash flow statement to get my Cash & equivalents (H95) total for the balance sheet (FY22).
2. My impairments line item from the CFO (H76) is flowing into my Ending Cash (H168) on the CFS and is added back to Cash and equivalents (H95). If you zero out impairments you can see cash is reduced by that $34,022 amount. I thought the impairments line item was also reducing the Net PP&E (H104) long-term asset on the balance sheet but I just double checked and it was not.
When I reduce my cash by the restricted cash and my PP&E by the impairment my out-of-balance total gets even bigger, but at least I made some progress I think.
I made the changes I specified to the original model you uploaded, not V2, and it balanced. Not sure what changed since but maybe you can reconcile.
Voluptatem et et animi. Nemo quia eius et vel. Rerum sed quo est animi molestiae. Voluptas molestiae quasi vel nostrum.
A dolor sint omnis quasi. Velit vel asperiores hic quae et.
Nostrum dolore fugiat culpa et omnis. Occaecati qui dolor et quo voluptatem. Quia eveniet nihil est est exercitationem ullam quaerat. Error voluptatibus aliquid quo delectus eius sint. Porro quae id aperiam vel. Voluptatum qui exercitationem sunt qui quo corporis voluptates. Voluptatem quo in quas.
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...