sales forecasting & production planning presented by dr. kern kwong

Sales Forecasting &

Production Planning

Presented byDr. Kern Kwong

Decision Form:

Excel Spreadsheet Templates:

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.

Also from our class Moodle site, with updated historical data


Use your own data from both industry and company reports:

After opening the BPG program, you can view all the reports:

Report J (see p.210 of the Player’s 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 Player’s Manual for a sample)

Company’s Current Operating Information – Output, inventory, and product sales

Report F (see pp.217-8 of the Player’s Manual for a sample)

Recent Industry Information – Real GDP, exchange rates, product sales, and product prices.

View reports:

A top-down approach will be used for sales forecasting:

Industry LevelThe 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 LevelFrom 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

Need to account for seasonal effects on sales:

See Section 1.A of the Lecture Notes on Forecasting.

Seasonal Indices (p.105 of the Player’s Manual)

Q1 (Winter) 0.92Q2 (Spring) 1.01Q3 (Summer) 0.91Q4 (Fall) 1.16

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.

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

Model #1:SA Sales = 0 + 1 Time

Model #2:SA Sales = 0 + 1 Real GDP

Model #3:SA Sales = 0 + 1 Avg Price

Model #4:SA Sales = 0 + 1 Time + 2 Real GDP

All these forecasting equations are to be estimated using Excel on the Sales Forecast Worksheet.

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

Model #5:SA Sales = 0 + 1 Real GDP + 2 Avg Price

Model #6:SA Sales = 0 + 1 Avg Price + 2 Time

Model #7:SA Sales = 0 + 1 Time + 2 Real GDP + 3 Avg Price

Best Model: highest Adjusted R-square with Correct Sign (e.g. Coefficient of Avg Price should be negative,

SA Sales decrease when Avg Price increases)

Best Model – M1, Y3Q1

Model Adj. R-Square Correct Sign

1 Time .545 Yes

2 Real GDP .530 Yes

3 Avg Price .023 Yes

4 Time + Real GDP .454 No-GDP

5 Real GDP + Avg Price .437 No-Price

6 Avg Price + Time .474 No-Price

7 Time + Real GDP + Avg Price .394 No-Price, GDP



1 Time

2 Real GDP

3 Avg Price

4 Time + Real GDP

5 Real GDP + Avg Price

6 Avg Price + Time

7 Time + Real GDP + Avg Price



1 Time

2 Real GDP

3 Avg Price

4 Time + Real GDP


6 Avg Price + Time


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:

1) To start, prepare initial data on regression variables using available historical data (see Section 2.A).

2) After setting up the data, estimate the forecasting regression equation using Excel (see Section 2.B).

3) Try different models and select the model that fits the data best (see Section 2.C).

4) Enter additional assumptions and your market share projection (see Section 2.D).

5) Repeat the forecasting exercise – steps 2 to 4 – after adding new data every quarter (see Section 2.E).

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

Read Lecture Notes on Production Planning (download it from http://www.calstatela.edu/faculty/klai/CL497.htm), OR from Moodle.

For our production analysis, we will use the following two Excel templates together:

Shipment Orders Worksheet

Production Schedule Worksheet


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.

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.

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

See Chapters 7 & 8 of the BPG Player’s 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)

Overall: Keys to Successful Production Management

A number of factors are crucial for a company’s success in production management:

Reasonably accurate sales forecasts;Excellent inventory control to cope with demand and production uncertainties;Proper allocation of product shipments to regional sales offices and thereby to customers;Efficient production scheduling to meet current production targets;Timely production capacity adjustment (including plant expansion or construction) to meet future product demand.

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

[email protected] quarter

Screen print of your

decision screen and

research report order (if your ordered)

Hope you will enjoy the Business Policy Game!

sales forecasting & production planning presented by dr. kern kwong

Documents

industry sales forecasts

real gdp avg price

time real gdp

avg price time

sa sales decrease

real gdp model

avg price model

time model