In this blog, we'll guide you through the frequently asked question of how to create a Year-to-Date Formula in Excel Reporting. These steps are demonstrated by Solution 7’s Co-Founder, Simon Miles, if you'd like to view this demonstration, you can watch the video here.
For more quick guides, you can explore the full playlist here: Ask the Expert Webinar Series.
In this blog, we will cover:
How to create a single period balance in Excel
To answer the initial question of “How to create a Year-to-Date formula”, Simon firstly demonstrates how to do a single period balance. 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 multi-period balances 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.
For the full walkthrough watch, Simon, the co-founder of Solution 7, demonstrate how to create a Year-to-Date Formula for Excel, in a step-by-step guide.
To learn more about Solution 7 for NetSuite reporting in Excel, book a demo or start a free trial.
Comments