Model not balancing - other tips have not worked
Hey
I have taken a stab at creating a 3 financial statement company model, but the resulting forecasted balance sheet is not balancing ! Very frustrating
I have looked at other threads to try and figure this out, but nothing has worked. The balance difference shows no particular trend - my guess is that there are a few confounding errors.
Don't know if this has anything to do with this, but I was unfamiliar with treatment of FX gains/losses as well as credit finance income/charges (both in financial income/expenses, lines 39-50). I also did not know what to do with currency translation differences (line 260)
Any tips ? I figured I would be best off asking you direct. Any help would be hugely appreciated
This is the craziest fucking model I've ever seen. You have random hardcoded formulas everywhere.
Here is what I would do: 1. Delete everything after 2015. 2. Start over. 3. Keep this in mind: For every action, there is an equal and opposite reaction.
In other words, everything that moves on your IS/BS/CF needs to show up somewhere else. If receivables go up, they need to subtract an equal amount from cash flow. If you have a non-cash expense (e.g. provisions), it needs to show up in your liabilities on the balance sheet (or subtract from your assets), flow into your net income/retained earnings and add back the tax-effected amount of cash, etc....
If you are going to have such detailed financial statements you need to do this in a disciplined, pedantic manner. Don't skip steps. Every time you type in a line item in one statement, think about where else it goes and reflect that change.
But no, I'm not going to go through your crazy fucking model and try to find the error.
I am Morbidly curious.... But I agree.
there is a reason why people are possessive about their models and get angry when others break them.... Start over from some point where it is simple.
Get it working Then add complexity once it works...
Felt like I was the only one who wanted final say in the models I build...
I'd agree with both mrb87 and AAROne. It would be tremendous help also if you learn to format for aesthetic purposes. My eyes are sore looking at this - use a lower border when you for summing assets/liabilities and other major sum items. Indent some of the summed components (e.g., cash, AR, inventory). Color code your text for hardcoded cells and cells that reference other tabs or worksheets. It's the little things that can make a model easy to follow.
hey can you take a look at my model, it won’t balance.
hey can you take a look at my model? it won’t balance
model won't balance because of market cap (Originally Posted: 11/01/2009)
I've got to send a model in a few hours and everything balances except for the market cap. I've got market cap to equal [last closing price * fully diluted shares outstanding]
What should I do? Does anyone know how CapIQ/Bloomberg actually calculates the fully diluted shares outstanding of a company?
Thanks!
Wait... What are you using CapIQ/Bloomberg for?
Get the common shares outstanding from the cover of the 10-K. At the bottom of the cover, it will say "As of XXXX, Company A had AAA,AAA,AAA shares outstanding." That's the correct common number to use. I'd guess the consolidated P&L is giving you the weighted-average common shares outstanding, which is the problem.
capiq/bloomberg is just to verify that my calculation for market cap matches what they have
CapIQ/Bloomberg isn't always correct. Better to run it on your own and trust your inputs.
Check to see if you may be missing warrants or convertible debt/preferred.
Oh, and last thing. You're going to want to use options outstanding, not exercisable. That likely has something to do with it. Not sure if this is a preference-type thing, but the reason we use outstanding vs. exercisable is that in a change of control transaction, most options vest and become exercisable. So try entering in options outstanding and see if that does anything.
hey thanks for the response. I was just reading a slide from training and it said "to calculate fully diluted shares for trading comps, use exercisable options as opposed to outstanding options since only vested options should be included for trading analysis".
No worries. Did it end up balancing when you tried using shares outstanding? I can't speak for CapIQ as I don't use it at work, but I'm 99.9% certain BB calcs diluted shares using options outstanding vs. options exercisable.
Model won't balance after spending more than 12 hours in. Please help... (Originally Posted: 10/28/2017)
Hi everyone,
Could someone please take a look and try and figure out why my BS won't balance? This is my first time modeling and my head has been hurting for over 3 days now. The company is Big C Supercenter in Thailand, a retail operator similar to Walmart. I think it has something to do with my CAPEX and PPE but can't figure out what's going on. Thanks in advance.
goo .gl/qpSjn2
dude the link doesn't even work
WSO won't let me paste the full link so I needed to add the space. Does the link not work even without the space?
Operating model not balanced, seek help (Originally Posted: 04/29/2016)
As the title says... I have crunched this model for like a week but still not balancing.... The attachment is the operating model page emits all the revenue and expense build since those are all estimates.
As you can see. I have only got 2015's balance sheet and income statement but not cash flow. To ensure I am doing it right, I even use change"balance sheet item" as cash flow item name to make sure every change on the balance sheet has been reflected on the cash flow statement.
Thanks
Honestly don't know where to begin here. It would basically take redoing your model for you to make it correct, so there's no point in continuing on with it. I would say this template is way too complex for what you're trying to do. Also you can't just have a "Balance Sheet Item" on your cash flow statement to make your BS balance, you might as well not even have a CF statement if you're going to do that.
IMO best move would be to start over with a simpler template or go through a tutorial. Try going through the first 5 steps of this Macabacus tutorial (http://macabacus.com/operating-model/introduction).
I would do the same, except instead of building a vertical model, build a horizontal model. So, instead of having everything on one tab, put all items (e.g. income statement, balance sheet, etc.) on separate tabs. This makes it easier to follow along. Also, COLOR CODE! I cannot edit models when everything is black. Hard-coded numbers should be blue (RGB 0, 0, 255), formulas should be black (RGB 0, 0, 0), and links to other tabs should be green (RGB 0, 128, 0).
Financial Model Won't Balance - Constant Negative Check Value (Originally Posted: 12/05/2017)
Hey WSO,
So I'm practicing creating a 3-statement model. Decided to make a simple one first just to iron out all the details in my head. It won't balance, the assets side is less than the liabilities and equity side for some reason.
I kept my assumptions very simple just to see the whole process flow:
-revenue grows at a fixed annual rate -non current assets (PPE, Goodwill, and software) are all constant throughout the year -COGS and SGA projected as % of revenue -Debt is amortized and is fully paid at the end of the whole projection period. -Balance working capital items are projected using DSO,DIO,DPO, and the remaining as % of revenues.
I'm thinking the mistake is either in cash flow statement or in the working capital items. Probably making a rookie mistake here. Help a brother out.
Thanks!
Global Trapstar
I tried changing every other variable in the balance sheet and the negative check values won't change except retained earnings.
However my calculation for retained earnings is simple. Previous year's + net income (no dividends paid out).
hey can you take a look at my model, it won’t balance.
I am looking at it now. It's not obvious to me using trace precedent where the problem is coming from.
You account for interest expenses twice - in both your operating and financial cashflow.
hey can you take a look at my model, it won’t balance.
The post above points out the issue, you're double counting the interest expense. You reconcile net income to CFFO by starting with net income, which is already after interest on debt and taxes. No need to deduct it again on the cash flow statement.
Also, when a model doesn't balance look at the unbalanced number and see if it matches a number in your 3-statement model. For example, the -123 matched 2018 interest expense, the -217 matches 2018 plus 2019 interest expense, and so on. If that doesn't work, try doubling or halving that number to see if it matches any numbers in the statements.
That's it!
Facepalm moment, classic rookie mistake I suppose! My mind can rest now haha.
Thank you so much brothas, SB's for all, I'd give out 10 each if I could (but can't lol). WSO is the best!
Valuation Model Balance Sheet Won't Balance. (Originally Posted: 03/28/2017)
I have uploaded my model. Just need to know what I am doing wrong. Any comment would be helpful. Thanks.
On first glance, why do you amortize intangibles infinitely? Your PP&E schedule is unclear. Your historicals don't add up. In your forecast, you added depreciation to your PP&E. I think structurally the model is fine. It's most likely smaller hiccups such as the above that are throwing you off. PS: anonymise your spreadsheet.
Model not balancing - Need help (Originally Posted: 01/01/2017)
Model not balancing. This is a first stab at a three statement model and would love have a second set of eyes to see if this is correct. See attached.
Thanks
Your model is not balancing for the following reason: - Tab "bs", line 14: for Definitive Life Intangibles, you are picking up the additional intangibles instead of the end balance. You should refer to line 18 of the "amort" tab, instead of line 8
Other minor comments: - I would add "asset write offs" to your cash flow statement (even if you assume them to be 0 now, you want your model to be fully dynamic) - I would add a circuit breaker as you are calculating interest based on the average debt quantum (which is right) - Your Long term debt repayment is written as positive, I guess that's a typo as you probably meant to put it negative, or to have it a long term debt issuance
Otherwise this model is well built
Just a nitpicking comment, but Toro's fiscal year ends October I believe, not December 31. Other than that, looks good.
URGENT- Model will not balance, please help (Originally Posted: 05/22/2016)
Hi everyone,
Could someone please take a look and try and figure out why my BS won't balance? I've spent hours on this now and cannot figure out why it won't balance.
something to note: - I was given very little information to do this exercise, only the FP&A tab. that's it. the rest I'm suppose to make assumptions... so don't focus on whether or not an assumption makes sense, but rather focus on why it's not balancing. -this model is weird due to a couple things: its a monthly projection as opposed to quarterly/yearly, and there is negative net revenue due to the contra revenue account and the timings. -may want to take a look at D&A as only Amortization was expressed in the FP&A tab....refer to my comments within the cells for more info. -also may want to take a look at the debt schedule.
SBs for anyone who can give me a hand....I GREATLY appreciate any help.
Attached is the model. thanks
On my phone so I can't completely figure it out but your balance sheet is (at least at the beginning) off by the sum of the Change in Other Liabilities cash flow item. Maybe your sum is missing that row?
trader_timmy, that appears to be exactly it... however, I don't see why this is the case as my sum function does appear to be catching it
BBDreamin Found my computer, here's the changes you need:
You have Other LT Assets (Row 61) referencing Change in Other Liabilities, you either need to project that line and make a corresponding cash flow item or just make it equal to the last historical year. That should make the first 3 months balance
In April you max your revolver commitment but that's only reflected on the cash flow statement not the revolver on the balance sheet. To fix this, change cell AD131 to =IF(AD152>0,-MIN(AD151,AD152),MIN(-AD152,AD155-AD151)) and drag across to the rest of the cells in that row. (There is probably a more efficient formula for but this should work for your purposes)
trader_timmy I genuinely appreciate you taking the time to look through that, thanks again. one last question: even though my A+ L+E, the Balance Check formula shoes (0), (0), 0 for September, Oct, and December. Any thoughts on why this is?
Model won't balance - any ideas? (Originally Posted: 06/10/2016)
Ok WSO--this site has been an incredible resource, and I'm at wits end with this model for LB. For whatever reason, I cannot get it to balance, and I'm all out of reasons why. Can someone, anyone, please take a look and tell me where I messed up? Getting desperate. In the middle of the self-study process, so those familiar with TTS will recognize the format.
You didn't sum up your total non-debt LT liabilities. That and your equation for inventories is off (linked incorrectly).
That'll balance it out for you.
Awesome. Thanks for your help. Much appreciated.
Please balance this model (Originally Posted: 03/26/2017)
Please balance this model for me. Will venmo the first person to do so 10 bucks. Lame, I know, because its probably something extremely obvious that I'm missing. Thanks for the help.
For one Your 'Other Intangibles' figure is increasing but is not flowing into the cash flow
Model Balancing Issue (Revolver & Cash) (Originally Posted: 03/21/2018)
I'm currently an advisor in my school's IB workshop, and I'm attempting to create an FSM + linked DCF model for the purpose of showcasing to the underclassmen and using it to teach them how changes in financial statement line items can affect a DCF model.
However, my model is unbalanced, and I can't figure out how to fix it. I kept passing the balance check, until I began creating the forecasts for the revolver and cash. Once I forecast those and linked them to the balance sheet forecasts, the model became unbalanced (it's showing there being too many assets, meaning that I have too much cash). However, after trying everything, I'm not sure how I can balance this.
How do you guys go about solving issues with the revolver? It's really such a goddamn fickle matter and I'm getting frustrated as shit.
Yankee Doodle, pure crickets, that's where I come in. Any of these useful?
No promises, but thought I'd mention a few relevant users that work in the industry: TheCityMonkey Wamm @Sussex"
If those topics were completely useless, don't blame me, blame my programmers...
Do you have a plug / what is it?
Few things cause this:
You're not properly adding back your debt repayments. Make sure drawdowns and repayment is linked both to the ending balance and in the cash flow statement.
You're using a plug incorrectly (wouldn't recommend).
Your min function doesn't have a negative in front or incorrect summation (in CF statement or BS).
make sure everything on the CFS is linked correctly as well. on the revolver, use a min on previous balance and cash flow available/needed from revovler
Quo dolorum nulla aut repellendus aperiam sint. Et dolores qui perferendis non. Pariatur provident quas in non non distinctio nisi quo. Delectus animi rerum rerum consequuntur.
Voluptatum cumque cumque iure dolore ad. Molestias ipsam molestiae quisquam ipsa in.
Est aut amet voluptas repudiandae perferendis debitis quia et. Velit rerum unde et optio ut adipisci quia. Consequuntur fuga pariatur dolores similique asperiores beatae earum molestias.
Dolor ut deleniti voluptas et corrupti sit. Consectetur ipsam similique non magni quia qui ut. Inventore similique aut excepturi. Qui dicta molestiae quia velit beatae rem quis.
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...
Deleniti iure autem numquam consequuntur quam ex. Esse possimus laborum quia quae et. Fugit provident deserunt quo sed dignissimos. A quos culpa totam itaque nihil adipisci. Corporis molestiae nihil consectetur.
Hic dolores eius reprehenderit ut voluptatem. Aut aspernatur et illum reiciendis aut accusantium ipsa. Reiciendis non dolores sed eum voluptatem et.
Alias est ab consequatur eum enim aperiam autem ut. Quam ipsa consectetur sunt ad eveniet non excepturi. Possimus natus ad nisi amet.
Vitae vel voluptatem autem adipisci. Ea aperiam sequi culpa temporibus. Ullam qui sequi possimus aliquam nam vitae. Non odio illum eos repellendus.
Et temporibus tenetur saepe sapiente sint ipsum iusto. Temporibus asperiores est fugiat non quia rerum molestias. Iure est aut at beatae architecto qui quisquam. Reprehenderit commodi esse ratione autem.
Earum provident eius in distinctio consectetur quos voluptates. Est temporibus quidem dicta modi. Voluptatem accusamus quam fugit molestiae nulla et voluptatum. Vel recusandae veritatis dolor non optio aut repudiandae.