Get more insights on a single page

With a single data set (NetSuite saved search) you can create multiple pivot tables to analyze your A/R not only by customer but by sales rep, items, or any other property desired. Add slicers as needed. Suggestions include subsidiaries, locations, departments, aging bucket, etc.

Keep your data up to date by leveraging real time updates with a single click (or schedule) with CloudExtend Excel Analytics for NetSuite.

Create an A/R Aging saved search in NetSuite

To see your Accounts Receivable Aging details in Excel follow the instructions below to create a NetSuite saved search and then use CloudExtend Excel Analytics to pull it into Excel either on demand or on a schedule.

Create a new NetSuite saved transaction search (Reports -> Saved Searches -> All Saved Searches -> New -> Transactions).

Add your criteria

This search will include all open invoices which will be aggregated into one or more pivot tables in Excel to allow you to drill down to the original transaction in NetSuite.

Use (or modify) the following criteria.

  • Type: Invoice

  • Status: none of Invoice: Paid in Full, Invoice: Pending Approval

  • Amount Remaining: not equal to 0

Add your results

Add your desired columns to the results. Be sure to use custom labels as needed.

Formulas used

Formula for 'Aging'
CASE WHEN trunc({today})-{duedate} < 0 THEN 'Current'

WHEN trunc({today})-{duedate} between 1 and 30 then '1 to 30'

WHEN trunc({today})-{duedate} between 31 and 60 then '31 to 60'

WHEN trunc({today})-{duedate} between 61 and 90 then '61 to 90'

ELSE '>90'

END

Formula for Link to NetSuite (substitute your NetSuite account number for NNNNN

'HTTPS://NNNNN.app.netsuite.com/app/accounting/transactions/transaction.nl?id=' ||{internalid}

Run your saved search from Excel

  1. Load CloudExtend Excel Analytics for NetSuite and add the saved search you created above.

  2. Create one or more pivot tables using Aging in the columns, Amount due in the Values, and Customer, Sales Rep, etc., in the rows.

  3. Add in a chart and slicers

  4. Refresh your data on demand (or put it on a schedule).

Did this answer your question?