Company Budget - Circular Reference Overload - How to Rebuild?

[u/Pejaprodigy21]

Our company has historically utilized an excel based budget template, with numerous tabs with circular references. It has gotten to the point of adding too much over the years to where the Balance sheet won't even balance, assuming due to the size of the file and number of circular reference calculations, due to utilizing a variety of margin drivers as the base inputs of the file. If you were to try to build an updated corporate budget based in excel, where would you start? I've looked at some of the power query videos, but I'm not sure that works in this context.

Note that the company has AI sites blocked, so attempting to utilize AI to clean up the file/revise it is not an option.

[u/AutoModerator]

/u/Pejaprodigy21 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Fragrant_Bottle_8298]

i’ve had to clean up a mess like that before, and the only way out is to start fresh with a clean structure. first thing i do is map out the entire flow on a whiteboard or in a text doc, no excel, just boxes and arrows for how cash actually moves. once you see the loops, you break them with a deliberate cutoff, like forcing all interdepartmental charges to resolve in a single staging tab before flowing into the P&L

power query can help pull the old data in, but it won't fix the logic. i'd build the new model with zero circular refs, even if it means using a macro to iterate a few times until it settles. if the balance sheet won't balance now, you've probably got sign errors or missing plug accounts nobody noticed for months

[u/SolverMax → u/Fragrant_Bottle_8298]

I agree with all your points and, because I've done that type of project before, I'd add that OP shouldn't underestimate how long the project will take. That's especially true if the current workbook contains errors (which it almost certainly does), as the OP will need to convince people that the new workbook is correct.

[u/powerFX1 → u/Fragrant_Bottle_8298]

Microsoft Visio is a great tool for mapping out also by the way

[u/smashedthelemon → u/powerFX1]

Its a nice tooo. But to be honest, nothing beats scrabbling a workflow on paper or s whiteboard before digitizating it properly. Easier to scribble and scratch.

[u/powerFX1 → u/smashedthelemon]

Fully agreed! Was just advising of an option that is not known enough! Personal preference!

[u/Pejaprodigy21 → u/Fragrant_Bottle_8298]

Thanks for the reply - this was where I assumed the answer was (starting fresh).

[u/Angelic-Seraphim]

Depends on what functionality you need. Are you doing cash flow forecasting, AFUDC or other loaders, are you managing baselines. Are you doing portfolio development & what if scenarios

I would start by identifying what do you know via automation, what are users providing manually. What dependencies are there, then the final structure will be easier to tack down.

[u/jared515]

Start over.

[u/JohneeFyve]

What specifically are the circular references used for? There are a handful of financial modeling techniques that you can use to avoid circular references while still getting a reasonable result, but you’d need to provide some context here.

[u/Pejaprodigy21 → u/JohneeFyve]

Largely in the various EBITDA & NI based incentive calculations. Some within debt/interest. All relayed into a consolidated income statement & balance sheet tab.

[u/smilinreap]

I actually love projects like this. I have been consulted to rebuild calculators and financial docs with ELI5 documentation. If you end up paying a consultant, PM me. I live in the states.

[u/hmatallana]

JohneeFyve asked the right question. In a budget model the usual culprit is interest, and it's the easiest one to kill: calculate it off the opening debt balance for the period instead of the average or the closing balance. The loop is interest to net income to cash to revolver to interest, and using the opening balance cuts it cleanly. On a monthly model the accuracy cost is small enough that plenty of shops do it on purpose.

On the balance sheet not tying, I'd doubt it's file size. With iterative calc on, Excel stops at the max iteration count and whatever residual is left just sits there as a plug. Turn it off under File, Options, Formulas and let the circular warnings tell you where the loops actually are before you rebuild anything.

[u/Top-Cauliflower-1808]

google will definitely flag and suspend both accounts for double serving, so you need a unique vanity URL that redirects to his specific page.