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.

  1. 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 Statement

  2. Your 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
Note this is a field from the linked Accounts table and can be found near the end of all fields in your drop down.

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.

  1. Get the path to your source file

    1. From your source file, choose File->Info->Copy Path

  2. Create a new Excel file and save it in your desired location.

  3. Connect your new file to the source file.

    1. Select Get Data > From Other Source > From Web (you might be tempted to choose From File, but don't).

  4. 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.

  5. 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.

  6. 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.

  7. 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


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 = true

  • Account Type is any of Cost of Goods Sold, Income, Expense, Other Income, Other Expense

  • Date (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 it

  • Class / 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

  1. Add a cell for year and give the cell a reference name such as IncStmtYear

  2. To get the value for the 1st month of the year use the formula
    =TEXT(DATE(IncStmtYear,1,1),"mmm yyyy")

  3. 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 months

  4. You'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])
    where customsearch_cean_gl_accounts
    is your table range that contains your list of GL accounts.

  5. The 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.

Did this answer your question?