NetSuite Comparative Income Statement in Excel

Behind the comparative P&L are two columns of the same Finsyte function and a layer of plain Excel arithmetic on top. It’s built on the five rules for financial models that scale: parameters are kept apart from calculations, every Finsyte function sits in its own cell, and every total is an AGGREGATE.

The parameter block

Four required cells head each period column (Subsidiary, Fiscal Year, Period Number and Period Range) with Book beneath them. Optional rows narrow a column by department, class, location, item, entity, transaction type, currency, posted status or a custom segment. Template Parameters sets which of those rows the sheet includes. Every amount reads from the parameters above it, so change one and the formulas in its column recalculate automatically.

The account tree

Load Accounts pulls your income and expense accounts from NetSuite and lays them out the way a P&L reads: Ordinary Income/Expense, with Income, Cost of Sales, Gross Profit and Expense, then Net Ordinary Income, Other Income and Expenses, Net Other Income and Net Income. A Net Income (Check) row closes the statement: net income pulled straight from NetSuite, which should match the Net Income line above it. Each level of your hierarchy is an Excel outline group, so you can fold the sheet to gross profit and operating expense or open every expense account.

Loading from the top-level (Consolidated) subsidiary brings in every income and expense account, including ones only some entities use. In an entity’s own column, those lines show 0.

Two period columns, one function

Every amount is one call to GLAccountBalance. With all the optional filter rows turned on, one account’s cell 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 dollar signs do the work. D$5 keeps the row fixed and $B22 keeps the column fixed, so in column E the same formula reads E$5 onward for the same account. Your sheet’s row numbers will differ, since they depend on your accounts and which filter rows you turned on.

You pick both fiscal years, since column E’s starts blank. The rest of column E follows column D through formulas like =$D7, so one Period Number drives both columns. For one month against the same month a year before, set Period Range to PTD. For two years to date, use YTD.

Variance and % Variance

Variance is column D minus column E:

=D20-E20

% Variance expresses it as a share of the size of column E, so a loss turning into a profit reads as an improvement. It shows n/m if column E is zero:

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

Read them down the statement to see where margin went: whether revenue grew faster than cost of sales, and which expense lines outpaced revenue. To explain a line, right-click the amount and choose Drill Down. A new sheet breaks it down by department, class, location, entity, item or transaction line, using that cell’s parameters.

Subtotals, calculated rows and signs

Group totals are AGGREGATE formulas over the rows above them:

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

Calculated rows repeat the subtotals they’re built from inside their own formula. Gross Profit, Income less Cost of Sales, looks like this:

=(AGGREGATE(9,0,D$13:D$18)-AGGREGATE(9,0,D$21:D$25))

AGGREGATE ignores nested subtotals, so no total counts another twice. Income and Other Income are sign-reversed so revenue reads as a positive number. Behind a reversed line is the familiar call with a minus in front, =-FSN.GLAccountBalance(…), and right-click › Switch Sign makes the same change on any account you choose.

Change what’s compared

After Load Accounts the parameter rows fold away. Expand them with the outline button and overwrite a cell in column E:

• Department, to set two departments’ costs side by side for the same period
• Subsidiary, to compare margins across entities
• Period Number, to compare this month with last month in the same year
• Class, Location or a custom segment, for product lines or regions

Any segment works, including custom segments such as Activity Code, and you can combine several in one column. Each column’s formulas recalculate automatically. For a single-period P&L by month, department or class, see the NetSuite income statement template.