While working in Microsoft Excel with the sales-related worksheets, sometimes we need to make a cash flow diagram of sales to understand the sales conditions for earning a profit for a company. Making a cash flow diagram in Excel is an easy task. This is a time-saving task as well. Today, in this article, we’ll learn five quick and suitable steps to draw a cash flow diagram in Excel effectively with appropriate illustrations.
Introduction to Cash Flow Diagram
A financial tool called a cash-flow diagram is used to show the cash flows related to a project, security, or company. Cash flow diagrams are frequently used in structuring and assessing securities, notably swaps, as shown in the illustrations. They can also provide a visual representation of bond, mortgage, and other loan payment schedules.
Management accountants and engineers use these to illustrate the cash transactions that will occur during a project in the context of business and engineering economics. Initial investments, maintenance expenses, anticipated project earnings or savings, as well as the equipment’s salvage and resale value, can all be included in transactions. The break-even point is then calculated using these diagrams and the related modeling. They are also used to further and more broadly assess operations and profitability.
How to Draw a Cash Flow Diagram in Excel: Step-by-Step Procedures
Let’s say we have a dataset that contains information about the income and expense statements of the XYZ group. The description of the income and expense and the running balance are given in columns B, and C respectively. First of all, we will make a dataset with parameters. After that, we will make a cash flow diagram in Excel to understand the running balance of the XYZ group. Here’s an overview of the dataset for today’s task.
Step 1: Make a Dataset with Proper Parameters
In this portion, we will create a dataset to draw a cash flow chart in Excel. We will create a dataset that contains information about the XYZ group’s income and expense statement. So, our dataset becomes.
Read More: How to Calculate Annual Cash Flow in Excel
Step 2: Applying Charts Group
Now, we will use the Charts group option under Insert ribbon to draw a cash flow diagram from our dataset. This is an easy task. This is a time-saving task as well. Let’s follow the instructions below to create a cash flow diagram in Excel!
- First of all, select the range of data to draw a cash flow diagram. From our dataset, we select B4 to C14 for the convenience of our work.
- After selecting the data range, from your Insert ribbon, go to,
Insert → Charts → Recommended Charts
Read More: How to Calculate Incremental Cash Flow in Excel
Step 3: Using Waterfall Chart Option
In this section, we will use the waterfall option to draw a cash flow diagram. Let’s follow the instructions below to learn!
- As a result, an Insert Chart dialog box will appear in front of you. From the Insert Chart dialog box, firstly, select the All Charts Secondly, select the Waterfall option. At last, press the OK option.
- After pressing the OK option, you will be able to draw a cash flow diagram using the dataset which has been given in the below screenshot.
Read More: How to Calculate Discounted Cash Flow in Excel
Step 4: Give a Title to Cash Flow Diagram
After creating the cash flow diagram, we will give that cash flow diagram a title. Let’s follow the instructions below to learn!
- Now, we will give the title of the chart. The title is “Cash Flow Diagram”.
Read More: How to Forecast Cash Flow in Excel
Step 5: Formatting Cash Flow Diagram
Now, we will give the format of the cash flow diagram. To do that, follow the instructions below.
- To give formatting of the cash flow diagram, first of all, press any place on that chart. Do like the screenshot below.
Read More: How to Calculate Cumulative Cash Flow in Excel
Cash Flow Diagram Calculator
You can use today’s workbook as a calculator to calculate the cash flow diagram. There is a sheet name Calculator.
Explore that sheet. You will find fields for Sales, Materials, Consulting, Direct Labour, Wages, Utility, Repair, Telephone, Vehicles, and Accessories. Insert your values. It will calculate the total revenue earned from the cash flows given in the below screenshot.
For your understanding purposes, I’ve given an example with several values of expense and revenue earned. You can insert as many profits and losses as you want.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable steps mentioned above to make a cash flow chart will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
