A Beginner’s Cash Flow Forecast: Microsoft’s Excel Template

Simple Excel Monthly Cash Flow Forecast

You can’t run out of cash or your business will fail. The first step to make sure you won’t run out of cash is creating a monthly cash flow forecast in a spreadsheet.

If you haven’t done this yet, read on.

A Simple Monthly Cash Flow Forecast

Here are some of the problems this simple monthly cash flow forecast can address:

  • You haven’t started your business yet, and you need to know how much cash you will need in the first year to launch
  • You’ve launched your business and want to grow, but you aren’t sure how much cash you will need to grow.

Importantly, there are several problems this particular tool can not address:

  • You’ve launched and cash seems tight every week. For this problem, you need a weekly, not a monthly, cash flow forecast.
  • You need to prepare a formal cash flow forecast for funders to get a term loan, line of credit, or equity. For this problem you need an accrual accounting based cash flow forecast that shows balance sheet changes. The simple Microsoft template discussed here mixes profit and loss with balance sheet changes. That may drive accountants crazy but it will work for you. Especially if you don’t care about accrual accounting.

Cash Flow Forecast Preparation

  1. Make sure you have Excel on your laptop or tablet (sorry, this is challenging on a smart phone) or Google Drive/Google Docs. An old version of Excel should be fine – this worked on my 2011 Mac Excel.
  2. Download the template from this Microsoft page
  3. If you are in business already, collect the information about your last three months of revenue and expenses, including any bank statements. A QuickBooks monthly Profit and Loss report is ideal but not necessary. If you are not yet in business, gather the financial estimates you may have started on already.

Excel To Google Doc

If you don’t own Excel, you can create a Google Account and get access for free to Google Drive and Google Docs. They have a simple spreadsheet application online that is free, and sufficient for this simple cash flow spreadsheet.

Here’s how to convert the Microsoft Excel template you downloaded above into a Google Doc spreadsheet.

  1. Open Google Drive in your browser window
  2. New [blue button on left]>File Upload
  3. Navigate to where you’ve downloaded the Microsoft Cash Flow template.
  4. Double click on the file tf00000055.xlsx to upload it to your Google Drive.
  5. Double click on the file within Google Drive after it is uploaded
  6. Click on the option in the middle “Open With Google Sheets”

Note: the following assumes that you have some basic understanding of spreadsheets. If any steps are confusing, it’s worthwhile to do an “Excel for Beginners” type of training before you continue.

General Cash Flow Forecast Layout

This simple spreadsheet organizes your forecast by rows of categorized Cash Receipts and Cash Paid Out including expenses such as Purchases (Merchandise) and Supplies (office & operations), and by columns of months.

The very simple logic is as follows:

  1. You start out every month with Cash On Hand (literally cash in your safe or cash drawer, and your business checking account balance)
  2. Some cash comes into the business during the month – Cash Receipts
  3. Some cash goes out of the business during the month – Cash Paid Out
  4. The spreadsheet does the work of adding and subtracting as appropriate, so that you finish the month with Cash Position (end of month), which is then copied into the next month’s Cash On Hand.

Change The Dates, Then Save As

You will see in cell B4 that there is a date labeled (in cell B3) as “Fiscal year begins:” In my downloaded copy it starts as 7/1/14. You want to change that to make it relevant. Change that date to:

  • The first of this month (such as 2/1/18) if you are already in business, or
  • The first month you will start spending money as a business.

If you’d like you can also change the label from “Fiscal year begins” to “Cash flow forecast begins.” Don’t worry if you don’t know what “fiscal year” means – it doesn’t matter for now.

As part of good spreadsheet practice use this opportunity to “Save As” and give it a name like Monthly Cash Flow Forecast.xlsx and put it in a folder where you will remember to find it. This way, the template is available in the future, unchanged, if you really screw up your forecast and have to start all over again. That happens!

Row By Row: Cash Receipts

The basic template comes with just three categories for Cash Receipts. Depending on your business, you may want to add a few more.

  • Cash Sales are literally cash sales – someone pays you in dollar bills. Depending on your business you may want to add additional lines for Credit Card Sales or Check Sales, but don’t worry too much about those details if you’re only trying to make a first pass at estimating cash requirements.
  • Collections from CR Accounts is for businesses selling on terms – for example, sending a bill that is payable within 30 days. If your business does this a lot, you will want to think hard about this line item. For example if you have one huge customer that pays very slowly, and lots of other customers that pay pretty much on time, you might want to break out those into two rows/two categories. Slow payments from big customers is a frequent cause of cash flow problems.
  • Loan/Other Cash Injections is for cash coming in from funding activities. It could be a big term loan or equity financing, or it might be a line of credit that you go back and adjust so that your “Cash On Hand” never goes below zero.
  • Total adds the three rows for that month to show total incoming cash.

Be careful where you add rows. If you haven’t done lots of spreadsheet work, here’s one common way that people mess up spreadsheets: they add in rows (or columns) that don’t automatically get included in a calculated Total.

Here’s the way to avoid that problem on this spreadsheet. If you are going to add a row in Cash Receipts:

  • Highlight the middle row – on the far left click on 10 showing that it’s row 10, and you will see all of row 10 highlighted.
  • Go to the top navigation and select Insert>Rows. In Excel it automatically adds a row above. In Google Docs, you have the option to add a row above or below.
  • Add the label you want in column B. The numbers you add in the monthly columns will automatically be added into the Total.

Simple Cash Flow Forecast highlight line 10

You’ll see that there is another automatically calculated row labeled Total Cash Available (before cash out). This will be useful for you to observe once the forecast is filled in, as a reminder that just because you have cash coming in, you don’t necessarily end the month with lots of cash. And it may help you realize you need to adjust expenses in months where cash coming in slows down, for example, in “off season” months.

Row By Row: Cash Paid Out

This simple Microsoft cash flow template has 21 different expense categories. Depending on your business that may be too many or too few. You should feel free to change the row labels in column B to make them suitable for your business.

Especially if you have not yet started your business, this is where you can expect to be surprised. Businesses have all sorts of expenses you may not learn about until you’ve started – or until you’ve talked with someone who knows your kind of business well. So start off with what you know, then spend some time and effort figuring out what the expenses are that you don’t yet know about.

  • Purchases (merchandise) – if you are a retail business this is where you put in cash paid out for merchandise you hold in inventory. Remember, this is cash paid out, so if you get payment terms (like 30 day net) make sure you account for this in your estimates.
  • Purchases (specify) – there are two lines for “fill in the blank.” Come back to these after you’ve filled in the other row categories to add missing things. For example, if you have to buy something in bulk for production – wine bottles, shipping containers – you might put that here.
  • Gross wages (exact withdrawal) – if you have employees on weekly payroll, enter here the wages that they receive precisely. Here’s one gotcha for cash flow: months with 5 weeks of payroll will have higher dollars going out. Look at the calendar!
  • Payroll expenses (taxes etc.) – it’s a good idea to separate the extra taxes you have to pay, as well as payroll service expenses, from wages paid. Here’s a huge gotcha: the Internal Revenue Service is extremely sensitive to non-payment of employer taxes. Payroll services pay that automatically but if you are doing your own payroll, missing these payments because of cash flow problems will bring the IRS to your door very quickly.
  • Outside services – these could include web hosting, marketing services, and maybe a few other things particular to your industry. If you want to break out the 3% credit card processing fee you might put it here.
  • Supplies (office & operations) – it’s surprising how much things like coffee, printer paper, mailing envelopes, and other such things can add up over time.
  • Repairs & maintenance – this may or may not be a big deal depending on your business. If you have brewery equipment, or vehicles for construction, you need to set aside cash for the inevitable.
  • Advertising – how much will you be spending on this? Depending on your industry you may want to include all your outside marketing expenses here instead of just advertising per se.
  • Car, delivery & travel – again this is highly dependent on your type of business as to whether it’s important or not. Feel free to delete this, or change it to something more relevant.
  • Accounting & legal – Accounting expenses can be seasonal, but also include monthly bookkeeping services. Legal may be a lot up front then not much after startup for a while. And remember that this is cash paid, not when services are rendered and invoices arrive.
  • Rent – pretty obvious, except that if you need to put a month’s deposit down up front, or pay for improvements, you need to make sure that’s included here.
  • Telephone, Utilities – will you have a business mobile phone? A Voice Over IP system? Do you need to pay for heat and electricity?
  • Insurance – pretty much every business needs some form of business insurance to protect against disasters. Depending on your industry you may be surprised how much business insurance costs.
  • Taxes (real estate etc.) – don’t ignore this one. Does your business collect, and need to pay sales taxes? Are there other recurring government fees such as licenses that have to be paid regularly?
  • Interest – if you have a term loan, line of credit, or some other form of debt you should separate out the interest payments from the repayment of principal (which is down on line 40). And if a new loan has fees associated with closing, don’t forget to include those here as well.
  • Other / Miscellaneous – the template gives you room to add other categories. “Miscellaneous” might be useful to put in an amount that you’re not sure about in specifics, but you want to forecast it “just in case.” Stuff happens!

Row By Row: Cash Paid Out (Non P&L)

The template has a quick reference to accounting here. P&L is short for Profit & Loss, one of the three primary accounting statements. Most of the above categories are associated with the P&L, but some Cash Paid Out items aren’t. These include:

  • Loan principal payment – every month that you have a loan, in general, you have to pay some towards the amount you borrowed (the principal), plus interest, and sometimes plus fees. This category breaks out your principal repayment so that if you have a month with lots of extra cash coming in, you can consider paying extra principal so that you can end up paying less in interest over the life of the loan.
  • Capital purchase (specify) – if you are buying equipment for manufacturing, or a vehicle, or other things that will productively contribute to your business for a while, accountants may ask you to put that purchase on your balance sheet and amortize the expense over time. But here, you just care about cash going out the door for big items that will last a while.
  • Other startup costs – if you haven’t yet started your business, you might stick one-time costs like logo design here. The point of this row is to remember things you have to do, but only have to spend money on in the very earliest stages.
  • Reserve and/or escrow – if you are buying or leasing property you may need to put some cash into escrow for future tax and insurance payments. Or, you may want to build up a “just in case” reserve fund in a savings account and consider that not a part of your regular Cash On Hand.
  • Owners’ withdrawal – some companies are actually able to pay something to their owners! There are tax consequences of this so it’s worthwhile breaking it out just to talk about with your tax accountant.

Totals, Cash Position (end of month)

The bottom line shows how much cash will be in your bank (and your safe) at the end of each month. As a side note, EOM or eom is sometimes used as an abbreviation for End Of Month.

Next Step: Fill In The Blanks

Now that you have a sense of the gory details, it’s time to start filling in the blank rows and columns for the months and categories you have a good idea about.

Then, start filling in guesses for other categories and months you know are relevant, but you’re not quite sure of.

Here’s the good news: it’s super easy to change the numbers that get put into this spreadsheet. So don’t worry too much that you don’t have an exact figure at first. Just like with a lot of other things in entrepreneurship, you:

  1. Guess
  2. Validate
  3. Iterate when necessary

Good luck, and happy forecasting!

Don Gooding

Add comment

Follow us

Get in touch! We love meeting interesting people and making new friends.