Production Planning Case

1
Production Planning Case

• Henry C. Co
• Technology and Operations Management,
• California Polytechnic and State University

2
Enter the given data as follows
Read this table carefully. Make sure you
understand each entry.
3
Constraints and Information

• Production capacity
• Beginning inventory for the year is estimated to
  be 2,000 containers
• End of year inventory of 2,000 containers.
• Therefore, total production for the year must be
  equal to total demand.

4
Cell C17C28 are the decision variables. C17
amount to produce in January, C18 amount to
produce in February, etc. From the preceding
slide, we know that SUM(C17C28) must be equal to
106, the total demand for the year.
D17D28 are the forecasted demand given in the
case. D29SUM(D17D28)
Construct the following table to keep track of
production and inventory.
5
Shown here is a feasible solution. Production
each month is 12 (maximum possible
capacity) Total for the year is C29
SUM(C17C28)106.
6
Ending inventory Beginning inventory
production demand (This is common sense. What
you have at the beginning, plus what you
produced, minus what you sold is what is left at
the end of the month. Thus E17B17C17-D17.
Copy E17 and paste all the way down to Cell E28.
7
Beginning Inventory in February is the ending
inventory in January (This is common sense).
B18E17. Copy and paste all the way down to cell
B28.
8
Before you go any further, make sure you have the
following results.
9
Construct this following table to keep track of
cost.
10
How do we keep track of labor cost?

• We will use an Excel function called VLOOKUP, as
  shown on the right.
• First, we need to construct a lookup table.

11
From the Labor Cost table given in the case (see
cells E3F6 below), construct the VLOOKUP table
(see cell M19N23 below). Cells M19M23 show
the range of production, and Cells N19N23 show
the corresponding labor cost (as given in the
case).
If we produce nothing (cell M190), the cost
(cell N19) is 0. The cost is 3,000 (cell N20) if
production 0.001 (cell M20), but less than M21,
etc. If production 5.001 (cell M21), the cost
is 4,000 (cell N21), etc. Before you go any
further, make sure you understand this table.
12
Click on cell G17 (the labor cost for January),
then click Insert, choose Function, as shown here
13
From the pull down menu, choose VLOOKUP. You will
then see the Function Arguments submenu.
14
The Lookup_value is Cell C17 (the production for
January). The Table_array is our lookup table
M19N23. Make sure you type the signs. We
will be pasting this formula later. The
Column_index_num is 2 (i.e., the answer is in the
second column.)
15
Copy the formula in Cell G17, and paste it all
the way down to cell G28. We have now complete
the labor cost of the production plan.
Make sure that you have G17VLOOKUP(C17,M19N
23,2)
16

• Now, let us tell Excel how many shifts we are
  running the plant each month.
• For January, this is Cell H17. From the case, we
  know that when we run the factory at one shift,
  the cost is either 3,000 or 4,000 (with
  overtime).
• Thus if the labor cost is more than 4,000, we
  are running the plant at two shifts.
• We will be using an Excel function called IF.
• The function IF looks like this
• IF (True/False question, result if true, result
  if false).
• For example, H17IF(G17gt4000,2,1). The True/False
  question is whether the value in cell G17 is
  greater than 4000. If the answer is true, then
  H172. If the answer is false, then H171.

17
The number of shifts we are running for January
is Cell H17. When we run the factory at one
shift, the cost is either 3,000 or 4,000 (with
overtime). Thus if the labor cost is more than
4,000, we are running the plant at two shifts.
Hence the formula for cell H17 is
H17IF(G17gt4000,2,1).
The formula is saying that IF G17 is greater than
4000, then H172, otherwise, H171. Copy and
paste this formula all the way down to H28.
18
Before you go any further, make sure you
understand this table
We are now ready to determine the cost of
changing shift. Again, we will be using Excel
function IF.
19
The table below is given in the case. The cost of
changing from 1 shift to 2 shifts is 1,500. The
cost of changing from 2 shifts to 1 shift is
1,000.
We know that the month before January (December
last year) we were running the plant at 1 shift.
In January, the number of shifts we are running
is shown in cell H17. We therefore ask the
following True/False question
20
I17IF(H171,0,1500). i.e., If H171 (running 1
shift in January), there is no change and
therefore cost is 0. Otherwise, the cost is 1500.
21
What about February? To find out if there is a
changeover cost, we must compare the number of
shifts in February (cell H18), with the number of
shifts in the preceding month (cell H17). We
write I18IF(H18gtH17,1500,IF(H18ltH17,1000,0)) T
his is a nested IF statement. Note that there are
two True/False questions. The first True/False
question asks if H18gtH17. If this is true,
then we are adding a shift, at a cost of 1500.
If this is false, we ask the second True/False
question Is H18ltH17? If this is true, then we
are reducing the number of shits, at a cost of
1000. If false, then there is no changeover, and
thus no cost.
22
Copy I18 and paste all the way down to Cell I28.
23
We are not quite done with the changeover cost
yet. In the case, we were told that if the
number of shifts in December is 2, we have to
change it back to 1 shift. Hence, we add the
following statement IF(H28gt1, 1000,0), i.e., if
the number of shift is greater than 1, we have a
changeover cost of 1000. Otherwise, there is no
changeover cost. The Excel formula for Cell I28
therefore is IF(H28gtH27,1500,IF(H28ltH27,1000,0)
)IF(H28gt1, 1000,0)
24

• Now, let us tell Excel to keep track of inventory
  cost.
• If ending inventory is positive, there is a
  carrying cost of 300 per 1000 (all units in the
  Excel worksheet are in 000). If ending inventory
  is negative, we have a backorder cost of 500 per
  1000. Click cell J17, and we write
• J17IF(E17gt0,E17300,E17500)

Copy J17 and paste all the way down to Cell J28.
25

• The last step is to add all the costs for each
  month K17SUM(G17,I17,J17)

Copy K17 and paste all the way down to Cell J28.
26

• Add the costs for the 12 months K29SUM(K17K28)

27
The objective is to minimize the total cost by
changing the production plan.

• Try to see if you can reduce the total to less
  than 80,000.