Excel functions · Tips and tricks

Compared to What?

ComparedToWhat

Comparative financial reports are a cinch in XLstatements, but here are a few tricks and tips.

If you are comparing more than two ledgers, consider using the LedgerIDs in the same columns as their formulas and referring to them using a relative column and absolute row. Refer to the previous post on consolidations for examples using CpnyID, as the same technique can be used for LedgerID. (It’s also useful with other arguments like SubAcct and Pd.)

It’s common for a financial report to show differences (changes) and also those differences as percentages.

Percent

Here we can see the difference from budget, which in cell E7, is just the formula =C7-D7. Cell F7 is selected, and you can see its formula in the formula bar: =IF(D7=0,””,E7/ABS(D7)). That formula is doing two things. Firstly, we do not want to see #DIV/0! where the budgeted value was zero, e.g., for the Supplies and Leaseholds rows in the above example. That’s what the IF part of the formula is handling. We chose to make the percentage blank (“”, not zero) where there was no budget amount, because any number divided by zero is undefined (hence the error without our IF). A blank percentage rather than zero shows that the percentage is not available. The other tip here is to use the absolute value (ABS) of the denominator. By doing that, we ensure that we are not dividing a negative number by another negative number and returning a positive result. If we had just divided the difference by the budget (E8/D8) for cell F8 on the Due from Subsidiaries row, that would have given 27%. The difference, though, is a negative number, so we want the percentage to also be negative. Using ABS accomplishes this, and rather simply.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s