Excel - forecasting cash receipts from sales

Excel - forecasting cash receipts from sales

Didn't find your answer?

I need to forecast receipts from individual key clients, each having different credit terms. I'm looking for a formula to place the cash receipt into the the right period based on the period of the sale and the credit term. I thought this might be possible with an OFFSET formula (it might be) but I can't work out how to do it.

Weekly sales are in columns going acoss the page, one row for each client. For most clients, there are sales in each week - and so each row will have multiple values which I need to output in the right place. 

There are three column headers going across the page in different rows: week number, date (dd/mm/yyyy) and the day number (0, 7, 14, 21 etc.).

So, what I was trying to do was: say, sale is in week 2 (starts on day 7), credit terms are 15 days from sale/invoice, and so cash should be received on day 22. Week 4 starts on day 21, and so I want the receipt to pop up there.

I've also got a cell for each client's credit terms (0 days, 15 days after invoice, 30 days, 45 days, 60 days). Sales = invoice in this circumstance. Each client only has one credit term, so the same is applied to all their sales (i.e. every value in the sales row).

Many thanks for your help.

Replies (6)

Please login or register to join the discussion.

avatar
By paulwakefield1
14th Apr 2011 13:37

This may be a start

If I understand it correctly, the sales are grouped by weeks but not days and the credit terms are expressed as days. In theory sales in a given week could be received in one of two future weeks. But this of course assumes that the credit terms are adhered to.

So a starting point might be =sumif(Range of Receipt week numbers,sales week number + credit period rounded to a whole number of weeks e.g. Int(credit period/7),range of sales values). It might look like this: =SUMIF($H$1:$Q$1,A$1+INT($G2/7),$A2:$F2)

This is the optimistivc version. Add +1 after the Int formula to move it along a week or use ROUNDUP. You could also expand the formulae to split one week's sales between the two weeks.

If the number of columns in the Receipts section is more than in the Sales values (probable), you will need to control the formulae so that they do not pick up spurious columns after the end of the sales matrix (or ensure there are only blank columns to the right of the sales matrix).

Thanks (0)
avatar
By joneschrisuk
14th Apr 2011 14:09

Thanks but...

Thanks Paul.

I've almost got it to work, but...

...I might have interpreted the formula wrong, or my data is structured differently/incorrectly...

I get the cash receipt appearing before the sale. So, I've got a sale in week 12, 30 day credit terms, I've got the receipt appearing in week 8. Where am I going wrong?

Thanks for your help.

Chris

Thanks (0)
avatar
By paulwakefield1
14th Apr 2011 14:20

Sounds like

the criteria part of the formula (the middle part) is negative.

Thanks (0)
avatar
By joneschrisuk
15th Apr 2011 11:25

Thanks - it works now that I've reversed the signs

Thanks (0)
avatar
By paulwakefield1
15th Apr 2011 11:47

Great

Glad it worked.

By the way, the Offset idea was not a bad one; I just think this is easier to understand and should be quicker if there are a lot of cells in use (Offset is a volatile function i.e. it always recalculates whenever there is a change; SUMIF is not - usually!)

Paul

Thanks (0)
avatar
By stanley Decosta
13th Jul 2011 19:01

excel help

Thanks Paul for this excel help. I was also looking for a solution for somewhat similar kind of thing. I am not very good in excel but know basic formulas and functions. This Sumif is something which i think comes under macros. Anyways, i also got my formula using SumIf. Excel really makes life easy with those formulas and functions.

 

Thanks (0)