NetSuite Comparative Cash Flow Statement in Excel

The comparative cash flow uses the same pieces as the other Finsyte statements: a parameter block, one function call per account, and Excel arithmetic on top. What makes it a cash flow is how the accounts are grouped and signed. The layout applies the five rules for financial models that scale: inputs live apart from calculations, each Finsyte function has its own cell, and totals use AGGREGATE.

The parameter block

Each period column starts with Subsidiary, Fiscal Year and Period Number, then Period Range and Book. Optional rows follow for Entity, Currency, Transaction Type and Posted, plus the segment fields: Department, Class, Location, Item and your custom segments. Pick the rows you want in Template Parameters. Change any cell and the formulas below it recalculate automatically.

On this template, Period Range offers RTD and PTD only. RTD covers the fiscal year from its first period through the one you pick. PTD covers that single period. On balance sheet accounts, both return the change over that span rather than the balance, which is what a cash flow is built from.

The account tree

Load Accounts builds the statement from account types, following the layout of NetSuite’s Cash Flow report:

• Operating Activities: Net Income, then adjustments for Accounts Receivable, Unbilled Receivables, Inventory Asset, Other Current Asset, Accounts Payable, Payroll Liabilities, Sales Tax Payable and Other Current Liabilities
• Investing Activities: Fixed Asset and Other Asset accounts
• Financing Activities: Long Term Liabilities and Equity accounts

Net Change in Cash for Period, Cash at Beginning of Period, Effect of Exchange Rate on Cash and Cash at End of Period close the statement. Within each type, your accounts keep their NetSuite hierarchy as Excel outline groups.

Accumulated depreciation is usually a Fixed Asset account in NetSuite, so depreciation sits in investing activities, netted against capital spending. To show it as a non-cash add-back under operating activities, move those rows up.

Consolidating? Choose the top-level (Consolidated) subsidiary in column D first, and Load Accounts picks up accounts from every entity. Where an entity doesn’t use one, its line reads 0.

Two period columns, one function

Each line is a call to GLAccountBalance. Almost every line on a cash flow is sign-reversed, so with every optional filter row turned on, a typical account 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)

Copy it one column right and it becomes a column E formula: the row-locked parameter references shift to E, while $B22 still points at the same account. The rows shown here are examples; yours shift with your chart of accounts and filter rows.

Both years are your choice: column E’s Fiscal Year is empty until you set it. Its Period Range follows column D’s, and so do its other parameters, through formulas such as =$D5, until you overwrite them. With RTD and your last period, the two columns compare full fiscal years. With PTD, each column covers one period of the year you gave it.

Variance and % Variance

Variance subtracts column E from column D:

=D20-E20

% Variance turns it into a percentage of the size of column E, so a line that used less cash than before reads as a positive change. Where column E is 0, it shows n/m:

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

Read down the variance column to see whether operations produced more or less cash than in column E’s period, and whether investing or financing made up the difference. Right-click any line and choose Drill Down to see the activity behind it by period, entity or transaction line.

Subtotals and signs

Each activity’s subtotals are AGGREGATE formulas over the lines above them:

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

AGGREGATE ignores nested subtotals, so no total is counted twice. Every account except Cash at Beginning of Period is sign-reversed, so an increase in receivables shows as a negative number: a use of cash. The leading minus is what right-click › Switch Sign adds.

Change what’s compared

Load Accounts folds the parameter rows away. Open them from the outline bar and change a cell in column E:

• Period Number, to set one month’s PTD cash flow beside the month before
• Subsidiary, to compare how two entities generate and use cash
• Currency, to show both columns in the top-level parent’s currency when the entities’ currencies differ
• Location or a custom segment, for a narrower slice of the ledger

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 Variance follows. For a single-period statement, see the NetSuite cash flow statement template.