For accountants and finance professionals, having a complete overview of an organization's financial data is crucial. Typically, NetSuite offers built-in reports for generating a trial balance. However, there are some good reasons you might want to consider creating your own trial balance using saved searches instead of using these standard reports.
Why Use Saved Searches for a Trial Balance?
NetSuite’s native reports are highly capable but often lack the customization and flexibility needed for specialized financial analysis. Saved searches can provide a more tailored approach with advantages such as:
Greater Flexibility: Saved searches let you create fully customized views that might be challenging to generate using standard reports. You can add complex filters, calculations, and even include customized fields that are unique to your financial operations.
Customized Data Visibility: Saved searches allow for tailored data presentation, letting you filter and organize information based on your unique needs. This can be particularly useful for identifying key trends or focusing on specific areas of your financial data that may not be as easy to extract from standard reports
Custom Formulas: You can add and manipulate data using custom SQL-like formulas. This allows you to create dynamic calculations, breakdowns, and groupings, giving you the flexibility needed for unique financial requirements.
Setting Criteria for the Trial Balance Saved Search
To create a trial balance using a saved search, start by creating a search against transactions and then set the criteria to ensure you're only pulling relevant transactions. The criteria should be:
Posting: Set to “True” so that only posted transactions are included. This ensures that you’re seeing final entries, not drafts or errors.
Account: Internal ID: Ensure this is not null. This guarantees that every record included has an account reference, which is critical for a complete trial balance.
Using Formulas for Results
To build a functional and insightful trial balance saved search, we need to create customized columns using formulas. Let’s break down each formula and understand its purpose.
Depending on your particular NetSuite configuration you may need to adjust the Consolidated Exchange Rate.
Account Classification
CASE WHEN {accounttype} IN ('Bank', 'Accounts Receivable', 'Accounts Payable', 'Other Current Asset', 'Fixed Asset', 'Deferred Expense', 'Other Current Liability', 'Deferred Revenue', 'Equity', 'Non Posting') THEN 'Balance Sheet' WHEN {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') THEN 'Income Statement' ELSE 'Balance Sheet' END
This formula classifies each account as either belonging to the Balance Sheet or Income Statement. It’s helpful for differentiating accounts when reviewing the trial balance, providing a clear categorization of assets, liabilities, equity, income, and expenses.
Account Number
CASE WHEN TRUNC({accountingperiod.enddate}, 'Y') < TRUNC(SYSDATE, 'Y') THEN CASE WHEN {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') THEN 'None' ELSE {account.number} END ELSE {account.number} END
Optional field (use if you need the Account Number displayed or would like to use it as a key field for your data model if exporting to other systems such as Power BI). This formula handles how account numbers are displayed. If the accounting period ended in a prior year and the account is an income or expense account, it shows “None” for the account number. Otherwise, it displays the account number as usual. This helps differentiate prior-year balances for revenue/expense accounts that have been closed out.
Account Name
CASE WHEN TRUNC({accountingperiod.enddate}, 'Y') < TRUNC(SYSDATE, 'Y') THEN CASE WHEN {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') THEN 'Retained Earnings' ELSE {account.name} END ELSE {account.name} END
This formula shows how account names are displayed. For income and expense accounts from prior years, it replaces the account name with “Retained Earnings”. This reflects the fact that net income from prior periods is typically closed to retained earnings.
Prior Year -2
CASE WHEN EXTRACT(YEAR FROM {accountingperiod.enddate}) < EXTRACT(YEAR FROM SYSDATE)-2 THEN NVL({debitamount}, 0) - NVL({creditamount}, 0) ELSE 0 END
This formula calculates the net balance for each account for periods older than two years. It subtracts the credit amount from the debit amount to get the net balance, ensuring older years’ values are properly represented.
Prior Year -1
CASE WHEN EXTRACT(YEAR FROM {accountingperiod.enddate}) < EXTRACT(YEAR FROM SYSDATE)-1 THEN NVL({debitamount}, 0) - NVL({creditamount}, 0) ELSE 0 END
Similar to the previous formula, this calculates the net balance for the prior year minus one. It’s useful to see the cumulative balance for accounts up until the previous year.
Prior Year
CASE WHEN EXTRACT(YEAR FROM {accountingperiod.enddate}) < EXTRACT(YEAR FROM SYSDATE)-0 THEN NVL({debitamount}, 0) - NVL({creditamount}, 0) ELSE 0 END
This formula calculates the net balance for the prior year, providing insights into how accounts stood at the end of the last full year.
This Year
NVL({debitamount}, 0) - NVL({creditamount}, 0)
This formula calculates the current year’s net balance for each account. It simply subtracts the credit amount from the debit amount, providing a real-time view of account balances.
Final Thoughts
Creating a trial balance using a saved search in NetSuite gives you flexibility and real-time data access that standard reports may not offer.
By using formulas like the ones above, you can customize the information to fit your organization’s unique needs, making it easier to generate valuable insights for your financial analysis.
Consider using pivot tables or lookup formulas to further refine your data to meet your organization's unique reporting needs.
ExtendInsights can help automate this process, making it easier to generate a trial balance and get your data into a more readable format. With ExtendInsights, you can connect directly to your data sources, apply custom formulas, and create a clean, organized view of your financial data.