Laboratory no.

Collaborative planning, forecasting and replenishment

1. Subject: Supply chain management and logistics

2. Master program: Service Engineering and Management

3. Lecturer: Silviu RAILEANU

4. Objectives

Given the demand forecast for a determined horizon the objective is to determine
the production level, inventory level and capacity level (internal and outsourced)for each
period that maximizes the firm’s profit over the planning horizon. The application consists
of a case study presented in (Sunil, Mendil, 2010).

5. Theoretical Summary

An aggregate planner requires the following information:

 Demand forecast Ft for each period t in a planning horizon that extends over
T periods
 Production costs
 Labour costs, regular time ($/hour), and overtime costs ($/hour)
 Cost of subcontracting production ($/unit or $/hour)
 Cost of changing capacity; specifically, cost of hiring/laying off
workforce ($/worker) and cost of adding or reducing machine capacity
($/machine)
 Labour/machine hours required per unit
 Inventory holding cost ($/unit/period)
 Stockout or backlog cost ($/unit/period)
 Constraints
 Integrated supply chains and logistics

 Limits on overtime
 Limits on layoffs
 Limits on capital available
 Limits on stockouts and backlogs
 Constraints from suppliers to enterprise

Using this information a company makes the following determinations through

Production quantity from regular time, overtime and subcontracted time: used to
determine number of workers and supplier purchase levels

Inventory held used to determine the warehouse space and working capital
required

Backlog/stockout quantity used to determine customer service levels

Workforce hired/laid off used to determine any labour issues likely to be

Machine capacity increase/decrease used to determine if new production

The first step in constructing an aggregate planning model is the definition of the
decision variables:

Wt = workforce for month t

Ht = number of employees hired at the beginning of month t

Lt = number of employees laid off at the beginning of month t

Pt = number of units produced in month t

It = inventory at the end of month t

St = number of units stocked out/backlogged at the end of month t

Ct = number of units subcontracted for month t

Ot = number of overtime hours worked in month t

Dt = demand in period t, specified by demand forecast

2
 n = time period (integer)

The objective function is to minimize the total cost (equivalent to maximizing total
profit as all demand is to be satisfied) incurring during the planning horizon. The cost
incurred has the following components (the total cost being the sum of all the intermediary
costs):

( )
∗hours
hour
Regular-time labour cost = n ∗days
day
∑ ∗W t
i=1 month

n
$
Overtime labour cost = ∑
i=1
( hour )∗O t

n
Cost of hiring and layoffs = ∑ cost ¿∗H t +cost ¿∗Lt +¿
i=1

n
Cost of holding inventory and stocking out = ∑ cost ¿∗I t + cost ¿∗S t
i=1

Cost of subcontracting and materials =

∑ materialcost∗Pt + subcontractin gcost∗C t

Table 1 - Costs used for practical application

Item Cost
Material cost $10/unit
Inventory holding cost $2/uni/month
Marginal cost of
stockout/backlog $5/unit/month
Hiring and training costs $300/worker
Layoff cost $500/worker
Labour hours required 4/unit
Regular time cost $4/hour
Overtime cost $6/hour
Cost of subcontracting $30/unit

Constraints

Workforce, hiring and layoffs constraints: the workforce size Wt in period t is

3
 Integrated supply chains and logistics

Wt = Wt-1 + Ht – Lt

Capacity constraints: in each period, the amount produced cannot exceed the
available capacity (in the example a worker can produce 40units/month on regular basis
and one unit for every four hours)

Pt=40 Wt + Qt/4

Inventory balance constraints: balances the inventory at the end of each period. Net
demand for period t is obtained as the sum of the current demand Dt and the previous
backlog St-1. This demand is either filled from current production (in-house production Pt
or subcontracted production Ct) and previous inventory It-1 (in which case some inventory
It may be left over) or part of it is backlogged St:

It-1 + Pt + Ct = Dt +St-1 It – St

I0=1000

In>=500

Overtime limit constraints: no employee work more than MAX_HOURS/month:

Qt<= MAX_HOURS * Wt

Additionally each variable must me integer

6. Application Description

The input data (demand forecast for a 6 month period and initial decision variable
values) is given in the table below, which will be used along with the Theoretical
Summary to solve the problem using the Solver from Excel/Google Spreadsheet.

It
Ht Lt Wt Ot Inventor St Ct Pt
Period Hired Laid off Workforce Overtime y Stockout Subcontract Production Demand
0 0 0 80 0 1000 0 0 0  
1 0 0 0 0 0 0 0 0 1600
2 0 0 0 0 0 0 0 0 3000
3 0 0 0 0 0 0 0 0 3200
4 0 0 0 0 0 0 0 0 3800
5 0 0 0 0 0 0 0 0 2200

4
 6 0 0 0 0 0 0 0 0 2200

7. Support Information

Chapter 8, Sunil Chopra, Peter Mendil, Supply Chain Management. Strategy,

References

Sunil Chopra, Peter Mendil, Supply Chain Management. Strategy, Planning and
Operation, 2010, Fourth Edition