How to Forecast Revenue for Multi-Month Service Quotes in NetSuite

NetSuite's Advanced Revenue Management (ARM) module provides a comprehensive, rule-based framework for revenue recognition that extends beyond just the sales order level, handling complex contracts, bundled offerings, and multi-element arrangements. This module offers automated revenue forecasting and reporting capabilities for post-sales order revenue.

For pre-sales order forecasting or specific scenarios requiring granular control, a custom approach can be implemented:

1. Create Custom Fields on the Quote Form: Define pairs of custom body fields for each period intended for revenue forecasting. Each pair should consist of one date field and one currency field. For example, if forecasting for 24 periods, 24 date fields (e.g., custbody17, custbody19, etc.) and 24 currency fields (e.g., custbody18, custbody43, etc.) would be created.

2. Populate Custom Fields: Users manually enter the projected revenue amount and the corresponding recognition date for each period directly on the quote form.

3. Develop a Custom Saved Search: A saved search can be configured to aggregate these custom field values. The search will sum the values based on the month, allowing for period-specific revenue projections. While NetSuite's advanced forecasting features like ARM and rolling forecasts provide dynamic, forward-looking revenue projections, a custom search based on this method will typically reflect the values as of the date the report is executed.

   The following sample formula can be used within the saved search to sum values for up to 24 periods, checking if the custom date field falls within the current month:

CASE WHEN TRUNC({custbody17},'MM' ) TRUNC({today},'MM' ) THEN {custbody18} ELSE 0 END+CASE WHEN TRUNC({custbody19},'MM' ) TRUNC({today},'MM' ) THEN {custbody43} ELSE 0 END+CASE WHEN TRUNC({custbody20},'MM' ) TRUNC({today},'MM' ) THEN {custbody44} ELSE 0 END+CASE WHEN TRUNC({custbody21},'MM' ) TRUNC({today},'MM' ) THEN {custbody45} ELSE 0 END+CASE WHEN TRUNC({custbody22},'MM' ) TRUNC({today},'MM' ) THEN {custbody46} ELSE 0 END+CASE WHEN TRUNC({custbody23},'MM' ) TRUNC({today},'MM' ) THEN {custbody47} ELSE 0 END+CASE WHEN TRUNC({custbody24},'MM' ) TRUNC({today},'MM' ) THEN {custbody48} ELSE 0 END+CASE WHEN TRUNC({custbody25},'MM' ) TRUNC({today},'MM' ) THEN {custbody49} ELSE 0 END+CASE WHEN TRUNC({custbody26},'MM' ) TRUNC({today},'MM' ) THEN {custbody50} ELSE 0 END+CASE WHEN TRUNC({custbody27},'MM' ) TRUNC({today},'MM' ) THEN {custbody51} ELSE 0 END+CASE WHEN TRUNC({custbody28},'MM' ) TRUNC({today},'MM' ) THEN {custbody52} ELSE 0 END+CASE WHEN TRUNC({custbody29},'MM' ) TRUNC({today},'MM' ) THEN {custbody53} ELSE 0 END+CASE WHEN TRUNC({custbody30},'MM' ) TRUNC({today},'MM' ) THEN {custbody54} ELSE 0 END+CASE WHEN TRUNC({custbody31},'MM' ) TRUNC({today},'MM' ) THEN {custbody55} ELSE 0 END+CASE WHEN TRUNC({custbody32},'MM' ) TRUNC({today},'MM' ) THEN {custbody56} ELSE 0 END+CASE WHEN TRUNC({custbody33},'MM' ) TRUNC({today},'MM' ) THEN {custbody57} ELSE 0 END+CASE WHEN TRUNC({custbody34},'MM' ) TRUNC({today},'MM' ) THEN {custbody58} ELSE 0 END+CASE WHEN TRUNC({custbody35},'MM' ) TRUNC({today},'MM' ) THEN {custbody59} ELSE 0 END+CASE WHEN TRUNC({custbody36},'MM' ) TRUNC({today},'MM' ) THEN {custbody60} ELSE 0 END+CASE WHEN TRUNC({custbody37},'MM' ) TRUNC({today},'MM' ) THEN {custbody61} ELSE 0 END+CASE WHEN TRUNC({custbody38},'MM' ) TRUNC({today},'MM' ) THEN {custbody62} ELSE 0 END+CASE WHEN TRUNC({custbody39},'MM' ) TRUNC({today},'MM' ) THEN {custbody63} ELSE 0 END+CASE WHEN TRUNC({custbody40},'MM' ) TRUNC({today},'MM' ) THEN {custbody64} ELSE 0 END+CASE WHEN TRUNC({custbody41},'MM' ) TRUNC({today},'MM' ) THEN {custbody65} ELSE 0 END