Here is a dataset showing Project wise forecast of open opportunities.

1. Topic is the Project Name
2. Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client
3. Duration is the time (in months) for which the project would run
4. Amount is the total amount that would be billed for that project

Clients are invoiced annually only. So in the example below:

1. 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.
2. 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 ABC 01-10-2017 24 1,000,000 GEF 01-02-2018 18 2,000,000 XYZ 01-03-2018 30 1,000,000

The expected result should look like this:

 Row Labels Oct-17 Feb-18 Mar-18 Oct-18 Feb-19 Mar-19 Mar-20 Total ABC 500,000 500,000 1,000,000 GEF 1,333,333 666,667 2,000,000 XYZ 400,000 400,000 200,000 1,000,000 Grand Total 500,000 1,333,333 400,000 500,000 666,667 400,000 200,000 4,000,000

I have solved this problem using Power Query and PowerPivot. You may download my solution workbook from here.

• Purpletiger says:

Ashis:

• Hi,

• Dylan says:

Hi Ashish – I am really interested to see this but I can’t download it.
Error: This item might not exist or is no longer available
This item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information.