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.

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.

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

• After that, apply the following formula in cell H12 to get the total. Then, copy the formula down using the fill handle icon.

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.

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

• Enter the following formula in cell C66. Then drag the fill handle icon to the right.

📌 Step 7: Estimate Net Cash Change

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

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

📌 Step 8: Calculate Month Ending Cash Position

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

• Now enter the following formula in cell C70. Then drag the fill handle icon to the right.

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.

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

Download Sample Workbook

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

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

• Cash Flow Statement Format in Excel for Construction Company
• Create Cash Flow Statement Format Using Direct Method in Excel

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