How to Create Weekly Cash Flow Statement Format in Excel

A Cash flow statement is a financial statement that provides information regarding the cash inflows and cash outflows. More importantly, the cash flow statement provides the closing balance after calculating net cash flows. In this article, we will show how to create a weekly cash flow statement format in Excel. Most companies create weekly cash flow statements to review the cash flow on the regular basis. I hope you find this article informative and gain lots of knowledge regarding the weekly cash flow statement.

To create a weekly cash flow statement format in Excel, we have shown step-by-step procedures through which you can create one easily. First, we create weekly time intervals. After that, we create some sections for opening balance, cash inflows, and cash outflows. Then, using the cash inflow and cash outflow, we estimate the net cash flows. Finally, we get the closing by adding the opening balance and net cash flows. To understand the whole procedure, follow the steps carefully.

Step 1: Record Time Intervals for Weekly Cash Flow Statement Format in Excel

First, we need to enter the period. As we want to create a weekly cash flow statement format in Excel, so, we need to use weeks. Let’s assume our cash entries will be at each week.

First, select cell C4.
Then, write Week 1 as a period.

After that, as many weeks as you need for your cash entries. We take eight weeks.

Step 2: Create a Section for Opening Balance of the Weekly Cash Flow Statement

In this step, we need to create the opening balance section. For any cash flow statement, the opening balance is a must. We need to put it at the top before starting the cash flow calculation. Select cell B5 and write Opening Balance.

Then, format the whole row where we can put our input values. See the screenshot.

Step 3: Documenting All the Cash Inflows in the Cash Flow Statement

After creating a section for the opening balance section, we need to prepare the documents of all cash inflows. In this section, we need to add all the cash inflows regions from which your company will get cash. You can add several types of cash inflow regions in this section. We add four different sections where we can get the money to use for business.

Step 4: Calculating the Total Cash Inflows

After creating the cash inflows documents, we need to compute the total cash inflows from four different regions. To do this, we will use the SUM function.

First, select cell B11 and write down Total Cash Inflows.

Then, select cell C11.
Write down the following formula in the formula box.

=SUM(C7:C10)

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon up to cell J11 to replicate the formula for all cases.

Step 5: Listing All the Cash Outflows from the Weekly Cash Flow Statement

After completing the segment of cash inflows, we will turn our focus toward cash outflows. In a company, cash outflows can be of several types. You need to document all kinds of cash outflows for your company. We take five different regions from where cash outflows can happen.

Step 6: Calculating the Total Cash Outflows

After finishing the document of all cash outflows, we need to calculate the total cash outflows in our company. To calculate the total cash outflows, we need to add all the possible cash outflows by using the SUM function.

First, select cell B18 and write down Total Cash Outflows.

Then, select cell C18.
Write down the following formula in the formula box.

=SUM(C13:C17)

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon up to cell J18 to replicate the formula for all cases.

Step 7: Obtaining the Net Cash Flows in the Weekly Cash Flow Statement

Now, we have both total cash inflows and total cash outflows. So, the next step is to calculate the net cash flows. We all know that the net cash flows are the difference between total cash inflows and total cash outflows.

First, select cell B19 and write Net Cash Flows.

Then, select cell C19.
Write down the following formula.

=C11-C18

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon up to cell J19.

Step 8: Estimating Closing Balance of the Weekly Cash Flow Statement Format

After calculating the weekly net cash flows of a company, we will focus on estimating the closing balance. The closing balance is the summation of the opening balance and the net cash flows.

First, create a section for Closing Balance in cell B20.

Then, select cell C20.
Write down the following formula.

=C5+C19

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon up to cell J20 to replicate the formula.

Step 9: Applying the Starting Balance Formula to the Remaining Cells

Every period’s closing balance is equal to the opening balance of the next period. That is how a company works. The net cash flows can be negative. In that case, the closing balance also decreases. For this reason, the opening balance of the next period will also decrease. We want to create a link between the closing balance and the opening balance of the next period.

First, select cell D5.
Then, write down the following formula.

=C20

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon up to cell J5 to replicate the formula.
This is our weekly cash flow statement format. See the screenshot.

Step 10: Verifying Weekly Cash Flow Statement Format with Data

After creating the weekly cash flow statement format in Excel, we need to verify it by applying several data.

First, select cell C5 and enter a value of $10000 as an opening balance.
As there is no cash inflow and cash outflow, you will see the same closing balance.

Then, set the cash inflow values in week 1.
Because of this, there is a chance between opening balance and closing balance.

After that, put the cash outflow values in the cash statement format.
As a result, you will see a change in net cash flows and closing balance.

As the weekly cash statement format is working well in the first week, we need to put all the values for the next seven weeks. To get the entire scenario.
This is our weekly cash flow statement in Excel.

Download Practice Workbook

Download the practice workbook below.

Weekly Cash Flow Statement Format.xlsx

Conclusion

We have shown step-by-step procedures to create a weekly cash flow statement format in Excel. All of these steps are fairly easy to use. To create the weekly cash flow statement, we utilize cash inflows, cash outflows, and net cash flows. I think we covered all possible areas of this topic. If you have any further questions, feel free to ask in the comment box.

How to Create Cash Flow Statement Format in Excel

<< Go Back to Cash Flow Template | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!