Sales Forecast Model

5 Year Sales, Margin and Inventory Forecast System

The Distribution Forecast Sys V2 program will forecast monthly sales, margins and inventory levels for 60 months by region by sales channel (and/or major customer) by product. The program has the capacity for 15 regions, 15 sales channels and 35 products per region. Sales, margins, inventory turns and compound growth percentages are entered by region by channel by product for the initial 12 to 36 month period. Sales are projected using up to three levels of compound growth percentages and any one of five statistical methods. All reporting is generated using Excel Pivot tables which add the flexibility to report by region by channel by product in monthly, quarterly or annual intervals. Graphs and trends are plotted from the Pivot Table data.

The program saves all forecast data in two Excel databases. The Annual Sales database maintains all regional data entry so it can be easily recalled and modified. This database holds up to 7900 records (15 Regions x 15 Channels x 35 Products). After the regional sales data is loaded to the Annual Sales database then processed, the monthly forecast results are posted to the Sales Activity database. This is the source for Pivot Table analysis and graphs.

The Sales Activity database holds up to 472,500 records (15 Regions x 35 Products x 15 Channels x 60 Months). With Excel 2003 you are limited to 32,500 records. But this is still adequate because every region may not sell every product into every channel. Few implementations will exceed 30,000 records. To run the system at capacity requires Excel 2007 which has expanded capabilities to 1 million rows.

The Distribution Forecast Sys V2 can save any number of scenarios. The scenario files are stand-alone Excel workbooks that contain just the forecast data (the Annual Sales DB sheet). To facilitate collaboration, this workbook is small (about 60K in a zip file format) and can be easily emailed, recalled, processed then analyzed.

There is a variance analysis feature which provides for making changes to key forecast assumptions. Both the base forecast and changed forecast results are posted to the SalesActivity DB so the effects of the changes can be compared and using Pivot tables.

A companion Regional_Entry_Template.xls workbook is distributed to each region’s sales manager. When the template is completed, it is emailed to the corporate office and added to the Company's Annual Sales database.

5 Year Financial Forecast

There is also a companion (and equally as sophisticated) 5 year financial forecast Excel workbook that includes monthly regional operating detail, income statements, cash flow, balance sheets and financial summary analysis. Sales, Cost of Sales and Inventory balances are linked to the sales forecast system results.

Display and print options are menu driven:

An interactive contents page is provided to help navigate the model:

Financial reports are designed in both an attractive and professional manner:

This is a complex distributed sales and financial forecasting system automated with thousands of lines of Visual Basic code. But, it is still an Excel model and knowledge of Excel (especially the pivot table features) is really helpful if you want to get the full benefit from the system. Also, this model is not generally recommended for companies with less than $5 million in sales. The cost to implement the system will be between $1,000 and $5,000 depending on the size and complexity of your operations and expertise of your staff.

If you are interested in implementing the Sales Forecast System V2 for your business, please contact me. I will send you the program in zip file format (1.3 MB file) and the sample data (1.5 MB file), help you with installation and do a demo over the phone. If you like what you see, I'll send you a quote to help your financial and sales teams implement the system.

Learn More ..... Download the 5 Year Sales and Financial Forecast System draft documentation.

This document is a 1 MB MSWord file. It is 37 pages with lots of analysis, illustrations and and screen shots. The document is presented in three sections:

Section I : Sales Forecasting Guidelines

Section II : 5 Year Sales, Margin and Inventory Forecast System

Section III : 5 Year Financial Forecast System