How to forecast AP?

Not sure if this is the right place to ask.

Currently interning at a large corporate gig and I was assigned with a task to forecast individual AP (monthly) from suppliers but cannot figure out how to do it properly. The company already has a way they do it, but they want to try to make their forecasts more accurate so they gave me a shot at it.

I have access to almost any company info. I tried a de-seasonalizing method to forecast, but that didn't work as it cannot forecast AP accurately enough when I compare the results against actuals.

Any suggestions?

Comments (7)

Jul 29, 2019

I don't have any experience in a corporate fp&a role to go off of but here's a few things I would explore:

  • How do they currently forecast AP? Break it down to see if any part of the process seems to be the "weakest link." Can you improve that?
  • Look into Time Series modeling
  • Something with finding the correlation between each supplier AP and COGS to weight the projection... just an idea.

Good luck

Jul 29, 2019

Thanks a lot. Will make sure to check it out!

Jul 29, 2019

I'm no expert, but couldn't you do it using historical DPO and forecasted COGS? You could rearrange the DPO formula to solve for AP balance instead of calculating DSO and use the trailing 3 year, or whatever, DPO average as the plug for payable days.

Jul 29, 2019

At a high level, you could use Lester's comment and run a COGS forecast through the DPO function and back into an AP balance. Could also use purchases instead of COGS in the function, if your inventory is shaped differently than COGS (i.e. if inventory/purchases swell for a period of time before the inventory turns).

You mention forecast by supplier in your post, which might indicate you're looking for a different technique. Recommendation here would be to look at the average days to pay, by supplier. If you're able to forecast spend by supplier, you could evaluate how mix impacts your weighted average days to pay and, subsequently, estimate the direction/magnitude of the DPO impact.

    • 1
Jul 29, 2019

Have you thought about breaking down the AP into individual invoices with their Payment terms, payment history, and amounts paid with the corresponding date, then building out a model based on the invoice history. Instead of focusing weighting against cogs or using DPO, etc. you have the actual data to analyze. If I were you, I would start with a small supplier, review its actuals for a 3 year period to see the fluctuations in spending, then build a forecast for that supplier. Once you have the forecast for that supplier without any payment info added in (ex. is it an X Net Y vs being a Net X structure), I would then add payment history to see how long it takes for the bills to get paid based on the terms of the contract. From there, you should be able to forecast your netted balance and see how it compares to the existing model.

Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

Jul 30, 2019

Havent seen how we calc it at my current employer (probably some incorrect way like half of the other shit) but at my old job basically you would want to lag your expenses by whatever your average payment terms are. So if you're net 30 on avg your accounts that involve paying third parties would basically lag into the next month (you can use by day calcs or by week calcs to engineer something a little more exact than that).

Jul 30, 2019
Comment
    • 1