Notes on the Retail Forecasting Tool
There are five interlocking spreadsheets in the system:
- Operating Budget
- Sales and Cost of Sales Forecast
- Merchandise Budget
- Cash Flow Forecast
- Operating Loan
Certain elements are the result of variables entered by the user. Once a variable is entered, other numbers are generated as a result. For example, sales entries in the Sales and Cost of Sales Forecast sheet will automatically populate the sales numbers in the Operating Budget and the Cash Flow Forecast. Credit card fees are generally a percentage of sales and are therefore calculated automatically in the Operating Budget. If the sales numbers in the Sales Forecast sheet are changed, other figures such as Cost of Sales and Credit Card Fees are automatically recalculated.
This is a financial model of the entire business for the year. Monthly operating statements can be compared with the budget numbers to see how the business is performing, compared to expectations. Certain lines in the Operating Budget are dependent on other sheets or are generated automatically:
Sales and Cost of Sales: Generated from the Sales and Cost of Sales Forecast Sheet
Operating Expenses: Input from the user except Bank Charges which are generated automatically
Column and Row Totals: All of these sums are calculated by the system and are locked
Sales and Cost of Sales Forecast Sheet:
This sheet enables the user to forecast sales for each month in each category of merchandise, along with the corresponding Cost of Sales percent. The corresponding Cost of Sales and Gross Profit amounts for each month are generated automatically, depending upon the estimated sales and the corresponding cost of sales percentages.
Sales Forecast: User Input
Cost of Sales Percentages: User Input
Cost of Sales Amounts and Gross Profit Amounts: All locked.
Column and Row Totals: All locked
Merchandise Budget Sheet:
The merchandise budget is based on forecast sales and the cost of merchandise required to generate those sales. Once the Sales and Cost of Sales has been generated by the Sales Forecast, the merchandise budget is determined, with only two variables; the inventory at cost at the beginning of the period and the target inventory at cost, at the end of the period. These two numbers are the only two cells available to the user in the Merchandise budget Worksheet.
Cash Flow Forecast:
The purpose of the Cash Flow Forecast is to highlight points throughout the year when there might be a shortage of cash. This enables the business to arrange in advance a temporary supply of cash to sustain it through a lean period. Cash is the lifeblood of a business. A viable cash flow plan is essential to the survival of the business.
The Cash Flow Forecast is generated from the Sales Forecast, the Merchandise Budget and the Operating Budget. The only cell available to the user is the Cash Balance at the beginning of the year.
The Cash Flow Forecast identifies periods where additional cash may be needed to keep the business solvent. Obviously, if the business is operating at a loss, over an extended period borrowing may become a problem. The assumption is that operating loans will be repaid within a year. Banks, in particular like to see operating loans at zero at least once during a year.
Download the Retail Budget and Forecasting Tool, plug in your own numbers and give it a try:
Remember, the sheets are locked; you’ll have to unlock them to modify the numbers. Take care not to over-write cells containing formulas.