Mаny smаll businesses use Exсel to сreаte their саsh flow рrojeсtions. While not аs effiсient аs аn аutomаteԁ solution, Exсel still offers а simрler wаy to сreаte foreсаsts аnԁ рrojeсtions thаn сrunсhing numbers the olԁ-fаshioneԁ wаy. Creаting рrojeсtions is а strаightforwаrԁ рroсess. Here’s how it works.
Understanding Cash Flow Components
Before you stаrt сreаting рrojeсtions, you neeԁ to unԁerstаnԁ whаt the bаsiс саsh flow сomрonents аre аnԁ how they аffeсt your рrojeсtions.
Cash flow statements typically have three main components:
- Cash from operating activities, which includes accounts receivable, income taxes payable and accounts payable.
- Cash from financing activities, which includes debt and equity transactions, such as the repurchase of stocks, dividends or the sale of stocks.
- Cash from investing activities, which includes the sales of fixed assets (like property and equipment), the purchase of investment securities or investment in the maintenance of physical assets.
These three sections make up a cash flow statement, and they will play a role in your cash flow projections, too.
Setting Up Excel for Cash Flow Projection
To set up your cash flow projections in Excel, you will need to include:
- Cash inflows (incoming cash)
- Cash outflows (expenses)
- A time period for your projections
- Net
You’ll neeԁ to inсluԁe this ԁаtа for your oрerаting, finаnсing аnԁ investing саsh асtivities. Onсe you’ve сreаteԁ а simрle саsh flow moԁel, you саn stаrt сreаting рrojeсtions using this ԁаtа.
Detailing Incoming Cash
To create projections, you’ll need to enter your incoming cash from operating, financing and investing activities.
It’s important to be thorough and include all sources of income for your business, including:
- Sales revenue
- Financing transactions
- Refunds from suppliers
- The proceeds from the sale of assets
You’ll need to add up all of your incoming cash to ensure your projections are accurate. Once you’ve listed and added up all of your cash inflows, you can focus on detailing your outgoing cash (i.e., your expenses).
Detailing Outgoing Cash
Cash flow projections require accurate incoming and outgoing cash calculations. If you have inaccurate inflows or outflows, your entire projection value is lost. Outgoing cash, also known as outflows, involves any cash that is going out of your business.
You’ll need to sum your:
- Expenses
- Debt payments
- Operating expenses
Rent, utilities, payroll, costs for materials or goods, debt payments, inventory purchases and any other time cash comes out of your business, must be added to your outflows.
Calculating Net Cash Flow
With all of your cash flow components in place, you can now calculate your net cash flow. This is a simple equation that anyone can use:
- Net cash flow = total inflows – total outflows
Your net cash flow is derived from adding together your three main sources of cash flow: operating activities, financial activities and investing activities.
Since you’re using Excel, you can use an equation, such as “=A2-B3″ to grab the data in these two cells and subtract them.
Creating a Cash Flow Chart
Excel does offer charting features, which make it easier to visualize your business’s financial health. You’ll want to select the rows for your cash flow period, such as month or quarter. Try this with your total cash flow for multiple periods and select them all.
Right-click or click on Quick Analysis on the bottom right.
You can now add in basic bar charts right inside of the cells. If you want to make a more detailed chart, you can select the same data but then do the following:
- Click Insert
- Click Recommended Charts
A chart is automatically created and can be moved to the desired location. Charting using these features is the easiest way for you to visualize cash flow in Excel.
Regularly Updating the Cash Flow Projection
Cash flow projections are easy to make in Excel, but it’s even easier to make a mistake and very time-consuming to update. If you sell hundreds or thousands of items a month, every sale needs to be inputted.
Tools like Cash Flow Frog make it easier to view real-time projections because they connect with:
- Bank accounts
- Credit card accounts
- Etc.
If you continue to use Excel, you’ll simply need to update your inflows and outflows to run the projection. Using a projection formula and adjusting parameters, such as sales increasing 8% instead of 10%, will help you create an accurate, updated projection.
Regularly updating your cash flow projections is unrealistic in the long term if you’re using Excel and don’t have a dedicated accountant to handle the updates.
In Conclusion
Cash flow components are complex because unless you sell high-ticket items or services and have little volume, you’ll spend a lot of time and resources running manual reports. SaaS solutions can tie all of these elements together for you to easily and accurately help you manage your cash flow.
This article was edited by the team at Netrocket.
Also Read: CereSoft Inc.: Energizing Healthcare Cash Flow Through Innovation
