# AP Forecast A 12-month accounts-payable timing forecast: vendor panel with terms (days) and a monthly purchases plan, weighted-average DPO, a 12x12 payment matrix routing each month's purchases to the month they are paid, a 12-month AP roll-forward (opening / purchases / payments / closing) with implied DPO each month, and a dashboard with peak-AP and timing-distribution status. - Canonical: https://finamodel.com/templates/ap-forecast - Excel download: https://finamodel.com/templates/ap-forecast.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: CFOs & FP&A, Founders & operators, CFOs, FP&A teams, Controllers, Treasury - Tags: accounts payable, dpo, working capital, vendor terms, cash forecast ## Overview 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's included - 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 - Weighted-average DPO across the panel weighted by annual spend - 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 - Vendor panel with terms (days) and 12-month purchase plan per vendor - Vendor Master with annual spend, % of panel, weighted DPO contribution, and timing bucket per vendor - 12x12 payment matrix routing each month's purchases to the month they are paid based on vendor bucket - 12-month AP roll-forward: opening AP, purchases, payments, closing AP, implied DPO each month - Weighted-average DPO across the vendor panel weighted by annual spend - Peak AP balance and the month it occurs, plus AP intensity (AP / monthly purchases) at peak - Payment timing distribution: % of annual spend paid same month / +1 month / +2 months / +3 months ## 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. ## Built for cash-out timing When the question is "when do we actually pay our vendors?", an AP forecast is the cleanest answer. This template feeds the operating-cash leg of a 13-week cashflow forecast, a 3-statement model, or a runway projection without you having to stitch payment timing together by hand. ## Designed for one-edit responsiveness Every vendor term, bucket cutoff, threshold, and monthly purchase line is a named-range or named-cell input. Flex one number and the payment matrix re-routes, the AP balance path updates, and the dashboard status flips - no formula rewrites. ## Honest about timing precision Real vendor terms have daily nuance; this template uses a four-bucket model (paid same month, +1, +2, +3) because the trade-off favours interpretability over false precision. The bucket cutoffs are user-set inputs, so you can tighten or loosen them to match how your AP team actually pays. ## Built for cash-out timing When the question is "when do we actually pay our vendors?", an AP forecast is the cleanest answer. This template feeds the operating-cash leg of a 13-week cashflow forecast, a 3-statement model, or a runway projection without you having to stitch payment timing together by hand. ## Designed for one-edit responsiveness Every vendor term, bucket cutoff, threshold, and monthly purchase line is a named-range or named-cell input. Flex one number and the payment matrix re-routes, the AP balance path updates, and the dashboard status flips - no formula rewrites. ## Honest about timing precision Real vendor terms have daily nuance; this template uses a four-bucket model (paid same month, +1, +2, +3) because the trade-off favours interpretability over false precision. The bucket cutoffs are user-set inputs, so you can tighten or loosen them to match how your AP team actually pays. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: vendor panel, monthly purchases, opening AP, bucket cutoffs, thresholds. - 8 vendors with terms (days) and a 12-month purchases plan per vendor - Opening AP balance - Three bucket cutoff days (same-month, +1 month, +2 month) - DPO and AP-intensity traffic-light thresholds (green / amber) ### Vendor Master Per-vendor terms, annual spend, weighted-DPO contribution, and timing bucket. - One row per vendor - Annual purchases via SUM across the 12 monthly named ranges - % of panel and DPO contribution (% of panel times terms days) - Bucket label via nested IF on the user-set cutoffs ### Payment Schedule A 12x12 matrix routing each month's purchases to the month they are paid. - Row r = purchase month, column c = payment month - Cell (r, c) computed via SUMPRODUCT against vendor bucket labels - Post-period column captures payments deferred past M12 - Column sums feed the AP Schedule payments row ### AP Schedule 12-month AP roll-forward with implied DPO each month. - Opening AP rolls from previous month's closing - Purchases per panel from the Assumptions monthly named ranges - Payments pulled from the Payment Schedule column totals - Closing AP = opening + purchases - payments - Implied DPO = closing AP / monthly purchases x 30 ### Dashboard Headline metrics with traffic-light status and payment-timing distribution. - Weighted-average DPO with On track / Watch / Stretched status - Peak AP balance and the month of peak - AP intensity at peak with On track / Watch / Heavy status - Annual purchases, in-period payments, and post-period spill - Bucket distribution: % of annual spend paid +0 / +1 / +2 / +3 months ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: vendor panel, monthly purchases, opening AP, bucket cutoffs, thresholds. - 8 vendors with terms (days) and a 12-month purchases plan per vendor - Opening AP balance - Three bucket cutoff days (same-month, +1 month, +2 month) - DPO and AP-intensity traffic-light thresholds (green / amber) ### Vendor Master Per-vendor terms, annual spend, weighted-DPO contribution, and timing bucket. - One row per vendor - Annual purchases via SUM across the 12 monthly named ranges - % of panel and DPO contribution (% of panel times terms days) - Bucket label via nested IF on the user-set cutoffs ### Payment Schedule A 12x12 matrix routing each month's purchases to the month they are paid. - Row r = purchase month, column c = payment month - Cell (r, c) computed via SUMPRODUCT against vendor bucket labels - Post-period column captures payments deferred past M12 - Column sums feed the AP Schedule payments row ### AP Schedule 12-month AP roll-forward with implied DPO each month. - Opening AP rolls from previous month's closing - Purchases per panel from the Assumptions monthly named ranges - Payments pulled from the Payment Schedule column totals - Closing AP = opening + purchases - payments - Implied DPO = closing AP / monthly purchases x 30 ### Dashboard Headline metrics with traffic-light status and payment-timing distribution. - Weighted-average DPO with On track / Watch / Stretched status - Peak AP balance and the month of peak - AP intensity at peak with On track / Watch / Heavy status - Annual purchases, in-period payments, and post-period spill - Bucket distribution: % of annual spend paid +0 / +1 / +2 / +3 months ## Features - **Bucket-based payment timing:** Vendor terms (in days) are mapped to a four-bucket timing model: paid same month, +1 month, +2 months, or +3 months. Cutoff days for each bucket are user-set named-range inputs, so a single edit to one cutoff cell reshapes the entire payment matrix. - **12x12 payment matrix:** Each row is a purchase month; each column is a payment month. Cell (r, c) shows the dollars from month r's purchases that get paid in month c, with a post-period spill column for purchases whose payment lands beyond M12. Column sums feed the AP Schedule payments row directly. - **Weighted DPO not flat-averaged:** Weighted-average DPO is computed as the sum of each vendor's annual purchases share times that vendor's terms days, so big-spend vendors dominate the headline number the way they would in a real cash forecast. No equal-weight averaging. ## Use cases - **Cash-out timing for treasury:** Take a budgeted purchases plan, push it through vendor terms, and read the month-by-month cash outflow off the AP Schedule payments row. Pairs directly with a 13-week cashflow forecast or a runway model. - **Working-capital sizing for fundraising:** AP intensity at peak (AP / monthly purchases) sizes how much vendor financing the business carries; compare that against the AR side from a separate working-capital template to bridge to net working-capital investment. - **Vendor-terms renegotiation case:** Flex one vendor's terms days in Assumptions, watch the weighted DPO move, the bucket flip, the payment matrix re-route, and the peak AP balance respond. Quantifies the cash impact of a terms negotiation before you walk into the room. ## Frequently asked questions ### 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. ## Related templates - [Working Capital Model](https://finamodel.com/templates/working-capital-model) - [Cashflow Model](https://finamodel.com/templates/cashflow-model) - [3 Statement Model](https://finamodel.com/templates/3-statement-model)