NetSuite Comparative Balance Sheet in Excel

The comparative balance sheet is a grid of ordinary Excel formulas. Two columns call the same Finsyte function with different parameters, and everything else is arithmetic you can read in the cell. It follows the five rules for financial models that scale: inputs sit apart from calculations, each Finsyte function has a cell to itself, and totals use AGGREGATE.

The parameter block

Subsidiary, Fiscal Year, Period Number, Period Range and Book sit at the top of each balance column. Below them come the optional filters: Item, Class, Department, Location, Entity, Transaction Type, Posted, Currency and your custom segments. You choose which of them appear in Template Parameters. Every balance in a column reads from these cells, so change one and the formulas in that column recalculate automatically.

The account tree

Load Accounts reads your hierarchy from NetSuite: Assets, then Liabilities & Equity, down to each account and subaccount. Every level becomes an Excel outline group, so you can collapse the sheet to its main sections for a board pack or open it to every bank account for the close.

For a consolidated balance sheet, or to compare subsidiaries later, set column D to the top-level (Consolidated) subsidiary before you load, so every account comes in. An account a subsidiary can’t use returns 0.

Two period columns, one function

Each balance is a single call to GLAccountBalance. With every optional filter row turned on, the cell for one account in column D reads:

=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)

The parameter references lock the row and the account reference locks the column, so the same formula in column E reads column E’s parameters for the same account. Row numbers in these examples vary with your chart of accounts and the filter rows you include.

Column E’s Fiscal Year starts blank, and you pick both years. Its other parameters are formulas such as =$D5, so they follow column D until you overwrite them. For year-end against year-end, set Period Number to your last period and Period Range to YTD, which returns each balance as of period end. For this month-end against last, give both columns the same year and type the earlier period into column E.

Variance and % Variance

Variance subtracts column E from column D on the same row:

=D20-E20

% Variance divides that change by the size of column E, so it always carries the same sign as Variance. It shows n/m (not meaningful) when column E is 0 but column D isn’t:

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

On a balance sheet, these two columns answer the working capital questions: how much receivables grew, how far payables fell, how much cash the year added. An account that is new this year reads n/m rather than a misleading 0%.

To see what’s behind a change, right-click the balance in either column and choose Drill Down. A new sheet breaks it down by period, subsidiary, entity or transaction line, within that cell’s parameters.

Subtotals and signs

Each group total is an AGGREGATE over the rows above it:

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

AGGREGATE skips nested subtotals inside its range, so a section total never counts the totals beneath it twice. Collapse a group and its header row shows the total in their place.

Accounts under Liabilities & Equity are sign-reversed so the sheet totals like a printed balance sheet. A reversed account is the same call with a leading minus, =-FSN.GLAccountBalance(…), which is what right-click › Switch Sign does. Equity also gets Retained Earnings and Net Income rows, followed by a Cumulative Translation Adjustment block.

Change what’s compared

The parameter rows collapse after Load Accounts. Expand them with the outline button, then overwrite one of column E’s cells:

• Subsidiary, to set two entities side by side as of the same date
• Currency, to show both columns in the top-level parent’s currency when the entities’ currencies differ
• Book, to compare your primary accounting book with a secondary one
• Location or a custom segment, to compare two parts of the business

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. The formulas recalculate automatically, and Variance follows. For a single balance sheet by month or by subsidiary, see the NetSuite balance sheet template.