NetSuite Comparative Trial Balance in Excel

A comparative trial balance sets every account in your ledger against two periods. Each balance is its own Finsyte function call, and the comparison is plain Excel. The design comes from the five rules for financial models that scale: the inputs sit in one block, every Finsyte function has a cell of its own, and totals use AGGREGATE.

The parameter block

Book and the four required parameters, Subsidiary, Fiscal Year, Period Number and Period Range, sit above each period column. Department, Class, Location and Item filters, along with Entity, Transaction Type, Posted, Currency and custom segments, follow as optional rows. Those rows come from your choices in Template Parameters. Each balance reads from the cells above it, so change one and that column’s formulas recalculate automatically.

For month-end review, set Period Range to YTD. Balance sheet accounts then show their balance as of period end, and income statement accounts show activity for the year to date.

The account tree

Load Accounts brings in every active account in your chart of accounts, covering both the balance sheet and the income statement. Statistical accounts and the Cash at Beginning of Period and Net Income system accounts are left out.

Comparative Grouped Trial Balance organizes those accounts by your NetSuite chart of accounts, with Excel outline levels you can collapse. Comparative Trial Balance lists the same accounts flat. Pick the top-level (Consolidated) subsidiary in column D before you load, and the list covers every account in every entity. An entity’s column shows 0 for any account it can’t use, and those rows can be ignored.

Two period columns, one function

Every balance is one call to GLAccountBalance. Here is one account’s balance in column D, with every optional filter row included:

=FSN.GLAccountBalance(D$5,D$6,D$7,D$8,$B22,D$9,D$10,D$11,D$12,D$13,D$14,D$15,D$16,D$17)

Because the parameters are row-locked and the account is column-locked, column E holds the same pattern and reads its own parameter cells. Expect different row numbers on your sheet: they follow your chart of accounts and the filters you include.

Column E’s year is the one input you fill yourself, since it starts empty. Its other parameters follow column D through formulas such as =$D7. For month-end against the prior month-end, enter the same year in both columns and overwrite column E’s Period Number with the earlier period.

Variance and % Variance

Variance is column D minus column E for each account:

=D20-E20

% Variance is the change over the size of column E’s balance, so credit balances keep the same sign as Variance. An account that is new in column D shows n/m:

=IF(E20=0,IF(D20=0,0,"n/m"),(D20-E20)/ABS(E20))

Together they show where the ledger changed between your two periods. Right-click any balance › Drill Down, and a new sheet splits it by subsidiary, period, entity or transaction lines, using the cell’s own parameters.

Totals and signs

Group totals in the grouped version sum the accounts above them with AGGREGATE:

=AGGREGATE(9,0,D$20:D$29)

AGGREGATE ignores nested subtotals, so no group is counted twice. A final Total row sums every account in the column.

Account signs aren’t reversed on a trial balance. The one exception is the Cumulative Translation Adjustment block, whose cells carry a minus in front of the call, =-FSN.GLAccountBalance(…), exactly what right-click › Switch Sign would add.

Change what’s compared

Once the accounts load, the parameter rows sit collapsed. Click the + in the outline bar to show them, then type over a column E cell:

• Period Number, for this month-end against last month-end
• Subsidiary, to set two entities’ ledgers side by side
• Currency, to show both columns in the top-level parent’s currency when the entities’ currencies differ
• Book, to put a secondary accounting book next to the primary one
• Location or a custom segment, to review one slice at a time

A segment works when your balance sheet postings carry it, including custom segments such as Activity Code, and you can combine several in one column. Each column’s formulas recalculate automatically, and the variances follow. For a single-period trial balance, see the NetSuite trial balance template.