Sales Forecasting

1
Sales Forecasting Production Planning
Presented by Dr. K. Lai
2
For the business game, you need to enter your
decision numbers onto a Decision Form
3
Excel spreadsheet templates are available

• Several templates will be provided
• Historical Data Worksheet
• Sales Forecast Worksheet
• Shipment Orders Worksheet
• Production Schedule Worksheet
• An Excel file containing these templates can be
  downloaded online at
• http//www.calstatela.edu/faculty/klai/CL497.htm
• Additional lecture notes, along with some flow
  charts, can also be downloaded from there.

4
Use your own data from both industry and company
reports

• After opening the BPG program, you can view all
  the reports available as electronic files on the
  Decision Disk
• Report J (see p.210 of the Players Manual for a
  sample)
• Historical Data for Years 1 and 2 GDP, CPI,
  product sales, and product prices.
• Report D (see p.215 of the Players Manual for a
  sample)
• Companys Current Operating Information
  Output, inventory, and product sales
• Report F (see pp.217-8 of the Players Manual for
  a sample)
• Recent Industry Information Real GDP, exchange
  rates, product sales, and product prices.
• Do not use any data from the trial run. The game
  will be reset with new data after the trial run.

5
To view company and industry data
6
A top-down approach will be used for sales
forecasting

• Industry Level
• The method starts with sales forecasting at the
  industry level for each market area
• M1 (Merica 1)
• M2 (Merica 2)
• M3 (Merica 3)
• M4 (Nystok, Pandau, or Sereno)
• Company Level
• From industry sales forecasts, company sales
  forecasts for the corresponding market areas can
  then be obtained as
• Company Sales Forecast
• Industry Sales Forecast Expected Market
  Share

7
Need to account for seasonal effects on sales

• See Section 1.A of the Lecture Notes on
  Forecasting.
• Seasonal Indices (p.105 of the Players Manual)
• Q1 (Winter) 0.92
• Q2 (Spring) 1.01
• Q3 (Summer) 0.91
• Q4 (Fall) 1.16

8
Use a regression model to forecast industry sales

• See Section 1.B of the Lecture Notes on
  Forecasting.
• Dependent variable (Y)
• SA Sales Seasonally Adjusted Industry Sales
• Independent variables Predictors (X)
• Real GDP Real Gross Domestic Product
• Avg Price Industry Average Price
• Time Time Trend Index
• Note Real GDP is an often used indicator for
  the general demand and business conditions. The
  Time variable can capture demand changes
  generated by demographic trends.

9
Try a few different forecasting equations and
identify the best one

• Model 1
• SA Sales ?0 ?1 ? Real GDP ?2 ? Avg Price
• Model 2
• SA Sales ?0 ?1 ? Time
• Model 3
• SA Sales ?0 ?1 ? Time ?2 ? Real GDP
• Model 4
• SA Sales ?0 ?1 ? Time ?2 ? Real GDP ?3 ?
  Avg Price
• All these forecasting equations are to be
  estimated using Excel on the Sales Forecast
  Worksheet.

10
Step-by-step forecasting exercise

• When using the Excel template for forecasting,
  you should read Sections 2.A to 2.E of the
  Lecture Notes on Forecasting for step-by-step
  instructions.
• We will go through all the steps when looking at
  the template later
• To start, prepare initial data on regression
  variables using available historical data (see
  Section 2.A).
• After setting up the data, estimate the
  forecasting regression equation using Excel (see
  Section 2.B).
• Try different models and select the model that
  fits the data best (see Section 2.C).
• Enter additional assumptions and your market
  share projection (see Section 2.D).
• Repeat the forecasting exercise steps 2 to 4
  after adding new data every quarter (see Section
  2.E).

11
After obtaining company sales forecasts, we next
determine how much to produce

• Read Additional Notes on Production Planning
  (download it from http//www.calstatela.edu/facult
  y/klai/CL497.htm).
• For our production analysis, we will use the
  following two Excel templates together
• Shipment Orders Worksheet
• This is used to estimate the need of each market
  area in terms of shipments of new product units
  to these market areas in coming quarters.
• Production Schedule Worksheet
• This is used to determine the production
  schedule needed to satisfy forecasted product
  demand and inventory needs for each market area
  over the next few quarters.

12
To determine a production target, we need to
think about inventory management

• How much inventory to hold in each market area?
• Carrying too little inventory may lead to costly
  stockouts
• Stockouts can result in not only a loss of
  present sales but also a loss of some future
  sales. Some dissatisfied customers may not come
  back.
• Carrying too much inventory can be costly too
• Warehouse storage cost
• Financing cost for tying up working capital
• Product obsolescence.

13
Choose an inventory ratio that balances between
over- and under-stocking costs.

• Choose an inventory-to-sales ratio for each
  market area (when using the Shipment Orders
  Worksheet)
• Under normal situations, a ratio from 25 to 45
  should be sufficient for the game.
• An example Suppose the ratio is chosen to be
  25. If the sales demand is forecasted to be
  100,000 units, then
• Desired Inventory 100,000 25 25,000
  units.
• The choice of inventory-to-sales ratios will
  affect how many product units to be shipped to
  different market areas.

14
How should production be scheduled?Should
production capacity be expanded?

• See Chapters 7 8 of the BPG Players Manual
  (read also Section 3 of the Lecture Notes on
  Production Planning)
• Normal operations 40 hours per line each week
• Schedule overtime Up to 8 hours per line
• Add second work shifts (Take 1 quarter to
  complete)
• Create new production lines (Take 1 quarter)
• Reactivate some idle lines (Take 1 quarter)
• Add more space to a plant (Take 2 quarters)
• Build a new plant (Take 3 quarters)

15
Company Sales Forecasts by Market Area
Desired Inventory Ratio
Estimated Shipment Orders to Sales Offices by
Market Area
Planned Production Target
Production Scheduling Lines, Overtime, and
Second Shifts
Production Capacity Expansion New Lines or
Plants?
Production Cost Analysis
Capital Budgeting Analysis
16
Hope you will enjoy the Business Policy Game!

• We will next look at
• how to use our Excel templates.