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.


Download Practice Workbook

Download the practice workbook below.


Step-by-Step Procedure to Create Weekly Cash Flow Statement Format in Excel

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

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.

Read More: How to Prepare Daily Cash Flow Statement Format in Excel


Step 2: Create Section for Opening Balance

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.

Read More: Create Cash Flow Statement Format Using Direct Method in Excel


Step 3: Document All Cash Inflows

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.

Weekly Cash Flow Statement Format in Excel

Read More: Create Cash Flow Statement Format with Indirect Method in Excel


Step 4: Compute 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)

Weekly Cash Flow Statement Format in Excel

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

Weekly Cash Flow Statement Format in Excel

Read More: Cash Flow Statement Format in Excel for Construction Company


Similar Readings


Step 5: Document All Cash Outflows

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.

Weekly Cash Flow Statement Format in Excel

Read More: How to Create Cash Flow Projection Format in Excel


Step 6: Compute 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)

Weekly Cash Flow Statement Format in Excel

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

Weekly Cash Flow Statement Format in Excel


Step 7: Calculate Net Cash Flows

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

Weekly Cash Flow Statement Format in Excel

  • Then, press Enter to apply the formula.

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

Weekly Cash Flow Statement Format in Excel

Read More: How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)


Similar Readings


Step 8: Estimate Closing Balance

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

Weekly Cash Flow Statement Format in Excel

  • Then, press Enter to apply the formula.

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

Weekly Cash Flow Statement Format in Excel


Step 9: Replicate Starting Balance Formula for 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

Weekly Cash Flow Statement Format in Excel

  • 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: Verify 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.

Weekly Cash Flow Statement Format in Excel

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

Weekly Cash Flow Statement Format in Excel

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

Weekly Cash Flow Statement Format in Excel

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

Weekly Cash Flow Statement Format in Excel

Read More: How to Create Monthly Cash Flow Statement Format in Excel


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. Don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo