Cash Flow Statement Dashboard

High Level Design

Overview

A cash flow statement is a financial statement that provides aggregate data regarding all cash inflows a company receives from its ongoing operations and external investment sources, as well as all cash outflows that pay for business activities and investments during a given period. The cash flow statement (CFS) measures how well a company manages its cash position, meaning how well the company generates cash to pay its debt obligations and fund its operating expenses.

In this BI solution we propose the method of using Sisense BI analytics in the assessment of the Cash Flow statement. In our CF analysis solution, we use cash basis accounting instead of accrual basis accounting. The purpose of this Industry analysis is to show where an entities cash is being generated (cash inflows), and where its cash is being spent (cash outflows), over a specific period (usually quarterly and annually). It is important for analyzing the liquidity and long-term solvency of a company.

We calculated Cash Flow by making adjustments to net income by adding or subtracting differences in revenue, expenses, and credit transactions (appearing on the balance sheet and income statement) resulting from transactions that occur from one period to the next. And we apply direct method of cash flow calculating (these figures are calculated by using the beginning and end balances of a variety of a business accounts and examining the net decrease or increase in the accounts).

Data sources are commonly used and can be General Ledger, Trial Balance, Balance sheet, P&L or Cash Flow Statement itself. It is used by a variety of stakeholders, such as credit and equity investors, the government, the public, and decision-makers within the organization. These stakeholders have different interests and apply a variety of different techniques to meet their needs. (For instance, CFS allows investors to understand how a company's operations are running. The CFS is important since it helps investors determine whether a company is on a solid financial footing).

Goals

Prepare comprehensive cash flow analysis to make proper disclosure for the liquidity and long-term solvency of a company and enable to make accurate managerial decisions for the effective liquidity management.

Objectives

The set of the objects below enables to reach out the goal above:

  • Prepare Cash Flow Statements under IFRS based on available General Ledger, Balance Sheet, and Profit and Loss Account;
  • Provide well-visualized dynamics of the major items of the cash flow statements;
  • Investigate the possible gaps of liabilities repayment schedule to define the amount of required financing and time when it`s expected to be required;
  • Analyze Net Change in Cash and Free Cash flow to define the company`s demand on financing during the operating period;
  • Analyze Net Operating Cash Flow and Gross Operating Cash flow with the EBITDA disclosure that enables to see major risk in the financing of operating activity;
  • Analyze amount Interest Accrued from one hand and Net Cash Flow from other hand to be able to find out whether it`s possible to repay debt during the analyzed period;
  • Estimate the level of the company`s internal accruals that effect fin result but not effect cash flow;

KPIs Architecture

Objectives

KPIs

Measures

Data Source

1.Cash Flow Statements

Cash Flow Statements 

Cash flow Statement prepared under IFRS

Fact.General_Ledger

2.Working investment changes  

Change in Inventory

Change in Receivables

Change in Payables

Change in Accrued Liabilities

Dynamics of major Cash Flow Operational items

FACT.Cash_Flow

3.Loan Default Analysis

Repayment Ratio

NOCF / Interest expense for current period  

FACT.Cash_Flow

Net Operational Cash flow

EBITDA – SUM (Change in Inventory,

Change in Receivables,

Change in Payables,

Change in Accrued Liabilities)

FACT.Cash_Flow

4. NCC vs FCF (Debt Service)

Net Change in Cash

Resulting item in the Cash flow prepared by Direct method

FACT.Cash_Flow

EBITDA

Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses)

P&L_transpose

FREE CASHFLOW A/DEBT SERVICE

SUM (NET OPERATING CASHFLOW) – SUM (Investment Income (Interest / Dividend), Debt Repayments, Lease Expense, Other Income / Expense)

FACT.Cash_Flow

 

 

 

 

 

 

 

 

5. NOCF vs GOFT

EBITDA

(earnings before interest, tax, depreciation, amortization) 

Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses)

Sales, Cost of Sales, SG&A Expenses, Other Operating Income – from FACT.P&L_transpose

Net Operating Cash Flow  

GROSS OPERATING CASHFLOW – sum(changes in Inventory, AR, AP, AL, working investments)

FACT.Cash_Flow

Gross Operating Cash Flow

Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses) 

–       Cash Interest Paid – Taxes Paid.

FACT.Cash_Flow – P&L.Transpose

6. Net_CF vs Interest Accrued

Net Cash Flow

Resulting item in the Cash flow prepared by Direct method

Fact.Cash_flow

EBITDA (earnings before interest, tax, depreciation, amortization) 

Sum(Sales)- Sum(CoS) – Sum(R&D Expense)

– Sum(SG&A Expense)

-Sum(Depreciation)

+ Sum(Other Operating Income/-Expenses)

P&L.Transpose

Interest Accrued  

Interest accrued from Balance sheet and P&L

Balance_sheet_values

P&L_transpose

7. Internal Accruals Analysis

Depn/ Total CapEx & Acquisitions

Depreciation (Balance Sheet analysis) / CapEx & Acquisitions (cash flow)

)

FACT.Balance_sheet_values

Cash_flow_transpose 

Depreciation/ Gross Fixed Assets

Depreciation (Balance Sheet)

Assets (balance sheet)

 

FACT.Balance_sheet_values

Plugins & Scripts

Implementation
Kit

The following resources will enable you to design your dashboard and data model with sample data and then apply it to your own data. Note that you will need to have a previously installed version of Sisense (you can use the free trial version if you’re not a customer).

Sample data and dashboard examples (direct download)

Documentation