Here is a dataset showing Project wise forecast of open opportunities.
- Topic is the Project Name
- Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client
- Duration is the time (in months) for which the project would run
- Amount is the total amount that would be billed for that project
Clients are invoiced annually only. So in the example below:
- Project ABC is for US$1 million with a duration of 24 months and is expected to be closed in Oct. 2017. We need to model the data to show the billing every 12 months. So for ABC US$500K would be billed in Oct-2017 and another US$500K in Oct-2018.
- Project GEF is for US$2 million with a duration of 18 months and is expected to be closed in Feb. 2018. We need to model the data to show US$1.3 million in Feb-2018 and another US$666K in Feb-2019. The monthly billing is US$2 million divided by 18 and then multiplied by 12 – this amounts to US$1.3 million.
|Topic||Est. Close Date||Duration (Months)||Amount|
The expected result should look like this:
I have solved this problem using Power Query and PowerPivot. You may download my solution workbook from here.