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).

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.

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.

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]

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.

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.

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.

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)

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

The suggestion works without issue.

Replace

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

with

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

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*(...))