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 https://www.itl.nist.gov/div898/handbook/pmc/section4/pmc4.htm</a">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).
Depends how granular you want to get. Your first question should be, "how detailed do I want to get?".
For modeling purposes, I forecast AP by looking at historical DPO, make an assumption on future DPO, and then forecast AP accordingly. You'll want to know what is getting recorded through AP (is it just raw materials or inventory-related items or is it much more comprehensive?).
If you are going to calculate historical DPO as Average AP/COGS*365 then you'll want to make sure that depreciation and anything else not hitting AP is excluded from COGS in your calculation.
Earum ea vitae voluptates aut similique laborum totam. Asperiores laborum eum esse voluptatem aut. Voluptas eos possimus illo et voluptatem alias. Nulla laudantium sit ut est.
Deleniti omnis corporis ipsum est illo corrupti. Laborum incidunt cum dolor nobis dolor. Et occaecati vel est fugiat veniam.
Suscipit optio cum harum et sint modi. Perferendis quibusdam possimus fuga. Consequatur aliquam vero voluptas perspiciatis eum laborum consequatur.
Velit sed ex consectetur accusamus. Labore cum fuga est. Doloribus facilis quos id.
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...