Forecast sales based on whole year revenue prediction

[Anonymous]

Hi,

I would like to create a line chart which shows the revenue forecast based on oppertunities made by accountmanagers. 

My data is as follow:

Opportunity	Accountmanager	Customer	Estimated first delivery	Revenue (year)	Probability
Opportunity A	Accountmanager A	Customer A	21-5-2018	1000	80
Opportunity B	Accountmanager A	Customer G	28-8-2018	2000	70
Opportunity C	Accountmanager A	Customer B	12-4-2018	3000	90
Opportunity D	Accountmanager A	Customer H	6-11-2018	4000	30
Opportunity E	Accountmanager A	Customer C	11-8-2018	2500	60
Opportunity F	Accountmanager B	Customer I	1-6-2018	3500	20
Opportunity G	Accountmanager B	Customer D	1-7-2018	4500	90
Opportunity H	Accountmanager B	Customer E	1-2-2019	500	100
Opportunity I	Accountmanager B	Customer F	1-8-2018	8000	70

The revenue is based on a whole calender year.

I would like to create a line chart which shows the expected progress in revenue in 2018 by Month

E.g. Oppertunity A has an estimated Revenue of 1000 in one calender year. The Estimated first delivery is 21-May-2018. So the Revenue that is expected for 2018 = (1000/365)*224=613,7 

In May Oppertunity A will make a revenue of (1000/365)*10=27,4, in June 82,19, in july 84,93 etc.

I hope someone can help me with this. 

If any questions or requests please ask.

Regards,

Guido

[v-yuta-msft]

Hi Guidoow1,

Could you please clarify more details about your definition of expected revenue? For example, you said “In May Oppertunity A will make a revenue of (1000/365)*10=27,4, in June 82,19, in july 84,93 etc.”, what does the number “10” mean and how to achieve this number?

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft sure!

The oppertunities are for example restaurants. 

The expected revenue is the total revenue from a restaurant for 1 calender year. 

In Oppertunity A the estimated first delivery is 21 May 2018, meaning they will create revenue in may for 10 more days.

In June the restaurant will create revenue for 30 days in July for 31 etc..

Oppertunity A has a total expected reveneu of 1000 in 1 calander year. 

1000/365 days a year = 2,74

2,74 * 10 days left in May = 27,4

2,74 * 30 days in June = 82,19

2,74 * 31 days in July = 84,93

I hope this makes it more clear

Regards,

Guido

[Anonymous]

Anyone that could help me with this, please?

Still did not find a solution