I need a simple formula to show the net change in a balance sheet account but I can`t seem to figure it out. I need it for a DSC ratio calculation and need to be able to add principal payments to interest paid on LTD. So the change in a balance sheet account added to the balance in an income statement account.

TIA!!
Jean

Hi Jean,

There are 2 possible codes for the yearly net changes: either NET for the full year net changes, or Y for the year-to-date net changes.
There are 2 possible codes for the balances: CURBAL for the current balance on file, or BAL for the balance as of the selected period.
Of course for an income statement account which resets to 0 each fiscal year, BAL would give the same result as Y.

So for the formula, you can add the two different values together. Say for example you want the amount as of the end of a selected fiscal period, it could look like:

=GLAMT("Y","B/S acct #") + GLAMT("BAL","I/S acct #")

If you wanted to use different codes, just replace the "Y" or "BAL" above with the desired code.
If you wanted a different value than those referenced above, a great tool is the GL!SuperSpec90 sample specification, that allows you to type in an account number and see all the available codes and the resulting respective dollar amounts, to identify what code is right for you.

Is there a formula that will show me the monthly net change for a balance sheet account? The above formulas are only yearly net changes.

Use:

You can see the results for all of the possible codes by using File | Open Template and choosing GL!SuperSpec90 from the list.

Thanks Andrew - that template is very helpful.

A question though...What is the difference in the following formulas? Is there any difference in the result?

=ADGET("GLDATA.LY","3300")
=GLAMT("LY","3300")

There is not difference in the returned result. GLAMT() is easier to type and remember.

Perfect - thanks!!

Is there something wrong with this formula? I'm getting an "Error!" result with it.

=GLAMT("BAL",$G478)

...cell G478 has the account number in it

Hi, Jean,

The formula works for me, granted in my case it references cell $G4. Even if I enter an invalid account in cell G4, I don't receive the Error! result you are.

Exactly what the issue is is difficult to tell over the forum. If you haven't gotten this one working yet, you might want to contact your dealer for more direct assistance.

Make sure that your account number is being interpreted as text by preceeding it with a "tick" ('). My guess is that it is being interpreted as a number.