A Cash Flow Diagram is one of the crucial tools you need to account for every coin getting in and out of your business.
So, what is it?
You can use this chart to visualize the expenses and income generated by your business for clarity.
The visualization design comes in handy in organizing and examining securities, especially investment swaps. You can also leverage it to track installment plans for bonds, mortgages, and other kinds of credits.
The charts are amazingly easy to read and interpret.
Excel lacks ready-made Cash Flow Diagrams for your visualization needs. You don’t have to dump this spreadsheet app in favor of other expensive instruments.
You have an option of installing a particular add-in to access ready-made and insightful Cash Flow Diagrams for your data stories. Keep reading to discover more.
In this blog, you’ll learn:
Before jumping right into the how-to guide, we’ll address the following question: what is a Cash Flow Diagram?
Definition: Cash flow is the net amount of cash and cash equivalents coming in and out of your business.
The cash you’ve received represents inflows. On the other hand, the expenditures represent outflows. Your ability to create value for shareholders is determined by your brand’s ability to generate positive cash flows.
The amount of data you can capture with cash flow models is massive. And this means you risk analysis paralysis if you don’t visualize this data for clarity.
This is where Cash Flow Diagrams, such as Sankey Diagram, come in.
A Cash Flow Diagram in Excel (Sankey Diagram example) displays insights into the income and expenses in a specified time. It consists of nodes and flows that represent the cash in and out-flows. In the coming section, we’ll address the types of cash flows you can use in your data stories.
In the coming section, you’ll discover how to create a Cash Flow Diagram in Excel.
Excel is one of the most-used visualization instruments. However, the spreadsheet application lacks ready-made Cash Flow Diagrams, such as Sankey Diagram.
We’re not advising you to do away with Excel in favor of other expensive tools.
And this is because there’s an amazingly affordable visualization tool that comes as an add-in you can easily install in Excel to access insightful and ready-to-go Cash Flow Diagrams. The tool is called ChartExpo.
So, what is ChartExpo?
ChartExpo is an add-in you can easily install in your Excel without needing tutorials.
With many ready-to-go charts, graphs, and maps, the ChartExpo turns your complex, raw data into easy-to-interpret and visually appealing Cash Flow Diagrams using Sankey Chart in Excel that tell data stories in real-time.
More benefits
In the coming section, we’ll take you through how to plot a Cash Flow Diagram in Excel.
This section will use a Sankey Diagram to visualize the tabular data below for analyzing cash flow.
Income Source | Income Type | Income | Spending Source | Spending Type | Count |
Salary | Earned Income | Income | Deduction | Income Tax | 494 |
Salary | Earned Income | Income | Deduction | Social Justice | 677 |
Salary | Earned Income | Income | Core Expenses | Bill Expenses | 758 |
Salary | Earned Income | Income | Core Expenses | Food | 933 |
Salary | Earned Income | Income | Core Expenses | Personal Care | 649 |
Salary | Earned Income | Income | Core Expenses | Transportation | 825 |
Salary | Earned Income | Income | Financial Independence | Pension | 536 |
Salary | Earned Income | Income | Financial Independence | Investment | 392 |
Credit Card Reward | Passive Income | Income | Financial Independence | Real Estate | 287 |
Dividends | Passive Income | Income | Disposable Income | Emergency Fund | 262 |
Interest | Passive Income | Income | Disposable Income | Leisure | 147 |
Next, we will show how to visualize data using the Cash Flow Diagram(Sankey Diagram) with the help of a video tutorial.
In the following video, we will show you how to create a cash flow chart in excel in a few clicks without any coding.
There’re three types of cash flows, namely:
Operating cash flow is generated from your operations. And it include cash sales and the cost of goods sold (utilities, salaries, etc).
Conversely, cash flow from investments is the money spent on buying stocks or bonds. Cash inflow is generated by interest and dividends paid on these securities.
Lastly, financing cash flow is the expense of acquiring capital.
So, what are the cash flow statement features?
A Cash Flow Statement is very dynamic because it records the investment of cash from the start to the end of a particular financial year.
This statement does not recognize matching principles. Also, you can use it to calculate cash from operations flows.
Besides, it displays the changes in your financial positions associated with the operational, investing, and financial, respectively.
Next, we’ll show you how to calculate a cash flow.
Calculating a cash flow is not complicated.
And this is because it’s a reflection of your business’s cash in and outflows. The simplicity ends there for most small business owners. We recommend every business owner to take time and understand cash flow calculations.
Below are the 3 key cash flow formulas you can use.
One of the key cash flow formulas is Free Cash Flow (FCF).
While a traditional cash flow statement gives you a picture of your cash flow at a given time, it doesn’t help with planning and budgeting. And this is because it does not reflect available cash.
Calculating Free Cash Flow is easier than you think. To start, generate your income statement.
First, let’s define some terms to make everything easier for you.
The net income is the total income left after deducting your costs from gross revenue. You’ll find the aforementioned details in the Income Statement.
A huge chunk of your business assets lose value over time.
Depreciation measures how that value decreases. On the other hand, amortization is breaking down the initial cost of an asset. Depreciation and amortization data are in your Income Statement.
The working capital is the difference between assets and liabilities. Also, it represents the capital you use in your business operations.
Calculate your working capital using the total assets and liabilities on your Balance Sheet.
Capital expenditure is the cash you spend on fixed assets, like land, real estate, etc. Capital expenditure data is in the Statement of Cash Flows.
Check out the final Free Cash Flow (FCF) formula.
Free Cash Flow = Net income + Depreciation/Amortization – Change in Working Capital – Capital Expenditure
Look at the example below.
You’re a copywriter and intend to calculate your Free Cash Flow to evaluate whether it’s pragmatic to hire a virtual assistant.
Assume the figures below depict your finances.
So, your Free Cash Flow (FCF) is represented by:
$150,000 + $0 – $20,000 – $3,500 = $126,500
$126,500 is the Free Cash Flow (FCF) available to reinvest back into your business.
Keep reading because, in the coming section, you’ll discover how to plot a Cash Flow Diagram in Excel. You don’t want to miss this.
While FCF shows the cash available to reinvest in the business, it doesn’t always paint a reliable picture of your normal cash flow.
And this is because Free Cash Flow (FCF) formula doesn’t account for:
If you sell a high-value large asset, the FCF will go way up, which does reliably reflect your average cash flow.
To calculate an Operating Cash Flow, pull out your balance sheet and income statement.
First, let’s define some terms to make everything easier for you.
The Operating Income is also the Earnings before Interest and Taxes (EBIT).
To calculate this metric, subtract operating expenses ( wages paid and cost of goods sold) from total revenue.
The Operating Cash formula (OCF) is:
Operating Cash Flow = Operating Income + Depreciation – Taxes + Change in Working Capital
Look at the example below.
Operating Cash Flow formula is represented by:
$75,000 + $0 – $11,000 + (-$19,000) = $45,000
While Free and Operating Cash Flow give you a good idea of income flow, they won’t help you with planning for the future. This is where forecasting your cash flow comes in.
Cash Flow Forecast is one of the easiest metrics to calculate.
Check out the CFF formula below.
Cash Flow Forecast (CFF) = Beginning Cash + Projected Inflows – Projected Outflows = Ending Cash
First, let’s define some terms to make everything easier for you.
Beginning income is the cash your business has on hand today. You can get this data at your Statement of Cash Flows.
The project inflows is the income you expect to receive in a particular period. It includes current invoices that will come due. And future invoices you expect to receive cash for.
The project outflows are the aggregate costs you incur in a given financial year.
Look at the example below.
Check out the Cash Flow Forecast (CFF) formula below
$60,000 + $20,000 – $10,000 = $70,000
The CFF is $70,000
There’re three types of cash flows, namely:
Operating cash flow is generated from your operations and cash sales. Cash flow from investments is the money spent on buying stocks or bonds (investments).
Lastly, financing cash flow is the expense of acquiring capital.
A Cash Flow Diagram is one of the crucial tools you need to account for every coin getting in and out of your business.
You can use this chart to visualize the expenses and income generated by your business for clarity.
The visualization design comes in handy in organizing and examining investment securities. You can also leverage it to track installment plans for bonds, mortgages, and other kinds of credits.
The charts are amazingly easy to read and interpret.