# Incremental Budget Forecasting based on Monthly Due Date Percentages

✭✭✭✭✭
edited 04/06/20

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!

«1

• ✭✭✭✭✭✭
edited 04/06/20

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

• ✭✭✭✭✭✭

Standby...

😜

• ✭✭✭✭✭✭

I have some sample sheets I created if you want me to share them to you :)

• ✭✭✭✭✭✭

@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! 😁

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!