How to accurately forecast accounts payable (walkthrough template)

Are you struggling to predict your upcoming expenses, leaving your business vulnerable to cash flow surprises? For many businesses, managing accounts payable is a reactive process, but it doesn't have to be. 

Businesses often recognize the need to improve their payable forecasting, yet feel daunted by complex financial jargon and an unclear starting point. This guide will walk you through a simple, step-by-step process to accurately forecast your accounts payable, using tools you likely already possess.

We'll also provide you with a free invoice aging report template. 

What is accounts payable forecasting, and why does it matter to you as a business

Accounts payable (AP) forecasting is simply predicting how much money your business will owe to its suppliers and when those payments will be due. For many businesses, this foresight is incredibly valuable. It allows you to:

• Improve cash flow management: By knowing what's coming, you can avoid unexpected cash shortfalls and ensure you always have enough money to cover your expenses.
• Strengthen supplier relationships: Paying your suppliers on time, every time, builds trust and can lead to better terms or services in the future.
• Make more strategic business decisions: With a clear picture of your future financial obligations, you can make informed decisions about investments, expansion, or resource allocation.
• Identify opportunities for early payment discounts: Forecasting helps you spot opportunities to pay invoices early and take advantage of discounts offered by suppliers, saving your business money.
  
  

What you need before you start forecasting your accounts payable

Before you dive into the forecasting process, it's essential to gather a few key pieces of information and have some basic tools ready. Don't worry, you likely have most of these readily available. Having this foundation will make the forecasting process much smoother and more accurate.

Gathering your key financial documents

To get started, you'll need to access a few critical financial records:

• Your recent balance sheets and income statements: These documents provide a snapshot of your company's financial health at a specific point in time (balance sheet) and over a period (income statement). They'll give you a good overview of your current liabilities and past expenses.
• A list of your suppliers and their payment terms: Knowing who you owe and when payments are due is fundamental to accurate forecasting. This includes details like net 30, net 60, or specific due dates.
• Your recent bank statements: These will show you your actual cash inflows and outflows, helping you reconcile your forecasts with real-world activity and identify any discrepancies.
  
  

Choosing your tool: Excel/Google Sheets vs Accounting software

For most businesses, a simple spreadsheet program like Excel or Google Sheets is perfectly adequate for accounts payable forecasting. 

These tools offer flexibility and are likely already familiar to you. You can set up columns for invoice date, due date, supplier, amount, and payment status, then use basic formulas to calculate upcoming obligations.

While spreadsheets are effective, dedicated accounting software like Xero or QuickBooks can significantly streamline the process. 

Accounting software like Xero or QuickBooks automates most of this work. Instead of manually typing invoice details, the software captures them automatically and tracks due dates for you.

They can also offer features like automated reminders for due dates, helping to ensure timely payments.

A step-by-step guide to forecasting your accounts payable

Now that you've gathered your necessary documents and chosen your preferred tool, it's time to put everything into action. 

This section will guide you through the practical steps of building your accounts payable forecast, whether you're using a simple spreadsheet or more advanced accounting software. 

Follow these instructions to create a clear and actionable overview of your upcoming financial obligations.

Step 1: List your current and upcoming payables

Start by gathering all invoices you've received that haven't been paid yet. This includes physical invoices, emailed invoices, and any bills for recurring services. For each invoice, you'll want to extract key pieces of information to build your list.

If you're using a spreadsheet, create columns for each of the data points below. If you're using accounting software, navigate to your "Accounts Payable" or "Bills" section, where this information is typically already organized.

Here's a breakdown of the essential details to capture for each outstanding payable:

• Supplier Name: Who is the invoice from?
• Invoice Number: The unique identifier for the invoice.
• Invoice Date: The date the invoice was issued.
• Due Date: The date the payment is actually due. This is critical for forecasting cash flow.
• Amount Due: The total amount owed on the invoice.
• Payment Status: (Optional but recommended) A simple status like "Unpaid," "Partially Paid," or "Due Soon."

Here’s a basic table structure you can use to organize your current and upcoming payables. This can be replicated in Excel, Google Sheets, or used as a guide for data entry into your accounting software.

Step 2: Categorize your expenses

After listing all your current and upcoming payables, the next crucial step is to categorize them. 

Grouping your payables (e.g., rent, utilities, inventory, marketing) allows for a more detailed analysis of your spending habits and provides valuable insights beyond just the total amount due.

To categorize your expenses effectively, add a new column to your spreadsheet or utilize the categorization features within your accounting software. Common categories for businesses include:

Operating expenses:

• Rent/lease: Payments for your office, retail space, or warehouse.
• Utilities: Electricity, gas, water, internet, and phone bills.
• Salaries/wages: If you process payroll through a payable system.
• Office supplies: Regular purchases of stationery, printer ink, etc.
• Software subscriptions: Monthly or annual fees for business software.
• Insurance: Premiums for business insurance.
• Professional services: Fees for accountants, legal advice, consultants, etc.

Cost of goods sold (COGS) related:

• Inventory/raw materials: Purchases of goods for resale or materials used in production.
• Freight/shipping: Costs associated with getting inventory to your location.

Marketing and sales:

• Advertising: Payments for online ads, print ads, social media campaigns.
• Promotional materials: Costs for brochures, flyers, event banners.

Other expenses:

• Loan payments: Principal and interest payments on business loans.
• Equipment purchases: Payments for new machinery, computers, or tools.
• Maintenance and repairs: Costs associated with keeping assets in working order.

When adding these categories, be consistent. If an invoice covers multiple categories, you may need to split it or assign it to the primary category it represents.

Step 3: Choose a forecasting method that works for you

Now that you have your payables listed and categorized, the next step is to choose a forecasting method. Different methods offer varying levels of detail and accuracy. Select the one that best suits your business's complexity and your comfort level with financial analysis.

Method 1: The historical average method (the simplest start)

The historical average method is an excellent starting point for businesses new to accounts payable forecasting. It involves using your past payment data to predict future payments. The assumption here is that your future spending patterns will generally resemble your past patterns.

Explanation: Using past payment data to predict future payments

This method looks at how much you've typically paid out in previous months or quarters for specific categories of expenses. For example, if your average utility bill over the last six months was £150 GBP, you might forecast £150 GBP for the upcoming month.

Step-by-step instructions with a simple example

1. Gather historical payment data: Look at your bank statements or accounting software for the past 3-6 months.
2. Identify key expense categories: Use the categories you established in Step 2.
3. Calculate the average for each category: Sum the payments for each category over your chosen period and divide by the number of periods.

Example:

Based on this, you would forecast £1,000 GBP for rent, £133.33 GBP for utilities, and £600 GBP for inventory in the next month.

Method 2: The invoice aging method (for better accuracy)

The invoice aging method provides a more precise forecast by focusing on the actual due dates of your outstanding invoices. This method highlights your immediate payment obligations, giving you clear visibility into next week's cash needs.

Explanation: Grouping your payables by their due dates

Instead of just looking at averages, you group your current outstanding invoices based on how soon they are due. This gives you a clear picture of what needs to be paid in the short term (e.g., within 0-30 days), medium term (31-60 days), and longer term (61-90+ days).

How to create a simple aging report in a spreadsheet

1. Add a "days outstanding" or "days until due" column: In your spreadsheet, calculate the number of days between the current date and the invoice due date.
2. Create aging buckets: Set up columns for different aging categories (e.g., "Due in 0-30 Days," "Due in 31-60 Days," "Due in 61-90 Days," "Over 90 Days Past Due").
3. Sum amounts in each bucket: Use spreadsheet formulas (like SUMIF or pivot tables) to total the amounts for invoices falling into each aging category.
   

Example aging report structure:

You can then sum the totals for each column to see your upcoming obligations by time bucket.

Free invoice aging report template (Excel/Google Sheets)

Method 3: The Days Payable Outstanding (DPO) method (for a deeper dive)

The Days Payable Outstanding method offers a more analytical approach, providing insights into how efficiently your business is managing its payments to suppliers. It's particularly useful for understanding payment cycles and identifying potential areas for improvement.

A simple explanation of what Days Payable Outstanding is and why it's useful

Days Payable Outstanding (DPO) tells you how long you take to pay suppliers. If your DPO is 45 days, you can predict you'll have about £9,000 GBP in outstanding bills at any time (based on £200 GPB daily spending).

A lower Days Payable Outstanding (DPO) means you pay suppliers quickly, which can be good for relationships but might tie up cash unnecessarily. It's useful for:

• Benchmarking your payment efficiency.
• Identifying trends in your payment cycles.
• Optimizing cash flow by adjusting payment terms.

A simplified Days Payable Outstanding (DPO) formula for businesses

A simplified DPO can be calculated using readily available figures:

• Average accounts payable: Sum your Accounts Payable at the beginning and end of a period (e.g., a quarter) and divide by 2. This figure can be found on your balance sheet.
• Cost of goods sold : The direct costs attributable to the production of the goods sold by your company. This is on your income statement.
• Number of days in period: Typically 90 for a quarter or 365 for a year.
  
  

How to use your Days Payable Outstanding (DPO) to forecast future payables

Once you know your average Days Payable Outstanding (DPO), you can use it to estimate future accounts payable. For example, if your DPO is 45 days, it means you typically take 45 days to pay an invoice. 

If you anticipate £10,000 GBP in COGS next month, you can use your DPO to estimate your average outstanding payables. While not a direct forecast of individual payments like the aging method, it gives you a high-level view of your average Accounts Payable balance over time, which can then be used to estimate your cash outflow for payables for a given period.

For example, if your DPO is consistently 45 days, and your average daily COGS is £200 GBP, you would expect to have approximately £9,000 GBP (£200 GBP x 45) in accounts payable outstanding on any given day. This helps in high-level cash flow planning.

Best practices for effective Accounts Payable forecasting

Now that you understand the different methods for forecasting your accounts payable, let's explore some best practices to ensure your forecasts are as accurate and useful as possible. Implementing these strategies will not only improve your financial foresight but also enhance your overall business operations.

Be realistic about payment timings

Think beyond just due dates. While invoice deadlines are important, how you actually pay can be different. If you tend to pay certain suppliers a week early, make sure to include that in your forecast. This will give you a much more realistic picture of when your cash will truly leave your account.

Regularly review and update your forecast

Your accounts payable forecast shouldn't be a static document. Market conditions, supplier terms, and even your own business operations can change rapidly. Regularly reviewing and updating your forecast is paramount to its accuracy and usefulness. 

Treat it as a living document that evolves with your business. Set a consistent schedule (e.g., weekly or monthly) to review your actual payments against your forecast, adjust for new invoices, and revise any assumptions based on recent trends. This continuous refinement ensures your forecast remains a reliable tool for managing your cash flow.

Communicate with your suppliers

Open lines of communication with your suppliers are invaluable for effective accounts payable management. Your forecasting efforts can become a powerful tool in these conversations. 

By having a clear understanding of your upcoming payment obligations and cash flow, you can:

• Negotiate better terms: If your forecast shows a period of tight cash flow, you can proactively discuss extended payment terms with suppliers before invoices are due.
• Request early payment discounts: Conversely, if your forecast indicates strong cash reserves, you can approach suppliers about offering discounts for early payment, saving your business money.
• Manage expectations: If unforeseen circumstances arise that might delay a payment, your forecast allows you to identify this early and communicate with your supplier in advance, maintaining good relationships and avoiding late payment penalties.
• Build stronger relationships: Proactive communication, based on a solid understanding of your financial position, fosters trust and collaboration with your suppliers, transforming reactive payment management into a strategic partnership.
  
  

Involve your team

Accurate Accounts Payable forecasting requires collaboration, especially from those making purchases. To get their input:

• Explain "why": Show how accurate forecasting benefits them (e.g., ensures project funds, better supplier terms).
• Establish clear communication: Use regular check-ins, a centralized input system, or a PO system for early visibility.
• Provide clear guidelines: Offer simple templates or instructions for required details.
• Integrate with workflows: Incorporate information gathering into existing processes.
• Offer feedback: Show how their input improves forecasting, reinforcing its value.

Common pitfalls when forecasting Accounts Payable and how you can avoid them

Even with the best tools and methods, forecasting accounts payable can present challenges. Understanding common pitfalls and how to steer clear of them is just as important as knowing how to build a forecast. This section will highlight frequent mistakes businesses make and provide actionable advice to help you avoid them, ensuring your Accounts Payable forecasts are as robust and reliable as possible.

Forgetting one-off or irregular expenses

One of the most common pitfalls is focusing solely on recurring bills and overlooking less frequent but significant expenses. These could include annual software subscriptions, quarterly tax payments, large equipment purchases, or seasonal marketing campaigns.

How to avoid it:

• Maintain a separate list for irregular expenses: Keep a running log of all known non-monthly expenses with their anticipated due dates and amounts.
• Review historical data for anomalies: Periodically scan past bank statements and accounting records for large, infrequent payments that might reoccur.
• Communicate with department heads: Encourage team members to flag any planned large expenditures well in advance.
  
  

Being overly optimistic about payment timings

Businesses often assume they will pay all invoices on their due dates. In reality, payment processes can sometimes take longer due to approvals, bank holidays, or internal delays.

How to avoid it:

• Factor in processing time: Add a buffer of a few days to a week to your forecast due dates to account for internal processing.
• Track actual payment patterns: Analyse your historical payment data to understand your true average payment cycle, even if it's slightly beyond the official due date.
• Don't rely solely on automated reminders: While helpful, automated reminders should supplement, not replace, a realistic assessment of when funds will actually leave your account.
  
  

Not updating the forecast regularly

A forecast is a living document, not a one-time exercise. Business conditions, supplier terms, and unexpected events can change rapidly, rendering an outdated forecast inaccurate.

How to avoid it:

• Schedule regular review sessions: Set a consistent schedule (e.g., weekly, bi-weekly, or monthly) to review your forecast.
• Integrate updates into daily operations: Make it a routine to add new invoices, adjust due dates, and update payment statuses as they occur.
• Compare actuals to forecast: Regularly compare your actual payments against your forecast to identify discrepancies and refine your forecasting assumptions.
  
  

Ignoring the impact of seasonality on your business

Many businesses experience seasonal fluctuations in sales, and consequently, in their purchasing and expense patterns. Failing to account for these cycles can lead to significant forecasting errors.

How to avoid it:

• Analyze historical seasonal trends: Look back at several years of data to identify patterns in your inventory purchases, marketing spend, and other variable expenses that align with peak or off-peak seasons.
• Adjust forecasts based on expected activity: If you anticipate a busy period, increase your forecast for related expenses (e.g., more inventory, temporary staff). Similarly, reduce forecasts during slower periods.
• Incorporate sales forecasts: Align your Accounts Payable forecast with your sales forecast, as increased sales often lead to increased costs of goods sold or operational expenses.

When to consider using accounts payable software

While spreadsheets are an excellent starting point for AP forecasting, there comes a time when manual processes become too cumbersome or error-prone. Recognising these signs can help you decide when to upgrade to dedicated accounts payable software.

Signs that you might be outgrowing your spreadsheet

• You're spending too much time on manual data entry: If you find yourself manually typing in invoice details from PDFs or emails, or constantly cross-referencing different documents, AP software can automate much of this.
• Errors are becoming frequent: Human error is inevitable with manual processes. If you're regularly finding mistakes in your payment calculations or due dates, it's a clear sign for automation.
• Lack of real-time visibility: Spreadsheets require constant manual updates to reflect the current state of your payables. If you can't get an immediate, accurate view of your outstanding obligations, your cash flow management will suffer.
• Collaboration is difficult: Sharing and collaborating on spreadsheets, especially with multiple team members, can lead to version control issues and confusion.
• Reporting is limited or time-consuming: Generating meaningful reports (like an aging report or cash flow projections) from a complex spreadsheet can be a laborious task, limiting your ability to make quick, informed decisions.
• You're missing out on early payment discounts: If your manual process is too slow to identify and act on opportunities for early payment discounts, you're leaving money on the table.
• Supplier relationships are strained by late payments: If you're consistently missing payment due dates, it indicates a need for more robust tracking and reminder systems.
  
  

What to look for in Accounts Payable software for your business

When you're ready to make the leap to dedicated accounts payable software, consider these key features and functionalities that are particularly beneficial for businesses:

• Ease of use and intuitive interface: The software should be easy to set up, learn, and navigate for your team.
• Invoice automation: Look for features that allow for automated invoice capture (e.g., via email, scanning, or direct integration), data extraction, and routing for approvals.
• Customisable approval workflows: The ability to set up simple or multi-step approval processes ensures that the right people review and authorise payments before they go out.
• Integration with accounting software: Seamless integration with your existing accounting platform (like Xero, QuickBooks, or Sage) is crucial to avoid duplicate data entry and ensure financial consistency.
• Real-time reporting and dashboards: The software should offer clear, up-to-date insights into your accounts payable, including aging reports, cash flow projections, and spending by category.
• Supplier portal/communication: Features that allow for easy communication with suppliers regarding invoice status or queries can significantly improve relationships.
• Payment processing options: Consider if the software facilitates various payment methods (e.g., BACS, Faster Payments, international payments) or integrates with payment platforms.
• Scalability: Choose software that can grow with your business, accommodating increased invoice volumes and more complex needs as you expand.
• Cost-effectiveness: Evaluate pricing models (per user, per invoice, tiered plans) to ensure it aligns with your budget and provides good value for money.

What are the best accounts payable software solutions?

Several excellent accounts payable software solutions are on the market, each with its strengths:

Xero: While primarily an accounting platform, Xero has robust Accounts Payable features, allowing you to manage bills, set up recurring payments, and integrate with many third-party apps for advanced Accounts Payable automation. It's known for its user-friendly interface.

QuickBooks: Similar to Xero, QuickBooks Online offers comprehensive Accounts Payable management within its broader accounting ecosystem. It's widely used and provides good reporting capabilities and extensive integrations.

Lightyear: This dedicated Accounts Payable automation tool focuses heavily on invoice data capture, intelligent coding, and approvals. It integrates with major accounting software and is designed to significantly reduce manual processing time.

Wrapping up

The key takeaway from this guide is simple: forecasting your Accounts Payable doesn't have to be a daunting task reserved for large corporations. 

With common tools like spreadsheets, and by following a step-by-step approach above, you can significantly improve your business's financial health, enhance cash flow management, and strengthen supplier relationships.

Don't let perceived complexity hold you back. The benefits of accurate Accounts Payable forecasting are too significant to ignore. Why not take the next step and put this knowledge into practice? 

Download our free Invoice Aging Report Template now and start building your first accounts payable forecast today. It's the simplest way to gain invaluable foresight into your business's future financial obligations.

FAQs