AP Forecast
Corporate Finance Financial Model (Free Excel Download)
Forecast supplier invoices, payment timing, and outstanding payables to plan cash requirements, monitor overdue balances, and keep working capital under control.
professionals from Deloitte
Used by professionals from
About this model
An AP forecast translates a monthly purchases plan into a 12-month projection of the accounts-payable balance and the cash outflow to vendors. This template lays the full mechanic on six sheets: an Assumptions sheet with a panel of eight vendors, each with payment terms in days and a 12-month purchases plan, plus opening AP, the four bucket cutoffs that map terms days to a payment-timing bucket (paid same month / +1 / +2 / +3), and the DPO and AP-intensity traffic-light thresholds; a Vendor Master sheet that pulls each vendor's terms and annual purchases, computes its % of panel and DPO contribution, and labels its timing bucket; a Payment Schedule sheet with a 12x12 matrix that routes each month's purchases to the month they are paid based on vendor bucket, with a post-period column for purchases whose payment lands beyond the 12-month window; an AP Schedule sheet that rolls opening AP, purchases, payments, and closing AP through 12 months with an implied DPO each month; and a Dashboard sheet with weighted DPO, peak AP balance, AP intensity at peak, closing AP at M12, annual purchases and payments, and the payment-timing distribution by bucket.
The payment routing uses a SUMPRODUCT against the vendor bucket labels, so a single edit to a vendor's terms days or to a bucket cutoff reshapes the entire payment matrix and the AP balance path. Weighted DPO is computed properly - each vendor's share of total annual purchases is multiplied by its terms days and summed - so big-spend vendors dominate the headline number the way they would in a real cash forecast. The model maintains the AP identity (opening + purchases - payments = closing) at every month, and the post-period spill column ensures column sums tie to the underlying purchases.
CFOs, FP&A teams, controllers, and treasury managers use this template for cash-out timing (drive the operating-cash leg of a 13-week forecast off the AP Schedule payments row), working-capital sizing (read AP intensity at peak to size vendor financing carried by the business), and vendor-terms renegotiation cases (flex one vendor's terms days in Assumptions and quantify the cash impact on weighted DPO and peak AP before walking into the negotiation). The bucket model is intentionally caveman-simple - real vendor terms have more nuance than four buckets - so the trade-off is interpretability and one-edit responsiveness over the false precision of a daily payment-date calendar.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the AP Forecast
- Vendor panel with terms (days) and 12-month purchases plan per vendor
- Vendor Master with annual spend, % of panel, weighted-DPO contribution, and timing bucket
- 12x12 payment matrix routing each month's purchases to the month they are paid
- Post-period spill column capturing payments deferred past M12
- 12-month AP roll-forward: opening AP, purchases, payments, closing AP, implied DPO
- Peak AP balance, the month it occurs, and AP intensity (AP / monthly purchases) at peak
- Payment timing distribution: % of annual spend by bucket (+0 / +1 / +2 / +3 months)
- Dashboard with DPO and AP-intensity traffic-light status against user-set thresholds
AP Forecast: Vendor Payment Timing and Cash Planning
This AP forecast template helps treasury and CFO teams translate a monthly purchases plan into a 12-month accounts-payable cash forecast. It shows when vendor invoices are likely to be paid by routing purchases through payment terms, building an AP roll-forward, and surfacing peak cash needs, DPO views, and concentration metrics.
The preview download is values-only.
Operating drivers behind the payment forecast
The model is driven by a vendor panel that carries terms in days, a scenario toggle, and a monthly purchases plan. Each vendor's active terms are chosen from Base, Stretch, or Tight via a scenario selector, and those terms determine a bucket offset of zero to three months.
- Weighted-average DPO is calculated from each vendor's share of total panel purchases, so changing a vendor's annual volume or terms immediately shifts the overall payment profile. Opening AP and a four-bucket runoff curve determine how much existing payable drains in the early months.
- Prior-period purchases for the three months before the forecast feed the payment carry-over, which is a required input for early-month accuracy. FX fields and a discount percentage are also captured for downstream exposure and discount analysis.
How purchases become monthly payments
Purchases are routed through a payment-timing matrix that maps each month's invoices to the month they are expected to be paid. For each vendor bucket, a time offset is applied: for example, invoices from a vendor with net-30 terms may be paid in the following month, while net-90 invoices may spill into later months or beyond the 12-month horizon.
- The matrix combines monthly purchase volumes with bucket assignments using a lookup that sums purchases by vendor bucket. Opening AP is then added to early-month payments through a runoff curve, which drains the existing balance over the first few months.
- Monthly payments are the sum of scheduled payments from the matrix for that month plus any runoff from opening AP. This structure ensures that payments never occur before the invoice month and that the payment schedule remains upper-triangular.
AP roll-forward and DPO outputs
Each month the model builds an AP roll-forward: opening AP plus purchases minus payments equals closing AP. This identity is verified monthly by checks, and closing AP never goes negative.
- From the roll-forward, the template produces three independent implied DPO views: a this-month DPO based on closing AP and monthly purchases, a trailing-three-month DPO, and a COGS-basis DPO using an annualized factor. The Dashboard reports these alongside a weighted DPO from vendor terms, and a reconciliation block exposes the gap between them.
- The gap is a known structural artifact of bucket-based routing on a finite horizon, and the checks allow a tolerance of up to 30 days. Other outputs include peak cash required, AP intensity, and a post-period clearing amount.
Practical use for treasury planning
Treasury and CFO users can flex the scenario toggle between Base, Stretch, and Tight to see how stretching or tightening vendor terms changes cash required each month. The model's sensitivity sheet shows closing AP against runoff share and terms multiplier, and peak AP against purchases growth and seasonality strength.
- The dashboard highlights vendor concentration using HHI and top-three share, early-pay discount APR compared with WACC, FX exposure with a one-sigma VaR, and closing AP aging. The monthly mini-view provides a compact summary of opening, purchases, payments, and closing AP.
- While the public download is a values-only preview, the underlying model captures these relationships so that an operator can assess payment timing and working capital needs.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is an AP forecast?+
An accounts-payable (AP) forecast translates a monthly purchases plan into a month-by-month projection of the AP balance and the cash outflow to vendors. It is the AP side of a working-capital forecast and the input that drives the operating-cash leg of a 13-week cashflow or 3-statement projection.
How are vendor terms turned into payment timing?+
Each vendor's terms in days are mapped to one of four buckets via user-set cutoffs on the Assumptions sheet: same month, +1 month, +2 months, or +3 months. The model uses a SUMPRODUCT against the vendor bucket labels on the Vendor Master sheet to route each month's purchases into the right payment column.
Why does the dashboard show payments deferred past M12?+
Vendors with longer terms can have purchases in the last few months of the forecast whose payment lands beyond the 12-month window. The payment matrix has a post-period column that captures this spill so the totals tie and you can see how much of the late-year purchasing rolls over into the next year.
How is weighted DPO computed here?+
Weighted DPO is the sum of each vendor's share of total annual purchases multiplied by that vendor's terms days. So vendors with bigger spend get more weight, and the headline DPO reflects the panel's actual cash-timing centre of gravity rather than a flat average across vendors.
Can I add more vendors?+
Yes. Extend the vendor block on Assumptions, add the corresponding row on Vendor Master, and extend the named ranges for terms, purchases, and buckets to cover the new rows. The payment matrix and AP Schedule will pick up the additions automatically through the named ranges.
Have more financial modelling questions? Contact us
Related templates
Working Capital Model
Model cash conversion cycle with DSO, DIO, and DPO to forecast working capital needs.
Cashflow Model
Detailed monthly and annual cash flow projections with sweep and liquidity mechanics.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
M&A Modeling & Valuation
Comprehensive M&A valuation with DCF, comparable companies, and precedent transactions analysis.
