How to Prepare Daily Cash Flow Statement Format in Excel

This article illustrates how to prepare a daily cash flow statement format in excel. A daily cash flow statement is to update the cash flows of a company on a daily basis. It will include all the cash receipts and payments. The cash receipts may include cash sales, interest income, tax refund, etc. On the other hand, cash payments include the costs of goods sold, operating expenses, and other additional expenses. Follow the article to learn how to create a customized daily cash flow statement format for your company in excel.


Download Sample Workbook

You can download the free template from the download button below.


Steps to Prepare a Daily Cash Flow Statement Format in Excel

Follow the 9 steps below to make a daily cash flow statement format in excel.

πŸ“Œ Step 1: Create a Header and Input Company Information

  • Then, add an input section for company information as below.

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


πŸ“Œ Step 2: Create a Date Row for Daily Cash Flow

  • Next, create a section to enter the date of the day for the daily cash flow inputs. You can format the dates as required.

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


πŸ“Œ Step 3: Record Beginning Balance

  • After that, add another section to record the cash on hand i.e. the beginning balance.

daily cash flow statement format in excel


Similar Readings


πŸ“Œ Step 4: Input Cash Receipts

  • Next, you can use the SUM function to total the records. Enter the following formula in cell C18 and drag the fill handle icon to the right.
=SUM(C12:C17)

  • After that, apply the following formula in cell H12 to get the total. Then, copy the formula down using the fill handle icon.
=SUM(C12:G12)

daily cash flow statement format in excel

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


πŸ“Œ Step 5: Record Cash Payments

  • Now you need to record the daily cash payments for the cash flow statement. First, input the cost of goods sold. You can get the totals in the same way as earlier.

  • Then, record the daily operating expenses.

daily cash flow statement format in excel

  • Next, input all the other daily additional expenses as shown below.

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)


πŸ“Œ Step 6: Calculate Total Cash Payments

  • Now you need to calculate the total payments using the following formula.

Total Cash Payments = Total ( Cost of Goods Sold + Operating Expenses + Additional Expenses )

  • Enter the following formula in cell C66. Then drag the fill handle icon to the right.
=SUM(C27,C56,C64)


Similar Readings


πŸ“Œ Step 7: Estimate Net Cash Change

  • Net cash change is the difference between receipts and payments.

Net Cash Change = Total Cash Receipts – Total Cash Payments

  • Apply the following formula in cell C68. Then drag the fill handle icon to the right.
=C18-C66

Read More: How to Calculate Net Cash Flow in Excel (3 Suitable Examples)


πŸ“Œ Step 8: Calculate Month Ending Cash Position

  • The month-ending cash position follows the formula given below.

Month Ending Cash Position = Cash On Hand + Net Cash Change

  • Now enter the following formula in cell C70. Then drag the fill handle icon to the right.
=SUM(C9,C18,-C66)

daily cash flow statement format in excel

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


πŸ“Œ Step 9: Finalize the Daily Cash Flow Statement

  • Your daily cash flow statement format is almost ready. Now you need to enter a formula to calculate the beginning balance at the start of each day.
  • First, enter the cash on hand at the start of the first day in cell C9. Then, apply the following formula in cell D9. Next drag the fill handle icon to the right.
=C70

  • Do not enter data in column G i.e. the column immediately before the Total column. Right-click on it and insert a new column before it to enter more data in the coming days. This way you won’t have to worry about changing any formulas.

Things to Remember

  • You need to apply the accounting number formatting to the daily cash flows.
  • Instead of entering data in the last column before the Total column, use it only to insert columns before it. This is to make the formulas in the Total column auto include the inserted cells.

Conclusion

Now you know how to prepare a daily cash flow statement format in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo