Ma & Pa's Next 12 Months Cash Projection

Return To Tutorial

Ma and Pa keep getting fancier and fancier. They heard about this business tool called a Cash Forecast and decided that just because their old doesn't mean that they can't be taught how to use new tools to aid them in running their business. Shucks, with a little outside help (me), they've been preparing their own financial statements.

Go To Bean Counter's Cash Forecasting Workbook

Go To Bean Counter's Cash Forecasting General Information Section

Ma & Pa's Information, Assumptions, and Plans
Needed For Their 12 Month Cash Forecast:

Prior Year Actual Ending Balances (Beginning Balances For The Forecast)

Account Amount
Cash 6,322
Accounts Receivable 35,000
Inventory 21,000
Accounts Payable 22,800
Sales Tax Payable 3,500
Other Accruals 5,500

The above amounts were obtained from Ma's Actual Balance Sheet For Year 3 (Last Year).

Ma & Pa's Customer Credit Terms and Collection History

Ma analyzed her selling terms and collections and determined that:

  • On the average 10 % of her monthly sales are "Cash" Sales.
  • 20 % of her monthly sales are sold with terms of due in 10 days.
  • 70 % of Ma's monthly sales are sold with terms of due in 30 days.

Based on these terms and her analysis of her collections, Ma arrived at the following collection estimates for her sales:

  • % of Cash Sales - 10% Collected In Month Of Sale
  • % Of Sales Sold With Terms Collected In Month Of Sale - 14%
  • % Of Sales Sold With Terms Collected One Month After Sale - 76%
Ma's Collection Analysis Worksheet

Ma analyzed her end of year Accounts Receivable Balance of $35,000 and estimated that:

  • $30,000 would be collected in Month 1 of the Cash Forecast
  • $5,000 would be collected in Month 2 of the Cash Forecast
Ma's Accounts Receivable Aging Report

Ma's Terms and Payment History With Suppliers

  • 10 % Of the Purchases made in a month are "Cash" Purchases and are Paid In The Month Purchased.
  • Ma's remaining Purchases have 30 day terms and Ma always pays on time.

Of the 22,800 Prior Year Accounts Payable Balance, Ma estimates that the full amount will be paid in Month 1 of the Cash Forecast.
Ma's Accounts Payable Aging Report

Sales Tax Payable

Ma's sales tax is due and payable in the month following the sale. Based on this, Ma's sales tax owed of $3,500 is scheduled for payment in Month 1 of the Cash Forecast.

Ma's Current Sales Tax Rate is 8 %.

Ma sells to both wholesalers and retail customers. Based on Ma's history, 60% are wholesale sales and 40 % are retail sales.

Other Amounts Owed at Year End (Beginning Of Projection)

Ma's Other Amounts owed of $5,000 for business taxes and licenses Ma scheduled to be paid in Month 1 of her Cash Forecast.

Ma's Accounting Calendar

Ma divides her year into what is known as a 5-4-4. The first month of each quarter has 5 weeks and the second and third each have 4 weeks. This totals up to Ma's 52 week year.

Ma's Sales Estimate

Although Ma had a Sales Analysis for 3 years, she decided to omit year one because that was her first year of operations and would not be representative of a so called "normal" year of operations.
Ma's Sales Analysis

Ma also prepared sales estimates for an additional three months to be used for calculating Ending Inventories and Purchases.
Ma estimated the following additional sales amounts:

  • Month 13 - 40,000
  • Month 14 - 44,000
  • Month 15 - 45,000

If you navigated here from the Workbook click here to return to the workbook.
Back To Bean Counter's Cash Forecasting Workbook

Ma's Cost Of Sales, Purchases, and Inventory Estimates

Ma used her percentage relationship of her cost of sales to her sales from her prior year's income statement.
Cost Of Sales / Sales
249,300 / 447,000 = 55.77 % or .5577

Ma also calculated her Inventory Turnover Rate as follows:
Cost Of Sales / Ending Inventory
249,300 / 21,000 = 11.87

These amounts were obtained from Ma's Income Statement.
Ma's Income Statement For The Prior Year

In addition Ma prepared an Analysis of her Cost Of Sales, Purchases, and Inventory For Year 3.
Ma's Cost Of Sales, Purchases, and Inventory Analysis

Ma's Draws

Ma set her draws at $3,500 a month to be used in her Cash Forecast.

Current Loans and Notes

Ma and Pa do not currently have any outstanding loans.

Plans For Borrowing Money

Ma and Pa have never like to borrow any money or be beholding to anyone. Ma foresees no need to obtain any additional financing during the next 12 months other than maybe some short term needs for any cash shortages. Ma has an excellent relationship with her neighborhood banker. If in any month, Ma and Pa run a little short of cash, their friendly banker has them set up with a $20,000 line of credit that they can use to cover any shortages.

Equipment Needs

Ma and Pa do not have any plans to purchase any additional equipment during the next 12 months.

Other Receipts

Ma and Pa have no plans for investing (contributing) any additional capital to the business during the next 12 months.

Operating Expenses

Ma and Pa reviewed their Profit and Loss Statements for the last three years and decided to use the information from their most current year's statement for their Cash Projection estimates of their operating expenses.

Based on her analysis Ma prepared the following schedule of her Estimated Operating Expenses to use in her Cash Projection.
Ma's Operating Expense Estimates

I don't know about you, but it looks to me as if Ma has done a pretty good job of gathering and preparing the information needed to prepare a pretty good Cash Forecast.

If you navigated here from the Workbook click here to return to the workbook.
Back To Bean Counter's Cash Forecasting Workbook

If you recall, I earlier said that it helps to have a business tool to aid in preparing your Cash Forecasts. Well, it just so happens, that if you don't already, you now do. Your final task for completing this Cash Tutorial is to prepare Ma and Pa's 12 Month Cash Forecast using my Bean Counter's Cash Forecast Workbook and the information and assumptions above.

What do you need to perform this task ?
You need Microsoft's Excel or Open Office's Free Spreadsheet.

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

Why would I subject you to this "torture" ?

Well, think about having the directions given to you and going some where new in your car. If you are the passenger and just along for the ride, you normally don't pay that much attention to where you're going ; however, if you're the driver you notice every turn and nook and cranny along the way. After returning home, who do you think will have the better chance of going back to where you were ? The Driver !

What do you think I'm going to do to you now ? I'm going to let you drive and I get to ride along as the passenger (co-pilot). Let's get going and prepare Ma and Pa's 12 Month Cash Forecast using My Cash Forecast Workbook and the information that Ma assembled and prepared.

Prepare Ma's Forecast using Bean Counter's Cash Forecast Workbook

Return To Tutorial