Results 1 to 15 of 26

20160220, 16:10 #1
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
Trying to calculate income spread across months
I have a sheet that looks like the below. We close a sale and 30 days later we start work. I want to spread the sale amount over four months (average time across varying size projects) for cash flow analysis. And show the total expected to invoice for each month based on that. I show in the example only a few months. Hope you get the picture. I'm looking for a formula for the second row to do the calculation. I'm getting nowhere with this!
I see the table isn't showing so adding an attachment.
Last edited by bdesilva; 20160220 at 16:25.

20160220, 16:32 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,458
 Thanks
 29
 Thanked 61 Times in 57 Posts
Not sure I fully understand.
Do you want the first calc in column D row 2 to be: =0.25*SUM($C$4:C4) ? Then, fill that across 3 columns.
Then, in G: =0.25*SUM(C4:$E$4) and fill that across three?Last edited by kweaver; 20160220 at 16:38.

20160220, 17:09 #3
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
Was hoping to figure out how to do it all in one formula, one row, handling how many ever months there are. Something that could count the number of times the sales amt had been used then drop it.
Last edited by bdesilva; 20160220 at 17:14.

20160221, 06:57 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
how about a formula like:
=SUM(INDIRECT("R4C" & COLUMN()4,FALSE):INDIRECT("R4C" & COLUMN()1,FALSE)) / 4
Put this in row 5 (or some other row) and fill across.
The only (?) problem with the formula is that you can't start it before column E (ie, it won't work in columns AD) because the "COLUMN()4" part looks 4 columns to the left of the present column. There is no column 4 columns to the left of D.
An easy solution would just be to start your data so that Jan is in column E.
I think with a little more work, I could avoid that restriction also. Just need to work on the "4" part a little. But it's early here and my brain still isn't fully awake.
Fred

20160221, 07:13 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
OK, it's 5 minutes later and the brain is starting to function.
To adjust for the 4, try:
=SUM(INDIRECT("R4C" & COLUMN()IF(COLUMN()<5,COLUMN()1,4),FALSE):INDIRECT("R4C" & COLUMN()1,FALSE)) / 4
Personally, I think this is messy just to account for the fact of where you started your data. I'd move the column of labels to col D so that your first month's data is in col E. But that's me.
Also, if for whatever reason you decide to do away with the column of labels and start your data in col A, then the "1" part towards the end would also become a problem. Sounds like that won't happen but I think you can see how to adjust the formula if you decide to do that.
Fred

20160221, 10:25 #6
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
ok. very cool. thanks. now I get to try and figure out how this works!
it's a bit hard to move the data as it gets swept into our forecast tool, but I'll try moving everything to E and see if I can remap the import

20160221, 12:18 #7
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
I don't know if you saw my second post.
There is no need to move the data to start in col E if you use the revised formula of my second post.
You mentioned in post #3 that you wanted a single formula, so this will do it.
But with your data beginning in col C but you wanting the previous FOUR months, that creates a problem (referring back 4 cols) until you get to col E.
For example, if your data began in col C but you only wanted to go back 2 cols (sum up cols A and B), there would be no problem. If your data began in col E but you wanted to sum up 5 months, there would be a problem.
The problem, generally, is that you can NOT go back past (to the left of) col A.
From a purist point of view, the formula of post #5 is a bit messy. Compare the formula in post #4 vs #5 (when I was awake). The only difference is that 4 in the first one is replaced by that
IF(COLUMN()<5,COLUMN()1,4)
Note what the IF does: if the column where you want to start the sum is less than 5 (columns, although usually identified by a letter, can also be referenced by the corresponding number  col A is 1, col B is 2, etc) meaning to the left of E, then we do NOT subtract 4 since that would take us back beyond the left of col A (which is NOT allowed, equivalently a col with a number of 0 or less). Instead we start the sum from col A (or the first col).
If the column where you want to start the sum is 5 or more (col E and beyond to the right), then the test
COLUMN()<5
is false, and the result of IF will always give 4.
So if you look at the 100th or 952nd or 2500th col, the IF is always FALSE and you're always starting the summing 4 cols to the left of the present column. At this point, you might wonder why the IF is there, rather than just a 4.
The present column where you're putting the formula has a col given by COLUMN(). We don't have to actually know which column we're in to reference the current column.
So, I think your options are
1) move the labels to col D and start the data in col E; then you can use the formula in post #4 EVERYWHERE.
At this point, it could also be simplified to something like
SUM(A4: D4)/4 for col E.
2) if you want to keep the labels in col B, then
2A) use the formula in post #5 everywhere or
2B) use the formula in post #5 for just col D (and col C if you want) but then use the formula in post #4 from col E forever to the right.
As to the rest of the formula, the 2 INDIRECTs are putting together a starting point and an ending point for summing. Since these change with every column, you can't have a fixed column reference. That's what the COLUMN()4 and COLUMN()1 do. When added to the end of (concatenated with) the fixed string "R4C", you get something like R4C2 for a start and R4C5 for an end. (This is an alternative form of referencing cells called "RC" referencing, where you give the Row # after the "R" and the Col # after the "C" all as one string.)
Note that the 4 is because your sums are in Row 4. If you change your mind for this, you'd have to change the 4 to the proper row # (there are other alternatives than using a constant row # but those really don't seem to be needed here based on what you originally posted).
Hope this helps.
FredLast edited by fburg; 20160221 at 12:30.

The Following User Says Thank You to fburg For This Useful Post:
bdesilva (20160222)

20160221, 12:32 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,559
 Thanks
 111
 Thanked 620 Times in 565 Posts
bdesilva
A VBA approach. Here is some code that will update the invoice values with no formulas when a new sales value is entered. The values will also be updated if you remove the values. Place the code in a worksheet module.
HTH,
Maud
Code:Private Sub Worksheet_Change(ByVal Target As Range) ' 'SETUP ERROR TRAPPING On Error GoTo Enable Application.EnableEvents = False ' 'DECLARE AND SET VARIABLES Dim LastCol As Integer, I As Integer, J As Integer Dim SalesRow As Integer, StartSalesCol As Integer, rng As Range SalesRow = 4 StartSalesCol = 3 LastCol = ActiveSheet.Cells(SalesRow, Application.Columns.Count).End(xlToLeft).Column ' 'CLEAR INVOICE VALUES PRIOR TO CALCULATIONS ActiveSheet.Range(Cells(SalesRow + 1, 3), Cells(SalesRow + 1, ActiveSheet.Cells(SalesRow + 1, _ Application.Columns.Count).End(xlToLeft).Column)).ClearContents ' 'LOOP THROUGH SALES AND UPDATE PAYMENTS OVER NEXT 4 MONTHS For I = StartSalesCol To LastCol If Cells(SalesRow, I) <> "" Then For J = 1 To 4 Cells(SalesRow + 1, I + J) = Cells(SalesRow + 1, I + J) + (Cells(SalesRow, I) / 4) Next End If Next I ' Enable: Application.EnableEvents = True End Sub

The Following User Says Thank You to Maudibe For This Useful Post:
bdesilva (20160222)

20160222, 08:51 #9
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
Thanks Maudibe. That's a cool approach I'll review tonight.
Thanks Fred so much for all that detail. I've learned a lot today!

20160526, 20:01 #10
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
expaning on calculating income spread across months
Hi Fred or anyone!
So this solution has worked very well and now it's time to improve. I need a kick start! The current formula looks at the past 4 months and uses 25% from each month to create the total. Perfect. But what if I want to have this more dynamic. Say spread the income 30%/30%/20%/20% instead. Or spread it out across five months instead of four. Can the formula be modified to do a lookup for number of months to use and % for each month?

20160527, 06:55 #11
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
I'm looking for the post I just made in response so I could edit it but can't find it. So I'll try again.
NOTE: I just found my previous post and deleted it. This new post is more direct.
In your original spreadsheet, you had sales for each month. One might assume that the total sales figures are the result of several sales.
So when you ask about changing the rule of "spread each month's sales over 4 months evenly", would it be safe to assume that you want to change the rule to the total sales? Doing otherwise (changing the rule on a persale basis) doesn't seem feasible since your original spreadsheet did not go to that level of detail.
Also, when you ask about changing the rule, I'd assume that the change would apply to all months. Is that true? Or would you want the sales of some months to be treated differently than for other months. In other words, could you have the sales in Jan spread over 4 months but the sales of Feb spread over 5 months? Same question applies to the percentages (sales for Jan treated evenly but for Feb treated as 30302020).
Lastly, do you want to be able to compare, in one spreadsheet, the cash flow for different rules? Or just apply a rule, look at the result, maybe print it out, and then just do another rule? It would seem that some place is needed to store the different rules regardless of your answer. It would also seem that a VBA solution might be needed.
Depending on your answers, I'm thinking this is getting beyond my skills (and it's still early here so my brain is not awake). But I'll bet that Maud, RG, Zeddy and others would be able to help.
FredLast edited by fburg; 20160527 at 07:00. Reason: deleted previous post.

20160527, 10:16 #12
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post

20160530, 11:54 #13
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
Maud, RG, Zeddy, fburg?? Any ideas appreciated. I'm just not getting anywhere on my own.

20160531, 08:11 #14
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Attached is a spreadsheet that only takes into account a change to the # of months over which the sales are counted. This looks like it should work but I only did limited testing. You should definitely do a bit more testing with your data.
Note I put in the # months in B1 (and put a label in A1). This is used in the calculations.
Also note that I did not account for any potential rounding effects. For example, if Jan sales = 100 and that is to be spread over 3 months, then Jan will be counted as 33.3333 for the next 3 months. This could be rounded but then each of the 3 months would be 33.33 or 33.34. Rounding so that the first 2 months are 33.33 and the last month is 33.34 would be harder.
I left the rows for your original explanation but moved these rows down just to be out of the way. However, they are no longer correct (or needed?) if the # months is not 4.
Being able to change the % allocation per month to NOT be the same each month (as opposed to keeping it equal based on the # months, which is what I did) would be harder  perhaps not even doable without VBA. Perhaps a modification to the code that Maud posted? I looked at his code and it doesn't look too difficult to modify for both features (# months, unequal %). However, I won't be able to get to either approach (unequal % with or without VBA) today.
Is there some # of MAXIMUM months over which the sales would be spread? I'm thinking of a nonVBA solution where I'd have "helper" rows (rows where intermediate calculations are done rather than trying to capture everything in 1 formulas) for each of those months. If the maximum number of months is NOT used, then those rows would be 0. Then there's be 1 more row to sum all the helper rows, which is what you want. If necessary, the helper rows can be hidden.
Fred

20160531, 18:35 #15
 Join Date
 Jan 2003
 Location
 Greeley, Colorado
 Posts
 188
 Thanks
 8
 Thanked 1 Time in 1 Post
Arrg. Swear I answered this ten minutes ago but cannot find it!
Thanks again Fred. I've added the change to my model and it works. I'll take a lot at the VBA tonight and see what I can make of it. Haven't done programming stuff for over a decade though!
For the short term I'd say the maximum spread will be 9 months. Ultimately I'd like to change it based on what accounting shows really happens post forecast but I haven't done enough research to know what that is! So if I could spread 49 months that'd be enough for 2016!