Part 1 - Start with Your Actual Business Performance
The first step to creating a cash flow projection is looking at your actual budget which reflects how your company has historically performed. As each month passes, enter the amount of revenue and costs you have over a 12-month period. This creates a schedule that shows you, from a cash flow standpoint, what your company is actually doing.
Get a Cash Flow Projection tailored for your business. Consult a CFO today.
Step 1 - Enter Beginning Cash
Start by entering the cash balance at the beginning of every month.
In the example above, $97,584 is the Ending Cash from December 2018. This number is from QuickBooks once the month has been reconciled. This is NOT the number from your bank account balance. If you start with your bank account balance, the forecast will be incorrect. That’s because it does not take into account the checks you’ve written that haven’t cleared yet, the checks you haven’t deposited yet, and other variables. Get this number from your bookkeeper by asking for the reconciled cash balance at the end of the month.
Step 2 - Enter Profit and Loss
After that, cut and paste the numbers from your Profit and Loss statements from the same periods(Jan, Feb, all the way to Dec) into the spreadsheet.
Include your revenue(total income) and costs(cost of goods sold). The total income is your gross revenue or everything you sold that month. The cost of goods sold(cogs) is the expenses that are directly related to what you sell. For example, if you manufacture ball caps, your cogs will be the cotton fabric you purchase to make hats, the plastic for snapback closures, and the labor for sewing machine operators. It does NOT include your costs for marketing, rent, or insurance. If you have a service-based industry, your billable employees who perform the service are part of your cogs. Whereas your staff and administrative employees’ salaries are not a part of cogs.
In the example below, the total cost of goods sold in Jan 2019 is $114, 516 including merchant fees, postage and shipping, payroll, freight, and materials.
Learn more about the cogs calculation.
Step 3 - Enter Gross Profit
“Gross profit is one of the most underutilized metric on a financial statement” - Ryan Steck, Director of CFO Services
The gross profit shows you how profitable your business would be if you had no costs to run your business or if operated from your basement. Steck explains that gross profit answers the question, “Can you be successful at your business doing what you do at the most streamlined level?”
In the example above, the gross profit for January is $200,239.
Gross profit = Total Income - Total Cost of Goods Sold
Steck recommends showing your gross profit as a percentage to see how much out of every dollar made you can keep. To figure out your gross profit percentage, divide your gross profit($200,239) by your total sales($314,755). In January, $.64 of every dollar made can be applied to operating costs to keep the business running.
200,239 / 314,755 = .64
To determine if you have a healthy gross profit, the higher the percentage the more money you are making. If you have a high volume business that manufactures items that are cheaper to make and priced lower, you can operate with a lower gross profit if you sell a lot of items every month. Generally, having a gross margin of 50% or more is a good sign for your business. Steck notes that every industry is different.
Step 4 - Enter Total Expenses
The next step is entering all of your overhead costs or operating expenses. In the example below, the total expenses are $119,427 for January.
After totaling your expenses, you can calculate the Expense to Revenue to see how much of your income is used for your overhead costs. You can see from the example above that almost 38% of the money made in January was used to run the business. In other words, for every dollar generated you spend $.38 to keep the lights on.
119,427 / 314,755 = .379
Your advertising and marketing budget is included in your operating expenses. Marketing and advertising expenses are a growth component and should not be treated as a sunk cost.
Step 5 - Net Income
Your net income is NOT your net cash. To calculate net income, take the difference between your total expenses($119,427) and your gross profit($200,239). In the example above, the net income is $80,812 in January.
200,239 - 119,427 = 80,812
Figuring the net income is where most entrepreneurs go out of business. You cannot add your beginning cash and net income to calculate your ending cash. The movement of cash in and out of your business never stops and you have to account for that. Inventory, loans, and timing of AR/AP all impact how much cash you have available but do not factor in your expenses. Your net income is not a great indicator of what is happening to your cash that month. It shows you what you earned that month, but not your cash position.
Entrepreneurs love a clean P&L statement. You want to know what your bottom line is or the net income, but that’s only half of the story. If you feel like you're behind on your cash flow, it’s time to embrace your Balance Sheet.
“If you’re going to gauge the overall health of the company, it starts with the Balance Sheet.” -Ryan Steck
Step 6 - Enter Balance Sheet Activity
To figure your ending cash correctly, you have to understand how the Balance Sheet is affecting your cash that month. Here’s how:
Look at the balance at the end of the previous month. That’s Dec 31, 2018 in this example. Then look at the current month’s ending balance(Jan 2019) and find the difference between the 2 balances.
The Accounts Receivable line shows a positive increase in cash of $2,576 from December to January, meaning you collected more cash in January 2019.
Jan 31, 2019 Receivables - Dec 31, 2018 Receivables = 2,576
Make sure you are using the last day of the month’s balances to calculate the difference.
On the next line, Inventory is showing negative $40,525. That’s a significant amount of cash going out of the business.
In this case, the negative number means you purchased a lot of new inventory that month.
When you purchase inventory, it goes on your Balance Sheet, not your cost of goods sold. You don’t expense inventory when you first purchase it, even though your cash goes down. The moment you sell the inventory is when it’s recognized as cogs and your income and cash go up. The reason why you don’t expense your inventory as soon as you buy it is to match the sale to the actual cost. It’s important to understand your profit per item sold.
Learn more about common inventory mistakes.
Any negative number(Inventory, Other Assets, Other Liabilities) is showing money going out. Any positive number(Accounts Receivable, Distributions) is showing cash going into your business.
At the end of the Balance Sheet Activity are Distributions. Distributions will show positive in the first month and negative in the rest of the months of the year. As business owners, you have the ability to pull money out or put money back into the company. That is what distributions track. This is not a wage or W-2 salary. Your withdrawals from the company are not an expense but do affect your cash flow forecast.
In the example above, the owner put $14k into the business and withdrew small amounts in the following months($557 in February, $270 in March, and so on). At the end of the year an amount of $13k was taken out in distributions as repayment or possibly a bonus.
Step 7 - Calculate the Ending Cash
After figuring your balance sheet activity, you can now calculate the ending cash. For January, the total balance sheet activity is a negative $68,828. This includes the difference in cash you received from December to January, the inventory you purchased, and the debt you paid down.
To calculate the ending cash add the beginning cash and the net income, then subtract the total balance sheet activity.
Ending Cash = Beginning Cash + Net Income - Total Balance Sheet Activity
The ending cash in January is $109,558.
97,588 + 80,811 - 68,838 = 109, 558
Step 8 - Enter Ending Cash as the Beginning Cash for the Next Month
Now that you have the ending cash for January, use this number as the beginning cash for February in the next column of your sheet. Continue with the same process of entering numbers from the P&L and Balance Sheet to calculate the ending and beginning cash for each month for the remainder of the year. Repeat the steps for a 12 month period to see the seasonality of the company. This will reveal the big picture of how your company performed that year.
Your actual budget is complete! You may be scratching your head and thinking, “How does this help me make projections for the future?” The first part was making the actual budget. The second part is where you make the projections for the next year based on your company’s performance last year with the schedule you just created.