# How to Prepare Daily Cash Flow Statement Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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. ### 📌 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 • 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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.

## Related Articles 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 Advanced Excel Exercises with Solutions PDF  