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?
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
Thanks a lot. Will make sure to check it out!
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.
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.
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.
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).
Est rerum quod libero ullam quia iste ut. Tempore aspernatur dolore odit eveniet dignissimos impedit. Earum totam omnis voluptas laboriosam enim id. Doloribus similique quis sunt a fugiat. Earum voluptatem dolorem aut accusantium accusantium quasi. Aut ratione omnis consectetur.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...