Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn moreIf you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email [email protected] to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.

## Answers

Charley KydTitle:FounderCompany:ExcelUserRobert,

Let's take an example...

Suppose at the end of January you owe $1000. If interest were 1% per month, at the end of February you'd owe $1000 + $10 on the beginning balance.

If you borrowed an additional $100 on February 15, you'd owe approximately $100 more plus about .5% of $100, or 50 cents for the interest.

This would give you a total of about $1110.50 at the end of February...if my mental arithmetic is correct.

Then, in March, you'd repeat that logic, with no circular reference.

What am I missing?

Robert MenziesTitle:DirectorCompany:Menzies Aviation ConsultingHi Charley,

Sorry my query was not clearer.

The missing bit is that the business needs large amounts of funds spent on fixed assets, so I need to have a formula that says in February there is say $2,000 outgoings on capex so the additional funds are $2,000. Over the 10 year plan I need a formula that checks in what months does the business need to borrow more. Once you have a logic formula to check IF the cash is < zero, then borrow the missing amount, then it becomes circular.

So the calculations are yours PLUS a logic test to see WHEN the business needs to increase or decrease the loan.

Sarah JacksonTitle:Associate EditorCompany:ProformativeHere's aaccounting spreadsheets, plus all kinds of other excel resources, such as tests, tutorials and more:

treasure trove of free excel spreadsheets, freehttps://www.proformative.com/resources/free-accounting-spreadsheetPlus, this free

"Excel Shortcuts Cheat Sheet"https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Don WallTitle:CFOCompany:Double B Foods, IncRobert,

Sounds like the cash calculation is in a balance sheet format, cash is dependent on the loan balance and other balance sheet items. If I am correct, take the calculations of cash and loan to a separate section, then if cash <0 you can add the loan proceeds to bring to 0, and adjust both cash and loan balances on the balance sheet from the separate calculation areas. This should eliminate the circular reference.

Robert MenziesTitle:DirectorCompany:Menzies Aviation ConsultingDon,

Thanks but when I enter an “IF’ logic formula to borrow the amount I am short Excel gives me a circular reference and instead of correctly calculating the amount of the loan as negative cash shortfall it just gives the result “o” and the circular reference.

I tried to move the calculations to another sheet or below the balance sheet but still have the same problem.

Jake FeldmanTitle:Managing DirectorCompany:Global TaxFin Advisory Group LLCHi Robert,

This is a classic funds flow spreadsheet analysis issue, especially if you are preparing both income statement and balance sheet projections. Besides the cash/debt circularity, the balance sheet also depends on current period income in the equity section but income depends on interest income/expense which depend on cash/loan balances. You can solve this with simultaneous equations or proper sequencing of equations in Excel. One approach is to setup a separate plug line at the end of the balance sheet that is the net cash/debt difference between all the other balance sheet items, excluding cash/debt. If the plug is > 0, you set cash = plug. If the plug < 0, you set debt = -plug (minus plug). Interest income/expense should be equal to rate*average plug balance [(prior+current)/2]

It's OK to have circularities in Excel as long as they are properly sequenced, so that Excel can follow its iterative sequential logic to solving such circularities.

Hope this is sufficiently clear.

Jake

Robert MenziesTitle:DirectorCompany:Menzies Aviation ConsultingHi Jake,

Thanks but when I enter an “IF’ logic formula to borrow the amount I am short Excel gives me a circular reference and instead of correctly calculating the amount of the loan as negative cash shortfall it just gives the result “o” and the circular reference.

I tried to move the calculations to another sheet or below the balance sheet but still have the same problem.

Jake FeldmanTitle:Managing DirectorCompany:Global TaxFin Advisory Group LLCRobert,

We've reached the point of diminishing returns from online discussion, so please send me the spreadsheet and I'll have a look.