Dynamics 365 Sales: Product Forecasting – Part 1

If your organization has implemented Dynamics 365 Sales, then you are probably familiar with the forecasting feature that is included in the product. Usually forecasts show data from an org chart or sales rep perspective, meaning you’ll see all the different dollar amounts by forecast bucket by the sales rep that owns the related opportunities in a forecast record. This is called an org chart forecast and is probably what most organizations are using the forecast capabilities for. There are other ways forecasting can be used. Did you know that forecasting in Dynamics 365 lets you create different types of forecasts out of the box? Think about the ability to show a forecast by territory, or even the ability to build a custom forecast based on custom tables in the application! Another option is the product forecast, which I will be discussing in these two articles.

What is a product forecast?

Product forecasts are forecasts that look at the underlying opportunity lines tables which are related to opportunities, combined with the product catalogue in Dynamics 365. The important thing to note here is that you will need to have product records in your environment, and the products need to have a parent-child relationship configured. What this means is that you will need to tie the products you want to use in the forecast to a product family. Product families are located in the same product table, the difference is that the product family rows distinguish themselves from ‘regular’ products as the value of the product structure column is set to ‘Product Family’. Before setting up your product families and products, you need to think about how you would like the data in the forecast to be visualized. Do you just want to show one product table or do you want to break it out in different categories? For example if I had a product table that contains all sorts of furniture, I could create one product table called ‘Furniture’ and tie all the different products to that product family. Products could include tables, couches and chairs. But what if I wanted to actually see my forecast data roll up to these different types or categories of furniture? For example what I would want to see the forecasting data for chairs tables and couches broken out individually, is that possible? Yes it is and I am going to tell you how we can do that!

Product Setup

I am going to use the above scenario to configure a product forecast in Dynamics 365. As mentioned I have 3 different ‘categories’ that I want to report on, and I’ve listed the products belonging to each category below:

• Sofa’s and Love Seats
  • Venetian Sofa & Love seat
  • Simpol Love Seat
  • Simpol Sofa
  • Kerby Sofa
  • Kerby Love Seat
• Chairs
  • Waca Accent Chair
  • Pillowfort Tulip Chair
  • Mallry Tub Chair
  • Cole Lounge Chair
  • Lawrence Club Chair
• Tables
  • Lancaster Dining Table
  • Internion Coffee Table
  • Zinus Dining Table
  • Alace Dinging Room Table
  • Miesville Dining Table

I will create 4 product families in Dynamics 365, the first one is the ‘Furniture’ product table, and all the data will roll up to that product family. Below the ‘Furniture’ product family I will have 3 additional product families, which are named after the different categories you see above. I will create products families for ‘Sofa’s and Love Seats’, ‘Chairs’, and ‘Tables’. Make sure that these product tables are rolling up to the furniture product family by entering ‘Furniture’ in the ‘Parent’ column. Below you can see what this looks like in Dynamics 365.

Once you added the product tables in Dynamics 365, now we can upload or enter the individual products in Dynamics 365. Don’t forget when you add the data to Dynamics 365, to populate the correct product family in the ‘Parent’ column when creating or importing the product. So for the chairs select ‘Chairs’ as the product family, for all the tables select ‘Tables’ as the product family and so on. Once all the products have been added we can start to configure the forecast.

Product Forecast Configuration

In order to create any forecast in Dynamics 365 Sales, you will need to change the app area to ‘App Settings’. The app switcher is on the bottom of the sitemap, which is on the left side of the screen. You’ll notice ‘Forecast configuration’ below the ‘Performance Management’ section when you scroll down on the sitemap. When you click on ‘Forecast configuration’ you’ll see the ‘Start your forecast’ page. On the top of the page you see the different types of forecasts you can configure from here. Click on ‘Product Forecast’ to start the configuration. This will bring up the ‘General‘ screen of the forecast configuration. Make sure you enter a name for your forecast and then start the configuration. You’ll notice that ‘Opportunity Line’ is automatically selected as the rollup entity, but you could also use quote lines, order lines, or any other tables that have a relationship with the product table. The hierarchy entity is set to ‘Product’ and the ‘Rollup to hierarchy relationship’ is prepopulated with ‘Opportunity line>Existing Product (Product) >Product’. The ‘Top of hierarchy’ field will define which product (family) is at the top of the hierarchy. Since we created a ‘Furniture’ row which all the other product families roll up to, this is what we will select here. You’ll notice a preview of the hierarchy on the right side of the page.

For the ‘Scheduling’ section, this is where you will define the time frames for each period. In my example I enabled advanced scheduling and configured 4 quarterly periods, with a start date of October 1st. These periods represent the time frames that the system will look at when it decides if opportunity lines related to opportunities should be included in a period. The system will look at the opportunity’s ‘Est. Close Date’ column to decide this: if the data in the opportunity’s ‘Est. Close Date’ column is within the forecast period, the related data in the opportunity lines will be included in the forecast. If the data in the opportunity’s ‘Est. Close Date’ column is outside of the period, it will not be included in the forecast. Once you’ve completed the configuration, click next to go to the “Permissions’ page.

The ‘Permissions’ page is where we can decide who has access to the forecast. Since this is a product type of forecast, I am setting the user lookup field on the product table to ‘None’, as who owns the product record doesn’t necessarily needs access to the forecast. If you set this field, this means that the user that is populated in user lookup field of the product record (created by, owned by) will have access to the forecast. You’ll notice there are options to give certain security roles access to the forecast. Below the ‘Share Forecast’ section we can set if the forecast can be shared to other users. The ‘Read and adjust’ option allows not just sharing, but also writing privileges on the forecast. The writing privilege allows users to update the value in any of the forecast columns, IF these are configured to allow a manual update. I’ll talk about that next week in the ‘Layout’ page of the forecast configuration.

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