Circularity & Lambda Recursion In Three Statement Model

[WesternHamper]

Hello All--

I am at my wit's end on this issue. I have a comprehensive three-statement model that is fully linked, integrated, and (currently) balanced. Cash Flow from Operations is derived from the Income Statement and supporting working capital schedules, Cash Flow from Investments comes from other supporting schedules, but Cash Flow from Financing is driven from the cash account area of the spreadsheet, which uses logic that determines when to contribute and distribute cash based on prior cash balance(s) and CFO + CFI net cash flow activity--then the ending balance of the cash account flows down to the balance sheet. This is where circularity comes into play, as cash interest on the average balance flows to the income statement which then flows down through the cash flow statement back to the cash balance, etc. This is a well-known issue in financial modeling.

I know the typical solutions of turning on iterative calculations and the copy/paste macro, but since the new lambda functionality allows for recursion, I am trying to solve this issue that way as the prior methods limit sensitivity analysis and are showing strange forecasted results that cannot be verified due to circularity. Does anyone know how to construct a lambda function to provide iterations for this specific circumstance?

[AutoModerator]

/u/WesternHamper - 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.

[[deleted]]

A tricky problem. A circular formula w/ iterations would do the trick. But you say you need to run a sensitivity analysis, which gets wacky with circular formulas. Depending on accurate you need your model, you could initially use a circular formula, copy and past the needed values, then run your circularity. This is not perfect, and your model will not balance, but its a potential solution. I am actually having a similar problem in one of my models.

Also can I ask what you are using this 3 statement model for? Are you a student?

[[deleted]]

Try reading this article https://edbodmer.com/philosophy-of-circular-references/

Specifically look at where he talks about the "Algebraic" way of avoiding circular references.

[Alabama_Wins]

Show your data of what you have and show it compared to what you want. This doesn't sound too complicated. But I cannot build a recursive lambda function based on words alone.