Cash Projection Worksheet
Cash Forecasts > Forecasting Exercise
Bean Counter's Cash Forecasting Workbook
Putting The Pieces of the Puzzle Together
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.
Back to that saying that a picture's worth a thousand words so we'll take a look at the Complete Cash Projection Worksheet.
Cash Projection Worksheets
Note: If you Click on the Links In the Individual Sections or Click on the Links at the Bottom Of The Sheets you can explore the entire workbook.
Additional Instructions
In the following section we discuss each of the 6 Main Sections.
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.
The introduction provides you with an overview about the Workbook and how to navigate to the different Worksheets that make up the Workbook.
(2) Cash Projection Worksheet is the Main Worksheet and contains the following information:
(a) Cash Summary Section that summarizes all the estimated cash receipts and payments.
(b) Basic Financial Information Section
(b) Basic Financial Information Section
(c) Other Receipts and Payments Section
(d) Sales and Payment information summarized and calculated from the other two sheets - SalesForecast and CashPayments.
(e) Estimated purchases and inventory calculations for businesses that buy and sell product.
(d) Sales and Payment information summarized and calculated from the other two sheets - SalesForecast and CashPayments.
(e) 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 the number of weeks contained in each month that you want to estimate collections and payments for.
Enter your basic financial information, collection and payment patterns, and beginning balances in the Basic Financial Information Section.
Enter your Other Receipts and Payments Information in the Other Receipts and Payments Section.
All areas that require user input are highlighted in yellow.
(3) Sales Forecast Worksheet used to estimate and enter 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
(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 on 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.
(4) Cash Operating Expenses Worksheet is used to estimate and enter 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.
(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) either Automatically or Manually 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.
(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.
Why is no information displayed ? Good question. The reason is simple - no information has been entered into the Worksheets yet.
What's next ?
Main Cash Projection Worksheet