497 Lecture
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 497 Lecture as PDF for free.
More details
- Words: 1,099
- Pages: 5
Excel spreadsheet templates are available:
Sales Forecasting & Production Planning
Presented by Dr. K. Lai
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.
For the business game, you need to enter your decision numbers onto a Decision Form:
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 USB Decision drive: 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.
Do not use any data from the trial run. The game will be reset with new data after the trial run.
To view company and industry data:
In forecasting sales, we need to account for seasonal effects: See Section 1.A of the Lecture Notes on Forecasting. Seasonal Indices (p.105 of the Player’s Manual) Q1 (Winter) Q2 (Spring) Q3 (Summer) Q4 (Fall)
0.92 1.01 0.91 1.16
A toptop-down approach will be used for sales forecasting:
Use a regression model to forecast industry sales:
Industry Level
See Section 1.B of the Lecture Notes on Forecasting.
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
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:
After obtaining company sales forecasts, we next determine how much to produce:
Model #1:
Read Additional Notes on Production Planning (download it from http://www.calstatela.edu/faculty/klai/CL497.htm).
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.
StepStep-byby-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) 2) 3) 4) 5)
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).
For our production analysis, we will use the following two Excel templates together (read Sections 2.B of the Lecture Notes on Production Planning for step-by-step instructions): 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.
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 overover- and underunder-stocking costs.
Overall: Keys to successful production management
Choose an inventory-to-sales ratio for each market area (when using the Shipment Orders Worksheet):
A number of factors are crucial for a company’s success in production management:
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)
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 and future 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
Hope you will enjoy the Business Policy Game!
We will next look at how to use our Excel templates.
Sales Forecasting & Production Planning
Presented by Dr. K. Lai
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.
For the business game, you need to enter your decision numbers onto a Decision Form:
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 USB Decision drive: 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.
Do not use any data from the trial run. The game will be reset with new data after the trial run.
To view company and industry data:
In forecasting sales, we need to account for seasonal effects: See Section 1.A of the Lecture Notes on Forecasting. Seasonal Indices (p.105 of the Player’s Manual) Q1 (Winter) Q2 (Spring) Q3 (Summer) Q4 (Fall)
0.92 1.01 0.91 1.16
A toptop-down approach will be used for sales forecasting:
Use a regression model to forecast industry sales:
Industry Level
See Section 1.B of the Lecture Notes on Forecasting.
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
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:
After obtaining company sales forecasts, we next determine how much to produce:
Model #1:
Read Additional Notes on Production Planning (download it from http://www.calstatela.edu/faculty/klai/CL497.htm).
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.
StepStep-byby-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) 2) 3) 4) 5)
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).
For our production analysis, we will use the following two Excel templates together (read Sections 2.B of the Lecture Notes on Production Planning for step-by-step instructions): 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.
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 overover- and underunder-stocking costs.
Overall: Keys to successful production management
Choose an inventory-to-sales ratio for each market area (when using the Shipment Orders Worksheet):
A number of factors are crucial for a company’s success in production management:
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)
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 and future 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
Hope you will enjoy the Business Policy Game!
We will next look at how to use our Excel templates.
Related Documents
497 Lecture
June 2020 2
497
October 2019 5
Surahtaubahpages476-497
July 2020 3
497-355
May 2020 5
Colamulse 497
April 2020 3