Step-by-Step Use Case for Forecasting Revenue Spread Over Time

The following use case demonstrates the flexibility and complex capabilities of the External Source Model functionality.

This example shows how ESM can spread a sales opportunity for Annual Recurring Revenue (ARR) over a period defined by a Service State Date and a Term. The Sales Segment then copies the resulting summary data into a Master model.

Inputs:

• From the source data: Opportunity Name, Sales Segment, Opportunity Owner, Lead Source, Term, ARR, Service Start

Calculations:

• Calculate the Service End based on the Term and Service Start
• Calculate the monthly revenue based on ARR, Service Start, Service End, and Term

Viewing the Source Data

Here is the source data, an Excel spreadsheet of sales opportunities. Opportunity Name would represent the prospective customers. Opportunity Owner would represent the Sales Rep's name. ARR is the revenue potential over a Term of months beginning on Service Start.

We will calculate the Service End, and spread the revenue for all opportunities across months beginning from a Planning Start Month of January 2018 to a Planning End Month of December 2019.

Defining the External Source Model

1. To define the source model, go to Model > External Source Model > Source Model.
2. Give the Source Model a name, such as Opportunity Data.
3. Fill in the fields and formulas as listed below.
4. Click Save.

Here are all the fields to create in the External Source Model. The first 7 fields match the 7 columns in the source data.

All remaining fields are calculated or preset with constants. In all these fields, Included in Data Load is No. Explanation of the Lookup and Round, If, and DaysFactor functions are provided below.  

The resulting Source Model appears as follows:

Explanation of the Lookup Function

To derive the value for Service End, a Lookup function is used. Although the calculation of the Service Start Date plus a Term of n months is a simple formula in Excel, we are demonstrating how to use the Lookup function to accomplish the same goal.

Lookup uses Key fields, Service Start and Term in this case. It looks for the value of the Key fields in another ESM model, which is Opportunity - Daily Calendar in this case. When Service Start and Term are found in the same transaction in Opportunity - Daily Calendar, the value of Service End is returned to ESM model Opportunity Data and fills the Service End field (the field with the Lookup function).

Here is the definition of Opportunity - Daily Calendar and its data.

The rows of data continue all the way down to 12/31/2021 with a term of 36 months ending 12/31/2024.

For example, the first row in our opportunity data has a Service Start of 02/14/2019 and a Term of 24. Looking up these Key fields in the Opportunity - Daily Calendar returns a Service End date of 02/14/2021.

For more information about the Lookup function and other functions, see External Source Model Field Types, Expressions, and Formulas.

Explanation of the DaysFactor Function

To derive the value for each month's revenue forecast, a DaysFactor function is used. DaysFactor is a function with 5 arguments:

• [Start Date Field]

• [End Date Field]

• [Sub-Start Date Field]

• [Sub-End Date Field]

• "MMM-YYYY"

DaysFactor counts the days Sub-Start Date to the specified "MMM-YYYY" (assuming month end), and divides that count by the number of days in the MMM-YYYY month.

For example, the first row in our opportunity data has a Service Start of 02/14/2019.

The first formula using DaysFactor in the Source Model, Jan-18, is this:

• DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jan-2018")

• With the values filled in, the formula is DAYSFACTOR(01/01/2018, 12/31/2019, 02/14/2019, 02/14/2021, "Jan-2018").

• DaysFactor counts the days from 02/14/2019 to the month end of Jan-2018, inclusive of the first day, and divides that count by 31 (the number of days in Jan 2018). In this case, Jan-2018 is before 02/14/2019, so the value is 0/31 = 0. The IF function regulates what to do with the resulting value of DaysFactor.

• IF DAYSFACTOR()>0.5, then return a value of [ARR]/12 otherwise return 0. Since DaysFactor for Jan-2018 is 0, then IF returns 0.

Skipping ahead in the Source Model to the field for Feb-19, the formula is:

ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Feb-2019")>0.5,[ARR]/12,0),0)

To evaluate this function:

• DAYSFACTOR(01/01/2018, 12/31/2019, 02/14/2019, 02/14/2021, "Feb-2019") returns 15 days divided by 28 = .5357.

• IF DAYSFACTOR()>0.5, then return a value of [ARR]/12. Since DaysFactor for Feb-2019 is .53, then IF returns ARR/12, or 37,705/12 = 3142.083.

• ROUND makes the result of the IF function an integer by rounding off the decimal places. This results in a final number of 3142.

When the Source Model is loaded with data and the formulas are calculated, you can see that the ARR is spread across the different months, beginning in the month of the Service Start (so long as the Service Start was in the first half of the month).

For more information about the DaysFactor function and other functions, see External Source Model Functions.

Loading Data into the Opportunity Data External Source Model

After creating the Source Model and defining all the fields that are derived from formulas, it is time to load data.

1. Select Model > External Source Model > Source Data.

2. Select the name of the Source Model from the drop-down.

3. Open the source data workbook.

4. Copy and paste the source data into the first 7 columns.

   
   
   

5. Click Load Data.

6. Click Refresh. You will then see the derived fields filled in.

   
   
   

Creating a Master Model

You can create a Master model and then copy the data from the ESM Source Model to the Master model. The Source Model is a 2-dimensional table of data whereas the Master model is a multidimensional model, so you will need to map the fields from the Source Model to the dimensions and members in the Master model.

1. Go to Model > Setup.

2. Create a new model, such as Forecast Model, of type Master.

3. Define dimensions, such as Sales Segment, Time, Scenario, and Account.

   
   
   

4. Save the model.

5. Add members to the dimensions to match up to the Source Model you will be loading.

6. Return to Model Setup and click Generate Model.
   Note:

   here is a look at the same Master model from the Web interface, Model Manager.

Defining a Map from Source Model to Master Model

The Source Model is a 2-dimensional table of data whereas the Master model is a multidimensional model, so you will need to map the fields from the Source Model to the dimensions and members in the Master model.

1. Go to Model > External Source Model > Source Map.

2. Enter a name for the map.

3. Select the Master model from the Model drop-down.

4. Select the Source Model from the Source Model drop-down.

5. Select Data for Type.

6. Optionally set Append Missing Dimension Members to Yes. This option detects if there are leaf members detected in the Source Model that are not in the Master model and adds them dynamically at runtime.

   
   
   

7. Now specify the fields from the Source Model that map to a member in the dimension hierarchy of the Master model. Generally, fields that are text are mapped to dimension members. Fields that are numeric are mapped to values. Not all fields must be mapped.

   

8. Save the map.

Using a Calculation to Load Data into the Master Model

To copy data from the Source Model to the Master model, you must use a calculation to run the External Source Map.

1. Go to Model > Calculation.

2. Select the Master model name from the Model drop-down.

3. Give the calculation a name.

4. Create the first step of the calculation by selecting ExternalSourceMap from the Type drop-down.

5. Select the name of the map you created in the previous task from the name drop-down.

6. Optionally add an Aggregation step to the calculation.

7. Save the calculation.

8. Run the calculation.

   
   
   

   When using a calculation to execute an ExternalSourceMap, records from an external source model are copied to a Master Model. While the map is running, if the copy operation fails due to invalid dimension members, a listing of up to 100 invalid members will be added to the calculation notification, emailed to the user running the calculation, and the map execution will stop. After reviewing the email, the user has the opportunity to review the map, data, and master model and take necessary actions to correct the errors. The calculation can then be run again until no invalid members are found.

Viewing the Data in the Master Model

To view the data in the Master model, create a view.

Using Views to Input Future Opportunities into the Source Model

You can directly edit data in the Source Model using a view. A Power user must create the view, then all other users can use the view.
We have a series of sales opportunities already loaded into the Source Model from an external data source, and now you can create a view to allow the user to add more opportunities directly.

1. Start Spotlight (Web).

2. Under Analyze > Data, select the name of the Source Model, Opportunity Data.

3. Select the Default view. Here is a snapshot of the existing data.

   
   
   

4. Decide on a field that will be listed as a Point of View lookup on the page axis, such as Sales Segment.

5. Select Design View.

6. Optionally, remove excess columns by right-clicking the column and selecting Delete Column. Remember, to calculate all the fields with formulas in this model, the user need only input 7 fields. Leave the fields in row 1.

7. Save the view.

8. Select the Properties icon.

9. To be able to save the view, you must set the Master Model Map property and a POV Dimension.

   Select the Master Model Map from the drop-down.

   Below POV dimension, add a blank row by right-clicking on a blank row and selecting Insert Row. Under POV Dimensions, type the name of the field you decided on in step 4.

   Set Enable Save to Yes.

   
   
   

10. Click Save.

11. Select Analyze > Data to run the view. Now you have a data entry form you can fill in. Or you can select a Sales Segment from the POV drop-down and view existing data.

    
    
    

12. To enter a new opportunity, go to the first blank row below the list of opportunities and add another. Fill in the first 7 columns.
    
    
    

13. Save the data. The screen refreshes to show the values and results of the formulas.