David Violette Chase Sizzling Springs Company
March 15, 1998 BADM 635
Case Study Dr. Co
Month Demand '000 Production Information
January 5 Level Constraints and Information:
February 4 1 shift 5 $ 3,000
March 5 1 shift + overtime 7 $ 4,000 Beginning inventory for the year is estimated to
April 7 2 shifts 9 $ 6,000 End of year inventory of 2,000 containers (Cons
May 10 2 shifts + overtime 12 $ 8,000
June 12
July 14 Changeover Cost Challege: Can you find a solution better than $77,400?
August 13 1 shift to 2 shifts $1,500
September 11 2 shifts to 1 shift $1,000
October 8
November 10 Inventory Holding Cost $300 per 1000 held at end of month
December 7 Backlog cost $500 per 1000
Total 106
Beginning Ending No. of Level Inventory
Month Inventory Produce Demand Inventory Labor Shifts Change $ Cost
January 2 5 5 2 $ 3,000 1 $ - $ 600
February 2 4 4 2 $ 3,000 1 $ - $ 600
March 2 5 5 2 $ 3,000 1 $ - $ 600
April 2 7 7 2 $ 4,000 1 $ - $ 600
May 2 10 10 2 $ 8,000 2 $ 1,500 $ 600
June 2 12 12 2 $ 8,000 2 $ - $ 600
July 2 12 14 0 $ 8,000 2 $ - $ -
August 0 12 13 -1 $ 8,000 2 $ - $ 500
September -1 11 11 -1 $ 8,000 2 $ - $ 500
October -1 8 8 -1 $ 6,000 2 $ - $ 500
November -1 10 10 -1 $ 8,000 2 $ - $ 500
December -1 7 7 -1 4000 1 $ - $ 500
Total 103 106 $ 71,000 $ 1,500 $ 6,100
E17=B17+C17-D17 Ending inventory = Beginning inventory + production - d
G17=VLOOKUP(C17,$M$19:$N$23,2) Use VLOOKUP function
H17=IF(G17>$F$4,2,1)
I17=IF(H17=1,0,E9) Operating at 1 shift production at beginning of year
I18=IF(H18>H17,$E$9,IF(H18H27,$E$9,IF(H281,E10) Will be operating at 1 shift production at beginning of fol
J17=IF(E17>0,E17*$E$12,-E17*$E$13)
Page 1
David Violette Chase Sizzling Springs Company
March 15, 1998 BADM 635
Case Study Dr. Co
Page 2
David Violette Chase Sizzling Springs Company
March 15, 1998 BADM 635
Case Study Dr. Co
y for the year is estimated to be 2,000 containers
ry of 2,000 containers (Constraint in Solver)
a solution better than $77,400?
Total VLOOKUP TABLE
$ 3,600 Production Information
$ 3,600 ≥ Labor Cost
$ 3,600 0 0
$ 4,600 0.00 3000
$ 10,100 5.000001 4000
$ 8,600 7.000001 6000
$ 8,000 9.000001 8000
$ 8,500
$ 8,500
$ 6,500
$ 8,500
$ 4,500
$ 78,600
g inventory + production - demand
on at beginning of year
roduction at beginning of following year.
Page 3
David Violette Level Sizzling Springs Company
March 15, 1998 BADM 635
Case Study Dr. Co
Month Demand '000 Production Information
January 5 Level Constraints and Information:
February 4 1 shift 5 $ 3,000
March 5 1 shift + overtime 7 $ 4,000 Beginning inventory for the year is estimated to
April 7 2 shifts 9 $ 6,000 End of year inventory of 2,000 containers (Cons
May 10 2 shifts + overtime 12 $ 8,000
June 12
July 14 Changeover Cost Challege: Can you find a better solution?
August 13 1 shift to 2 shifts $1,500
September 11 2 shifts to 1 shift $1,000
October 8
November 10 Inventory Holding Cost $300 per 1000 held at end of month
December 7 Backlog cost $500 per 1000
Total 106
Beginning Ending No. of Level Inventory
Month Inventory Produce Demand Inventory Labor Shifts Change $ Cost
January 2 3.000 5 0 $ 3,000 1 $ - $ -
February 0 5.000 4 1 $ 3,000 1 $ - $ 300
March 1 5.000 5 1 $ 3,000 1 $ - $ 300
April 1 7.000 7 1 $ 4,000 1 $ - $ 300
May 1 12.000 10 3 $ 8,000 2 $ 1,500 $ 900
June 3 12.000 12 3 $ 8,000 2 $ - $ 900
July 3 12.000 14 1 $ 8,000 2 $ - $ 300
August 1 12.000 13 0 $ 8,000 2 $ - $ -
September 0 12.000 11 1 $ 8,000 2 $ - $ 300
October 1 8.000 8 1 $ 6,000 2 $ - $ 300
November 1 9.000 10 0 $ 6,000 2 $ - $ -
December 0 9.000 7 2 6000 2 $ 1,000 $ 600
Total 106 106 $ 71,000 $ 2,500 $ 4,200
E17=B17+C17-D17 Ending inventory = Beginning inventory + production - d
G17=VLOOKUP(C17,$M$19:$N$23,2) Use VLOOKUP function
H17=IF(G17>4000,2,1)
I17=IF(H17>1,1500,0) Operating at 1 shift production at beginning of year
I18=IF(H18>H17,1500,IF(H18H27,1500,IF(H281,1000) Will be operating at 1 shift production at beginning of fol
J17==IF(E17>0,E17*300,IF(E17$F$4,2,1)
I17=IF(H17=1,0,E9) Operating at 1 shift production at beginning of year
I18=IF(H18>H17,$E$9,IF(H18H27,$E$9,IF(H281,E10) Will be operating at 1 shift production at beginning of fol
J17=IF(E17>0,E17*$E$12,-E17*$E$13)
Page 6
David Violette Solver Sizzling Springs Company
March 15, 1998 BADM 635
Case Study Dr. Co
Page 7
David Violette Solver Sizzling Springs Company
March 15, 1998 BADM 635
Case Study Dr. Co
y for the year is estimated to be 2,000 containers
ry of 2,000 containers (Constraint in Solver)
a solution better than $77,400?
Total VLOOKUP TABLE
$ 8,925 Production Information
$ 8,880 ≥ Labor Cost
$ 10,040 0 0
$ 10,605 0.00 3000
$ 10,275 5.000001 4000
$ 9,350 7.000001 6000
$ 7,830 9.000001 8000
$ 6,615
$ 6,005
$ 6,300
$ 6,000
$ 7,600
$ 98,425
g inventory + production - demand
on at beginning of year
roduction at beginning of following year.
Page 8