Sales forecast | Prediction and Opportunity Trend | Microsoft Sales

Previously only forecast we had in sales was sales pipeline… Which was our sales pitch in every pre-sales. How management can see revenue in pipeline and how you can take action accordingly.

“But now since crm is evolved into AI powered customer engagement , that one single chart is not a very interesting features to get insight about your sales “

Sales forecast does more than displaying pipeline, it helps you to setup quota to your team see their progress, trend and flow. It also allows user to move opportunities around in Kanban view.

Along with normal sales performance it can calculate product and territory performance as well.. which is quite useful (I used to build a custom solution around product performance now I can sip coffee instead 😀 )

This post is focused on the Org Chart Forecast ,what is it ? how can you set it up ? let’s find out.


First thing first , make sure you have manager hierarchy in place. something like shown in image below where you have lots of people working together.

User Manager Hierarchy

Forecast Configuration

Before you can see forecast you need to configure it first. navigate to App Settings and Under Performance management open forecast configuration. You will see something like the image below.

Forecast Configuration page

Before I start configuration steps , let’s see what these different type of forecast are and where can we use them

Org chart forecast

“Rollup columns and projections are based on your organization’s reporting structure. This template uses the Manager field on the User entity for the hierarchy.”

Product Forecast

“Rollup columns and projections are based on the product hierarchy.”

Territory forecast

“Rollup columns and projections are based on the sales territory hierarchy.”

The 8 step process

Step 1 : General

Give you forecast a name and fill other fields for your reference here are some details

  • Rollup Entity : By default it should be Opportunity since that’s where the money is
  • Hierarchy entity : User by default
  • Rollup to hierarchy relationship : Default relationship between opportunity and user
  • Default underlying record view : Opportunity Forecast view.

If you have some customized opportunity setup, you should modify the settings otherwise default settings are enough.

In “top of hierarchy” select user who is on top of your organization structure (or for the team you are creating forecast for).

Final setting will look something like below image,

Once you have selected hierarchy and entity.. Select the schedule of your forecast, it can be quarterly or monthly

Step 2 : Permission

Next step is to set up security roles and owner filed, you don’t really have to change the User lookup field but in case you have a scenario where ownership of the opportunity is defined by another user lookup field you should change that.

Now Security roles are the key factor to control who should see the forecast , choose your security roles from different option, I would suggest choosing third option “Specific Security Roles” for controlled access.

Here is detailed information about these three options

  • No additional security roles : Security field users can see the data that belongs directly to them or their descendants and can make adjustments.
  • All security roles : All users are able to see the full forecast; only security field users can make adjustments to their own forecast and their descendants.
  • Specific security roles: Security field users can see the data that belongs directly to them or their descendants and can make adjustments.

Click Next for next step

Step 3 : Layout

In layout you can choose which column to display on your sales forecast view. You can only chose option set value. Based on the scenario you can pick either Forecast , sales pipeline or any other option set category (Just make sure that option set is mandatory to fill so that no opportunity get missed in calculation)

By default Quota and Prediction will be available select them.

You have to Add an option list to display value. you can select any field but I have selected Forecast category (It’s made for each other anyway 😀 )

Step 4 : Add Additional Filter

If you have some specific filter criteria to filter out opportunity (or the record which is being used in forecast in case it another entity )

Step 4 : Drill Down

You can also select related entity to drill down the forecast further, I find this feature most useful , because you alway need something to drill down to see what other factor are driving your sales

You have three option (if you chose opportunity )

  • By Account
  • By Product
  • By Parent Product

Step 5 : Snapshot

Snapshot help you to visualize trend of the sales , Snapshots are automatically taken daily during a six hour window assigned to each forecast. , by default it is disabled , turn it on.

Step 7 : Advance

Auto-hide duplicate rows that appear directly under parent rows when there is no quota, simple columns or underlying records available to show for that row

Step 8 : Activate & add Quotas

Activate your forecast by clicking on the activate button , It will take some time (Mine took more than an hour so have some patience.

After activating your forecast quota data section will get activated for quota upload. download the template by clicking on the “Download simple data column template” , it will provide you an excel with child and parent user record pre populated , all you need is to put quota in their respective column

Download your excel template from here

This is an sample excel which is generated for quota upload , it will have all the user which falls under the top level user in hierarchy we selected earlier

Want to know how I embed this excel in wordpress ? Read here Embed OneDrive for Business Document in PowerApp Portal in Five Steps |PowerApp Quick Tip

Upload your quota and upload back and click on the finish button

Get insight

Sales Forecast Grid

Once you have set everything up , all you need is to wait for the opportunity to progress and based on the revenue and opportunity history you can see details like this.

Organization Sales Forecast View in Grid Format


Based on the won loss ratio of past opportunity prediction will be calculated automatically.


Trend chart will allow you to see timeline of sales forecast

There are lots of other thing too since this post is already too long I’ll cover flow and Kanban in another post


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: