Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    2 Star Lounger
    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.

    Attached Files Attached Files
    Last edited by bdesilva; 2016-02-20 at 17:25.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    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; 2016-02-20 at 17:38.

  3. #3
    2 Star Lounger
    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; 2016-02-20 at 18:14.

  4. #4
    Silver Lounger
    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 A-D) 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

  5. #5
    Silver Lounger
    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

  6. #6
    2 Star Lounger
    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

  7. #7
    Silver Lounger
    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.
    As you fill to the right, Excel will adjust the formula to SUM(B4:E4)/4 for col F, then SUM(C4:F4)/4 for col G, etc.

    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.

    Fred
    Last edited by fburg; 2016-02-21 at 13:30.

  8. The Following User Says Thank You to fburg For This Useful Post:

    bdesilva (2016-02-22)

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 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
    Attached Files Attached Files

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    bdesilva (2016-02-22)

  11. #9
    2 Star Lounger
    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!

  12. #10
    2 Star Lounger
    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?

    Quote Originally Posted by fburg View Post
    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

    As you fill to the right, Excel will adjust the formula to SUM(B4:E4)/4 for col F, then SUM(C4:F4)/4 for col G, etc.

    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.

    Fred

  13. #11
    Silver Lounger
    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 per-sale 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 30-30-20-20).

    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.

    Fred
    Last edited by fburg; 2016-05-27 at 08:00. Reason: deleted previous post.

  14. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Right. I'm not trying to make a big leap. So not project by project and same rules for entire forecast. Just total sales for the month like last time. In line answers below in CAPS

    Quote Originally Posted by fburg View Post
    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. TRUE

    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 per-sale basis) doesn't seem feasible since your original spreadsheet did not go to that level of detail. CORRECT. MAYBE BY TYPE OF PROJECT NEXT YEAR

    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 30-30-20-20). SAME NUMBER MONTHS FOR ENTIRE FORECAST

    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. I'M GOOD WITH ONE SHEET, ONE RULE, ONE FORECAST.

    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.

    Fred

  15. #13
    2 Star Lounger
    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.

  16. #14
    Silver Lounger
    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 non-VBA 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
    Attached Files Attached Files

  17. #15
    2 Star Lounger
    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 4-9 months that'd be enough for 2016!

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •