Gradually Increasing a Weekly Numbers

[SullivanNJD]

Hello! hoping to get a little help here. We had created a monthly forecast/budget and recently began tracking everything on a weekly basis, so I essentially turned the total month number into weekly numbers on a straight line basis. This resulted in many weeks having the same amount, due to the straight line.

I'd like to have it set up so that each week gradually increases (i.e last week is less than current week and so on) while still keeping our total amount the same. I'm sure theres an easy way to do this that I am missing.

Thanks!

 

[Trebor76]

Hi SullivanNJD,

Welcome to MrExcel!!

You could have another tab where you list your revenue and expense account numbers down Col. A their total in Col.B and then their weekly allocations across columns C to F slowly increasing to 100 percent like so for instance:

12% 20% 30% 38%

You would then use VLOOKUP to multiply the forecast/budget by each percentage from its week/column. So say your allocations are in Sheet1 starting from cell A2 and the account, amount and allocations were in Sheet2 across columns A to F, you'd use these formulas in Sheet1 (change the references to suit):

Cell B2: =VLOOKUP($A2,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A2,Sheet2!$A:$F,3,FALSE)
Cell B3: =VLOOKUP($A3,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A3,Sheet2!$A:$F,4,FALSE)
Cell B4: =VLOOKUP($A4,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A4,Sheet2!$A:$F,5,FALSE)
Cell B5: =VLOOKUP($A5,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A5,Sheet2!$A:$F,6,FALSE)

Hope that helps,

Robert

 

[SullivanNJD]

Hi SullivanNJD,

Welcome to MrExcel!!

You could have another tab where you list your revenue and expense account numbers down Col. A their total in Col.B and then their weekly allocations across columns C to F slowly increasing to 100 percent like so for instance:

12% 20% 30% 38%

You would then use VLOOKUP to multiply the forecast/budget by each percentage from its week/column. So say your allocations are in Sheet1 starting from cell A2 and the account, amount and allocations were in Sheet2 across columns A to F, you'd use these formulas in Sheet1 (change the references to suit):

Cell B2: =VLOOKUP($A2,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A2,Sheet2!$A:$F,3,FALSE)
Cell B3: =VLOOKUP($A3,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A3,Sheet2!$A:$F,4,FALSE)
Cell B4: =VLOOKUP($A4,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A4,Sheet2!$A:$F,5,FALSE)
Cell B5: =VLOOKUP($A5,Sheet2!$A:$F,2,FALSE)*VLOOKUP($A5,Sheet2!$A:$F,6,FALSE)

Hope that helps,

Robert

Thanks - are you suggesting to run the increasing allocations on the monthly amounts or will it work on the weekly numbers as I showed in the image I uploaded?

 

[Trebor76]

The monthly amount would go into cell B2 and its weekly allocation in cells C2:F2 of Sheet2.

 

[SullivanNJD]

The monthly amount would go into cell B2 and its weekly allocation in cells C2:F2 of Sheet2.

Got it thanks - I think the tricky thing for me is dealing with the "overlapping" weeks because we analyze Monday-Sunday. For example the week beginning April 27 is 4/27 - 5/3. Because we budgeted on a monthly basis - that week weights the April and May budgeted amounts, if that makes sense

 

[Trebor76]

That does complicate things. Might need a formula in cell B2 of Sheet2 proportioning out the budget amount based on the the number of days for that week i.e. 4/30 x April budget total + 3/31 x May budget total and use that the amount you then allocate.