You don't need to use a particular app or even hire a bookkeeper to create financial documents. All you need is Microsoft Excel and a little know-how.
Most financial statements are composed of the cash flow statement, the income statement, and a balance sheet. But for now, let's focus on the first document.
So, here's a guide on how to make your cash flow statement in Excel.
What Is a Cash Flow Statement?
A cash flow statement is a financial document that shows the movement of cash and cash equivalents in your business. You could see where all the money in your business came from and where you spend it.
This document can tell you how you're managing your operations. Do you have enough cash to operate your business? Can you pay off your coming obligations?
You can accurately tell the cash you have available for any given period with this.
1. Choose a Period to Cover
Cash flow statements are usually broken down into monthly periods. That way, you can see how much you gained and spent.
Most businesses start their fiscal year at their founding date. But, you can choose to begin in other months (like January) if it's more convenient for you.
2. Prepare Your Data
Before creating your cash flow statement, you should have your data on hand. This allows you to get accurate results. So, if you have a journal, keep it within your reach.
If you don't have a journal, a list of all your expenses and revenue is a good starting point. As long as you write down the date, payee, payor, details, and amount of every cash movement, you can make a cash flow statement.
3. Sort Your Data
When you have all your transactions, it's time to break them down into three categories: Operations, Investments, and Financing.
Operations relate to cash made and spent for day-to-day activities. These include money made from selling products and services. Cash paid on overhead expenses, salaries, and inventory also fall under this category.
Investing covers expenses on long-term assets like property and equipment. Any cash made from the sale of these assets is also considered here.
The financing comes from investors (including the owners) and from loans. In this section, we also discuss dividends and loan repayments.
4. Create Your Excel File
To start making your Cash Flow Statement, open up Excel and create a new file.
At the top row, write down your [Company Name] Cash Flow Statement. This lets you easily identify the file upon opening.
Leave one row empty for formatting, then write Period Beginning and Period Ending in the next two rows. Do this to know what period you're exactly covering.
Again, leave one row empty, then write Cash Beginning and Cash Ending. These rows will show reflect what you have at the start and the end of the period.
5. Determine Your Subcategories
The three main categories generally remain the same across companies. However, the subcategories vary wildly. They would depend on your type of business and operations.
To help get you started, here are some excellent examples per category.
1. Operations
-
Under Cash Inflow
- Sales
-
Under Cash Outflow
- Inventory
- Salaries
- Operating expenses: these include overhead costs like rental, telecoms, electricity.
- Interest: this is the interest amount you pay on loans you've made.
- Taxes
2. Investments
-
Under Cash Inflow
- Assets Sold
- Loans Paid Back: these are payments to loans you've given to persons or institutions.
-
Under Cash Outflow
- Assets Bought
- Loans Issued: these are the amount you've lent to persons or institutions.
3. Financing
-
Under Cash Inflow
- Borrowing: these are money you receive from lending institutions.
- Stock issuance: these are investments made by the owners and other third parties.
-
Under Cash Outflow
- Loan Repayment: this is the amount you spend paying back the principal on your loans.
- Dividends: this the cash spent when sharing profits with investors and owners (including yourself).
These are the commonly used subcategories, but feel free to add more as you see fit. Just a reminder: each category must relate to actual cash spent and received.
Add an empty row at the end of each category list, then write Net Cash Flow - [Category]. This is the subtotal that shows how much money you've made (or spent) for each section.
Finally, after listing all categories, subcategories, and subtotals, at the bottom, write Net Cash Flow. This shows the total inflow (or outflow) of money you had for the period.
Don't forget to add an empty row between categories (after the subtotal) and an indent for each subcategory. You can find the Indent button under the Alignment section of the Home ribbon. This allows for cleaner formatting and makes it easier to see and analyze.
Also, resize the first column to avoid its contents from spilling over to the next columns. Double-click on the line between columns A and B to do this automatically.
6. Prepare Your Formulas
Cash Flow Statement formulas are pretty simple. All you need is to use the sum command to subtotal each category.
- First, select the Net Cash Flow - [Category] cell under the corresponding period and category subtotal.
- Then, type =sum( and choose all the cells for each section. Don't forget to hold the shift key so that you can select multiple cells.
- Once done, press the Enter key, and you should see the subtotal for that category.
4. To get the net cash flow, repeat the procedure above.
5. Type =sum(, then choose each corresponding subtotal.
6. This time, hold the ctrl key to select multiple cells that aren't beside each other.
7. Once done, press the Enter key again, and you have your total cash flow for the chose period.
To get your cash ending:
- Go to the corresponding cell and type =sum(
- While holding down the ctrl key, click on the cells with the net cash flow and cash beginning values for the corresponding period.
- Press the Enter key, and you'll get the amount you should have at the end of your chosen period.
7. Setting Multiple Months
If you want to compare your cash flow over multiple months, you need to set this formula first. Under Beginning Cash for the next month, write "=" then click on the corresponding cell for Ending Cash of the last month. This automatically copies the Ending Cash for the previous month to the Beginning Cash of the following month.
As for the rest of the formulas, all you need is to copy them across the rest of the months you want to cover.
- Hold the shift key to select all the cells from Cash Ending all the way to Net Cash Flow.
- Once done, type ctrl + c to copy them.
- Then, click on the corresponding cell for Cash Ending for the following month and type ctrl + v.
- Excel will automatically adjust these formulas to reflect the correct corresponding column.
Note: make sure there are no other values in the cells you selected except the formulas.
8. Formatting Your Rows and Numbers
Format your entries, so negative numbers appear red. This lets you analyze your statement much easier.
- To do that, select all numerical entries, then click on the dropdown menu in the Number section.
- You can find them at the Home ribbon.
3. Click More number formats... A new window called Format Cells will open.
4. Under the Number tab, go to the Category menu, then choose Currency.
5. Choose the correct Symbol in the dropdown menu.
6. Then, under the Negative numbers: subwindow, select the -$1234.10 with a red font color option.
This should let you see your expenses in red, making it easier to differentiate inflow from the outflow.
You can also select each category and subtotal row and fill them with different colors. This will make it easier to differentiate between sections at a glance.
9. Input Your Values
After you've prepared everything, all that's left is to input the actual values. Don't forget to add a negative sign on expenses! Once done, you now have a completed cash flow statement.
You can access and save a copy of this cash flow statement here.
Plan Your Next Move
Now that you've completed your cash flow statement, you have a better view of your business's liquidity. It can help you plan your next moves, allowing you to make wise decisions.
With a cash flow statement, you have the first document you need to see your overall business performance. Pair that with an income statement and a balance sheet, then you'll know exactly where you stand.