Dynamics 365 Sales: Product Forecasting – Part 2

Last week I wrote part 1 of my series on the product forecasting capabilities in Dynamics 365 Sales, and if you haven’t read that article yet, you can do that here. Since the article became a bit long I decided to split it up into two different parts. In part 1 I started talking about the configuration of a forecast, so I am picking up where I left off in the previous article.

Product Forecast Configuration (continued)

The ‘Layout‘ page of the forecast configuration allows us to configure the columns of the forecast. You can you the out of the box Forecast Category option set for this (These values are related to the opportunity table) but you can also select a different option set. In my case I am going to stick with this option set. In order to configure each column you can click on the gear icon on the right side of the column name. Please note that quota column values cannot be updated from there at this time. The quota will be imported as a last step using an excel file. Clicking into some of the other columns also shows you how some of these values are calculated. From the column properties section you’ll be able to view and/or change the way some of these calculations are setup. On top of that you’ll be able to select the amount field for which data will be summarized in the column. Since we selected ‘opportunity lines’ as the rollup entity for this forecast, we can select parent records (in this case the opportunity) as the ‘Date Field Entity’. The amount field will come from the opportunity line table.
On the bottom of the column configuration you can configure to ‘Allow Adjustments’, which is the ability to manually adjust the value in this column, to ‘Show the progress compared to quota’ which is exactly that, and whether or not you want to show this data in the trend chart (if you have access to this in your environment.)*

The ‘Filter Data‘ page will allows you to add hierarchy filters (in this case we can filter to only show data filtered based on the product table) and/or rollup filters. In this case the roll-up table is the opportunity line table, so we could filter out these records, and any records related to the opportunity line table. For this scenario I am going to leave this blank.

The ‘Drill Down‘page is where you can configure drill down options you’re users will have to go deeper into the underlying data of the forecast. Account and User drilldowns are already automatically configured, so I am going to add an opportunity drill down. This will allow users to view the opportunity the opportunity lines are related to when drilling down. In order to configure this you’ll need to click the ‘Add drill down’ button and select ‘Custom drilldrown’. Enter ‘Opportunity’ as the drill down entity and enter the same name as the display name. Under ‘Amount Data’ we will need to select which amount fields we want to use. I choose the opportunity line table as the ‘Amount entity’. Lastly you’ll need to select the relationship below ‘Entity relationships’, for which I selected the only one available, which is the opportunity relationship with the opportunity lines: ‘Opportunity Line > Opportunity (Opportunity) > Opportunity‘.

On the ‘Snapshots‘ page you can enable the system to take periodic snapshots of the dataset. Users can use snapshots to see how the underlying data changed over time. This is a very nice tool to get a better understanding of what happened to a deal, and when this change took place. When the daily snapshots are enabled, every 6 hours a snapshot is created for the forecast.*

*In order to use the trend chart and snapshot functionality you’ll need Dynamics 365 Sales Premium or Dynamics 365 Sales Enterprise.

The last page on the Forecast is the ‘Advanced‘ page. This is where we can configure to auto-hide duplicate rows that have no quota. The multi-currency selection setting allows users to view the forecast amounts in a different currency if this is turned on (this conversion happens in real-time).
The ‘Set kanban as default(preview)‘ setting allows us to show the Kanban view when drilling down into the data vs showing a list view of records. Lastly you can also configure the ‘Default selections‘, or groupings and set a default view for underlying records. Below that section is also where we can add jscript for a more advanced configuration.

The last page is the ‘Activate & add quotas‘ page. From here you’ll need to click the ‘Activate’ button first. Once the forecast has been activated, you can then download the ‘Simple data column template’. This is an excel file where you will enter the quota for the different furniture product families and the individual products. Please note that here are two tabs on the spreadsheet, one for the individual products (direct) and one for the parent quota. The parent quota should be the sum of the underlying child products, so for example if all individual chair products have a quota of $15,000, then my parent quota for the Chairs product family should be $15,000 x 5 (I only have 5 chair products)=$75,000.

Once the forecast has been activated and the quota has been uploaded you can access the forecast by navigating back to the sales area in the sales hub. You’ll notice the forecasts below the performance section on the site map. Clicking on ‘Forecasts’ will take you to the forecast screen from which you can change the forecasts you’re viewing from the view selector. You’ll notice that the 3 product families we created earlier are shown as a line in the forecast, including the dollar amounts in each forecast category. If you need to view the underlying products in a product family, can expand the data by clicking on the arrow on the left side of the product family name. This will show you the quota for each product and the amounts in the forecast buckets.

In order to drill down on the underlying data, you need to select an item from the list when you click on ‘Drill down by’. When selecting a drill down option, you’ll notice there are now blue arrows showing next to the products and product family names. Since I selected the opportunity drill down, the opportunities related to each opportunity line are now showing in the forecast. If you select the account drill down, you’ll see the related account in the forecast. You can also click on a product (family) and see the underlying records shown in a page that opens up from the bottom. From here you can also group the records by different fields, I.E Owner, Opportunity, etc.

I hope you found this article informative! The related YouTube video showing the entire setup is shown below. Be sure to check in again next week for a new article or subscribe here to never miss another post!