How to Draw a Cash Flow Diagram in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

cash flow diagram in excel


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.

Using Waterfall Chart 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.

Using Waterfall Chart Option

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”.

Give a Title of 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.

Formatting Cash Flow Diagram

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.

Cash Flow Diagram Calculator

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.


Related Articles


<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo