Sales Forecasting in Dynamics 365 for Sales

Goals were introduced to Dynamics back in the CRM 2011 days and have remained unchanged ever since. While a great tool for tracking your progress against a target, the set up process wasn’t the easiest to understand and felt a bit clunky.

Now, nearly 10 years later, Microsoft has introduced Forecasts. A new way to track your opportunity pipeline, and keep tabs on how your sales people are performing against their quotas.  

Even nearly 10 years on, there is no escaping the need for some configuration before getting started. We can access forecasts and their configuration options through the out of the box sales hub app. So let’s start by opening up the app settings and selecting forecast configuration, click Enable Forecasting and then Enable to enable forecasting in the environment.

Once enabled, we then need to select the template for our forecast, the currently available options being Org chart forecast or Territory forecast. The difference between the two templates being how the forecasts are rolled up. Org chart forecasting is based on your Organisation hierarchy which is defined by the Manager field on a user record and the Territory forecast is based on the hierarchy defined by the sales territory entity.

For this post, we are going to look at setting up an Org chart forecast, aside from the hierarchy route the rest of the configuration remains the same for both templates.

After selecting our template, we are then presented with a setup wizard to configure the rest of our forecast.

Let’s start by giving our forecast a name. The Rollup entity and Hierarchy route fields are already set for us by the template we selected and can’t be changed.

The default underlying records view is the view that is presented to users when they want to view the opportunities that make up a particular row or cell of the forecast, you’ll see this in action later.

The top of hierarchy field defines the user or territory that you want at the top of your selected hierarchy route.

If you are creating a forecast for a sales manager who wants to see a forecast for his team, for example, you would select the sales manager’s user record and a preview of the hierarchy will be presented in the right hand pane.

At the bottom of this step, in the scheduling section you can define the period that you want your forecast to run over.

The forecast period determines the length of time each forecast runs for, either monthly, quarterly. You can only schedule forecasts for up to one year, so your selection here will dictate the number of periods you are able to define in the Number of periods field, which indicates the number of forecast periods that will be generated.

You can also select the fiscal year for the forecast, which is populated based on your organisations fiscal year settings. And when the forecast starts, the options for this is again determined by your selection in the forecast period.

The valid from and to fields at the bottom of the section will be populated automatically to show you when your forecast will start and end based on your selections.

In the next step of the forecast configuration, we can define who has access to adjust the forecast data at each level of the forecast hierarchy.

In the user security field section, we can see that the Hierarchy entity is selected for us based on our selected template. We can use the User lookup field to indicate who the owner of the forecast row is, by default this is set to User for Org chart forecasts and Manager for Territory forecasts. We can change this value to another SystemUser field related to the Hierarchy entity and it will give that User permission to edit this and their descendants forecasts.

In the additional security roles section we can set who can view the forecast. No additional security roles means that only the forecast owner can view the forecast, All security roles means that everyone can see the forecast, and the final option, allows us to define specific security roles users need to have, in order to see the forecast. At the bottom of this section we have a handy message that gives us a brief description to help us understand how the forecast security we have selected is applied.

In the layout step we are able to select the columns that appear in the forecast as well as how those columns behave.

Before we start adding new columns we can see a couple have been added for us already, these are Quota, which is the target for the forecast and Prediction, which is only available if predictive forecasting is enabled for your organization, this is a premium feature that comes with Dynamics 365 Sales Insights and uses AI to analyse historical data and make predictions about your forecast.

The first thing we must do for each forecast is to select an option set to define our roll-up columns, Microsoft suggests using the new Forecast category option set and then selecting auto-configure to automatically populate these columns.

Doing so adds a column for each value in the option set. Of course we can select any option set from the opportunity entity, however, the forecast category has a real time workflow associated to it out of the box, which automatically sets the value to won or lost when an opportunity is won or lost. If you are considering using your own option set, you may want to consider implementing a similar workflow for your particular option set.

Lets check out the configuration of one of these columns to see how they have been configured and what options we have when it comes to adding other columns.

The type field defines the columns' behaviour, with four options available:

Rollup aggregates a value from a field that we define for records that are grouped by a selector, which we also define. For example, our pipeline column aggregates the estimated revenue for all records where the forecast category is set to pipeline.

Calculated lets us define a formula, which can calculate a new value based on the other columns in the forecast.

Simple columns allow us to manually add data to our forecast, this along with Quota information can be uploaded into our forecast from an Excel workbook. We'll see this in the next step.

And with Hierarchy related we can display additional attributes from our hierarchy entity, either the user that owns the forecast or the territory, depending on the type of forecast we have created.

There are also plenty of other options for us to configure depending on the column type we have selected, for example in a rollup field not only can we define our Selector and Amount field, we can also define the Date field that determines which forecast period this value should be included in.

In the description, we can add some text which will appear as a tooltip on the column header.

The allow adjustments checkbox toggles whether or not to allow users to manually adjust this column's value and the Show progress compared to quota, presents the user with a progress bar beneath the column's value indicating the percentage achieved so far against the quota column.

The show in trend chart is part of the predictive forecasting feature of Dynamics 365 Sales Insights which we will cover in a future video.

Finally unique name is the name used to identify this column if we want to use it in a formula for a calculated column.

Before moving on to the final step of our forecast configuration, we are also able to apply some additional filters to our forecast, enabling us to set conditions to automatically exclude certain opportunities from the forecast and works very similarly to setting filters in Advanced Find.

Now in our final configuration step we can activate our forecast as well as populate our quotas and any other simple columns we may have added in the previous step. Simply click the download link to download the Excel workbook.

The workbook is divided into groups of two sheets for each simple column added to the forecast. In each sheet we can see a Record Id, the Hierarchy field which in our case is the User's Name and then a column for each forecast period. To set the values for our simple columns simply edit the values in the appropriate cells in each of the work sheets. Once done, we can save the workbook and upload it back into our forecast by browsing to the file, or dragging and dropping the template back onto the forecast. Now click Finish and our forecast is ready to use.

So let's navigate over to the Sales Area of the app and take a look at our forecast, you'll find it in the Performance section of the navigation and initially we are presented with a grid displaying all of the columns as they were defined in the configuration.

In the top left we have a view selector that allows us to navigate between the different forecasts we may have configured, as well as a drop down that will allow us to select the period we want to see.

The forecast grid itself shows a row for each user in our hierarchy, with each row displaying a cell for each column that we defined.

Clicking on the row shows us the underlying opportunity records which make up that users forecast for the period, we can further filter this view by clicking on an appropriate cell within the row.

The underlying opportunities can either be displayed in an editable grid, or a kanban board, making it easy for users to edit their opportunities and see the impact it has on their forecast.

If any of the columns are configured to allow adjustments, users are able to do this by clicking on the pencil in the appropriate cell and adding the new value as well as a note for the adjustment. The new value is then displayed in the cell as well as the system calculated value in parentheses and struck through next to it. You can also fall back to the system calculated value, by adjusting again and clicking the reset button. The history tab in the adjustments window, will keep track of all the adjustments made.

Finally if we open up one of the opportunities, we can see that the new Forecast category field has been added to the default opportunity form, which means we can set this as part of our day to day work on our opportunities.