Model won't balance. Any help?

I think it would be easier for people to help if you made your model easier to read. Right now, everything in there is black. Impossible to figure out without going through each individual cell.

It helps a lot for your own doublechecking to do this, as well. Try this: blue for inputs, black for formulas, green for any cell or sheet references. You can add a fourth color like purple in the future for cells with relevant formulas (if you use a calculations sheet).

Also, you should find a model with a good debt schedule - see an LBO model somewhere, because yours, frankly, doesn't make a lot of sense.

Yikes man. See a lot of things that don't look right either. As your model's running now your cash available to paydown debt amort. is negative, that means you need to be drawing down on your revolver / credit facility to cover.

Also, idk what you're going for (know you're not asking about this) but that's not how you model an E&P company. Sent you a PM.

Updated with revisions.

The balance is off by 433 increments. Any tips would be greatly appreciated.

Thanks!

Your model is not balancing because your CF statement is completely screwed.

You are adding back a whole host of adjustments that have no corresponding BS movement. You can't just randomly throw cash around on your CF statement, make no changes to your BS and expect it to balance. Go through CF and think about what is and isn't moving on the BS.

e.g: - CF, row 11: You need to impair assets as this is non cash - CF, row 12-14: You are adding back items to Net Income, ok, so what are you moving on the BS to compensate for these? Equity is still going up by net income, so you need to either increase assets or decrease liabilities. - CF, row 31: These dividends aren't flowing to change in equity - etc

Also, your DS is screwed. Check out your average calculations...

To debug, try this:

• link to your BS check on your CF so you can see as you move things how that changes
• delete rows 11,12,13,14,16,18, 24, 31, 32, 33 (from column M to W)
• your model now balances
• systematically work through each row, adding back the adjustment, one by one, and think about the corresponding BS change you need to make
• don't move to the next until you make the BS balance

Why not use an existing template like the one I linked?

Also, your DCF has terminal year FCF jumping 71% because, among other things, your D&A=Capex. That's not realistic.

also put some thought into user interface and ease of use as this thing is a pain to audit. why empty columns between years?

FYI, your NCI payments and stock option exercises arent linked to the equity portion of your BS. This doesnt account for your entire 433, but it accounts for part of it (im sure theres a few more cashflow items that arent linked up).

mop33

Also, your DS is screwed. Check out your average calculations...

Why are there empty columns between the years? Did you manually do every formula (i.e. =[prior year]+1 for each year cell) or did you set it up properly (i.e. =[prior year]+1 and drag it over) and then insert the columns? Regardless, there is no need for the extra space.

Also, why is the risk-free rate 5.5%?

Aside from all the CF/BS changes mentioned above, you also need to fix the sign in your calculation in Row 38 on the Balance Sheet (where you account for the dividends). As you have it now, dividends are being added to retained earnings instead of subtracted. This, along with the changes mentioned above, should balance the model.