How to forecast receivables and payables?

Replies to This Discussion

Permalink Reply by Sabin balan on November 22, 2009 at 10:01am

First you need to have an idea of what your turnover would be in the coming months, for which i suggest you contact your planning department. Once you have forecasted the sales with their help, planning receivable becomes easy, based on the collection period for each customer. Forecast your raw material requirements based on sales plan (use some percentage basis on sales), once done forecast your payables based on payment period . Use some reasonable assumption

▶ Reply to This

Permalink Reply by Mike Adhikari on November 23, 2009 at 11:00pm

I have worked in this area and often find surprising results. Depending on operating policies one may have to average 2 or 3 or more months. The forecast formula you develop has to be tested with history. Would help if you send a spreadsheet.

▶ Reply to This

Permalink Reply by Scarlet on November 24, 2009 at 5:22am

I can upload spreadsheet, but I think that it won't help. I forecast P&L and Balance sheet. Compute FCFE and compute price. BUT my FCFE is NEGATIVE((((

Attachments:

• For Forum.xlsx, 41 KB

▶ Reply to This

Permalink Reply by Mike Adhikari on November 24, 2009 at 8:58am

I could not open the files. They are .xml format, not .xls format.
Your question on A/R can be addressed even with negative FCFE.

▶ Reply to This

Permalink Reply by Scarlet on November 24, 2009 at 11:11am

Probably you have Excel 2003, I have Excel 2007. I created the same file only in Excel 97-2003. Now you should be able to open it.

Attachments:

• For Forum1.xls, 81 KB

▶ Reply to This

Permalink Reply by Mukund Bhide on November 28, 2009 at 1:29pm

Hi,
Are you averse to use ERP system in credit management & billing ,budgeting system?
Mukund

▶ Reply to This

Permalink Reply by Scarlet on November 28, 2009 at 2:48pm

Actually, I don't understand what are you talking about=) What is ERP system?

▶ Reply to This

Permalink Reply by Hadi Pranggono on December 1, 2009 at 9:15pm

Scarlet,

I tknik i know why you have FCFE negative.

I have looked at your model, seems that your policy of debt (Long Term Debt) is based on the need of financing after financing by working capital. The debt calculation in your model is asset minus equity and current liabilities, the effect of this make your long term debt fluctuate. So.. your company asset get bigger but your debt getting smaller, what for ?

Most of CFO target their company debt(LTD) by debt equity ratio, means that the debt proportion to equity or asset relatively stable in the long term. Why ? because In the real world, the transaction to get long term debt is costly (by nominal & effort) both for underwriting or terminating the long term debt.

My Suggestion is..
1. Find the ideal / usual debt equity ratio for your company (or in advance modelling try to target specific corp rating ). Use the ratio to find the debt for your company. It should get bigger if our asset get bigger. The logic is your company should easily to refinance their debt if the debt equity ratio ratio relatively constant or getting smaller.

2. Then try to find the ideal current debt using your formula.

3. If you have excess cash.. put it under marketable securities or investment. The gain/revenue will show up in other income not sales.

Regards,


Hadi Pranggono

▶ Reply to This

Permalink Reply by Manish Rawat on December 29, 2009 at 12:53am

Hey Scarlet,

You are right, your forecasted receivables and payables will jump and show a cyclical nature between two periods...It is because you are using the average recievables for forecast as well. In this case, while the sum of two consecutive periods falls in line with your historicals, when you subtract the one (which is probably a smaller amount) you are left with a higher recievable/payable for the period. same goes in the second period, when you subtract the one with higher amount, you are left with a lower amount of receivables, and the cycle goes on.

I would suggest you not to use the average for forecast, but on the net figure at the end of the period.

Please can you explain on "Negative cash flow to Equit and Debt"? Are you referring to the ratio. If yes, the yes they can be negative if your cash flow turns to be negative.

When you talk about the other methods, you mean the assumptions or the calculation?

Manish

▶ Reply to This

Permalink Reply by Fteran on January 18, 2010 at 9:41pm

Well .. I always work with an excel spread sheet as follows (vertical):

(1) start receivables account balance (f.e. december 31)
(2) sales for that particular month (forecast)
(3) received payment : (1) + (2) - (4)
(4) end receivables account balance : (2) / days of the month x DSO
The number 4 will be the start account balance for the next month...

regards.

▶ Reply to This

Permalink Reply by Fuzail Zubaid Ahmad on February 16, 2010 at 1:46am

Hi. I thought Receivables Turnover means Sales or Revenues divided by average receivables. Then you divide 365 with receivables turnover and you get Average age of receivables in days.
For the next year, you first fine tune the Average age of receivables in line with management policy that might be different from last year. Then you work back to figure out the new average receivables.
So supposing that you have $100m revenues last year, and average receivables were $8.33m. Ave. Rec. Turnover is 12, as you can see, which simply means that average age of receivables is 1 month.
Now if your sales projections for next year is $150m, your average receivables should be $12.5m. Work out your end of the year receivables.
If your end of receivables balance is large, you might need to inject new equity in the business or take out more short or long term debt.
If your end of receivables balance is small, you should have more cash balance at year end, which should be used to reduce debt, if any.
I have been financial modelling for more than 15 years and have never run into the difficulty you mentioned.
Regards
Fuzail

▶ Reply to This