Projecting Forecasted Business over Future Months

Steve@PGI · 02/26/21

I am looking for a creative solution for a business reporting situation. I am working with a customer that wants to see their annual volumes from sales distributed evenly across those 12 months. For example, they are forecasting a sale in March that is expected to start producing volume (let's say 12,000 lbs.) on May 1. They want to then evenly spread that volume across the next 12 months. That prospect/sale will be combined with all other business, so they can visualize their monthly volume on a dashboard (maybe delineated by forecasted and confirmed sales).

I can't think of how to achieve this and was hoping that someone out there may have solved something similar in the past.

Paul Newcome · 02/26/21

Are you able to provide a mocked up example with data manually entered to show how you want everything laid out and displayed?

Steve@PGI · 02/27/21

Paul,

Thanks for the response. Below is a simplified Excel version of what I am looking for. I would also then like to display it on a dashboard on a chart if possible. I would love to be able to do this for my customer, but have already told them we may not be able to do it. I welcome any ideas.

Steve

Paul Newcome · 03/01/21

I am going to suggest two helper columns on the customer sheet and one helper column on the summary sheet.

On the customer sheet, both will have the same formula but one will reference the Start Date and the other will reference the end date.

=VALUE(YEAR([Start Date]@row) + "" + IF(MONTH([Start Date]@row< 10, "0") + MONTH([Start Date]@row)

On the summary sheet you are going to want to enter the month and year in yyyymm format on each row.

Then to get each monthly total, you would use something along the lines of...

=SUMIFS({Annual Volume}, {Start Date Helper}, @cell <= [Helper Column]@row, {End Date Helper}, @cell >= [Helper Column]@row) / 12

Steve@PGI · 03/02/21

Thanks for the help again @Paul Newcome! I will take a closer look at this later this afternoon. It would be awesome if this works. My customer really wants to see their data this way.

Paul Newcome · 03/02/21

Happy to help. 👍️

Projecting Forecasted Business over Future Months

Answers

Categories