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

Watch the video and refer back to the article for the formulas used.

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.

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 your 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'll have an article on this process available soon.

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 on that process refer to this article

Determine the type of income statement you want to create

Quick'n dirty with a pivot table

You 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 (see appendix for more details).
    =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)

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

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