{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Chapter 12","Aggregate Planning","Aggregate Planning (1)","Aggregate Planning (2)","Transportation Model","Master Scheduling","Examples","Solved Problems"],"not_rendered":{"charts":6,"images":5,"macros":1,"external_links":0,"defined_names":149,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Chapter 12","index":0,"n_rows":21,"n_cols":4,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[null,"Excel Templates to accompany Operations Management, Seventh Canadian Edition"]
[null,"Prepared by: Dr. Aries Sutantoputra, Southern Alberta Institute of Technology (SAIT)"]
[null,"Copyright © 2021 by McGraw Hill Ryerson  All rights reserved."]
[]
[null,"Chapter Twelve - Aggregate Operations Planning"]
[]
[null,null,"Templates:","Aggregate Planning (B)"]
[null,null,null,"Transportation Model (Basic)"]
[null,null,null,"Master Scheduling (B)"]
[]
[null,null,null,"(B) - includes Basic template"]
[]
[null,null,null,"Examples"]
[]
[null,null,null,"Solved Problems"]
[]
[]
[]
[null,"See the Aggregate Planning tutorial for a demonstration of the Aggregate Planning template."]
[]
[null,"See the Transportation Model tutorial for a demonstration of the Transportation Model template."]

{"sheet":"Aggregate Planning","index":1,"n_rows":74,"n_cols":17,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
["Aggregate Planning",null,null,"Basic"]
["<Back",null,null,null,"Number of periods:",6]
[]
["Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
["Forecast",null,null,null,2000,2000,3000,4000,5000,2000,null,null,null,null,null,null,{"$f":"SUM(E5:P5)","v":18000}]
["Output"]
[null,"Regular",null,null,2800,2800,2800,2800,2800,2800,null,null,null,null,null,null,{"$f":"SUM(E7:P7)","v":16800}]
[null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E8:P8)","v":0}]
[null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E9:P9)","v":0}]
[null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E10:P10)","v":0}]
["Output - Forecast",null,null,null,{"$f":"+SUM(E7:E10)-E5","v":800},{"$f":"+SUM(F7:F10)-F5","v":800},-200,-1200,-2200,800,0,0,0,0,0,0,{"$f":"SUM(E11:P11)","v":-1200}]
["Inventory"]
[null,"Beginning",null,null,1000,{"$f":"IF(F4>counter11,0,E14)","v":1800},2600,2400,1200,0,0,0,0,0,0,0]
[null,"Ending",null,null,{"$f":"IF(E4>counter11,0,IF(E13+E11>=0,E13+E11,0))","v":1800},{"$f":"IF(F4>counter11,0,IF(F13+F11-E16>=0,F13+F11-E16,0))","v":2600},2400,1200,0,0,0,0,0,0,0,0]
[null,"Average",null,null,{"$f":"IF(E4>counter11,0,(E13+E14)/2)","v":1400},2200,2500,1800,600,0,0,0,0,0,0,0,{"$f":"SUM(E15:P15)","v":8500}]
["Backlog",null,null,null,{"$f":"IF(E4>counter11,0,IF(E13+E11<0,-(E13+E11),0))","v":0},{"$f":"IF(F4>counter11,0,IF(F13+F11-E16<0,-(F13+F11-E16),0))","v":0},0,0,1000,200,0,0,0,0,0,0,{"$f":"SUM(E16:P16)","v":1200}]
["Costs:"]
[null,"Regular","@",100,{"$f":"+E7*$D18","v":280000},280000,280000,280000,280000,280000,0,0,0,0,0,0,{"$f":"SUM(E18:P18)","v":1680000}]
[null,"Part Time","@",100,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Overtime","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,"Inventory","@",10,{"$f":"+E15*$D23","v":14000},22000,25000,18000,6000,0,0,0,0,0,0,0,85000]
[null,"Back orders","@",150,0,0,0,0,150000,30000,0,0,0,0,0,0,180000]
["Total",null,null,null,{"$f":"SUM(E18:E24)","v":294000},302000,305000,298000,436000,310000,0,0,0,0,0,0,1945000]
[]
["See the Aggregate Planning tutorial for a demonstration of this template."]
[null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
["Cumulative Forecast ",null,null,null,{"$f":"E5","v":2000},{"$f":"E29+F5","v":4000},{"$f":"F29+G5","v":7000},11000,16000,18000,18000,18000,18000,18000,18000,18000]
["Cumulative Production",null,null,null,{"$f":"SUM(E7:E10)","v":2800},{"$f":"E30+SUM(F7:F10)","v":5600},{"$f":"F30+SUM(G7:G10)","v":8400},11200,14000,16800,16800,16800,16800,16800,16800,16800]
["Ending Inventory",null,null,null,{"$f":"IF(E30>E29,E30-E29,\"\")","v":800},{"$f":"IF(F30>F29,F30-F29,\"\")","v":1600},1400,200,"","","","","","","",""]
["Backlog",null,null,null,{"$f":"IF(E29>E30,E29-E30,\"\")","v":""},{"$f":"IF(F29>F30,F29-F30,\"\")","v":""},"","",2000,1200,1200,1200,1200,1200,1200,1200]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Basic Template:",null,"You can simply copy the basic template below and paste into another worksheet."]
["^Top",null,"After pasting into another worksheet you should delete unneeded columns."]
[]
[]
["Aggregate Planning",null,null," "]
[]
[]
["Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
["Forecast",null,null,null,200,200,300,400,500,200,null,null,null,null,null,null,{"$f":"SUM(E54:P54)","v":1800}]
["Output"]
[null,"Regular",null,null,300,300,300,300,300,300,null,null,null,null,null,null,{"$f":"SUM(E56:P56)","v":1800}]
[null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E57:P57)","v":0}]
[null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E58:P58)","v":0}]
[null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E59:P59)","v":0}]
["Output - Forecast",null,null,null,{"$f":"+SUM(E56:E59)-E54","v":100},{"$f":"+SUM(F56:F59)-F54","v":100},{"$f":"+SUM(G56:G59)-G54","v":0},-100,-200,100,0,0,0,0,0,0,{"$f":"SUM(E60:P60)","v":0}]
["Inventory"]
[null,"Beginning",null,null,null,{"$f":"E63","v":100},{"$f":"F63","v":200},200,100,0,0,0,0,0,0,0]
[null,"Ending",null,null,{"$f":"IF(E62+E60>=0,E62+E60,0)","v":100},{"$f":"IF(F62+F60-E65>=0,F62+F60-E65,0)","v":200},{"$f":"IF(G62+G60-F65>=0,G62+G60-F65,0)","v":200},100,0,0,0,0,0,0,0,0]
[null,"Average",null,null,{"$f":"(E62+E63)/2","v":50},{"$f":"(F62+F63)/2","v":150},{"$f":"(G62+G63)/2","v":200},150,50,0,0,0,0,0,0,0,{"$f":"SUM(E64:P64)","v":600}]
["Backlog",null,null,null,{"$f":"IF(E62+E60<0,-(E62+E60),0)","v":0},{"$f":"IF(F62+F60-E65<0,-(F62+F60-E65),0)","v":0},{"$f":"IF(G62+G60-F65<0,-(G62+G60-F65),0)","v":0},0,100,0,0,0,0,0,0,0,{"$f":"SUM(E65:P65)","v":100}]
["Costs:"]
[null,"Regular","@",2,{"$f":"+E56*$D67","v":600},600,600,600,600,600,0,0,0,0,0,0,{"$f":"SUM(E67:P67)","v":3600}]
[null,"Part Time","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Overtime","@",3,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Subcontract","@",6,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,"Inventory","@",1,{"$f":"+E64*$D72","v":50},150,200,150,50,0,0,0,0,0,0,0,600]
[null,"Back orders","@",5,0,0,0,0,500,0,0,0,0,0,0,0,500]
["Total",null,null,null,{"$f":"SUM(E67:E73)","v":650},750,800,750,1150,600,0,0,0,0,0,0,4700]

{"sheet":"Aggregate Planning (1)","index":2,"n_rows":74,"n_cols":17,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
["Aggregate Planning",null,null,"Basic"]
["<Back",null,null,null,"Number of periods:",6]
[]
["Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
["Forecast",null,null,null,2000,2000,3000,4000,5000,2000,null,null,null,null,null,null,{"$f":"SUM(E5:P5)","v":18000}]
["Output"]
[null,"Regular",null,null,2800,2800,2800,2800,2800,2800,null,null,null,null,null,null,{"$f":"SUM(E7:P7)","v":16800}]
[null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E8:P8)","v":0}]
[null,"Overtime",null,null,null,null,400,400,400,null,null,null,null,null,null,null,{"$f":"SUM(E9:P9)","v":1200}]
[null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E10:P10)","v":0}]
["Output - Forecast",null,null,null,{"$f":"+SUM(E7:E10)-E5","v":800},{"$f":"+SUM(F7:F10)-F5","v":800},200,-800,-1800,800,0,0,0,0,0,0,{"$f":"SUM(E11:P11)","v":0}]
["Inventory"]
[null,"Beginning",null,null,1000,{"$f":"IF(F4>counter11,0,E14)","v":1800},2600,2800,2000,200,0,0,0,0,0,0]
[null,"Ending",null,null,{"$f":"IF(E4>counter11,0,IF(E13+E11>=0,E13+E11,0))","v":1800},{"$f":"IF(F4>counter11,0,IF(F13+F11-E16>=0,F13+F11-E16,0))","v":2600},2800,2000,200,1000,0,0,0,0,0,0]
[null,"Average",null,null,{"$f":"IF(E4>counter11,0,(E13+E14)/2)","v":1400},2200,2700,2400,1100,600,0,0,0,0,0,0,{"$f":"SUM(E15:P15)","v":10400}]
["Backlog",null,null,null,{"$f":"IF(E4>counter11,0,IF(E13+E11<0,-(E13+E11),0))","v":0},{"$f":"IF(F4>counter11,0,IF(F13+F11-E16<0,-(F13+F11-E16),0))","v":0},0,0,0,0,0,0,0,0,0,0,{"$f":"SUM(E16:P16)","v":0}]
["Costs:"]
[null,"Regular","@",100,{"$f":"+E7*$D18","v":280000},280000,280000,280000,280000,280000,0,0,0,0,0,0,{"$f":"SUM(E18:P18)","v":1680000}]
[null,"Part Time","@",100,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Overtime","@",150,0,0,60000,60000,60000,0,0,0,0,0,0,0,180000]
[null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,"Inventory","@",10,{"$f":"+E15*$D23","v":14000},22000,27000,24000,11000,6000,0,0,0,0,0,0,104000]
[null,"Back orders","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
["Total",null,null,null,{"$f":"SUM(E18:E24)","v":294000},302000,367000,364000,351000,286000,0,0,0,0,0,0,1964000]
[]
["See the Aggregate Planning tutorial for a demonstration of this template."]
[null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
["Cumulative Forecast ",null,null,null,{"$f":"E5","v":2000},{"$f":"E29+F5","v":4000},{"$f":"F29+G5","v":7000},11000,16000,18000,18000,18000,18000,18000,18000,18000]
["Cumulative Production",null,null,null,{"$f":"SUM(E7:E10)","v":2800},{"$f":"E30+SUM(F7:F10)","v":5600},{"$f":"F30+SUM(G7:G10)","v":8800},12000,15200,18000,18000,18000,18000,18000,18000,18000]
["Ending Inventory",null,null,null,{"$f":"IF(E30>E29,E30-E29,\"\")","v":800},{"$f":"IF(F30>F29,F30-F29,\"\")","v":1600},1800,1000,"","","","","","","",""]
["Backlog",null,null,null,{"$f":"IF(E29>E30,E29-E30,\"\")","v":""},{"$f":"IF(F29>F30,F29-F30,\"\")","v":""},"","",800,"","","","","","",""]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Basic Template:",null,"You can simply copy the basic template below and paste into another worksheet."]
["^Top",null,"After pasting into another worksheet you should delete unneeded columns."]
[]
[]
["Aggregate Planning",null,null," "]
[]
[]
["Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
["Forecast",null,null,null,200,200,300,400,500,200,null,null,null,null,null,null,{"$f":"SUM(E54:P54)","v":1800}]
["Output"]
[null,"Regular",null,null,300,300,300,300,300,300,null,null,null,null,null,null,{"$f":"SUM(E56:P56)","v":1800}]
[null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E57:P57)","v":0}]
[null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E58:P58)","v":0}]
[null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E59:P59)","v":0}]
["Output - Forecast",null,null,null,{"$f":"+SUM(E56:E59)-E54","v":100},{"$f":"+SUM(F56:F59)-F54","v":100},{"$f":"+SUM(G56:G59)-G54","v":0},-100,-200,100,0,0,0,0,0,0,{"$f":"SUM(E60:P60)","v":0}]
["Inventory"]
[null,"Beginning",null,null,null,{"$f":"E63","v":100},{"$f":"F63","v":200},200,100,0,0,0,0,0,0,0]
[null,"Ending",null,null,{"$f":"IF(E62+E60>=0,E62+E60,0)","v":100},{"$f":"IF(F62+F60-E65>=0,F62+F60-E65,0)","v":200},{"$f":"IF(G62+G60-F65>=0,G62+G60-F65,0)","v":200},100,0,0,0,0,0,0,0,0]
[null,"Average",null,null,{"$f":"(E62+E63)/2","v":50},{"$f":"(F62+F63)/2","v":150},{"$f":"(G62+G63)/2","v":200},150,50,0,0,0,0,0,0,0,{"$f":"SUM(E64:P64)","v":600}]
["Backlog",null,null,null,{"$f":"IF(E62+E60<0,-(E62+E60),0)","v":0},{"$f":"IF(F62+F60-E65<0,-(F62+F60-E65),0)","v":0},{"$f":"IF(G62+G60-F65<0,-(G62+G60-F65),0)","v":0},0,100,0,0,0,0,0,0,0,{"$f":"SUM(E65:P65)","v":100}]
["Costs:"]
[null,"Regular","@",2,{"$f":"+E56*$D67","v":600},600,600,600,600,600,0,0,0,0,0,0,{"$f":"SUM(E67:P67)","v":3600}]
[null,"Part Time","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Overtime","@",3,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Subcontract","@",6,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,"Inventory","@",1,{"$f":"+E64*$D72","v":50},150,200,150,50,0,0,0,0,0,0,0,600]
[null,"Back orders","@",5,0,0,0,0,500,0,0,0,0,0,0,0,500]
["Total",null,null,null,{"$f":"SUM(E67:E73)","v":650},750,800,750,1150,600,0,0,0,0,0,0,4700]

{"sheet":"Aggregate Planning (2)","index":3,"n_rows":74,"n_cols":17,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
["Aggregate Planning",null,null,"Basic"]
["<Back",null,null,null,"Number of periods:",6]
[]
["Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
["Forecast",null,null,null,2000,2000,3000,4000,5000,2000,null,null,null,null,null,null,{"$f":"SUM(E5:P5)","v":18000}]
["Output"]
[null,"Regular",null,null,2800,2800,2800,2800,2800,2800,null,null,null,null,null,null,{"$f":"SUM(E7:P7)","v":16800}]
[null,"Part Time",null,null,null,null,null,600,600,null,null,null,null,null,null,null,{"$f":"SUM(E8:P8)","v":1200}]
[null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E9:P9)","v":0}]
[null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E10:P10)","v":0}]
["Output - Forecast",null,null,null,{"$f":"+SUM(E7:E10)-E5","v":800},{"$f":"+SUM(F7:F10)-F5","v":800},-200,-600,-1600,800,0,0,0,0,0,0,{"$f":"SUM(E11:P11)","v":0}]
["Inventory"]
[null,"Beginning",null,null,1000,{"$f":"IF(F4>counter11,0,E14)","v":1800},2600,2400,1800,200,0,0,0,0,0,0]
[null,"Ending",null,null,{"$f":"IF(E4>counter11,0,IF(E13+E11>=0,E13+E11,0))","v":1800},{"$f":"IF(F4>counter11,0,IF(F13+F11-E16>=0,F13+F11-E16,0))","v":2600},2400,1800,200,1000,0,0,0,0,0,0]
[null,"Average",null,null,{"$f":"IF(E4>counter11,0,(E13+E14)/2)","v":1400},2200,2500,2100,1000,600,0,0,0,0,0,0,{"$f":"SUM(E15:P15)","v":9800}]
["Backlog",null,null,null,{"$f":"IF(E4>counter11,0,IF(E13+E11<0,-(E13+E11),0))","v":0},{"$f":"IF(F4>counter11,0,IF(F13+F11-E16<0,-(F13+F11-E16),0))","v":0},0,0,0,0,0,0,0,0,0,0,{"$f":"SUM(E16:P16)","v":0}]
["Costs:"]
[null,"Regular","@",100,{"$f":"+E7*$D18","v":280000},280000,280000,280000,280000,280000,0,0,0,0,0,0,{"$f":"SUM(E18:P18)","v":1680000}]
[null,"Part Time","@",100,0,0,0,60000,60000,0,0,0,0,0,0,0,120000]
[null,"Overtime","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Hire/Layoff  ",null,null,null,null,null,15000,null,null,null,null,null,null,null,null,15000]
[null,"Inventory","@",10,{"$f":"+E15*$D23","v":14000},22000,25000,21000,10000,6000,0,0,0,0,0,0,98000]
[null,"Back orders","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
["Total",null,null,null,{"$f":"SUM(E18:E24)","v":294000},302000,305000,376000,350000,286000,0,0,0,0,0,0,1913000]
[]
["See the Aggregate Planning tutorial for a demonstration of this template."]
[null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
["Cumulative Forecast ",null,null,null,{"$f":"E5","v":2000},{"$f":"E29+F5","v":4000},{"$f":"F29+G5","v":7000},11000,16000,18000,18000,18000,18000,18000,18000,18000]
["Cumulative Production",null,null,null,{"$f":"SUM(E7:E10)","v":2800},{"$f":"E30+SUM(F7:F10)","v":5600},{"$f":"F30+SUM(G7:G10)","v":8400},11800,15200,18000,18000,18000,18000,18000,18000,18000]
["Ending Inventory",null,null,null,{"$f":"IF(E30>E29,E30-E29,\"\")","v":800},{"$f":"IF(F30>F29,F30-F29,\"\")","v":1600},1400,800,"","","","","","","",""]
["Backlog",null,null,null,{"$f":"IF(E29>E30,E29-E30,\"\")","v":""},{"$f":"IF(F29>F30,F29-F30,\"\")","v":""},"","",800,"","","","","","",""]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Basic Template:",null,"You can simply copy the basic template below and paste into another worksheet."]
["^Top",null,"After pasting into another worksheet you should delete unneeded columns."]
[]
[]
["Aggregate Planning",null,null," "]
[]
[]
["Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
["Forecast",null,null,null,200,200,300,400,500,200,null,null,null,null,null,null,{"$f":"SUM(E54:P54)","v":1800}]
["Output"]
[null,"Regular",null,null,300,300,300,300,300,300,null,null,null,null,null,null,{"$f":"SUM(E56:P56)","v":1800}]
[null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E57:P57)","v":0}]
[null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E58:P58)","v":0}]
[null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E59:P59)","v":0}]
["Output - Forecast",null,null,null,{"$f":"+SUM(E56:E59)-E54","v":100},{"$f":"+SUM(F56:F59)-F54","v":100},{"$f":"+SUM(G56:G59)-G54","v":0},-100,-200,100,0,0,0,0,0,0,{"$f":"SUM(E60:P60)","v":0}]
["Inventory"]
[null,"Beginning",null,null,null,{"$f":"E63","v":100},{"$f":"F63","v":200},200,100,0,0,0,0,0,0,0]
[null,"Ending",null,null,{"$f":"IF(E62+E60>=0,E62+E60,0)","v":100},{"$f":"IF(F62+F60-E65>=0,F62+F60-E65,0)","v":200},{"$f":"IF(G62+G60-F65>=0,G62+G60-F65,0)","v":200},100,0,0,0,0,0,0,0,0]
[null,"Average",null,null,{"$f":"(E62+E63)/2","v":50},{"$f":"(F62+F63)/2","v":150},{"$f":"(G62+G63)/2","v":200},150,50,0,0,0,0,0,0,0,{"$f":"SUM(E64:P64)","v":600}]
["Backlog",null,null,null,{"$f":"IF(E62+E60<0,-(E62+E60),0)","v":0},{"$f":"IF(F62+F60-E65<0,-(F62+F60-E65),0)","v":0},{"$f":"IF(G62+G60-F65<0,-(G62+G60-F65),0)","v":0},0,100,0,0,0,0,0,0,0,{"$f":"SUM(E65:P65)","v":100}]
["Costs:"]
[null,"Regular","@",2,{"$f":"+E56*$D67","v":600},600,600,600,600,600,0,0,0,0,0,0,{"$f":"SUM(E67:P67)","v":3600}]
[null,"Part Time","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Overtime","@",3,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Subcontract","@",6,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,"Inventory","@",1,{"$f":"+E64*$D72","v":50},150,200,150,50,0,0,0,0,0,0,0,600]
[null,"Back orders","@",5,0,0,0,0,500,0,0,0,0,0,0,0,500]
["Total",null,null,null,{"$f":"SUM(E67:E73)","v":650},750,800,750,1150,600,0,0,0,0,0,0,4700]

{"sheet":"Transportation Model","index":4,"n_rows":92,"n_cols":13,"rows_kept":92,"cut":false,"hidden":false,"merged":[[3,10,21,10],[4,1,6,1],[7,1,9,1],[10,1,12,1],[13,1,15,1],[16,1,18,1],[19,1,21,1],[26,10,44,10],[27,1,29,1],[30,1,32,1],[33,1,35,1],[36,1,38,1],[39,1,41,1],[42,1,44,1]]}
["Transportation Model (Basic)",null,null,"Notes","Use Solver on Data Ribbon to solve - see notes below."]
["<Back",null,null,null,"Do not change or delete unshaded cells."]
["Input Matrix:",null,"Demand for: ","Period 1","Period 2","Period 3","Period 4","Period 5","Period 6"]
[null,null,"Beginning Inventory",0,1,2,null,null,null,100,"Supply Available"]
[null,{"$f":"D3","v":"Period 1"},"Regular Time",60,61,62,null,null,null,500]
[null,null,"Overtime",80,81,82,null,null,null,50]
[null,null,"Subcontract",90,91,92,null,null,null,120]
[null,{"$f":"E3","v":"Period 2"},"Regular Time",63,60,61,null,null,null,500]
[null,null,"Overtime",83,80,81,null,null,null,50]
[null,null,"Subcontract",93,90,91,null,null,null,120]
[null,{"$f":"F3","v":"Period 3"},"Regular Time",66,63,60,null,null,null,500]
[null,null,"Overtime",86,83,80,null,null,null,50]
[null,null,"Subcontract",96,93,90,null,null,null,100]
[null,{"$f":"G3","v":"Period 4"},"Regular Time"]
[null,null,"Overtime"]
[null,null,"Subcontract"]
[null,{"$f":"H3","v":"Period 5"},"Regular Time"]
[null,null,"Overtime"]
[null,null,"Subcontract"]
[null,{"$f":"I3","v":"Period 6"},"Regular Time"]
[null,null,"Overtime"]
[null,null,"Subcontract"]
[null,null,null,550,700,750]
[null,null,null,"Demand Required",null,null,null,null,null,null,null,"Total Supply = ",{"$f":"SUM(J4:J22)","v":2090}]
["Solution Matrix:",null,null,null,null,null,null,null,null,null,null,"Total Demand = ",{"$f":"SUM(D23:I23)","v":2000}]
[null,null,"Demand for: ",{"$f":"D3","v":"Period 1"},"Period 2","Period 3","Period 4","Period 5","Period 6",0]
[null,null,"Beginning Inventory",100,0,0,0,0,0,{"$f":"SUM(D27:I27)","v":100},"Supply Used"]
[null,{"$f":"B5","v":"Period 1"},"Regular Time",400,0,100,0,0,0,500]
[null,null,"Overtime",50,0,0,0,0,0,50]
[null,null,"Subcontract",0,30,0,0,0,0,30]
[null,{"$f":"B8","v":"Period 2"},"Regular Time",0,500,0,0,0,0,500]
[null,null,"Overtime",0,50,0,0,0,0,50]
[null,null,"Subcontract",0,120,0,0,0,0,120]
[null,{"$f":"B11","v":"Period 3"},"Regular Time",0,0,500,0,0,0,500]
[null,null,"Overtime",0,0,50,0,0,0,50]
[null,null,"Subcontract",0,0,100,0,0,0,100]
[null,{"$f":"B14","v":"Period 4"},"Regular Time",0,0,0,0,0,0,0]
[null,null,"Overtime",0,0,0,0,0,0,0]
[null,null,"Subcontract",0,0,0,0,0,0,0]
[null,{"$f":"B17","v":"Period 5"},"Regular Time",0,0,0,0,0,0,0]
[null,null,"Overtime",0,0,0,0,0,0,0]
[null,null,"Subcontract",0,0,0,0,0,0,0]
[null,{"$f":"B20","v":"Period 6"},"Regular Time",0,0,0,0,0,0,0]
[null,null,"Overtime",0,0,0,0,0,0,0]
[null,null,"Subcontract",0,0,0,0,0,0,0]
[null,null,null,{"$f":"SUM(D27:D45)","v":550},700,750,0,0,0]
[null,null,null,"Demand Met",null,null,null,null,null,null,null,"Total Cost = ",{"$f":"SUMPRODUCT(D4:I22,D27:I45)","v":124730}]
[]
[]
[]
["Notes:",null,"See the Transportation tutorial for a demonstration of a similar template."]
[null,null,"Unneeded rows and columns have been hidden in the above template."]
[]
[null,"1.","Enter the problem into the Input Matrix (top shaded area):"]
[null,null,null,"Enter the names of periods in the top row (optional)."]
[null,null,null,"Enter the total of production and carrying costs in row produced and column used."]
[null,null,null,"Enter the supply available from each production source in in the right column."]
[null,null,null,"Enter the demand required in each period in the bottom row."]
[null,null,null,"Unused rows or columns should contain only blanks and you may hide unused rows or columns."]
[null,null,null,"Unbalanced problems with supply exceeding demand can be solved without using a Dummy column."]
[null,null,null,"Unbalanced problems with demand exceeding supply are not feasible and will require a Dummy row."]
[]
[null,"2.","You can manually create solutions in the Solution Matrix (bottom shaded area):"]
[null,null,null,"Enter amounts in the row produced and column used in the middle of the Solution Matrix (shaded yellow)."]
[null,null,null,"Compare the supply used with supply available and the demand met with demand required to determine feasibility."]
[null,null,null,"Observe the total cost to determine the optimal solution."]
[null,null,null,"You can use heuristics (e.g. stepping-stone method) to improve the solution. "]
[null,null,"Warning: you should not delete or make any changes in the un-shaded portions of the template."]
[]
[null,"3.","Using Solver to find the optimal solution:"]
[null,null,null,"Select the Data ribbon, the Solver Add-in must be available in the Analysis group (see note below to add-in Solver)."]
[null,null,null,"Press Solver in the Analysis group of the Data ribbon."]
[null,null,null,"Press Solve in Solver Parameters dialog box (the parameters have all been set)."]
[null,null,null,"Read the message at the top of the Solver Results dialog box and then press OK."]
[null,null,null,"The template is set up to minimize cost, to maximize you can select Max in the Solver Parameters dialog box."]
[null,null,"Warning: any changes in the Solver Parameters dialog box will remain in effect and affect future solutions."]
[]
["More...","4.","Notes on the Solver solution:"]
[null,null,null,"Small numbers in scientific notation (e.g. 2.4091E-11) reflect the precision of Solver and can be treated as zero. "]
[null,null,null,"If total demand exceeds total supply then problem is not feasible and you will get an error message."]
[null,null,null,"Solver occasionally gives a \"false\" not feasible result (due to precision/tolerance settings), you might re-run Solver."]
[null,null,null,"If a problem has alternate optimal solutions, re-solving with Solver may (or may not) give a different solution."]
[null,null,null,"Existence of alternate optimal solutions may be indicated by the Sensitivity Report (an option in the Solver Results"]
[null,null,null,null,"dialog box) by a solution cell that has both a Final Value and Reduced Cost of zero."]
[]
[null,"5.","How to Add-In Solver if it does not appear in the Analysis group of the Data ribbon:"]
[null,null,null,"Press the Office Button (extreme upper left-hand corner of screen)."]
[null,null,null,"Press Excel Options (bottom of dialog box)."]
[null,null,null,"Select Add-Ins (left hand side of dialog box)."]
[null,null,null,"Press Go... beside Manage: Excel Add-Ins (bottom of dialog box)."]
[null,null,null,"Select Solver Add-In (checkmark will appear) in list of Add-ins available, and press OK."]
["^Top"]

{"sheet":"Master Scheduling","index":5,"n_rows":50,"n_cols":13,"rows_kept":50,"cut":false,"hidden":false,"merged":[]}
["Master Scheduling",null,null,"Basic"]
["<Back"]
[]
[null,"Week",null,null,0,1,2,3,4,5,6,7,8]
[null,"Forecast",null,null,null,70,70,70,70]
[null,"Customer Orders"]
[null,"(committed)",null,null,null,80,50,30,10]
[null,"Projected on-hand"]
[null,"inventory",null,null,null,{"$f":"+E9-MAX(F5:F7)+F10","v":20},50,80,10,10,10,10,10]
[null,"MPS",null,null,null,100,100,100]
[null,"ATP",null,null,null,{"$f":"E9+F10-F7-IF(G10>0,0,G7+IF(H10>0,0,H7+IF(I10>0,0,I7+IF(J10>0,0,J7+IF(K10>0,0,K7+IF(L10>0,0,L7+IF(M10>0,0,M7)))))))","v":20},{"$f":"IF(G10<=0,\"\",G10-G7-IF(H10>0,0,H7+IF(I10>0,0,I7+IF(J10>0,0,J7+IF(K10>0,0,K7+IF(L10>0,0,L7+IF(M10>0,0,M7)))))))","v":50},{"$f":"IF(H10<=0,\"\",H10-H7-IF(I10>0,0,I7+IF(J10>0,0,J7+IF(K10>0,0,K7+IF(L10>0,0,L7+IF(M10>0,0,M7))))))","v":60},{"$f":"IF(I10<=0,\"\",I10-I7-IF(J10>0,0,J7+IF(K10>0,0,K7+IF(L10>0,0,L7+IF(M10>0,0,M7)))))","v":""},{"$f":"IF(J10<=0,\"\",J10-J7-IF(K10>0,0,K7+IF(L10>0,0,L7+IF(M10>0,0,M7))))","v":""},{"$f":"IF(K10<=0,\"\",K10-K7-IF(L10>0,0,L7+IF(M10>0,0,M7)))","v":""},{"$f":"IF(L10<=0,\"\",L10-L7-IF(M10>0,0,M7))","v":""},{"$f":"IF(M10<=0,\"\",M10-M7)","v":""}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Basic Template:",null,"You can simply copy the basic template below and paste into another worksheet."]
["^Top"]
[]
[]
["Master Scheduling"]
[]
[]
[null,"Week",null,null,0,1,2,3,4,5,6,7,8]
[null,"Forecast",null,null,null,70,70,70,70]
[null,"Customer Orders"]
[null,"(committed)",null,null,null,80,50,30,10]
[null,"Projected on-hand"]
[null,"inventory",null,null,null,{"$f":"+E48-MAX(F44:F46)+F49","v":20},50,80,10,10,10,10,10]
[null,"MPS",null,null,null,100,100,100]
[null,"ATP",null,null,null,{"$f":"E48+F49-F46-IF(G49>0,0,G46+IF(H49>0,0,H46+IF(I49>0,0,I46+IF(J49>0,0,J46+IF(K49>0,0,K46+IF(L49>0,0,L46+IF(M49>0,0,M46)))))))","v":20},{"$f":"IF(G49<=0,\"\",G49-G46-IF(H49>0,0,H46+IF(I49>0,0,I46+IF(J49>0,0,J46+IF(K49>0,0,K46+IF(L49>0,0,L46+IF(M49>0,0,M46)))))))","v":50},{"$f":"IF(H49<=0,\"\",H49-H46-IF(I49>0,0,I46+IF(J49>0,0,J46+IF(K49>0,0,K46+IF(L49>0,0,L46+IF(M49>0,0,M46))))))","v":60},{"$f":"IF(I49<=0,\"\",I49-I46-IF(J49>0,0,J46+IF(K49>0,0,K46+IF(L49>0,0,L46+IF(M49>0,0,M46)))))","v":""},{"$f":"IF(J49<=0,\"\",J49-J46-IF(K49>0,0,K46+IF(L49>0,0,L46+IF(M49>0,0,M46))))","v":""},{"$f":"IF(K49<=0,\"\",K49-K46-IF(L49>0,0,L46+IF(M49>0,0,M46)))","v":""},{"$f":"IF(L49<=0,\"\",L49-L46-IF(M49>0,0,M46))","v":""},{"$f":"IF(M49<=0,\"\",M49-M46)","v":""}]

{"sheet":"Examples","index":6,"n_rows":153,"n_cols":19,"rows_kept":153,"cut":false,"hidden":false,"merged":[[132,11,150,11],[109,11,127,11],[110,2,112,2],[113,2,115,2],[116,2,118,2],[119,2,121,2],[122,2,124,2],[125,2,127,2],[133,2,135,2],[136,2,138,2],[139,2,141,2],[142,2,144,2],[145,2,147,2],[148,2,150,2]]}
["Chapter 12 - Examples",null,null,null,"Note: This worksheet displays results only, you must copy the shaded"]
["<Back",null,null,null,"area into the corresponding template to make additional calculations."]
[]
[null,null,"Aggregate Planning"]
[null,null,null,null,null,null,"Number of periods:",6]
[]
["12-1.",null,"Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
[null,null,"Forecast",null,null,null,2000,2000,3000,4000,5000,2000,null,null,null,null,null,null,18000]
[null,null,"Output"]
[null,null,null,"Regular",null,null,2800,2800,2800,2800,2800,2800,null,null,null,null,null,null,16800]
[null,null,null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Output - Forecast",null,null,null,800,800,-200,-1200,-2200,800,0,0,0,0,0,0,-1200]
[null,null,"Inventory"]
[null,null,null,"Beginning",null,null,1000,1800,2600,2400,1200,0,0,0,0,0,0,0]
[null,null,null,"Ending",null,null,1800,2600,2400,1200,0,0,0,0,0,0,0,0]
[null,null,null,"Average",null,null,1400,2200,2500,1800,600,0,0,0,0,0,0,0,8500]
[null,null,"Backlog",null,null,null,0,0,0,0,1000,200,0,0,0,0,0,0,1200]
[null,null,"Costs:"]
[null,null,null,"Regular","@",100,280000,280000,280000,280000,280000,280000,0,0,0,0,0,0,1680000]
[null,null,null,"Part Time","@",100,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Overtime","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,"Inventory","@",10,14000,22000,25000,18000,6000,0,0,0,0,0,0,0,85000]
[null,null,null,"Back orders","@",150,0,0,0,0,150000,30000,0,0,0,0,0,0,180000]
[null,null,"Total",null,null,null,294000,302000,305000,298000,436000,310000,0,0,0,0,0,0,1945000]
[]
[null,null,"See the Aggregate Planning tutorial for a demonstration of this template."]
[null,null,null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,"Cumulative Forecast ",null,null,null,2000,4000,7000,11000,16000,18000,18000,18000,18000,18000,18000,18000]
[null,null,"Cumulative Production",null,null,null,2800,5600,8400,11200,14000,16800,16800,16800,16800,16800,16800,16800]
[null,null,"Ending Inventory",null,null,null,800,1600,1400,200,"","","","","","","",""]
[null,null,"Backlog",null,null,null,"","","","",2000,1200,1200,1200,1200,1200,1200,1200]
[]
[]
["12-2.",null,"Aggregate Planning",null,null," "]
[null,null,null,null,null,null,"Number of periods:",6]
[]
[null,null,"Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
[null,null,"Forecast",null,null,null,2000,2000,3000,4000,5000,2000,null,null,null,null,null,null,18000]
[null,null,"Output"]
[null,null,null,"Regular",null,null,2800,2800,2800,2800,2800,2800,null,null,null,null,null,null,16800]
[null,null,null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,"Overtime",null,null,null,null,400,400,400,null,null,null,null,null,null,null,1200]
[null,null,null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Output - Forecast",null,null,null,800,800,200,-800,-1800,800,0,0,0,0,0,0,0]
[null,null,"Inventory"]
[null,null,null,"Beginning",null,null,1000,1800,2600,2800,2000,200,0,0,0,0,0,0]
[null,null,null,"Ending",null,null,1800,2600,2800,2000,200,1000,0,0,0,0,0,0]
[null,null,null,"Average",null,null,1400,2200,2700,2400,1100,600,0,0,0,0,0,0,10400]
[null,null,"Backlog",null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Costs:"]
[null,null,null,"Regular","@",100,280000,280000,280000,280000,280000,280000,0,0,0,0,0,0,1680000]
[null,null,null,"Part Time","@",100,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Overtime","@",150,0,0,60000,60000,60000,0,0,0,0,0,0,0,180000]
[null,null,null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,"Inventory","@",10,14000,22000,27000,24000,11000,6000,0,0,0,0,0,0,104000]
[null,null,null,"Back orders","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Total",null,null,null,294000,302000,367000,364000,351000,286000,0,0,0,0,0,0,1964000]
[]
[null,null,"See the Aggregate Planning tutorial for a demonstration of this template."]
[null,null,null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,"Cumulative Forecast ",null,null,null,2000,4000,7000,11000,16000,18000,18000,18000,18000,18000,18000,18000]
[null,null,"Cumulative Production",null,null,null,2800,5600,8800,12000,15200,18000,18000,18000,18000,18000,18000,18000]
[null,null,"Ending Inventory",null,null,null,800,1600,1800,1000,"","","","","","","",""]
[null,null,"Backlog",null,null,null,"","","","",800,"","","","","","",""]
[]
[]
["12-3.",null,"Aggregate Planning",null,null," "]
[null,null,null,null,null,null,"Number of periods:",6]
[]
[null,null,"Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,"Total"]
[null,null,"Forecast",null,null,null,2000,2000,3000,4000,5000,2000,null,null,null,null,null,null,18000]
[null,null,"Output"]
[null,null,null,"Regular",null,null,2800,2800,2800,2800,2800,2800,null,null,null,null,null,null,16800]
[null,null,null,"Part Time",null,null,null,null,null,600,600,null,null,null,null,null,null,null,1200]
[null,null,null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Output - Forecast",null,null,null,800,800,-200,-600,-1600,800,0,0,0,0,0,0,0]
[null,null,"Inventory"]
[null,null,null,"Beginning",null,null,1000,1800,2600,2400,1800,200,0,0,0,0,0,0]
[null,null,null,"Ending",null,null,1800,2600,2400,1800,200,1000,0,0,0,0,0,0]
[null,null,null,"Average",null,null,1400,2200,2500,2100,1000,600,0,0,0,0,0,0,9800]
[null,null,"Backlog",null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Costs:"]
[null,null,null,"Regular","@",100,280000,280000,280000,280000,280000,280000,0,0,0,0,0,0,1680000]
[null,null,null,"Part Time","@",100,0,0,0,60000,60000,0,0,0,0,0,0,0,120000]
[null,null,null,"Overtime","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Hire/Layoff  ",null,null,null,null,null,15000,null,null,null,null,null,null,null,null,15000]
[null,null,null,"Inventory","@",10,14000,22000,25000,21000,10000,6000,0,0,0,0,0,0,98000]
[null,null,null,"Back orders","@",150,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,"Total",null,null,null,294000,302000,305000,376000,350000,286000,0,0,0,0,0,0,1913000]
[]
[null,null,"See the Aggregate Planning tutorial for a demonstration of this template."]
[null,null,null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,"Cumulative Forecast ",null,null,null,2000,4000,7000,11000,16000,18000,18000,18000,18000,18000,18000,18000]
[null,null,"Cumulative Production",null,null,null,2800,5600,8400,11800,15200,18000,18000,18000,18000,18000,18000,18000]
[null,null,"Ending Inventory",null,null,null,800,1600,1400,800,"","","","","","","",""]
[null,null,"Backlog",null,null,null,"","","","",800,"","","","","","",""]
[]
[]
[]
["12-5.","Transportation Model"]
[]
[null,"Input Matrix:",null,"Demand for: ","Period 1","Period 2","Period 3","Period 4","Period 5","Period 6"]
[null,null,null,"Beginning Inventory",0,1,2,null,null,null,100,"Supply Available"]
[null,null,"Period 1","Regular Time",60,61,62,null,null,null,500]
[null,null,null,"Overtime",80,81,82,null,null,null,50]
[null,null,null,"Subcontract",90,91,92,null,null,null,120]
[null,null,"Period 2","Regular Time",63,60,61,null,null,null,500]
[null,null,null,"Overtime",83,80,81,null,null,null,50]
[null,null,null,"Subcontract",93,90,91,null,null,null,120]
[null,null,"Period 3","Regular Time",66,63,60,null,null,null,500]
[null,null,null,"Overtime",86,83,80,null,null,null,50]
[null,null,null,"Subcontract",96,93,90,null,null,null,100]
[null,null,"Period 4","Regular Time"]
[null,null,null,"Overtime"]
[null,null,null,"Subcontract"]
[null,null,"Period 5","Regular Time"]
[null,null,null,"Overtime"]
[null,null,null,"Subcontract"]
[null,null,"Period 6","Regular Time"]
[null,null,null,"Overtime"]
[null,null,null,"Subcontract"]
[null,null,null,null,550,700,750]
[null,null,null,null,"Demand Required",null,null,null,null,null,null,null,"Total Supply = ",2090]
[null,"Solution Matrix:",null,null,null,null,null,null,null,null,null,null,"Total Demand = ",2000]
[null,null,null,"Demand for: ","Period 1","Period 2","Period 3","Period 4","Period 5","Period 6",0]
[null,null,null,"Beginning Inventory",100,0,0,0,0,0,100,"Supply Used"]
[null,null,"Period 1","Regular Time",400,0,100,0,0,0,500]
[null,null,null,"Overtime",50,0,0,0,0,0,50]
[null,null,null,"Subcontract",0,30,0,0,0,0,30]
[null,null,"Period 2","Regular Time",0,500,0,0,0,0,500]
[null,null,null,"Overtime",0,50,0,0,0,0,50]
[null,null,null,"Subcontract",0,120,0,0,0,0,120]
[null,null,"Period 3","Regular Time",0,0,500,0,0,0,500]
[null,null,null,"Overtime",0,0,50,0,0,0,50]
[null,null,null,"Subcontract",0,0,100,0,0,0,100]
[null,null,"Period 4","Regular Time",0,0,0,0,0,0,0]
[null,null,null,"Overtime",0,0,0,0,0,0,0]
[null,null,null,"Subcontract",0,0,0,0,0,0,0]
[null,null,"Period 5","Regular Time",0,0,0,0,0,0,0]
[null,null,null,"Overtime",0,0,0,0,0,0,0]
[null,null,null,"Subcontract",0,0,0,0,0,0,0]
[null,null,"Period 6","Regular Time",0,0,0,0,0,0,0]
[null,null,null,"Overtime",0,0,0,0,0,0,0]
[null,null,null,"Subcontract",0,0,0,0,0,0,0]
[null,null,null,null,550,700,750,0,0,0]
[null,null,null,null,"Demand Met",null,null,null,null,null,null,null,"Total Cost = ",124730]

{"sheet":"Solved Problems","index":7,"n_rows":82,"n_cols":15,"rows_kept":82,"cut":false,"hidden":false,"merged":[]}
["Chapter 12 - Solved Problems",null,null,null,"Note: This worksheet displays results only, you must copy the shaded"]
["<Back",null,null,null,"area into the corresponding template to make additional calculations."]
[]
["1.","Aggregate Planning",null,null," "]
[null,null,null,null,null,"Number of periods:",9]
[]
[null,"Period",null,null,null,1,2,3,4,5,6,7,8,9,"Total"]
[null,"Forecast",null,null,null,190,230,260,280,210,170,160,260,180,1940]
[null,"Output"]
[null,null,"Regular",null,null,220,220,220,220,220,210,210,210,210,1940]
[null,null,"Part Time",null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Overtime",null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Subcontract",null,null,null,null,null,null,null,null,null,null,null,0]
[null,"Output - Forecast",null,null,null,30,-10,-40,-60,10,40,50,-50,30,0]
[null,"Inventory"]
[null,null,"Beginning",null,null,null,30,20,0,0,0,0,20,0]
[null,null,"Ending",null,null,30,20,0,0,0,0,20,0,0]
[null,null,"Average",null,null,15,25,10,0,0,0,10,10,0,70]
[null,"Backlog",null,null,null,0,0,20,80,70,30,0,30,0,230]
[null,"Costs:"]
[null,null,"Regular","@",6,1320,1320,1320,1320,1320,1260,1260,1260,1260,11640]
[null,null,"Part Time","@",null,0,0,0,0,0,0,0,0,0,0]
[null,null,"Overtime","@",null,0,0,0,0,0,0,0,0,0,0]
[null,null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0,0]
[null,null,"Hire/Layoff  ",null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Inventory","@",5,75,125,50,0,0,0,50,50,0,350]
[null,null,"Back orders","@",10,0,0,200,800,700,300,0,300,0,2300]
[null,"Total",null,null,null," ",1445,1570,2120,2020,1560,1310,1610,1260,14290]
[]
[]
[null,null,null,null,null,1,2,3,4,5,6,7,8,9]
[null,"Cumulative Forecast ",null,null,null,190,420,680,960,1170,1340,1500,1760,1940]
[null,"Cumulative Production",null,null,null,220,440,660,880,1100,1310,1520,1730,1940]
[null,"Ending Inventory",null,null,null,30,20,"","","","",20,"",""]
[null,"Backlog",null,null,null,"","",20,80,70,30,"",30,""]
[]
[]
["2.","Aggregate Planning"]
[null,null,null,null,null,"Number of periods:",8]
[]
[null,"Period",null,null,null,1,2,3,4,5,6,7,8,"Total"]
[null,"Forecast",null,null,null,1200,1200,1400,3000,1200,1200,1200,1200,11600]
[null,"Output"]
[null,null,"Regular",null,null,1200,1200,1200,1200,1200,1200,1200,1200,9600]
[null,null,"Part Time",null,null,null,null,null,2000,null,null,null,null,2000]
[null,null,"Overtime",null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Subcontract",null,null,null,null,null,null,null,null,null,null,0]
[null,"Output - Forecast",null,null,null,0,0,-200,200,0,0,0,0,0]
[null,"Inventory"]
[null,null,"Beginning",null,null,null,0,0,0,0,0,0,0]
[null,null,"Ending",null,null,0,0,0,0,0,0,0,0]
[null,null,"Average",null,null,0,0,0,0,0,0,0,0,0]
[null,"Backlog",null,null,null,0,0,200,0,0,0,0,0,200]
[null,"Costs:"]
[null,null,"Regular","@",4,4800,4800,4800,4800,4800,4800,4800,4800,38400]
[null,null,"Part Time","@",5,0,0,0,10000,0,0,0,0,10000]
[null,null,"Overtime","@",null,0,0,0,0,0,0,0,0,0]
[null,null,"Subcontract","@",null,0,0,0,0,0,0,0,0,0]
[null,null,"Hire/Layoff  ",null,null,null,null,100,null,null,null,null,null,100]
[null,null,"Inventory","@",null,0,0,0,0,0,0,0,0,0]
[null,null,"Back orders","@",1,0,0,200,0,0,0,0,0,200]
[null,"Total",null,null,null,4800,4800,5100,14800,4800,4800,4800,4800,48700]
[]
[]
[null,null,null,null,null,1,2,3,4,5,6,7,8]
[null,"Cumulative Forecast ",null,null,null,1200,2400,3800,6800,8000,9200,10400,11600]
[null,"Cumulative Production",null,null,null,1200,2400,3600,6800,8000,9200,10400,11600]
[null,"Ending Inventory",null,null,null,"","","","","","","",""]
[null,"Backlog",null,null,null,"","",200,"","","","",""]
[]
[]
["3.","Master Scheduling"]
[]
[]
[null,null,"Week",null,null,0,1,2,3,4]
[null,null,"Forecast",null,null,null,70,70,70,70]
[null,null,"Customer Orders"]
[null,null,"(committed)",null,null,null,80,50,30,10]
[null,null,"Projected on-hand"]
[null,null,"inventory",null,null,null,20,50,80,10]
[null,null,"MPS",null,null,null,100,100,100]
[null,null,"ATP",null,null,null,20,50,60,""]