NetSuite users can create income statements in NetSuite using NetSuite reports. Our customers have asked us how they can create a more customizable report using Microsoft Excel.
There are numerous ways to get your income statement detail into Excel via a NetSuite saved search. The benefits of each approach are summarized below, and each has a dedicated section with specific details on how to create the searches and build financials off them in Excel.
G/L Search
Because the G/L summary approach exports G/L summary data only (ie not individual transactions), it is extremely fast and can be run on demand in just a few seconds. This is the recommended approach. Typical use cases would involve building board ready financials or quickly cross checking that month end entries had the desired effect.
Transaction Detail Search
The transaction detail approach exports all transactions for the periods in question. Because of this, users can drill down from a financial pivot table into individual transactions. We advise caution when taking this approach and that you consider the periods you want to cover as well as the number of transactions that would be included.
How To
In this section we'll cover both saved search methods and show you how to get your search results in Excel and format your first Income Statement.
Naming Conventions
While not required, we recommend you use the following naming conventions.
Prefix your search title with CEAN (short for CloudExtend Excel Analytics), add a pipe character (|) and then a description of your search.
Example: CEAN | 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
Approach 1: Building an Income Statement with G/L Search (Recommended)
Create two NetSuite saved searches with the following Criteria and Results Definitions. Note that the second account search is optional.
Transaction Saved Search (Required)
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 periods |
Search Results
Field | Summary Type | Comment |
Account Type | Group |
|
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 |
Account: Internal ID | Group | Use Custom Label of GLAccountID |
Account | Group |
|
Amount | Sum |
|
G/L Account Saved Search
If you only plan to build your income statement with pivot tables, you can skip this step, however, note that our examples here use it. By exporting your G/L accounts you will be able to combine all the fields (description, etc.) from your account data with your Transaction Search Results into an Excel data model which can improve speed and add more slicing capabilities.
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 CloudExtend
Once you've saved your searches add them to CloudExtend. 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 CloudExtend 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 CloudExtend. 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.
Get the path to your source file
From your source file, choose File->Info->Copy Path
Create a new Excel file and save it in your desired location.
Connect your new file to the source file.
Select Get Data > From Other Source > From Web (you might be tempted to choose From File, but don't).
Next, paste the data you copied from File > Info >Get Path in the previous section. This is the link to your Excel File on SharePoint.
❗️ some weird reason you also need to remove the ?web=1 from the URL (let's just call it a Microsoft quirk). If you fail to remove this you will get an error.
You will then be presented with a list of table names in your Excel source workbook as well as worksheet names. Choose the table name, not the worksheet name.
Once you’ve selected the tables we recommend choosing ‘Transform Data’. This will give you an opportunity to remove unnecessary columns, add new calculated columns, and perform any data cleansing. The beauty of this is that the steps will be remembered each time the data is refreshed. If you forgot something you can always open the Query Editor in Excel and modify your steps. To complete the import you’ll want to be sure that you close and load to the data model.
On the next step
Format your Income Statement
Now that your data is in Excel, you're ready to build your model. Click here to skip ahead to the section on building your model (from here, the instructions are the same regardless of the saved search approach you took)
Watch the video and refer back to the article for the formulas used.
Building Financials With Transaction Details
The first thing we need to do is create a NetSuite saved search that gives us the details we'll need in Excel to customize a financial report. There are many ways to do this and we offer this method as a starting point.
Next, you'll want to decide if you want just summary data or if you want the ability to drill down into the details. This document assumes you'll want to drill into the details.
Overview
Option 1 - use a Lambda Function
Option 2 - use SUMIFS instead of Lambda
Create your NetSuite Saved Search
Start with a transaction search (Transactions->Management->Search Transactions, or whatever path works for you in NetSuite).
Add the following criteria
Posting
= trueAccount Type
is any of Cost of Goods Sold, Income, Expense, Other Income, Other ExpenseDate
(select the range desired, ie on or after 1/1/2020)If you have large volumes of transactions you may want to consider using multiple searches, ie a separate search for each month, and then using CloudExtend scheduling to run them during off hours. Use Excel Power Query to bring each of the months together into one Workbook for analysis. We wrote a separate article on how to do this, check it out here.
Add the following search results
We recommend naming the saved searches you'll be using so they are easy to identify as searches used in CloudExtend Excel Analytics for NetSuite. Here at CloudExtend, we add a prefix of CEAN | to each of our saved searches.
Type
(allows you to report on different transaction types in Excel)Internal ID
(allows you to link related records together if desired)Date
Subsidiary
(ignore if you're not a NetSuite OneWorld customer)Document Number
Account
(not required if you're using detail from the account record last few fields)but does not hurt to add itClass / Department, etc.
(add any other fields you may wish to report on)Amount
Account: Internal ID
Account : Account Type
Account : Number
Account : Name
Formula (Text) (be sure to add the ' as seen below)
'HTTPS://tstdrv2320150.app.netsuite.com/app/accounting/transactions/transaction.nl?id=' ||{internalid}
👉 To learn about other methods of adding hyperlinks see this article.
Add your saved search to CloudExtend
Once you've saved your search add it to CloudExtend. If you need help with that process refer to this article
Determine the type of income statement you want to create
Quick'n dirty with a pivot table
Your saved search already provides enough detail to create a basic income statement using a pivot table.
Add Amount to Values
Add Account Type and Account to the rows
Add Date as a Column (this should also automatically group by Year/Quarter/Month)
Add any slicers (such as subsidiary, class, department)
Sophisticated Board Ready Income Statement
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 re-usable income statement. You can add other metrics such as budgets and variances.
Formulas needed
Add a cell for year and give the cell a reference name such as
IncStmtYearTo get the value for the 1st month of the year use the formula
=TEXT(DATE(IncStmtYear,1,1),"mmm yyyy")To get the value for the second month use the formula
=TEXT(EDATE(E6,1), "mmm yyyy")
⚠️ Drag this formula to the right as desired to fill in the remaining monthsYou'll enter your G/L account numbers in column C (or as desired). After you enter the account number the next column (D) in our case is a lookup to another worksheet that has a list of your G/L account numbers and names. Note the G/L lookup data can also easily be imported with CloudExtend allowing you to always keep it up to date.
=XLOOKUP(C7,customsearch_cean_gl_accounts[Account Number],customsearch_cean_gl_accounts[Description])is your table range that contains your list of GL accounts.
where customsearch_cean_gl_accountsThe intersection of your date column and G/L account row is a summation of your main data table where the G/L and date intersect, ie the formula sums all the data where G/L account is 4000 and the date is from Jan 1, 2000 to Jan 31, 2000.
If you are on a newer build of Excel we recommend creating your own function using an Excel Lambda function. Not sure what a Lamdba function is? Check out this article (and video) from Leila Gharani.
Option 1 - use a Lambda Function
If you're familiar with Lambda's you will find that the formula below is much easier on the eyes. Note customsearch_cean_incomestmt_detail refers to the table created by your saved search and should be changed as needed.
🔵 Create the LAMBDA function below
=LAMBDA(Account,Month,SUMIFS(customsearch_cean_incomestmt_detail[Amount],customsearch_cean_incomestmt_detail[Number],Account,customsearch_cean_incomestmt_detail[Date],">="&Month,customsearch_cean_incomestmt_detail[Date],"<="&EOMONTH(Month,0)))
🔵 Then use the formula below at the intersection of your G/L and date.
=IncStmtMonthAmount($C7,E$6). Isn't this much easier to read than the SUMIFS below?
Option 2 - use SUMIFS instead of Lambda
Not familiar with LAMBDA's or just don't want to use them? No worries. You can do the same with a standard SUMIFS formula. Use the formula below at the intersection of your G/L and date.
Note customsearch_cean_incomestmt_detail refers to the table created by your saved search and should be changed as needed.
🚨 IMPORTANT, to extend this formula do not drag it to your other columns. Copy the formula, select the desired columns, and then paste it.
=SUMIFS(customsearch_cean_incomestmt_detail[Amount],customsearch_cean_incomestmt_detail[Number],$C7,customsearch_cean_incomestmt_detail[Date],">="&E$6,customsearch_cean_incomestmt_detail[Date],"<="&EOMONTH(E$6,0))
Go ahead and format your income statement as desired. We recommend removing the gridlines, adding subtotals, using borders as needed, etc.