Projecting Forecasted Business over Future Months
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.
Answers
-
Are you able to provide a mocked up example with data manually entered to show how you want everything laid out and displayed?
-
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
-
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
-
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.
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives