Md. Shamim Reza
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
- 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.
Similar Readings
- How to Create Cash Flow Projection Format in Excel
- Calculate Present Value of Future Cash Flows in Excel
- Apply Discounted Cash Flow Formula in Excel
- How to Calculate Payback Period in Excel (With Easy Steps)
📌 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)
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.
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
- Cash Flow Statement Format in Excel for Construction Company
- Calculate IRR in Excel for Monthly Cash Flow (4 Ways)
- How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)
- Calculate Future Value of Uneven Cash Flows in Excel
📌 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)
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.
