How to manipulate sales pipeline data to display a chart of prospect value by stage/date

Hi, I'd REALLY appreciate any suggestions on how best to organise the datasheet for a sales pipeline dashboard. I specifically want to be able to report on the number and value of sales leads in the pipeline at any point in time, by sales stage. And to compare that between periods. E.g. how many active sales leads do we have in the pipeline for Q1 2023 vs Q1 2022.

There are several sales stages e.g. 0_Early_Stage_Prospecting, 1.Requirements&Quote, 2.SubmittedforApproval, 3.Approved, 4.NotApproved, 5.ProposaltoClient25, 5.ProposaltoClient50, 6.NegotiationFinalStage, 7.Sold, 8.Contracted, 9.QuoteRejected

Right now the data is structured so that each sales opportunity is a row in a spreadsheet and it includes the date it entered the sales pipeline and then the number of days at each of the sales stages.

I can't work out how to manipulate the data format to put it into a structure that then allows comparison reporting from one period to another, or at any point in time.

In the attached workbook I've given an example on the tab 'Before. Raw Data' of what the data looks like from our sales pipeline. I've included 20 dummy rows of data. On the tab 'After. Example Reports' I've manually created one of the reports we might produce i.e. a stacked area chart showing the value in USD of the sales opportunity by sales stage and date. I had to manually build the data table feeding this example chart from the dummy data on the first tab, but I'm looking for a way to be able to be able to manipulate the data into the correct format.

Many thanks indeed!

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Administrative Note:

Welcome to the forum.

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Waiting for a sample workbook and a profile update.

This formula based proposal employs a lot of helper columns on the Before. Raw Data sheet.
Columns R:AB are populated using: =IF(G3=0,"",SUM($B3,$G3:G3))
Columns AC:KD are populated using:

Formula:

=IF(AC$1<$B3,"",IF(AC$1=$B3,INDEX($R$2:$AB$2,AGGREGATE(15,6,(COLUMN($R$2:$AB$2)-COLUMN($Q$2))/($R3:$AB3<>""),1)),IFERROR(INDEX($R$2:$AB$2,AGGREGATE(15,6,(COLUMN($R$2:$AB$2)-COLUMN($Q$2))/($R3:$AB3<>"")/($R3:$AB3>AC$1),1)),AB3)))

On the After. Example Reports sheet columns AB:AM are populated using: =SUMPRODUCT(('Before. Raw Data'!$F$3:$F$22)*('Before. Raw Data'!$AC$1:$KD$1=$AB3)*('Before. Raw Data'!$AC$3:$KD$22=AC$2))
Note that some formula results differ from those in columns A:L. I have set conditional formatting to highlight the differences, which start in row61, in yellow.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. If the solution works on your real datasheet, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.