Calculate cash flow: Explanation and Excel calculation with AnalyticsGate

Would you like to better understand and efficiently calculate your company's cash flow? In this article, we explain what cash flow is, what types there are and how you can easily calculate it. You will learn how to perform a precise cash flow calculation in Excel with AnalyticsGate and how to visualise it with Qlik Sense.

This is how a cash flow calculation with AnalyticsGate works:

1. Data collection: AnalyticsGate is more than just a standard Excel add-in. To use AnalyticsGate, all relevant cash flow data from the company's internal systems (ERP e.g. SAP) is merged in Qlik. Our IT specialists and data analysts can also easily take over the data collection or support your company's IT department in doing so.

2. AnalyticsGate integrates all company data in what is known as a democratised data management model. From now on, all data is kept up to date and made available in a simplified manner via Qlik Sense on-premise or cloud-based data storage.

3. Installation and Qlik connection with AnalyticsGate: Install AnalyticsGate and connect via Qlik to your financial data sources, e.g. your ERP system or your database. The data has been transformed in Qlik so that it can now be loaded into Excel. The data is always up-to-date at the touch of a button.

4. Create cash flow formulas: Use the imported data to calculate the various components of the cash flow. With AnalyticsGate, Excel templates for the cash flow calculation, can be customised to your company parameters and can be automated.

5. Use of AnalyticsGate functions: With the advanced features of AnalyticsGate, you can now create automated reports and dashboards by linking the calculated cash flow data directly to dynamic report templates. You can use pivot tables or special Excel formulas to analyse and visualise the cash flow data. All employees can work in Excel as usual.

6. Preparation of cash flow reports: Create a detailed cash flow report that shows the individual cash flow components as well as the overall situation of the company. AnalyticsGate offers tools to automate these reports and perform regular updates.

7. Analysis and interpretation: Finally, you can use the reports generated to analyse your company's cash flow and identify trends. Now you can check whether your company has sufficient liquidity and whether there are potential risks or opportunities for future investments.

What are the advantages of calculating cash flow with AnalyticsGate?

Cash flow calculation with AnalyticsGate gives you access to real-time data through direct integration with data sources such as ERP systems and databases via Qlik. This avoids potential errors and enables more accurate and efficient analyses.

Automate data processes with AnalyticsGate, making it easier to update reports and ensure that you are always working with the latest data. Another benefit is the ability to create dynamic and interactive reports and dashboards with the help of Qlik Sense, providing a clear visualisation of cash flow data. This gives you deeper insights and analyses that support informed decision-making. The flexibility of AnalyticsGate also allows calculations and reports to be customised to the needs of the company.

Cash flow reports can now be generated and distributed automatically using intelligent automation.

Case study from corporate practice: automated cash flow calculation at NAVIS Schifffahrts- und Speditions-AG

Initial situation:

NAVIS Schifffahrts- und Speditions-AG, a company with 180 employees that offers national and international freight forwarding services, was faced with the challenge of updating the daily cash flow report in Excel. The data was entered via Excel, with the main data sources being Microsoft Dynamics NAV and CARGOBASE. This process was error-prone, time-consuming and complex.

Solution:

NAVIS decided to implement AnalyticsGate to automate the cash flow calculation. The goal was to use Excel only as a presentation medium and to eliminate the manual data maintenance effort. AnalyticsGate now enables automated data updating through direct connection to Microsoft Dynamics NAV and CARGOBASE via Qlik. The reports are now automatically populated with the latest data, while user-specific comments are still possible.

Results:

• Automated updates: Cash flow reports are automatically updated daily, eliminating manual intervention, saving time and minimising errors.
• Increased efficiency: The direct connection to the data sources enables fast and error-free data transfer, which makes the entire cash flow creation more efficient.
• Familiar environment: Employees can continue to work in the familiar Excel environment, which facilitates acceptance and the transition to new processes.

10 practical tips for improving cash flow

Here are 10 practical tips that can help you optimise your company's cash flow:

1. Acceleration of incoming payments
Shorten the payment periods for your customers and offer incentives for early payments, such as discounts or rebates. You will notice that faster payment collection improves cash flow as the money is available sooner.

2. Efficient receivables management
Monitor your receivables regularly and implement strict dunning procedures to minimise late payments. Consistent receivables management will ensure that outstanding amounts are collected quickly.

3. Negotiation of supplier conditions
Negotiate longer payment terms or more favourable conditions with your suppliers. If you have more time to pay your invoices, you will retain liquidity in your company for longer.

4. Optimise inventory management
Reduce unnecessary stock levels and focus on just-in-time procurement to minimise capital commitment.

5. Cost control and reduction
Regularly review your operating costs and identify potential savings. Strict cost control will help you avoid unnecessary expenditure and free up more funds for cash flow.

6. Investment management
Plan investments carefully and prioritise those that bring the greatest benefit.

7. Diversification of revenue sources
Open up new markets or business areas to diversify sources of income. A broader income base reduces the risk of revenue shortfalls.

8. Credit management
Use credit lines and financing options strategically to bridge temporary liquidity bottlenecks. A well-managed credit line can serve as a buffer to solve short-term cash flow problems without jeopardising long-term financial stability.

9. Monitoring and analysis
Use tools and systems to continuously monitor and analyse your cash flow. Regular cash flow analyses will help you to identify problems at an early stage and take proactive measures to improve them.

10. Flexibility in pricing
Adjust your prices regularly to react to market changes. A flexible pricing strategy can help you to secure margins and stabilise cash flow.

What is cash flow - simply explained

Cash flow refers to the cash flow in a company, i.e. the difference between income and expenditure over a certain period of time. Cash flow shows how much money is actually coming into the company and how much is going out. A positive cash flow means that more money is coming in than is going out, which is good for the financial health of the company. A negative cash flow, on the other hand, can indicate financial difficulties. Cash flow is important to understand whether a company has enough cash to pay bills, invest in new projects or pay off debt.

What are the different types of cash flow?

There are three main types of cash flow, each covering different areas of a company's finances:

• Operating cash flow
• Investing cash flow
• Financing cash flow

The three main types of cash flow explained in detail:

1. Operating cash flow: Operating cash flow results from the normal business activities of a company, such as the sale of products or services. It shows how much money the company generates through its core business after all operating costs such as wages and salaries, rents and material costs have been paid. A positive operating cash flow shows that the company is able to cover its current expenses from current income.

2. Investing cash flow: Investing cash flow refers to the cash outflows and inflows resulting from investments. This includes the purchase or sale of equipment, property, machinery or other long-term assets. A negative investing cash flow is often a sign that a company is investing in growth and expansion, while a positive investing cash flow may indicate that the company is selling assets or reducing its investments.

3. Financing cash flow: Financing cash flow includes cash flows from financing activities such as raising or repaying loans, issuing or buying back shares and paying dividends to shareholders. A positive cash flow indicates that the company has raised capital in the form of loans or equity, while a negative cash flow indicates that the company has repaid debt or paid dividends.

How do you calculate cash flow?

There are two ways to calculate cash flow, depending on which aspect of cash flow you want to look at. Here is a simple explanation of how to calculate operating cash flow, which is often considered the most important cash flow measure:

Direct method:

Operating cash flow = income - operating expenses

To determine your income from sales, you collect all the income that your company has received from the sale of products or services. The operating expenses must be subtracted from this. They result from salaries, rent, materials and other operating costs.

Indirect method:

Operating cash flow = net income + non-cash expenses

The net result can be found in the income statement. Non-cash expenses such as depreciation and amortisation or provisions are added together, as these do not directly affect the cash flow. Take into account changes in current assets, such as the increase or decrease in receivables and liabilities.

An example of the cash flow calculation:

Your company has earned € 100,000 from sales, spent € 60,000 on operating costs and the net result is now € 20,000. Depreciation of € 5,000 is also taken into account. This results in the following calculation:

Operating cash flow = €100,000 - €60,000 + €5,000 = €45,000

The result shows how much money your company has actually generated after current expenses have been covered.