Main Worksheet
Cash Forecasts > Forecasting Exercise
Ma & Pa's Next 12 Months Cash Projection
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 and their bank reconciliations.
Using Ma & Pa's Assumptions, it's your turn to enter the data in the Cash Projection Worksheets. The Individual Worksheet's instructions along with the instructions and data I've provided below tell you what and where to enter information. Once you have the information it's basically a simple matter of filling in the blocks.
Entering Ma & Pa's Information, Assumptions, and Plans
Needed For Their 12 Month Cash Forecast:
Steps To Take To Prepare Your Projection
Enter Historical Financial Information, Collection and Payment Patterns, and Beginning Balances using the Main Cash Projection Worksheet. All required entries are highlighted in yellow.
Enter your Estimated Monthly Sales using the Sales Forecast Worksheet. All required entries are highlighted in yellow.
Enter your Estimated Monthly Payments using the Cash Payments Worksheet. All required entries are highlighted in yellow.
Now, that doesn't sound too difficult - does it ?
We'll enter our information a Section at a time.
Main Cash Projection Worksheet
Used to enter actual Financial Information and Percentages based on Analyses Performed
Beginning Cash Balance
Our First Task is to fill in our Beginning Cash Balance Amount Of $6,322 located at the Top of the Worksheet.
Basic Financial Information Worksheet
Basic Financial Information
Next, we need to enter Basic Financial Information obtained from the Analyses of Ma and Pa's Financial Information
A/R and Cash Sales Collected As % of Sales | |
Cash Sales | |
% of Cash Sales 10% | |
Sales On Account | |
% Collected Month Of Sale | 14% |
% Collected One Month After Sale | 76% |
Inventory and Cost Information | |
Actual Financial Data | |
Actual Ending Inventory | 21000 |
Actual Sales Prior Year | 447000 |
Actual Cost Of Goods Sold Prior Year | 249300 |
Payments On Purchases | |
Cash Purchases | |
Cash Purchases In Current Month | 10% |
Purchases On Account | |
% of Payments In the Month Of Purchase | 90% |
Payroll Tax Rates | |
Social security and Medicare % | 7.65% |
Federal Unemployment % Rate | .80% |
State Unemployment % Rate | 2.7 |
Accounts Receivable Prior Year | Totals | Month 1 | Month 2 | Month 3 |
Beginning Accounts Receivable Net Balance | 35000 | |||
Estimated Current Balance | 26000 | 21000 | 5000 | |
Estimated 1-30 Days Late | 5000 | 5000 | ||
Estimated Over 30 Days Late | 4000 | 4000 | ||
Future Sales (Extra 3 Months) | ||||
Estimated Sales Additional 3 Months | Future 1 | Future 2 | Future 3 | |
40000 | 44000 | 45000 | ||
Accounts Payable Payments | Month 1 | Month 2 | Month 3 | |
Beginning Accounts Payable Balance | 22800 | |||
Estimated Current Balance | 22800 | 22800 | ||
Estimated 1-30 Days Late | ||||
Estimated Over 30 Days Late | ||||
Other Financial Information | ||||
Beginning Inventory | 21000 | |||
Sales Tax Owed | 3500 | |||
Sales Tax Rate (Percentage) | 8% | |||
Percentage of Exempt Sales | 40% | |||
Percentage of Taxable Sales (Calculated) | 60% | |||
Other Amounts Owed | 5500 | 5500 |
Where Do We Get This Information ?
These Percentages and Amounts came from our historical Financial Information, Analyses of Receipts and Payments, and Sales and Costs Relationships.
I Need Help
Other Receipts and Payments
Other Receips and Payments require you to manually enter this information in the Main Sheet.
Ma does not have any Other Receipts that need to be entered. The only Other Payments Ma & Pa have are Owner Draws of $3,500 per month. These need to be entered in the Payments Section of the Main Worksheet.
I Need Help
What's next ?
Sales Projection Worksheet
