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

 
Best Response

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.

 

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 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".

 
  1. go to your file browser 2. find and click on "book1_0.xls" 3. drag file to the recycle bin 4. empty recycle bin

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).

 
trader_timmy:

1. go to your file browser 2. find and click on "book1_0.xls" 3. drag file to the recycle bin 4. empty recycle bin

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).

 

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.

 

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.

 

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

 

BBDreamin Found my computer, here's the changes you need:

  1. 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

  2. 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)

 

Yankee Doodle, pure crickets, that's where I come in. Any of these useful?

  • Help with a merger model.. Balance sheet won't balance have an issue like this.. do you typically start with the cash flow statement and check each line? ... Hi all, The issue is probably staring me straight in the face and I'm just missing it, but ... I figured I'd ask the experts for some help. For reference, I'm using the Macabacus merger model ...
  • Automatically Draw on a Revolver in Excel thoughts? Revolver Drawing with Negative Cash Balance Many operating models will be built on the assumption ... revolver if the cash balance dips negative. For example, if in Q4 their cash goes negative, I want the ... model to automatically draw on the revolver to get cash back to zero. However, this seems t
  • Model Help- Dynamic revolving credit facility How can I build a dynamic revolving credit facility in excel that once a min cash balance is set ... the revolver and cash balances increase to meet the minimum required balance? The problem is ... a circular reference when I calculate the required draw from the revolver, add it to the revolver, and add it ...
  • LBO Model Issue Hi guys- Building an lbo model for the first time and having trouble balancing the model. Gone ... through everything and not having any luck. Any help would be massively appreciated Cheers lbo model ...
  • revolver calc (with minimum cash balance) balance. Thanks revolver Building a Model ... Could someone give me a formula for a revolver paydown / drawdown that factors in a minimum cash ... balance? Thanks, currently using a simple-min for the calculation, but it does not assure a minimum cash ...
  • Reconciling Cash Flow Statement & Balance Sheet in Model Just a quick question regarding accounting/modeling. I have been learning to model with BIWS and ... recently tried to model two companies on my own from scratch. The place I'm having problems with is ... reconciling the current year BS and cash flow statement. For example, my formula for current year changes in ...
  • Learning How to Forecast Balance Sheet Accounts modeling out the statement with the balance sheet driving projections or the statement cash flow driving ... the CF driving the BS. Cash is just a minimum cash balance and sweep the rest to revolver ... statement modeling, the balance sheet can sometimes be to confusing to project and there are two methods for ...
  • More suggestions...

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...

I'm an AI bot trained on the most helpful WSO content across 17+ years.
 

Occaecati explicabo autem ut voluptatum vero. Quo omnis sunt eos et saepe autem. Ducimus molestiae magnam laborum tempore excepturi. Nam dolorem qui eos.

"Work ethic, work ethic" - Vince Vaughn

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”