# Billing Forecast

1. ## 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).  Register To Reply

2. ## 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.``  Register To Reply

3. ## 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  Register To Reply

4. ## 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.  Register To Reply

5. ## Re: Billing Forecast

I've changed the title. Awaiting your feedback. Thanks  Register To Reply

6. ## 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]  Register To Reply

7. ## 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?  Register To Reply

8. ## Re: Billing Forecast 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 ? 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.  Register To Reply

9. ## Re: Billing Forecast

The matrix is per the new file (attached). Thanks in advance for your feedback  Register To Reply

10. ## 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.  Register To Reply

11. ## 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.  Register To Reply

12. ## 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.  Register To Reply

13. ## 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.  Register To Reply

14. ## 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)  Register To Reply

15. ## 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).  Register To Reply

16. ## Re: Billing Forecast

The suggestion works without issue.

Replace

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

with

=IF(OR(SUM(\$U3)=0,SUM(\$AC3)=0),0,  Register To Reply

17. ## Re: Billing Forecast

Formula Worked. Thanks a million.  Register To Reply

18. ## Re: Billing Forecast

Formula worked. Thanks a million  Register To Reply

19. ## 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  Register To Reply

20. ## 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*(...))  Register To Reply