Incremental Budget Forecasting based on Monthly Due Date Percentages
In excel, we have two sheets:
Sheet 1 - A percentage breakdown per country based on historical data for each Month prior or after the Due Date month. Zero = Month Due Date. -1 = the Month prior to the Due Date Month, etc.
Sheet 2 - For Country, Due Date and Projected Total Spend, we calculate (via formulas) the Current Year Spend and Next Year Spend based on Sheet ONE percentage for each receipt month.
For example, if the Due Date is 11/1/2019 (row 3 above), what is the expected Spend in 2020 (current year)? The excel formula goes something like this…If the Month/Year Due Date is November 2019, then Pub Month is ZERO on SHEET ONE. Therefore +1 is December 2019, +2 is January 2020, +3 is February 2020, etc. The formula takes +2 to +12 (January 2020 – November 2020) forecast percentage on SHEET ONE multiply by total spend $1000. Therefore, the projected spend in Current year is 23% x $1000 = $230.
In Smartsheet, I see values for the formula below, but they do not match the excel results. I think I am using the wrong Smartsheet function. I noticed that in excel, the “:” between Index formulas collects a range of Sheet ONE data. I am unable to replicate this in Smartsheet.
The Smartsheet formula for Current Year Spend is:
=IF(YEAR([Due Date]1) = YEAR(TODAY(-365)), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (27 - MONTH([Due Date]1))), INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), 26))), IF(YEAR([Due Date]1) = YEAR(TODAY()), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (15 - MONTH([Due Date]1))), INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (26 - MONTH([Due Date]1))))), IF(YEAR([Due Date]1) = YEAR(TODAY(+365)), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (14 - MONTH([Due Date]1))), 0)))))
The Smartsheet formula for Next Year Spend is:
=IF(YEAR([Due Date]1) = YEAR(TODAY()), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (27 - MONTH([Due Date]1))), INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), 26))), IF(YEAR([Due Date]1) = (YEAR(TODAY()) + 1), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (15 - MONTH([Due Date]1))), INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), 26 - MONTH([Due Date]1)))), IF(YEAR([Due Date]1) = (YEAR(TODAY()) + 2), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL
COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (14 - MONTH([Due Date]1))), 0)))))
I could not get the COLLECT function to work. Any ideas on how to get the same results as excel?
Thank you so much for your help!
Best Answer
-
Ok. So my first solution used 50 helper columns in sheet 2.
My second solution uses 29 helper columns and a helper row in sheet 2 and a helper row in sheet 1.
The reason for so many helper columns is that all ranges within a function must match in size.
I have a published version of SHEET 1 and a published version of SHEET 2.
Sheet 1 is the sheet containing our percentages. In this sheet we added a helper row across the top that essentially replicates the -12 through 12.
Sheet 2 is the calculation sheet. We add in 29 columns. 2 columns will be used to calculate the high and low for the current year and 2 columns are used to calculate the high and low for the next year.
So for a date of 6/30/2020, the current year span would be -5 through 6 and the span for next year would be 7 through 12.
We can use these highs and lows to reference the remaining 25 helper columns. In these columns is where the helper row on this sheet come into play. Again we replicate the -12 through 12 in row 1. This allows us to reference Sheet 1 and pull percentages for each of the months based on the country. We can go ahead and nest pulling this percentage into a formula that multiplies it by the total amount.
What this accomplishes is providing how much of the total would be spent for each of the months -12 through 12.
Finally we use a formula that leverages our highs and lows to sum everything between these two numbers across those 25 columns.
The formulas themselves are displayed on the sheet. The data used replicates the data in your screenshots.
Feel free to plug all of this mess in and see how it works for you.
Answers
-
Sorry Emily - I went to answer, but realised after I'd posted that what I'd found was what you already know and doesn't progress you on! I am still looking into this but not sure if you are going to get what you want it to do...
-
Thanks Debbie for the reply, would it help if I gave you the working excel formula?
-
Hi Emily
I'm not sure it would to be honest. I understand you need to update a column reference in a range with a dynamic value. If only we could change the , to a : in the Sum function then it would work!
I've had a zoom call with a colleague and we can't work it out! Maybe @Paul Newcome or @Andrée Starå can come in with a solution! 😋
Here guys, what we need if for this bit in bold to be a range of cells instead of just 2 cells.
=IF(YEAR([Due Date]1) = YEAR(TODAY()), ([Projected Total Spend]1 * SUM(INDEX({SHEET ONE ALL COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (27 - MONTH([Due Date]1))), INDEX({SHEET ONE ALL COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), 26))), IF(...
So we want to change the comma from after the 3rd closed bracket and before INDEX # 2 to be a colon to make it a SUM(Here:There) rather than SUM(this cell, that cell)
At the moment it is summing" INDEX({SHEET ONE ALL COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (27 - MONTH([Due Date]1))) " (=in the case of November from the example above this equates to column 16 from the range) With "INDEX({SHEET ONE ALL COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), 26)))" (=Column 26 from the range). But instead of adding those two values together, the requirement is to use "INDEX({SHEET ONE ALL COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), (27 - MONTH([Due Date]1)))" as the syntax to start the SUM FROM and "INDEX({SHEET ONE ALL COLUMNS}, MATCH(Country1, {SHEET ONE COUNTRY}, 0), 26)))" as the syntax to the column within the range to SUM UP TO!
Is this possible?
Hope this helps Emily!! These guys are really good at complex formulae!
Kind regards
Debbie
-
-
I have some sample sheets I created if you want me to share them to you :)
Just let me know your email address and I'll send them over! (Save you reproducing)
-
@Debbie Sawyer I'll keep that in mind for sure. Thank you!
-
@EmilyH What are the chances you would be willing and able to add some "helper" columns that can later be hidden (to help keep the sheet looking clean)?
-
Hi @Paul Newcome, Yes! We can add (and hide) additional columns, if needed. Thanks!
-
@EmilyH Excellent. I am working on something now and will keep you posted.
@Debbie Sawyer I think I am going to build my own. It will help me get a better feel for exactly how everything needs to work together.
-
@Paul Newcome, would it help if I gave you the working excel formula?
-
It certainly wouldn't hurt to take a look at it.
-
For column “Current Year Spend”, the excel formula goes something like this:
=IF(YEAR(B2)=(YEAR(TODAY())-1),(C2*SUM(INDEX(Sheet1!$A$1:$Z$3,MATCH(Sheet2!A2,Sheet1!A:A,0),(27-MONTH(Sheet2!B2))):INDEX(Sheet1!$A$1:$Z$3,MATCH(Sheet2!A2,Sheet1!A:A,0),26))),(IF(YEAR(B2)=YEAR(TODAY()),(C2*SUM(INDEX(Sheet1!$A$1:$Z$3,MATCH(Sheet2!A2,Sheet1!A:A,0),(15-MONTH(Sheet2!B2))):INDEX(Sheet1!$A$1:$Z$3,MATCH(Sheet2!A2,Sheet1!A:A,0),(26-MONTH(Sheet2!B2))))),(IF(YEAR(B2)=(YEAR(TODAY())+1),(C2*SUM(INDEX(Sheet1!$A$1:$Z$3,MATCH(Sheet2!A2,Sheet1!A:A,0),2):INDEX(Sheet1!$A$1:$Z$3,MATCH(Sheet2!A2,Sheet1!A:A,0),(14-MONTH(Sheet2!B2))))),0)))))
B2 = Due Date
C2 = Projected Total Spend
-
We aren't going to be able to replicate that formula directly. We are definitely going to need some helper columns. I have a few ideas. I have one worked out and tested, but I am going to see if I can make it more simple as right now the solution I have worked up is rather bulky.
-
That would be great, thanks so much! 😁
-
Ok. So my first solution used 50 helper columns in sheet 2.
My second solution uses 29 helper columns and a helper row in sheet 2 and a helper row in sheet 1.
The reason for so many helper columns is that all ranges within a function must match in size.
I have a published version of SHEET 1 and a published version of SHEET 2.
Sheet 1 is the sheet containing our percentages. In this sheet we added a helper row across the top that essentially replicates the -12 through 12.
Sheet 2 is the calculation sheet. We add in 29 columns. 2 columns will be used to calculate the high and low for the current year and 2 columns are used to calculate the high and low for the next year.
So for a date of 6/30/2020, the current year span would be -5 through 6 and the span for next year would be 7 through 12.
We can use these highs and lows to reference the remaining 25 helper columns. In these columns is where the helper row on this sheet come into play. Again we replicate the -12 through 12 in row 1. This allows us to reference Sheet 1 and pull percentages for each of the months based on the country. We can go ahead and nest pulling this percentage into a formula that multiplies it by the total amount.
What this accomplishes is providing how much of the total would be spent for each of the months -12 through 12.
Finally we use a formula that leverages our highs and lows to sum everything between these two numbers across those 25 columns.
The formulas themselves are displayed on the sheet. The data used replicates the data in your screenshots.
Feel free to plug all of this mess in and see how it works for you.
Help Article Resources
Categories
Check out the Formula Handbook template!
