| The
model includes these features. |
| |
| • Segments revenue and unit sales by product x selling location
x sales channel x industry segments. |
| |
| • Uses linear regression on sales unit history to get raw
forecast for sales units (function Linest). |
– Imposes positive and negative limit bumpers on regression
coefficients, so that forecasts in small and
unstable segments do not blow up
the entire sales plan. |
– New products (products with zero sales in early time periods)
start their trend lines after estimated
product introduction dates. |
| |
• Forecasts revenue for product x selling location x sales
channel x industry segments, using unit sales plan,
list prices, and price discount percent. |
| – Uses list prices and actual price discount history to forecast
actual average selling prices by segments |
| – User can override assumed price discount plan with new inputs |
| |
• Manager’s revenue targets for various segments override the
raw regression and enable human inputs
to control the plan. |
| – Managers provide revenue targets for each product, location
and industry segment |
– The model computes suggested adjustment factors to raw
regression forecast to make the final forecast
closely match managers’ targets. |
| – The model includes adjustment factors for two-factor segments
(for example product-location segments). |
| |
| • Has separate summaries for segments and overall plan |
| – sales plan by product, suppressing location and industry |
| – sales plan by location, suppressing product and industry |
| – sales plan by industry, suppressing product and location |
| – sales plan by products x locations x industries |
| |
| • Handles seasonality in three ways. |
| – Estimates
growth trends free of seasonality. |
| ‒ Adds
historic seasonality back into forecast using clean and powerful regression
methods. |
‒ Includes trailing-4-quarter history and plan, to eliminate
seasonality and reduce effects of random
large orders. |
| |
• Starts forecast trend for recently-introduced products at
introduction (by excluding early time periods with
zero sales from the regression). |
| |
• Growth rates, average prices, and price discount percentages
are computed correctly for collections
of products (product families),
collections of geographic locations, and roll-ups of time periods, using
units-weighted averages instead of
straight averages. (This feature alone would be prohibitively
difficult to implement manually in
conventional spreadsheets.) |
| |
| • Three worksheets are provided for database input of sales unit
history, revenue history, and list price history. |
| |
| Additional features |
| |
• The model includes Excel charts that provide graphical views
of key variables. These charts are part
of the model, and they are
included by default in exported Excel workbooks. You can add more charts,
import them, and the new charts
will be included in exported Excel workbooks. |
| |
• The model computes contribution margins for each
product-market segment, based on variable cost per unit
that you supply for several types
of variable costs. |
| |
| • Reports prices and revenues for the Euro zone in dollars and
Euros |
‒ Allows exchange rate to change by quarter. (Sales planning
systems often don't reflect exchange rate
fluctuations during a plan year,
so that the sales force has a stable target to shoot at.) |
| |
| As you explore the model, we suggest that you |
• Read some of the Excel comments that are attached to Analysis
Variables throughout the workbook.
These comments also appear in
FinModel in convenient places. |
• View worksheet "Formulas" which shows the named
variables and symbolic formulas of the model
in a compact and readable form.
The symbolic formulas are not active in this Excel workbook, but they
give you some idea how the model
works, and how it looks in FinModel. |
| |
| Organizational benefits |
| |
| Besides providing better sales plans, this planning approach
delivers organizational benefits. |
• Enables product managers, location
managers, executives (and industry managers when the model is
extended) to contribute to the plan
in their areas of expertise without generating chaos. |
‒ This improves on the usual situation, where only channel
managers and top executives specify targets
because it is too hard to
reconcile product, channel, industry, and macro-economic factors. |
‒ When targets conflict, planners can make the plan agree
with channel managers’ targets or product
managers’ targets etc. |
• Enables
product managers to distinguish their recommended sales targets from the
company’s plan, for use
in variance analysis. |
| • Builds consensus behind the
plan. |