How to prepare a cash flow forecast

In this tutorial, you will use IBM Planning Analytics to prepare a cash flow forecast.

Cash flow forecasting, also known as future cash flow projections, is the forecasting process of estimating the financial liquidity of a business over a specified period. It involves projecting the cash receipts (inflows) and cash payments (outflows) that are expected to occur within that time frame.

The primary objective is to determine the net cash position (or amount of cash) of the business at various points in the future. This information provides insight into the business’s ability to meet short-term obligations, manage working capital and plan for growth, ensuring it always has enough cash.

This projection differs from profit forecasting, which focuses on revenue and expenses (or net income) rather than the timing of cash transactions. It does not account for the generation of free cash flow.

Cash flow forecasting is critical for an organization’s financial health and strategic planning. Key benefits include:

• Liquidity management: Cash flow forecasting allows businesses to anticipate potential cash shortages or surpluses, enabling proactive management of funds. This insight includes identifying periods where cash might run low, requiring immediate action such as securing short-term loans or delaying discretionary spending to cover potential shortfalls.
• Strategic decision-making: Accurate forecasts provide a foundation for informed decisions, such as expansion plans, capital expenditures, inventory management and staffing.
• Risk mitigation: By identifying potential cash flow problems in advance, businesses can implement strategies to mitigate financial risks. This proactive approach helps avoid situations like defaulting on payments.
• Stakeholder confidence: Lenders, investors and other stakeholders often require cash flow forecasts to assess the financial stability and viability of a business.
• Performance monitoring: Comparing actual cash flows against forecasted figures allows businesses to monitor performance, identify variances and understand the underlying reasons. This feedback loop is essential for continuous improvement in financial planning.

Review the following prerequisites before starting the tutorial.

1. You will need a Windows machine that is configured with Microsoft Excel.

2.     Set up an IBM account to register for a free Planning Analytics trial.

3.     Install and configure Planning Analytics for Microsoft Excel (PAfE) on your computer, ensuring you’ve also installed the appropriate Microsoft .NET Framework.

4.     Proceed to the instance of Planning Analytics Workspace that you were provided in step 1 (e.g. https://us-east-1.planninganalytics.saas.ibm.com).

5.     Navigate to the “Administration” section from the hamburger menu on the upper left of the home page.

6.     Click the “Excel and Customizations” section to download the latest version of PAfE.

7.    Install the PAfE.xll file as an add-in to Microsoft Excel
    a.    In case you are blocked from installing the .xll file, follow these instructions.

8.     Finally, connect Microsoft Excel to the Planning Analytics environment through the “IBM Planning Analytics” tab. Locate the “Connect” key icon and select “New connection”. Then, add the connection URL (the same instance and URL provided from step 1), enter a name for the connection under “Friendly name” and test the connection. You should now be connected.

Creating a cash flow forecasting model requires a structured approach to ensure that all relevant cash movements are captured.

Accurate cash flow forecasting relies on complete and reliable data, including historical financial data from financial statements. These statements provide the foundation for future projections, as past trends often indicate future patterns.

Income statements show revenues and expenses over a period. While not directly cash-based, they provide insights into sales volumes, cost of goods sold and operating expenses, which can be adjusted for non-cash items (for example, depreciation) to estimate cash impacts.

Balance sheets offer a snapshot of a company’s assets, liabilities and equity at a specific point in time. Information such as accounts receivable (money owed to the company), accounts payable (money the company owes) and inventory levels are crucial for projecting future cash inflows and outflows related to working capital.

Cash flow statements directly report the cash generated and used by a company during a period, categorized into operating, investing and financing activities, often prepared with either the direct or indirect method.

To continue with our PAfE example, we will use the “BusinessFlow” database as the source of financial statements and template for our cash flow forecast tutorial. This data source contains financial data for a sample organization included in the Planning Analytics trial.

From the “IBM Planning Analytics” tab in Excel, select the connection you made in the prerequisites section and retrieve the “BusinessFlow” data source. A list of reports will come up. Right click on “Cashflow Direct” and select “Open in viewer” .

This action displays a new window containing the cash flow statement for the entire organization. This report contains the historical data needed for future cash flow projections.

You can review the cash flow statement further in this view; you can drill down to specific geographies, years and scenarios here.

Once you’re done exploring, let’s filter for the “New York” organization on “Year 1” and “Actuals” only. Then, export the report back to Excel by clicking the reports icon in the upper left and then selecting “Quick Report”.

This action will open a quick report in Excel. Read more about Planning Analytics Quick Reports in the Planning Analytics docs.

You can adjust formatting and column widths as needed. This stage is also an opportunity to add sums and checks to verify how total and net values are calculated.

For each month, “Net cash from (used in) operating activities” should equal the sum of rows 12–15. Refer to the formula bar screenshot for an example.

For the three “Cash flow from …” sections, the “Total Year” column should aggregate the individual January through December amounts.

The numbers in row 26 “Net increase (decrease) in cash and equivalents” should be the sum of each “Net cash from (used in)” section.

Row 27, “Cash and cash equivalents at beginning of period”, should equal the “Cash and cash equivalents at end of period”. Cell M27 (beginning balance for December) equals cell L28 (the ending balance for November).

Similarly, note how cell N27 (beginning balance for the year) equals the beginning balance in January (cell B27). The “Cash and cash equivalents at end of period” for both December and the Total year reconcile to the same amount: USD 5,209,532.

Although these formulas take time to set up, they provide the foundation needed to create future cash flow forecasts.

The next step is to define the time frame and the granularity of the reporting periods.

Determine whether you are creating a short, medium or long-term forecast, depending on the purpose of the forecast.

Short-term forecasts typically cover periods of up to 30 or 90 days, focusing on daily or weekly cash movements. They are used primarily for managing immediate liquidity, day-to-day operational planning and determining the optimal use of current cash balances.

Medium-term forecasts extend over a period of three months to one year. They provide a broader view of cash flow trends and are used for budgeting, operational planning, decision-making for financing or investment opportunities within the coming year.

Long-term forecasts cover periods of one to five years or more and are strategic in nature. They project cash flows for major capital investments, long-term debt repayment and strategic growth initiatives.

For our tutorial, we will create a short-term forecast for the next month (January) following the year-end of our report.

Let’s create a new column in the worksheet for our forecast. Carrying over the same formulas and concepts we established on the initial worksheet in step 1, we can automatically calculate totals and net values.

Next, estimate all anticipated cash receipts for each reporting interval within the forecasting period.

Sales revenue is typically the largest cash inflow. To estimate revenue, start with your sales forecast, which projects future sales volumes and prices to assess the total sales revenue. Sales forecasts consider historical sales data, new products, market trends, promotional activities and expected economic conditions.

Accounts receivable collection patterns provide insight into the average time that it takes customers to pay. This insight helps project when sales revenue will convert into cash receipts. For example, if December has a 30-day collection period, the cash will likely be received in January.

Other income includes any non-sales-related cash inflows, such as interest income, proceeds from asset sales, government grants or investment returns.

In this tutorial example, we will estimate the inflow “cash received from customers”. Using the direct method, this action represents the “cash received from customers” in January from cash sales and from accounts receivable collections.

For our tutorial example, let’s estimate our January cash from customers by applying the average of the most recent three months.

Next, let’s estimate all anticipated cash payments.

Operating expenses made up of recurring costs such as salaries and wages, rent, utilities, insurance, marketing expenses and administrative costs should be recorded in the forecast. Some expenses can be fixed (for example, rent), while others can vary in cost, such as raw materials that scale with production.

Consider accounts payable by projecting when payments to suppliers will be made based on average payment terms. The average time taken to pay invoices can help in forecasting when cash will be disbursed for purchases.

For our tutorial forecast, let’s calculate “cash paid to suppliers and employees” similarly to our cash from customers by taking the average of the most recent three months. November and December in the prior year had a positive amount (cash inflow) instead of a negative amount. This change can be due to a supplier rebate or cash refund on faulty or damaged goods.

As the forecasting analyst for this organization, it is important to know the details of why there was an inflow of cash and for how long the cash inflow is expected.

Other potential cash outflows include principal and interest loan payments for loans, lines of credit or other debt obligations. We can forecast interest paid to remain USD 14,500 for January as it has for every month in the prior year.

For our New York organization, we will estimate capital expenditures to be USD 100,000 like the previous January. If sales continue to grow as they did in the previous year, there might be plans for more capital expenditures. These investments can include machinery, vehicles or property to help the business continue to grow. If so, those expenditures should be calculated here.

Other future cash outflows can include payments for income tax, payroll taxes, sales tax and property tax as well as any planned distributions to shareholders or the business owners.

For each reporting interval, calculate the net cash flow by subtracting total cash outflows from total cash inflows.

Formula: Net cash flow = total cash inflows - total cash outflows

A positive net cash flow indicates a positive cash flow for that period, while a negative cash flow figure indicates a cash deficit.

For January, the forecast net cash inflow is USD 501,965.

Calculate the ending cash balance by adding the period’s net cash flow to its beginning cash balance. The ending cash balance of one period becomes the beginning balance for the next.

Formula: Ending cash balance = beginning cash balance + net cash flow

In this example, the ending cash balance is USD 5,711,497, consisting of the beginning balance of USD 5,209,532 plus the net cash flow of USD 501,965.

Once the initial forecast is complete, it is important to review and refine it to enhance accuracy and identify potential issues.

• Validity check: Review the overall trends and ensure that the results seem reasonable in the context of your business operations and market conditions. Are there any unusually large or small numbers that need investigation?
• Sensitivity analysis: Test the impact of changes in key assumptions (for example, a 10% decrease in sales, a 5% increase in expenses) on the ending cash balance. This analysis helps understand potential risks and identify critical variables.
• Scenario planning: Develop multiple scenarios (for example, best-case, worst-case, most likely case) to prepare for different outcomes and assist with cash flow management.
• Compare to historical data: Benchmark your forecasted inflows and outflows against historical actuals to ensure that projections are realistic.
• Seek input: Consult with department heads (for example, sales, operations, CFO and finance team) to gather their insights and validate assumptions.
• Identify actionable insights: Based on the forecast, identify periods of potential cash shortage or surplus. Develop strategies to address shortages (for example, secure short-term financing) or use surpluses such as investing or repaying debt.
• Iterate: Cash flow forecasting is an iterative process. Be prepared to update and adjust your forecast regularly as new information becomes available.

In this tutorial, you prepared a cash flow forecast with Planning Analytics for Excel.

You connected Microsoft Excel to a real-time data source of financial data and retrieved historical data. Then, you projected cash inflows and outflows to calculate net cash flows and the ending cash balance.