Sales pipeline - Pivot table?

[JBK123]

Hi guys,

I am trying to create sales projections by customer over the next few years and I am currently stuck.

Example Data below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client name[/TD]
[TD]Salesman name[/TD]
[TD]Revenue per month[/TD]
[TD]Contract open[/TD]
[TD]Contract Close[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]Salesman A[/TD]
[TD]2,000[/TD]
[TD]March 2018[/TD]
[TD]December 2018[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]Salesman A[/TD]
[TD]2,500[/TD]
[TD]May 2018[/TD]
[TD]December 2018[/TD]
[/TR]
</tbody>[/TABLE]

I want to create a pivot table which would look like the below but I want to keep my original data set as basic as possible. I realise this is unlikely but hopefully I am missing something. The reason I want to use a pivot table is because I want to add slicers and timelines to analyse the sales projections quickly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]2,000[/TD]
[TD]20,000[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]2,500[/TD]
[TD]20,000[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for any help!

 

[ranman256]

that should work,
drag Client to Row labels
drag ContractOpen to column labels
Revenue in Sum

 

[JBK123]

Using this method I get 2,000 in March and 2,500 in May rather than the continuous revenue in the months following.

There must be a work around, I just can't think of one.