+ Reply to Thread
Results 1 to 20 of 20

Billing Forecast

  1. #1
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Exclamation Billing Forecast

    Please see attached file. I want to allocate (projected $/Amount left to bill) on future months per a pre-identified spending curve. How to use (IF) function to capture the correct % to the right field.

    For example: $1M left to finish building a new home (opening @ 10/30/10). How to spread the remaining $1M on future months (assuming last invoice will be paid 3 months after opening).
    Attached Files Attached Files
    Last edited by elfiky; 09-12-2010 at 03:13 AM. Reason: Poor Title

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: IF Function?

    edit: the title of your thread when I started replying seemed perfectly ok - the current title is not - "Billing Forecast" would be appropriate - please edit accordingly

    If I've understood...

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 09-08-2010 at 04:29 AM.

  3. #3
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: IF Function?

    Thanks for the feedback. It helps, but not exactly.

    You allocation of (Billing Forecast) was an even distribution after the 1st. month. It doesn't match the spending curve.

    Is there a way around it. Even if I have to change the monthly headers on a monthly basis

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: IF Function

    The even distribution for the ATM lines is based on the fact that your % allocation is even - ie 10% per calendar month.

    If you look at the New Store lines you will note the distribution is not even.

    The first month is always in effect an adjustment of sorts - ie difference between total presently paid and total expected to have been paid in the month specified (based on start date etc...)

    If you change your title as requested (suggested title in my 1st post) we can investigate further.

  5. #5
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    I've changed the title. Awaiting your feedback. Thanks

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    You have my feedback.

    Your spending curve for ATM is constant hence the distribution is constant.
    Similarly if you review New Store you will find the distribution is not constant given the % allocation increments are not constant.

    If you feel you're getting odd results post back with some manually calculated results so we can try and determine the logic you're applying.

    I applied the following logic: [Total to be Billed] * [% expected at given point in time - per header month and start date] - [payments made up to prior period]

  7. #7
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    Thanks. Two last questions plz.

    Plz see attached.
    1- What did I do wrong from your suggested formula (matrix AA4 - AP1131)
    2- I don't understand the last portion of your formula (SUM($J16,$Q16:Q16,-$Q16)))
    Cell Q16 is repeated 3 time (% billed). Is that correct?

    Kindly advise. Thanks

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    Quote Originally Posted by elfiky
    What did I do wrong from your suggested formula (matrix AA4 - AP1131)
    Above makes little sense to me I'm afraid - where did that range reference appear from ?

    Quote Originally Posted by elfiky
    I don't understand the last portion of your formula (SUM($J16,$Q16:Q16,-$Q16)))
    Cell Q16 is repeated 3 time (% billed). Is that correct?
    Yes it's correct.

    As to the why...

    In order to calculate the periodic balance for the current period you need to calculate:

    Accumulated Total Expected end of Current Period less Total Amount Accumulated Prior to Current Period

    So in order to sum prior amounts for months 0 to n-1 in Col R (month 1) you must subtract from Col Q to Col Q
    It follows that in month 2 you subtract the aggregate of Col Q to Col R, month 3 Col Q to Col S etc...
    (Col J is an opening balance type figure)

    We know that in reality Col Q (our month 0 in effect) stores a % value which is totally unrelated to the accumulated amount.
    Given % is a number it will impact our result unless we immediately discount it which we do by adding it back again in the same calculation.

    In short we use $Q16 as a means to create an appropriate dynamic range.
    Last edited by DonkeyOte; 09-08-2010 at 06:30 PM.

  9. #9
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    The matrix is per the new file (attached). Thanks in advance for your feedback
    Attached Files Attached Files

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    I will look at this tomorrow but at first glance the following issues jump out:

    a) you have rows whereby you owe (ie Col AC < 0)

    b) you have rows in which you have an amount left to bill but where column header month less start month sits outside the boundaries of your spend curve [which runs for 17 months: -11 open to +5 open]

    c) your INDEX range to the spend curve does not incorporate all rows
    You need to outline what you want to happen in both of the above scenarios

    There are probably other issues also but I shan't look in depth until tomorrow.
    Last edited by DonkeyOte; 09-08-2010 at 06:57 PM. Reason: edit: added c)

  11. #11
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Arrow Re: Billing Forecast

    I corrected the formula in the spreadsheet (attached). I found two errors when I pasted into my report.

    However, there are 3 issues that I do look for a solution for it. (I'll send you $30 for your time & effort in solving it. I can paypal you to your email address or send u a check).

    Issues:

    1- REF# Error: How to show it as (Zero)? These (REF# error) are for project outside our boundary range (Opened in the past) &/or have BLANK ODD - column (P)

    2- VALUE Error: How to fix it? These are for projects with future ODD (column P). These projects should have cash flow effect in future months. Yet, it is showing as (VALUE). For example: Row # 1061 - ODD (10/31/2011). This project should have $ values starting (ODD-11) - per spending curve

    3- Logic behind spreading ($ left to bill):
    I got your logic. However, there is one drawback for it. If ACTUAL spend was way more than forecasted spend at report date, then report will show BLANK projected spend for future months till it catch up.While in fact, we're rest assured, $ will be spend in future months.
    For example: $1M project to be opned in 3/31/11. Our spending curve says (ODD-7) is @ 7% cumulative. Yet, actual spend as of 8/30/10 (ODD-7) is @40% ($400K). Your formula will prjected spend for this project with ZERO $ till ODD-3, till it catches up @ ODD-2.

    This won't be realistic. Is there a way to adjust allocating projected $? I am thinking of always comparing what it should be to the actual & prorate this varince. Per our example: The variance of $-330K ($400K-$70K) can be allocated per spending curve & then be deducted from our projected spending curve. For example: If ODD-5 should be $50K, then we deduct the prorated share of the overage (lets says -$10K), then ODD-5 will be projected $ $40K vs. nothing..............so on & so forth till we reach the end of the spending curve.

    Make sense? -I am attaching the report.
    Waiting for your reply
    Attached Files Attached Files

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    Both Issues 1 & 2 can be circumvented using an IFERROR handler (given use of XL2007)

    Please Login or Register  to view this content.
    Though note the #VALUE! errors arise not only as a result of of invalid precedents (ie outside spend curve) but also because on occasion you have non-numeric values in either/and/or Col C & P.

    Regards Issue 3

    That's a call you have to make and it has a fundamental impact on the above approach.

    In theory you could look to pro-rate the remaining balance in line with the remainder of the spend curve but I think that decision would really be determined by the nature of what it is you're calculating.

    In short if you come up with some logic you're prepared to adopt for apportioning the remainder then we (the board) can look to implement.
    In reality it's simply not worth us (the board) conjuring up some fantastical approach to do this given it's more than likely the logic will fall foul of some business case or other - and remember we know nothing of these rules.

  13. #13
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Arrow Re: Billing Forecast

    LAST Question & I'll label it (Solved).

    How to add (AND) to your formula? In addition to (=IF(SUM($U414)=0,0,), I want to say (AND if ($AC14=0) as well, then bring any projected monthly allocation to zero.

    There are few projects with (Zero) under column (AC- Amount left to bill). Yet, the formula is allocating $ for future montns (since it fits the spending curve boundaries). I want to avoid that by adding the above condition.

    Thanks in advance

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    If you are to test AC I'm not sure you really need to test U at all - ie I would change U to AC in existing formula.

    If for whatever reason you want to test both use an OR
    (edit: I used OR rather than an AND - as I think you're saying "if to be allocated" is 0 then 0 irrespective of U)

    =IF(OR(SUM($U3)=0,SUM($AC3)=0),0,...)

    The reason we're using SUM is so that we account for text values also should they exist without generting coercion errors
    we could equally use N( )

    Regards denoting as Solved - that's fine - but I was under the impression that the real crux of the problem was yet to be resolved... ie allocating remaining spend.

    I confess that at times I've not been sure if we're looking at costs or revenue - which will impact best practice (ie erring on side of caution in either direction)
    Last edited by DonkeyOte; 09-10-2010 at 02:53 AM.

  15. #15
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    I am testing both (OR).

    However, I've tried your formula & it wasn't accepted. Can you kindly apply it in the attached report (v.3) & let me know. I've listed two rows only (2 projects). The first row should show ZERo spend (since column AC is Zero - even if open doors date is in the boundaries of spending curve).

    Thanks in advance
    Attached Files Attached Files
    Last edited by elfiky; 09-30-2010 at 03:22 PM. Reason: Re-open

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    The suggestion works without issue.

    Replace

    =IF(SUM($U3)=0,0,

    with

    =IF(OR(SUM($U3)=0,SUM($AC3)=0),0,

  17. #17
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    Formula Worked. Thanks a million.

  18. #18
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    Formula worked. Thanks a million

  19. #19
    Registered User
    Join Date
    05-21-2007
    Posts
    57

    Re: Billing Forecast

    Last Question:

    I need to capture only 1/3 of Dec. 2010 cash flow forecast. How can I adjust (Dec. Formula) to cpature 1/3 of Dec. forecast?

    For example: if the current formula for Dec. is reflecting $9000. I need it to reflect only ($3000). Thanks

  20. #20
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Billing Forecast

    Assuming the other 2/3 is to roll into the next time period perhaps:

    Please Login or Register  to view this content.
    in truth it would make more sense to store a divisor in a given row in each column (eg 1 or 3) then use that value instead of the (1+2*(...))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1