Multiple External Source Model - Use Case

The following example shows how you can use multiple external source models to generate valuable forecasting data for sales and consulting revenue.

• Data inputs about the prospective customer, sales rep, potential revenue amount (ARR), and potential implementation consulting revenue are loaded into an ESM called Revenue Opportunities.

• Sales revenue is forecast monthly based on a project start date and a term. To keep the ESMs organized and of a manageable size, there is one ESM for each year to store the monthly forecast.

• Consulting revenue is forecast quarterly based on a project start date and four quarters of payments. To keep the ESMs organized and of a manageable size, there is one ESM for each year to store the consulting forecast.

Revenue Opportunities identifies 8 data input fields that you need to load.

Here is the data load file for Revenue Opportunities.

The Revenue ARR amount is spread over 2018 and 2019 in this example. An ESM for each year is created. Only 4 fields from Revenue Opportunities need to be mapped to the ARR Spread models to generate the monthly forecast.

Here are the maps to copy the four input fields from Revenue Opportunities to the Spread models.

Here is the calculation to run the two ESM maps.

The resulting ARR spread data is as follows. For details on how the DaysFactor function works, see Explanation of the DaysFactor Function.

Similar to ARR, the Revenue Implementation Consulting amount is spread over 2018 and 2019 in the following example. To show a different use case, we assume that consulting will be paid in 4 equal quarterly payments and will last no longer than 4 quarters, even if the Term of the project is more than 12 months. An ESM for each year is created. Only 3 fields from Revenue Opportunities need to be mapped to the Consulting Spread models to generate the monthly forecast.

Explanation of the Quarterly Formulas

For syntax of the ESM functions, see External Source Model Field Types, Expressions, and Formulas.

• We have a Service Start date and need to calculate the dates of the following three quarters.

• Formulas are created which calculate what the month and year is 3 months later, 6 months later, and 9 months later.

  • If the Service Start date is late in the year, then adding 3, 6, or 9 months will push into the next calendar year. For example, 11/1/18 plus 3 months is 2/1/19.

  • If you add 3 to the Service Start date month (11 + 3 = 14), and the result is greater than 12, then you need to subtract 12 to get the correct month (14 - 12 = 2).

  • IF(MONTH([Service Start])+3<13, MONTH([Service Start])+3, MONTH([Service Start])+3-12)

  • Similarly with the year, if you add 3 to the Service Start date month (11 + 3 = 14), and the result is greater than 12, then you are into the next year, so you need to add 1 to get the correct year (2018 + 1 = 2019).

  • IF(MONTH([Service Start])+3>12, YEAR([Service Start])+1, YEAR([Service Start]))

• Once you have fields defined that store the month and year of the following three quarters, you can put it all together and create fields to store the Consulting Q2, Q3, and Q4 dates.

  • Date([Service Start Q2 Month],[Service Start Day],[Service Start Q2 Year])

• To create the monthly forecast of consulting revenue, we want 25% of the Implementation Consulting numeric value to appear in each of the four months of the Consulting Q2, Q3, and Q4 dates.

  • For Nov-2018, if the Service Start date, Consulting Q2 date, Consulting Q3 date, or Consulting Q4 date has a Month=11 and a Year=2018, then calculate Implementation Consulting]/4, otherwise 0.

  • In other words, if any of the following conditions are True, then the OR condition is True and will result in [Implementation Consulting]/4.

    • AND(MONTH([Service Start])=11, YEAR([Service Start])=2018)

    • AND(MONTH([Consulting Q2])=11, YEAR([Consulting Q2])=2018)

    • AND(MONTH([Consulting Q3])=11, YEAR([Consulting Q3])=2018)

    • AND(MONTH([Consulting Q4])=11, YEAR([Consulting Q4])=2018)

  • IF(OR(AND(MONTH([Service Start])=11, YEAR([Service Start])=2018), AND(MONTH([Consulting Q2])=11, YEAR([Consulting Q2])=2018), AND(MONTH([Consulting Q3])=11, YEAR([Consulting Q3])=2018), AND(MONTH([Consulting Q4])=11, YEAR([Consulting Q4])=2018)), [Implementation Consulting]/4,0)

Here are the maps to copy the three input fields from Revenue Opportunities to the Spread models.

Here is the calculation to run the two ESM maps.

The resulting Consulting Revenue spread data is as follows.