How to divide values across days/weeks/months and then sum by year?

Peter Hui

Hello,

Good day.

I'd like to ask your help in a task I'm trying to complete. I've tried several methods but can't quite get the result I was hoping for.

I'm tracking agreements with varying validity periods and $ values. I'd like to take the total value of a contract, divide it across the validity period and then sum the $ values by year. The assumption is that any $ value in the past (including today) will be considered actual and anything in the future will be forecast. Below is a screenshot of what data looks like:

I'm trying to build a sheet that will take the data above and sum the $ values per year, with $ values of agreements spanning multiple years being divided equally. The $ values in the current year will be split between actuals and forecast as the days move along.

Finally, the data would be presented in a dashboard chart with a date range covering the current year, the year before and after and any other previous/future periods grouped together. The years on the X-axis will be dynamic and change every year. The current year will show both actuals and forecast and will also change as the days progress. Below is a screenshot of what the chart will look like.

(Please note that the data in the first screenshot do not match the chart. They are just representations of what the data and chart might look like.)

Hope you could share your inputs.

Thank you.

Best regards,

Peter

Paul Newcome

Would you be able to provide an example based on the data in the first screenshot that shows the breakdown and how you would want these totals laid out?

Peter Hui

Hello Paul,

Thanks for responding.

The first screenshot is what the data I get looks like. It will be comprised of the vendor name, the total amount and the date range when the agreement will be in effect. Based on those information, I would need to divide the amount across the date range and compute how much of it has already been used (i.e. for part of the date range that is already in the past - termed as "actuals") and how much remains to be used (forecast). Then, the actuals and forecast will be summed at year level for all the vendors.

Below is another example that shows the process flow:

• Here is a sample of raw data that I receive.

• The data would then be processed where the amount will be divided across the date range and Actuals/Forecast will be computed. I imagine this would be done in another sheet. As we use a rolling date range in the calculation, the Actuals and Forecast were computed based on today's date - May 13, 2021. (Note: If you try to do your own calculation, there might be some differences in the values due to rounding or human error.)

• Finally, the data will be presented in a dashboard chart where the date range is grouped into 5 buckets: "current year", "current year+1", "current year-1", "> current+1", "< current year-1". The date range in the chart will also be dynamic. For this example, current year will be 2021. Any data from 2019 or earlier will be added and put into the bucket "< current year-1", while those that are for 2023 onwards will be added and placed in the "> current year+1" bucket.

Hope this helps explain the situation better.

Please let me know if there's any other information needed.

Thank you.

Best regards,

Peter

Peter Hui

Hello Paul,

To keep it consistent, below is an updated screenshot of the chart with the same chart type as the one in my first post. Data is still the same.

Thanks again.

Best regards,

Peter

Peter Hui

Hello Paul,

Just another clarification. In the second screenshot, the amounts were divided into years then further grouped into "current year", "current year+1", "current year-1", "> current+1", "< current year-1". When configuring the sheet in Smartsheet, breaking down the amounts for each year will not be necessary. They can immediately be grouped into "current year", "current year+1", "current year-1", "> current+1" and "< current year-1" as in the third screenshot. I showed the break down per year in the second screenshot just to illustrate the calculation logic but ultimately, they would only need to be summed up by "current year", "current year+1", "current year-1", "> current+1" and "< current year-1", with the amount in the current year broken down between Actual and Forecast based on the current date/today's date at the time it is viewed.

Apologies for any confusion caused.

Best regards,

Peter

Paul Newcome

Ok. I am going to have to play around with this a little bit and get back to you. I know it can be done, but I want to make sure I have the exact formulas to be used for you.

Peter Hui

Thank you so much, Paul.