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.

Prepare a Daily Cash Flow Statement Format in Excel: 9 Easy Steps

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

📌 Step 1: Create a Header and Input Company Information

• First, create a header for the cash flow statement.

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

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

📌 Step 3: Record Beginning Balance

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

📌 Step 4: Input Cash Receipts

• Now, record all of the cash receipts as happens daily. Then, apply the Accounting number format.

• 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)`

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

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

📌 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)`

📌 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`

📌 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)`

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

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

1. YOUR’RE THE BEST SERVICE PROVIDERS ON EXCEL KNOWLEDGE, THANKS FOR IMPARTING KNOWLEDGE IN ME. KEEP UP JUST.

• Hello Joellah,

Thanks for your appreciation, it means a lot to us. We are trying our best to provide Excellent articles. Keep reading to boost your knowledge.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF