Using Bean Counter's Cash Forecast Workbook
Putting The Pieces of the Puzzle Together

Bean Counter's Cash Forecasting Workbook

Return To Tutorial

Go Back To Ma' and Pa's Assumptions

Note: This page has links that take you back to Ma's Assumptions used for preparing this Cash Forecast and links that display what the Cash Forecast actually looks like. To return from a linked page use the Return To Tutorial Link.

This Cash Forecasting Workbook was designed by me to be an easy to use tool to aid small businesses in anticipating their cash needs. I've downloaded and looked at many templates that to me were either little or no help (basically just a form where you filled in amounts but no calculations were performed for you) and others that seemed much too complicated to actually use.

My Cash Forecast attempts to have you enter enough historical data to produce realistic future estimates of receipts and payments, but not have you spending a lot of time entering prior year's historical financial information from balance sheets, income statements and other financial documents.

Don't get me wrong, prior years information is one indicator of what may happen in the future but things change and you also need to get out your crystal ball (current knowledge of your market conditions and the economy) to prepare reasonable future estimates. In other words, you need to mix a dose of the past with a dose of the future.

The Workbook covers any period of time from a month up to a maximum of 12 months. Here again, many worksheets cover up to 5 years of projected financial information, but I intentionally limited this projection to the "near" future where you will have to plan for and deal with relatively soon.

Download Bean Counter's Cash Forecast Workbook

You will need Microsoft's Excel or Open Office's Free Spreadsheet. installed on your computer and some basic familiarity with how to use a spreadsheet in order to use the Cash Forecast Workbook.

If you don't have Excel on your computer and you haven't already, go to Open Office Free Software and download Open Office's Free Office Suite Software.
http://www.openoffice.org

If for any reason, you don't want to download the spreadsheet, you can omit the download steps. You can still follow along and benefit from this lesson without actually using the spreadsheet.

Excel Files

The download file for Excel is named BC.zip and and is made up of three Excel actual files.

  • BCCashForecast.xls
    This is what I call your Master File and should not be used for performing actual Cash Forecasts. You keep this file in the event that anything happens to your work copy file.
  • BCCashForecastWC.xls
    This is your Work Copy File for my Cash Forecasting Workbook and should be used to prepare your actual cash forecasts. You'll use this file when preparing Ma and Pa's Cash Forecast.
  • MaPa.xls
    This is the "answer" file that has Ma and Pa's actual forecast already prepared. Use this file after completing this lesson to check and see how well you did preparing Ma and Pa's Cash Forecast.

    After downloading BC.zip, you will need some software to unzip the file. The program is called a zip utility and what it does is extract the three previously mentioned files from BC.zip and set them up on your computer.

    The files will be located on your computer based on where you unzip them and contained in a a sub-folder named BCCashForecast.

    Download Bean Counter's Cash Forecast Workbook
    Download BC Cash Forecast
    http://www.dwmbeancounter.com/BCCF/BC.zip

    Open Office Files To Use

    The download file for Open Office is named BCOO.zip and and is made up of three Open Office actual files.

  • BCCashForecastOpenOffice.sxc
    This is what I call your Master File and should not be used for performing actual Cash Forecasts. You keep this file in the event that anything happens to your work copy file.
  • BCCashForecastWCOpenOffice.sxc
    This is your Work Copy File for my Cash Forecasting Workbook and should be used to prepare your actual cash forecasts. You'll use this file when preparing Ma and Pa's Cash Forecast.
  • MaPaOpenOffice.sxc
    This is the "answer" file that has Ma and Pa's actual forecast already prepared. Use this file after completing this lesson to check and see how well you did preparing Ma and Pa's Cash Forecast.

    After downloading BCOO.zip, you will need some software to unzip the file. The program is called a zip utility and what it does is extract the three previously mentioned files from BC.zip and set them up on your computer.

    The files will be located on your computer based on where you unzip them and contained in a a sub-folder named BCCashForecast.

    Download Bean Counter's Cash Forecast Workbook Open Office Version
    Download BC Cash Forecast
    http://www.dwmbeancounter.com/BCCF/BCOO.zip

    Zip Programs
    If you don't already have a zip program I've listed some free ones below and a brief description of what a zip program is. Many computers already have a zip program installed.

    Zip programs are used to compress and expand files in order to speed up data transfer over the internet. You often need one of these programs in order to be able to install computer programs downloaded from the internet.

    Free Zip File Programs
    Zipper   
    http://www.trans4mind.com/personal_development/zipper/

    QuickZip   
    http://www.quickzip.org/quickzip/downloads.php

    UltimateZip
    http://www.ultimatezip.com/download.htm

    The Workbook is made up of 6 main sections:

    (1) Introduction
    The introduction provides you with an overview about the Workbook and how to navigate to the different Worksheets that make up the Workbook.

    Introduction

    (2) The Main Worksheet is named Cash Projection and contains the following information:

    (a) Cash Summary Section that summarizes all the estimated cash receipts and payments.
    (b) Basic Financial Information Section
    (c) Sales and Payment information summarized and calculated from the other two sheets - SalesForecast and CashPayments.
    (d) Estimated purchases and inventory calculations for businesses that buy and sell product.

    Instructions for entering data in the Cash Projection Worksheet:

    Enter the number of periods that you want to prepare an estimate for from one to 12 months (1 - 12).

    Enter your basic financial information, collection and payment patterns, and beginning balances in the Basic Financial Information Section.

    Enter the number of weeks contained in each month that you want to estimate collections and payments for.

    All areas that require user input are highlighted in yellow.

    Cash Forecast Worksheet

    (3) Sales Forecast Worksheet used to estimate the sales amounts used in the Cash Forecast.

    Instructions for entering data in the Sales Forecast Worksheet:

    This worksheet is where you enter your estimated or historical sales data.

    There are two methods that you can use to prepare your Sales Estimate.
    (1) Simple Sales Estimate
    (2) Sales Estimate Using Historical Data

    The simple method only requires you to estimate your sales for the number of periods that you want to prepare a projection for.

    The historical data method requires you to enter sales amounts from your prior financial statements. You may enter up to five years worth of historical sales data. If you use this method your estimated sales are automatically calculated for you based the history that you entered.

    Month 1 represents the beginning month of your projections. If your beginning month is April for example, enter your April figures in Month 1, your May figures in Month 2, and so on in the yellow highlighted areas.

    Note: Only use one method to prepare your sales estimates.

    Sales Forecast Worksheet

    (4) Cash Operating Expenses Worksheet which is used to estimate your different types of payments (expenses).

    Instructions for entering data in the Cash Operating Expenses Worksheet:

    This worksheet is where you enter your other cash operating payments. The worksheet provides you with two methods for entering your estimates.

    (1) The first method allows you to enter estimate amounts for 4 week and 5 week periods OR enter a percentage of sales to use to calculate the expense. This information is then transferred to the Cash Projection Worksheet and entered in each of the monthly columns based on the percentage entered or whether the amounts entered represent a 4 week or 5 week period. If entering amounts, both the 4 Week and 5 Week amounts need to be entered. The percentage of sales figures or the monthly estimated amounts are entered in the Estimated Monthly Totals Table.

    (2) The second method allows you to enter amounts for each of the months that you are estimating. This information is entered in the Estimate Each Month Table.

    You can combine the two methods and enter some disbursements as a percentage or monthly four (4) and five (5) week periods in the Estimated Monthly Totals Table and enter other data individually for each month in the Each Month Table or you can enter all your data in either table.

    Do not enter data for the same type of payment in both tables. In other words if you use the Estimated Monthly Totals Table to enter your Salaries and Wages Estimate don't also enter Salaries and Wages in the Estimate Each Month Table.

    Enter Your Estimates in the Yellow Highlighted Areas of the tables.

    Additional space has been provided in the table below for you to add up to 25 additional expenses that you want to include.. Add your new categories to the yellow highlighted portion of the Estimated Monthly Totals Table. These same categories are automatically added to your Estimate Each Month Table. The added categories will then be displayed and available to use to enter data for the new categories in both tables.

    Cash Operating Expense Payments Worksheet

    (5) Weekly Forecast Worksheet which is used to analyze the cash flow for a user specified month.

    The Weekly Forecast Worksheet has been provided in order to enable you to break down a monthly forecast into weeks and compare your budgeted (estimated) amounts with your actual amounts. This may be helpful in cases were most of your receipts and collections arrive at the end of a month and a large percentage of your payments occur at the beginning of a month.

    To use this worksheet, all you do is pick a month that you want to analyze and allocate (spread) your monthly receipts and payments over the weeks that you estimate the receipts will be collected and the weeks that the payments will be made. If you also want to compare your estimated amounts to your actual amounts you need to enter your actual receipts and payments made during the weeks.

    Weekly Cash Forecast Worksheet

    (6) Charts Section which display useful charts and graphs.

    They say a picture's worth a thousand words, so here's where you take a look at your figures utilizing some charts and graphs.

    Charts and Graphs

    Steps To Take To Prepare Your Projection

    1. Enter Historical Financial Information, Collection and Payment Patterns, and Beginning Balances using the Cash Projection Worksheet. All required entries are highlighted in yellow.

    2. Enter and/or Calculate your Estimated Monthly Sales using the Sales Forecast Worksheet. All required entries are highlighted in yellow.

    3. Enter and/or Calculate your Estimated Monthly Payments using the Cash Payments Worksheet. All required entries are highlighted in yellow.

    Now, that's not too difficult - is it ?

    Optional

    As mentioned earlier, the Weekly Forecast Worksheet has been provided in order to enable you to break down a monthly forecast into weeks and compare your budgeted (estimated) amounts with your actual amounts. This may be helpful in cases were most of your receipts and collections arrive at the end of a month and a large percentage of your payments occur at the beginning of a month.

    To use this worksheet, all you do is pick a month that you want to analyze and allocate (spread) your monthly receipts and payments over the weeks that you estimate the receipts will be collected and the weeks that the payments will be made. If you also want to compare your estimated amounts to your actual amounts you need to enter your actual receipts and payments made during the weeks.

    Helpful

    The Chart Worksheet is provided to illustrate your results using charts and graphs.

    Where do you get the required Financial Information to input ?

    Balance Sheet(s)
    Income Statement(s)
    Accounts Receivable and Accounts Payable Ageing Analysis and Reports
    Prior Year(s) Monthly Sales Summaries and Reports
    Trade Publications -Industry Ratios
    Prior Year(s) Cost and Expense Analysis
    Prior Year(s) Collection Patterns and Payment Patterns Analysis
    Equipment Analysis and Needs Studies

    Remember your estimates are only as good as the information and assumptions that they are based on. GIGI - Garbage In - Garbage Out !

    All the information we need to prepare our Cash Forecast was presented in Ma & Pa's Information, Assumptions, and Plans Needed For Their 12 Month Cash Forecast.

    We'll refer to this page while entering the information in Bean Counter's Cash Forecast Workbook.

    Go Back To Ma and Pa's Assumptions

    Lets quickly review are steps for preparing a Cash Forecast.

    There are five basic steps to creating a cash flow projection:

    Let's apply these steps to Ma's Cash Forecast.

    • Gather up your historical financial information, develop and state your assumptions, and select the time period(s) covered.
    • Estimate sales and their collections for the time periods you want to cover by period.
    • Identify and estimate amounts and the period collected for other sources of cash.
    • Identify and estimate amounts for all items that you have to pay for and when (period).
    • Enter and formally summarize information on worksheet(s).

    Let's apply these steps to Ma's Cash Forecast.

    If you're going to work along with the example , you need to locate the file you downloaded named BCForecastWC.xls and double click on it to load it into Excel so that you can enter data into the Workbook as we go along.

    • Gather up your historical financial information, develop and state your assumptions, and select the time period(s) covered.

      This step was performed and summarized and presented in Ma & Pa's Information, Assumptions, and Plans Needed For Their 12 Month Cash Forecast.

      Ma's Information

      Here is where we start out with a Blank Cash Forecast Worksheet.
      Blank Cash Forecast Worksheet

      Now you need to enter all your historical information in the gray highlighted Financial Information Area. After entering your historical information your sheet should look like this.
      After Entering Historical Information

    • Estimate sales and their collections for the time periods you want to cover by period.

      Now we're ready to prepare our most important estimate - Sales. Ma has the necessary information ready for us to use from her historical sales analysis.

      Ma's Estimated Sales

      The Sales Forecast Worksheet is the worksheet you use two enter your estimated sales. We're going to use the second section and enter two years worth of actual sales information using Ma's prior years sales information. Let's take a look at our Sales Forecast Worksheet.
      Sales Forecast Prior To Entering Sales Information

      After entering the two years of historical sales data your Sales Forecast Worksheet should look like this.
      Sales Forecast With Two Years Of Sales Information

      Now let's see the affect entering our sales information had on our Main Cash Forecast Worksheet. The information entered in the Sales Worksheet is used to update the Sales amounts and also used to calculate Cost Of Sales, Purchases, Payments for Purchases, and Inventory Amounts based on formulas contained in the worksheet. These calculations are automatically performed for you.
      Cash Projection Worksheet with Sales Data Brought Forward

    • Identify and estimate amounts and the period collected for other sources of cash.

      Ma's plans didn't include any other estimated sources of cash receipts (inflows) so we won't be entering any information in that section of the Cash Forecast Worksheet so we'll move on and enter Operating Expenses.

    • Identify and estimate amounts for all items that you have to pay for and when (period).

      Refer back to Ma's Expense Analysis for preparing this worksheet.

      Ma's Cash Operating Expenses Analysis

      You use your Cash Operating Expense Payments Worksheet to enter your estimated Operating Expenses. We used the top table and Ma's Schedule to enter amounts for four and five week periods. In addition, we used percentage amounts for Ma's variable expenses. After entering the expenses your worksheet should look like this.
      Cash Operating Expense Worksheet Expenses Entered

      Now let's see the affect of entering her Operating Expenses. The expenses entered are automatically carried forward to the Cash Forecast Worksheet and the forecast amounts are automatically updated. The updated Cash Forecast should look like this.
      Cash Projection Worksheet Update for Operating Expenses

      Other Payments

      The only other payments Ma anticipates are her monthly cash draws. You use the Cash Projection Main Worksheet to enter any other Cash Payments in the section called Other Payments. Let's take a look at the forecast after entering Ma's draws.
      Cash Projection After Entering Other Payments - Ma Draws

    • The top portion of the Cash Projection Worksheet provides your Summarized Cash Forecast. This is automatically done for you based on all the information that you entered.

      Believe it or not we're done. Take a look at what Ma's completed Cash Forecast should look like.

      Summarized Cash Forecast

    Does Ma have a cash flow problem ?

    Yes, she does but not a big one. If you recall Ma and Pa said they dislike having to borrow money; but, based on their Cash Forecast they will end the next year owing the bank $2,048. In addition, they had to borrow during the year to cover temporary shortages.

    Is there an easy way to avoid this ? Yes there is, but Ma's not going to like it. Ma could reduce the amount of her draws for the year and not have to borrow any money.

    Are there any other things Mom could do ? Let's list a few possible actions Ma could take.

    • Eliminate the 10% Cash Purchases by getting these suppliers to give her terms.
    • Reduce her operating expenses.
    • Increase her sales.
    • Increase her selling prices ( gross margin % ).
    • Selling more items with Cash terms (pay when receive the goods).
    • Negotiate lower prices for her purchases (buy smarter).

    Hopefully , by now you at least have a pretty good idea of how to prepare and utilize a Cash Forecast and how to utilize my Bean Counter Forecasting Workbook included with this tutorial. Here's wishing you never have any cash problems and for easier forecasting days ahead. Shoot, I may even see you again in another one of my tutorials - you never know.

    Your friendly Bean Counter,

    Dave Marshall

    Return To Tutorial