Please read our Privacy Statement and Standard License Terms. By downloading any of the documents below, you are agreeing to our Standard License Terms.
Create a Year-to-Date Formula in Excel for NetSuite
In this article, we'll guide you through the frequently asked question of how to create a Year-to-Date Formula in Excel Reporting with these four topics:
How to create a single period balance in Excel
Here are the steps:
Launch a new query.
Choose the company or subsidiary you want to report on and select a range of accounts.
Specify your date range (here we are using January 2023) as the period.
If you are looking to report on one period, you only need to populate the ‘From Period’ field.
Click ‘OK’ then generate a Profit and Loss (P&L) balance for all the GL codes you highlighted for your selected period.
You should now have your single period balance.
How to create a year-to-date balance in Excel
Copy the single period balance formula from the previous steps.
Now, adjust the range to reflect a year-to-date balance by defining a start and end period. (For this example, the start period is January 2023 and the end period is April 2023.)
Click OK, this is now your year-to-date balance.
You should now have a year-to-date formula. We recommend using formulas to populate the heading fields to avoid typos or inconsistencies. In our standard demo, we show how to build out periods effectively, which you can watch here.
How to create a balance sheet balance in Excel
Before we delve into balance sheet balances, it is important to understand that NetSuite stores balance sheet data as monthly deltas (movement) and not cumulative balances.
To calculate a balance sheet balance: Specify data from the beginning of time to the reporting period that you want to report against.
This is now the balance for all four codes. To get the balance sheet codes you need to: Change the codes to focus on the balance sheet-specific codes. (Change the P&L codes such as "4" to balance sheet codes like “1”).
How to create a multi-period balance in Excel
This balance allows you to run reports for the likes of ad hoc or specific reporting periods.
Copy the year-to-date calculation and change the periods to a new range (e.g. May to July 2023).
Aggregate your custom blocks of time such as:
Quarterly reporting
6-month reporting
Multiple financial year reporting (calendar year vs. fiscal year).
By using multi-period balances in this way, it allows you to experiment with periods to suit your specific financial reporting needs.