Cash Flow Series #2: Building Your 18-Month Cash Flow Forecast

Welcome back to our cash flow forecasting series with Jirav! In our first article, we walked you through the three steps you need to take before you start forecasting your cash flow, which shows how much liquidity or cash your company has to cover its short-term liabilities, and should be included in a business plan. Looking at your company’s performance now with a cash flow statement and seeing your future cash positions with the projection you’re creating, provide you with an important view of your company, and will help you avoid cash shortfalls.

Since forecasting as part of cash flow management is crucial for you as a small business owner or startup entrepreneur to build a realistic financial model to keep you in business, begin by downloading the financial business model template and fill out the tab called Prep Sheet. The prep sheet follows Step 3 of the first article in the series. The information you input here is the basis of your forecast. 

You can also follow along with our video walkthrough of how to build and use the model below:

Now, let’s get forecasting.  

Running different scenarios  

Cash flow forecasting is a future-thinking process because you’re forecasting what you think, or in some cases, hope will happen. Some of the numbers you use will be based on what’s already happened in your business like, for example, monthly liability payments and fixed expenses. 

Numbers like how many new customers you’ll acquire each month are still based on factual information (like the drivers you identified in part 1 of our series), but they aren’t guaranteed. These are the numbers you’ll be adjusting as you build your financial model.  

Before you start forecasting, consider two to three scenarios you want to run in a cash flow forecast. Some examples are:

• Operating your business at 50–75% capacity 
• Meeting your break-even point
• Increasing revenue by 20% every month
• Hiring three new employees this year

Your business’s current challenges or goals, which you set in Step 1 of our prep article, will help you identify which scenarios to run. 

Pro tip: After you fill out the Prep Sheet tab, make several copies of the template so you can run different scenarios. To make a copy in Google Sheets, go to File → Make a copy. Pull up one of your copies as you read through this article so you can follow along. Let’s dive in!

Step 1: Revenue & COGS

The first step is to project your revenue for each product and service that you sell using the Revenue tab of the template. 

Each product or service that you listed on the prep sheet will auto-populate the Revenue & COGS tab. Month 1 is based on the current sales figures you entered on the prep sheet. Your job is to adjust the quantity and price of each product or service for the following months, based on your projections. (Tip: Press the + button to adjust the price and quantity for each product or service.)

Uhhhhhhh … but how do I project how much I’m going to make?

Begin by using your most recent month of financial data as a starting point. From there, consider the revenue drivers for each product or service listed. How will those drivers impact your revenue in the next six months? And how will your specific goals change those drivers?

Let’s say you’re a digital marketing agency and one of your revenue streams is new client projects. The average revenue from a new project is $1,000. 

You acquire new clients through website visitors. Currently, you receive 5,000 new visitors per month, and your conversion rate is 0.5%. Based on these numbers, every month, you receive 25 new clients, which is $25,000 in revenue. 

But, what happens if your goal is to increase new website visitors by 1,000 each month? Now your forecast would look like this:

Month 1: 6,000 x 0.5% = 30; 30 x $1,000 = $30,000

Month 2: 7,000 x 0.5% = 35; 35 x $1,000 = $35,000

Month 3: 8,000 x 0.5% = 40; 40 x $1,000 = $40,000

Also, consider what external factors could impact your revenue drivers. 

For example, say you’re a restaurant and one of your revenue streams is dine-in customers. A driver is the number of daily dine-in customers. Government-mandated COVID-19 shutdowns that ban indoor dining will impact that driver. 

Finally, keep in mind how drivers for different revenue streams can impact each other. Let’s go back to our restaurant that’s closed to dine-in customers. While that revenue stream may have plummeted to $0, some of those diners converted to take-out diners, which increased take-out revenue. 

Let’s lay out some assumptions to see the interplay:

Average daily dine-in customers: 60 (1,800 customers per month)

Average dine-in order value per customer: $40 ($72,000 revenue per month)

Number of dine-in customers converted to take-out diners: 20% (an increase of $14,400 in take-out revenue per month)

Knowing these numbers, we can now set a goal to increase the conversion of existing dine-in customers to take-out customers to recover revenue. Let’s set the goal to increase the conversion of dine-in customers to take out customers by 5% every month. Here’s how that would look:

Month 1: 1,800 x 0.25 = 450; 450 x $40 = $18,000

Month 2: 1,800 x 0.30 = 540; 540 x $40 = $21,600

Month 3: 1,800 x 0.35 = 630; 630 x $40 = $25,200

Pro tip: We know that keeping track of all these drivers is a lot. If you have complex and interrelated revenue drivers or want to create a more in-depth forecast, we recommend using Jirav’s budgeting-and-forecasting accounting software. Jirav connects to your accounting system and Gusto to import your historical financial and workforce data so that you can start planning right away.*

Step 2: Cost of Goods Sold

Under the revenue forecast is your COGS forecast. Like the revenue section, the COGS section is auto-populated by the information you entered in the Prep Sheet tab, and Month 1 reflects your current monthly sales that are cash inflows. 

For each month, project the quantity of each product that you’ll purchase and the cost of it during that period of time. 

Wait—why would the cost of my product change on a monthly basis?

As your business grows, you may find ways to decrease your product or service costs and increase your overall margins. This could even be one of your goals. 

You may change vendors, switch to using less expensive materials, or invest in machinery that reduces your labor costs. All of these changes would decrease the cost of your products or services. 

Step 3: Workforce

For many businesses, staffing has a significant impact on their overall cash flow, profitability, and ability to earn revenue. With more staff, a business’s capacity to sell their product or service (and earn revenue) increases. But payroll costs can add up, and many businesses wonder if they can afford the cost of new hires. 

When is the right time to hire someone? And how can you be sure you can afford your new hire? What about laying people off? At what point do you need to consider reducing your workforce? Forecasting your staffing needs answers these questions. 

It’s time to plan for staffing changes using the Workforce tab. In the Prep Sheet, you already added your existing staff, which will auto-populate the first 10 lines of the Workforce tab. 

New employees can be added on the extra lines and by completing the new employee’s start month and monthly base salary. The forecast will calculate your total staffing costs based on the employee’s first month of hire. 

You can also adjust the salary percentage of the benefits for each month if you find a lower-cost way to administer your benefits or decide to increase your benefits package. 

How do you predict changes to your staffing plan, like new hires or layoffs? It all goes back to your projected revenue and the people drivers associated with those projections. 

Let’s revisit our digital marketing agency, which is increasing the number of new clients each month. Currently, the agency has four employees and each employee’s capacity is nine new projects per month. 

At the agency’s predicted growth, there are enough employees to cover the first two months of work. By month three, the agency will need to hire a new employee, increasing the overall staffing and benefits cost moving forward. 

Step 4: Operating Expenses

The Operating Expenses (OpEx) tab is already auto-populated with the information you entered in the Prep Sheet tab. Now, it’s time to review these expenses and see if your expenses will increase or decrease based on your revenue and staffing projections.

You can manually update the month and expenses you think will change. The total monthly salaries, wages, and benefits cost that you calculated in the Workforce tab is at the top of the Fixed Expenses section. 

As you may have guessed, your revenue and staffing goals impact the changes to your operating expenses. 

Earlier, we said that our digital marketing agency is increasing its website visitors by 1,000 new visitors per month. They are doing that by running paid advertising to their website, which accounts for 50% of their new traffic. Their average cost per click is $1.75. 

Knowing this, we can project their advertising budget each month. 

Month 1: 6,000 x 0.50 = 3,000; 3,000 x $1.75 = $5,250

Month 2: 7,000 x 0.50 = 3,500; 3,500 x $1.75 = $6,125

Month 3: 8,000 x 0.50 = 4,000; 4,000 x $1.75 = $7,000

An example of an operating expense that would change due to staffing changes is the software license you purchase per employee. Our digital marketing agency pays $50 per month for each employee’s software license. When they hire a new employee in Month 3, their software cost will increase by $50. 

An example of an operating expense that would change due to staffing changes is the software license you purchase per employee. Our digital marketing agency pays $50 per month for each employee’s software license. When they hire a new employee in Month 3, their software cost will increase by $50. 

Step 5: Capital Expenses

In the Prep Sheet tab, you listed two types of capital expenses: expenses related to staffing and general capital expenses. Based on your Staffing Plan, you’ll update the Capital Expenses (CapEx) tab with any staff-related expenses. Then, you’ll update the tab with any general capital expenses. 

Note: The information you entered in the Prep Sheet tab won’t auto-populate to the Capital Expenses tab. You can use this as a reference, but you’ll need to manually input the asset name and cost. 

Once again, your revenue plan and planned growth can impact your capital expenses. Let’s go back to our restaurant that’s converting their dine-in customers to take out. They may discover that one way to convert more customers to take out is by offering a delivery service. The restaurant decides to invest in a vehicle that they’ll use for delivery, which will also save them money on expensive delivery service commission fees. This is a general capital expense.  

An example of a staffing-related capital expense is purchasing a new laptop for each new hire. If we go back to our favorite digital marketing agency, we know that in Month 3, they’ll hire a new employee. Each employee’s laptop costs $1,500. In Month 3, they’ll have $1,500 in capital expenses. 

Note: To keep things from getting too complicated, this spreadsheet model doesn’t take into account depreciation or amortization expense—just the initial outlay for the capital purchases. If you’re interested in a model that automatically calculates the accrual basis impact of capital purchases, consider Jirav’s financial modeling software.

Reviewing the numbers

Your forecast is complete! That wasn’t too bad, was it?

Now it’s time to review the numbers and the results of your forecast. In our next article, we’ll go in depth about analyzing these results and making decisions based on your projections. For now, let’s do a basic overview of the Profit and Loss (aka income statement), Balance Sheet, and Cash Flow tabs. 

Profit and Loss

This tab summarizes the information from the Revenue & COGS and Operating Expenses tabs. 

Here’s you’ll see your monthly:

• Gross revenue: The total revenue you earn each month. 
• Cost of goods sold (COGS): The total you spend on COGS each month.
• Net revenue: Gross revenue minus your COGS. This is how much you earn after you cover the direct cost of selling your products or services. 
• Fixed expenses: Your total fixed expenses, including compensation and benefits. 
• Variable expenses: Your total variable expenses.  
• Net income: Your net revenue minus your fixed and variable expenses. 

All of these numbers have an essential role to play in your overall business model. But today we’re just focusing on the Net income (Loss) line and seeing if the number is positive or negative. 

A positive net income means that your business is earning more than it spends on COGS and operating expenses. In other words, you have a profit! A negative number means that your business is spending more than it makes, and you have a loss.

Balance Sheet

The Balance Sheet tab shows your month-to-month financial position. From here, you can see a snapshot of your assets (what you have), liabilities (what you owe), and equity (your assets minus your liabilities).

Assets include your cash on hand, accounts receivables, and fixed assets. Let’s break these down a little further:

• Cash: This is based on the cash balance you entered in the Prep Sheet tab and then takes into account your projected monthly receivables and payables, fixed assets purchases (aka your capital expenses), your liability payments, and your profit (or loss) from that month. 
• Accounts Receivable (A/R): This is what your customers owe. If you click on the + button on the left, you’ll see your A/R broken down into three types of terms—30 days, 60 days, and 90 days. Based on the information in the Prep Sheet tab, this line will calculate your projected receivables every month. 
• A/R Increase or Decrease: You must click on the + button next to row 10 to see this line. You’ll notice that each month your A/R balances change. That’s because each month a portion of these balances is being incurred and paid. This line simply shows you the change month to month. 
• Fixed Assets: This line is based on the starting value of your fixed assets from your Prep Sheet tab, plus any capital expenses that you purchased that month. If you click on the + button, you’ll see the lines Increase and Decrease. The Increase line pulls from the Capital Expenses tab. If you sell assets, you can manually enter in the value of the asset you sold, and this will decrease the overall value of your fixed assets. 
• Other Assets: Here you can manually enter additional fixed assets that you purchase. 
• Total Assets: On this line, you’ll see all of the assets you’ll have each month based on your projections. 

Liabilities include your current accounts payables and cash outflows like loan payments. 

• Accounts Payable (A/P): These are the payments that you owe your vendors. Like the accounts receivable line, if you click on the + button you’ll see more details about your A/P broken out by your terms and the balance of what’s due. 
• A/P Increase or Decrease: As you pay off your vendor bills, the balance of your A/P account will change. This line shows you the change to your A/P account balance month to month. 
• Notes Payable: These are your monthly loan payments. The starting balance is based on what you entered on the Prep Sheet tab. If you click on the + button, you can manually enter increases and decreases to your total Notes Payable. 

An increase is debt that you plan to take on. So, if you take out a $25,000 loan, you’ll put the total loan disbursement amount in the month that you receive the loan. If your loan disbursement is split, enter the disbursement amounts for each month. 

A decrease is a payment towards your debt. If you make monthly loan payments, enter the amount of the loan repayment that goes towards the principal. Another example of a decrease is having your Paycheck Protection Program (PPP) loan fully or partially forgiven. You would enter the forgiveness amount here.

Equity shows you how much money you would receive if you liquidated all your assets and paid off all your debts. It’s what would be left over for you, the owner. Sometimes people call this the net worth of their business. 

• Owner’s Equity: The starting balance is based on your total assets minus your liabilities. If you plan to add more equity to your business (for example, you choose to invest your own money into your business), click the + and enter the amount you’ll invest on the Contributions line. If you plan to take money out of your business, like through an owner’s draw or distribution, enter the amount you’ll take out on the Distribution line. 
• Retained Earnings: This is the profit that is retained in the business. In other words, the profit that you leave in the business and don’t take out. These earnings can be retained for future investments in the business.  
• Total Equity: This is total equity that you, the owner, have in the company.

Next, we have the Liabilities & Equity line, which, you may notice, matches the total on the Total Assets line. In the accounting world, this is how a balance sheet lives up to its namesake by balancing out. 

The very last line of the Balance Sheet tab is called Check. This line ensures that the Balance Sheet does balance out. This line should always be zero. If it’s not, there’s an error on the Balance Sheet. 

Cash Flow

The Cash Flow tab shows you if you have enough cash on hand to cover your bills, inventory or manufacturing costs, payroll, and debts, plus what’s left after that monthly outflow. 

The numbers here will likely be very different from your net income. That’s because your cash flow takes into account other financing activities, like receiving loans or putting your own money into the business. It also accounts for other types of outflow in your business, like liability payments. 

There are four sections of the Cash Flow tab. 

• Operating Activities: This section accounts for all the inflow and outflow of cash from your revenue, operating expenses, accounts receivables, and accounts payables. Net cash from operating activities is the total cash left after you take into account all of these activities. 
• Investing Activities: Remember those capital expenses you purchased? Here’s where you’ll see the cash go out for those. 
• Financing Activities: If you put your own money in your business, or take out a loan, those activities will be reflected here. In the next article, we’ll talk more about how to use the Cash Flow tab to predict how long a loan will last or when you need to take out a loan.
• Increase (Decrease) of Cash: For each month, you can see if you have positive or negative cash flow. Positive cash flow means that more cash has come into your business than has gone out. Remember, cash doesn’t mean revenue. Cash can also come from financing activities. Negative cash flow means that more cash has gone out of your business than has come in. 
• Cash at the beginning and end of the period: These lines show you how much projected cash you’ll have in the bank at the end of each month. Cash is a type of working capital to cover your operations on a daily basis. If the number is negative, that means that you wouldn’t have enough cash that month to cover all of your activities. 

You just finished your first cash flow forecast! At this point you likely already have a lot of useful information and ideas about what’s next for your business.

In the next article, we’ll show you how to use the information in this forecast to make cash flow projections about your business and plan proactively for your future. The template (a Google Sheet that could also be downloaded in Excel) includes a worksheet with a cash flow projection template.