There are numerous ways to get your income statement detail into Excel via a NetSuite saved search. This article covers the G/L search method which is the fastest and most popular.
Watch the video or follow the steps below!
See this article with links to creating balance sheets and other methods of creating income statements.
Housekeeping
Naming Conventions
While not required, we recommend you use the following naming conventions.
Prefix your search title with XLANS (short for ExtendInsights Analytics), add a pipe character (|) and then a description of your search.
Example: XLANS | Income StatementYour search ID will become the name of the table in Excel that holds your search results. We recommend using an _ followed by your search name.
Example: _cean_income_stmt
Building Your Saved Search
We recommend building two searches, one to export your G/L transaction summaries, and the other a list of G/L accounts and joining them together in the Excel data model based on the internal ID of the G/L account. This will increase the efficiency of your search and will also set you up for future success in slicing/dicing or working in Power BI.
🖥️ If you're on a Mac (Excel Power Query is limited but Microsoft has said feature parity is expected in the future) then you will want to expose your desired G/L account fields (number, description, etc.) on the primary transaction search results and build your pivot table off that search rather than joining the tables later.
Transaction Saved Search (Required)
Start with a NetSuite transaction search with the following criteria and results.
Search Criteria
Filter | Description | Comments |
Posting | = True |
|
Account Type | any of Cost of Goods Sold, Income, Expense, Other Income, Other Expense |
|
Date | Set your range such as on/after Jan 1, 2020 | Because the amount of data exported in the G/L summary search is minimal consider broadening your data range to make it easy to report on multiple years / periods |
Search Results
Field | Summary Type | Comment |
Account: Internal ID | Group | Use Custom Label of GLAccountID |
|
| If you're on a Mac or choose not to use Excel Power Query then add G/L account fields desired here (account number, name, etc.) here and group them. Otherwise ignore this. |
Period | Group | Grouping on period will neatly summarize your results for each period |
Subsidiary | Group | Subsidiary, Department and Class are optional fields, Consider adding them (and others) to further slice your financials. |
Department | Group | ditto above |
Class | Group | ditto above |
Amount | Sum |
|
G/L Account Saved Search
As noted earlier this search is optional but recommended. This will increase the efficiency of your search and will also set you up for future success when working in Power BI or slicing your data. It will also be used to lookup Account Names and Descriptions in the formatted Income Statement you build.
🖥️ | Should you choose to skip this step then you should add these fields to the top of the Transaction Saved Search above and group them.
Search Criteria
Filter | Description | Comments |
There is no filter required |
|
|
Search Results
Field | Summary Type | Comment |
InternalID |
| Use Custom Label of GLAccountID |
Number |
| Use Custom Label of Account Number |
Name |
|
|
Account Type |
|
|
Description |
|
|
Add your search results to Excel with ExtendInsights
Once you've saved your searches add them both to Excel with ExtendInsights. If you need help with that process refer to this article.
Save your file to OneDrive for Business or SharePoint
By saving your Excel file to OneDrive for Business or SharePoint ExtendInsights will be able to update your data should you choose to enable scheduling now or at a future date. We recommend that you treat this as a source file and connect to it via Excel Power Query from another workbook.
Why use a source file approach? Once your source file is created it only needs to be opened by you if you need to perform a manual refresh via ExtendInsights. Scheduled jobs can run with no issues because the file is never in use by someone else.
Connect to your source file with Excel Power Query
If you're not familiar with Power Query it's Excel (and Power BI's) best kept secret. There are plenty of resources on the web where you can learn more about it. We also created this brief article for you to learn how to connect your source file with Power Query.
Build Your Pivot Table
The first step is to pivot your data. This may be good enough for internal consumption but will also be used to feed the more formatted income statement that will be created in a subsequent step.
Pivot Table
Because we added our two tables to the data model we can connect them together by a common key field and then pivot the data model as if it was one table. It's like VLOOKUP on steroids.
The first step is to link the two files together by a common key field, in this case the internal ID of the G/L account which we named GLAccountID.
Click the Power Pivot menu in Excel, and then select "Manage Data Model".
In the data model tab click Diagram View to show the 2 tables. You may need to move/expand them to see all the fields. Then, connect the two tables by clicking on GLAccountID in one and dragging the line to the second table and dropping it on top of the GLAccountID in the second table. Go ahead and close the data model tab.
Next, we'll build our pivot table by selecting Insert-Pivot Table-From Data Model. It's important that you choose From Data Model as this will include both tables. To see this option, select the arrow under Pivot Table.
This is where Power Query really shines. By joining the tables you've eliminated the need for VLOOKUPS and can now add data from both tables to your pivot table.
Add Account Type, Number, and Description to the rows.
Add Period to the columns (you should see Excel auto group them when added)
Add Amount to the values field.
From here you can start to make your pivot table more readable by
Changing it to tabular format (Design->Report Layout->Tabular)
Moving the Account types so Income Accounts are at the top, COGS in the middle, and Expense at the bottom. This is best done by clicking on any Account Type field such as Income and then choosing Pivot Table Analyze from the ribbon, and then selecting Collapse Field.
Remove grand totals
Remove the + and - indicators on the grouped fields (on the Ribbon, under PivotTable Tools tab, click the Analyze tab. and then click the +/- Buttons command, to toggle the buttons on or off).
Next, add some slicers and format them. In our example we've added slicers for Year, Subsidiary, Account Type, and Department. To add slicers, click anywhere in the pivot table and select Insert->Slicer. from the Pivot Table Analyze tab.
If you're comfortable using a pivot table and don't need board ready formats, then you're done. Remember to put your source data file on a schedule or rerun the searches manually before updating the pivots. To update the pivots, choose Data-Refresh All and this will refresh data from your source file and refresh your pivots in just a second or so. You can also right click your queries, choose Properties, and put them on a schedule as well.
Beyond Pivot Tables
Excel is a blank canvas, and you have unlimited flexibility to design your income statement. The steps below are meant to give you a head start on crafting a custom formatted re-usable income statement sourced from NetSuite that can be updated in seconds. You can add other metrics such as budgets and variances as needed.
Build your formatted Income Statement
In our example we're going to build an income statement that shows income and expenses by month.
Modify your Pivot Table
Because we are showing monthly data, we want to change the pivot table so only months appear in the columns.
Create Your Formatted Income Statement
Insert a new worksheet in the same workbook as your pivot table and give it a name such as Income Statement.
Leave a little room on the top and sides of your worksheet and create a shell to hold your data.
Add months across the rows (let's add January in F4 and then drag out to cell Q4 to get Jan-Dec
Add a column called Total after December and insert a formula to sum all the months (this will be 0 until your data populates).
Add the text "Account Number" in C4 and then "Account Name" in D4.
Add a known G/L account number in C5 (in our example we added 4000). We'll use this to test our formula in the next step.
Build your formula in cell F5 to extract the value for the intersection of January and account 4000 from your pivot table.
The below formula works when F$4 is the 1st month (January) and $C5 is the 1st G/L Account Number (4000). Be sure to use the relative references so you can drag your formulas. The IFERROR is necessary so that you can substitute 0 for a value if it does not exist in the pivot table. Note you may need to substitute the names of your tables in the formula below.
=IFERROR(GETPIVOTDATA("[Measures].[Sum of Amount]",PivotInc!$A$1,"[customsearch_cean_income_stmt_header_ex].[Period (Month)]","[customsearch_cean_income_stmt_header_ex].[Period (Month)].&["&F$4&"]","[customsearch_cean_gl_accounts_ex].[Account Number]","[customsearch_cean_gl_accounts_ex].[Account Number].&["&$C5&"]"), 0)
Here's a link to a resource on YouTube with more information on the GETPIVOTDATA command for tables based on the Excel data model which behaves differently than a normal GETPIVOTDATA formula.Drag your formulas down and across as desired and add a few more account numbers in Column F and cross check your Pivot Table to ensure that the formula is working properly. Don't worry about total rows and formatting yet, we'll get there.
At this point your sheet should look something like this.
Now we need to look up the Account Name based on the Account Number. You could do a VLOOKUP against the pivot table using cell references but that's not going to be dynamic. Instead, we're going to load our G/L search results as a table (remember before we just created the connection) and use a simple XLOOKUP.
Open up your Queries, right click on your G/L account query, and then choose Load as a Table in a new Worksheet (keep it in the data model).
Now we've got a table with all our G/L accounts, and we just need to add a formula in our Income Statement to pull in the Account Name based on the Account Number.
Use the formula below to lookup your Account Name from your list of G/L accounts (assuming cell C5 is the first field under Account Number).
=XLOOKUP(C5,customsearch_cean_gl_accounts_ex[Account Number],customsearch_cean_gl_accounts_ex[Name],"No Matching Account")
Once you drag your formula down your account names will then update any time you add/change the G/L account number.
Format your income statement
Now that you see how to add your accounts and formulas you can start moving things around and formatting them. These are just a few suggestions; you can probably do better.Turn off gridlines (View->Uncheck Gridlines checkbox)
Bold and center your column headers (row 4, ie Account Number through Months and Total)
Right align the Account Name values
Insert a row at the end of your revenue accounts and use formulas to sum each month and the totals
Insert a thick bottom border in the last row of your revenue row.
Do the same thing and segregate your Cost of Goods Sold and Expenses
Add a Gross Margin Row subtracting Total Revenue from Total Cost of Goods Sold and give it a double border
Total up your expenses in a new row using borders, etc.
Add a row to calculate Net Profit (double border underline) and subtract total expenses from Gross Margin.
Add your slicers by selecting them in your pivot table and then choosing "Cut" (not copy) and "Paste" them onto your formatted Income Statement sheet.
Format your slicers and place them as desired.
Add your company logo
Your final product should look something like this.
Keep Your Numbers Up to Date
You've built your model, it looks great, but now you want to refresh your data.
Source data
Consider putting the searches in your source data file on a schedule so they run in the background and are continuously updated. If you prefer to manually update your data, you can open the source file, load ExtendInsights, and select Refresh All.
Income Statement
Your income statement is connected to your source file. To refresh the data you have several options.
Choose Data-Refresh All.
Set your query to refresh when the file opens or very X number of minutes. Open Queries and Connections and right click on the Query and select Properties.
