Developing Master Production Schedule Based On Mrp Records Developed In Excel.

Results Forecasting Forecasting using research method Period Product 1 Product 2 Product 3 Product 4 Product 5 Period Forecast result 21 150 169 131 82 45 21 79 22 147 187 106 85 40 22 79 23 147 204 95 89 42 23 79 24 148 191 82 92 40 24 79 25 148 159 136 95 53 25 79 Planning Product 1 Product 2 Product 3 Product 4 Product 5 Level Chase Level Chase Level Chase Level Chase Level Chase Demand 740 740 910 910 550 550 443 443 220 220 Backordered units 3 0 37 0 25 0 0 0 1 0 Workers Needed 25 25 35 33 20 21 15 17 10 10 Worker Hired 0 0 0 1 0 2 0 1 0 0 Workers Fired 5 5 3 5 6 6 7 7 8 8 Avg. inventory 0 0 21 0 6.5 0 $760 0 13 0 Regular Production Cost $74,000 $75,000 $72,800 $72,800 $13,750 $13,750 $66,750 $66,450 $44,000 $44,000 Undertime Production $1,600 $1,600 $22,400 $12,800 $8,000 $12,800 $1,200 $16,080 $12,800 $12,800 Worker Hired $0 $0 $0 $12,000 $0 $24,000 $0 $10,000 $0 $0 Workers Fired $125,000 $125,000 $75,000 $125,000 $150,000 $150,000 $140,000 $140,000 $200,000 $200,000 Back Order cost $75 $0 $666 $0 $250 $0 $0 $0 $75 $0 Holding Cost $0 $0 $252 $0 $667 $0 $0 $0 $241 $0 Total Cost $200,675 $201,600 $171,118 $222,600 $172,667 $200,550 $208,710 $232,530 $257,116 $256,800 MRP MRP for Sub-products 1 to 4 Sub-Product 1 Q = 1000 SS = 100 LT = 1 Week 21 22 23 24 25 Gross Requirement 1261 1212 1233 1226 1370 Schedule Receipt Proj. Available Bal./450 189 982 761 557 214 Net Requirement 811 1207 1221 1204 1343 Planned Order Receipt 1000 2000 1000 1000 1000 Planned Order Release 1000 2000 1000 1000 1000 Sub-Product 2 Q= 200 SS = 0 LT = 0 Week 21 22 23 24 25 Gross Requirement 320 125 131 132 148 Schedule Receipt Proj. Available Bal./150 150 30 105 174 42 94 Net Requirement 170 125 131 132 148 Planned Order Receipt 200 200 200 0 200 Planned Order Release 200 200 200 0 200 Sub-Product 3 Q= 2 periods SS = 25 LT = 1 Week 21 22 23 24 25 Gross Requirement 451 480 503 464 454 Schedule Receipt Proj. Available Bal./200 533 25 523 25 25 Net Requirement 251 508 535 498 482 Planned Order Receipt 784 1033 482 Planned Order Release 784 1033 482 Sub-Product 4 Q= Lot for lot SS = 50 LT = 1 Week 21 22 23 24 25 Gross Requirement 2196 2339 2452 2287 2217 Schedule Receipt Proj. Available Bal./650 50 50 50 50 50 Net Requirement 1546 2339 2452 2287 2217 Planned Order Receipt 1596 2339 2452 2287 2217 Planned Order Release 1596 2339 2452 2287 2217 MPS Total MPS - Based on MRP Sub Product Week 21 Week 22 Week 23 Week 24 Week 25 1 3000 1000 1000 1000 2 200 200 200 0 200 3 784 1033 0 482 0 4 3935 2452 2287 2217 0 Machines required Station 21 22 23 24 25 1 35 18 12 13 2 2 36 25 15 17 3 3 31 20 9 12 2 4 52 25 24 23 1 5 61 37 28 28 4 6 42 23 22 20 2 7 45 22 20 20 1 MPS Week 21 Week 22 Week 23 Week 24 Week 25 Product 1 150 147 147 148 147 Product 2 169 187 204 191 159 Product 3 131 106 95 82 136 Product 4 82 85 89 92 95 Product 5 45 40 42 40 53 Product 1 Sub Product 1 Period Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 Past Due 21 22 23 24 25 4 1 150 147 147 148 147 Forecast 1261 1212 1233 1226 1370 1 3 450 441 441 444 441 Order (Planned Order Release) 1000 2000 1000 1000 1000 PAB/450 ATP Product 2 MPS Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 3 2 338 374 408 382 318 Q=1000; SS=100 4 9 1521 1683 1836 1719 1431 Sub Product 2 Period Past Due 21 22 23 24 25 Product 3 Forecast 338 125 131 132 148 Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 Order (Planned Order Release) 200 200 200 0 200 1 1 131 106 95 82 136 PAB/150 150 3 1 131 106 95 82 136 ATP 4 4 524 424 380 328 544 MPS Q=200; SS=0 Product 4 Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 Sub Product 3 Period 1 5 410 425 445 460 475 Past Due 21 22 23 24 25 2 1 82 85 89 92 95 Forecast 469 480 503 464 454 4 1 82 85 89 92 95 Order (Planned Order Release) 784 1033 482 PAB/200 200 ATP Product 5 MPS Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 1 6 270 240 252 240 318 Q=Fixed 2 Per; SS=25 2 1 45 40 42 40 53 Sub Product 4 Period Total MPS Past Due 21 22 23 24 25 Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 Forecast 2277 2339 2452 2287 2217 1 15 1261 1212 1233 1226 1370 Order (Planned Order Release) 1596 2339 2452 2287 2217 2 2 338 125 131 132 148 PAB/650 650 3 3 469 480 503 464 454 ATP 4 15 2277 2339 2452 2287 2217 MPS Q=L4L; SS=50 Total MPS - Based on MRP Sub Product Week 21 Week 22 Week 23 Week 24 Week 25 1 3000 1000 1000 1000 2 200 200 200 0 200 3 784 1033 0 482 0 4 3935 2452 2287 2217 0 MRP Total MPS Total MPS - Based on MRP Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 Sub Product Week 21 Week 22 Week 23 Week 24 Week 25 1 15 1261 1212 1233 1226 1370 1 3000 1000 1000 1000 0 2 2 320 125 131 132 148 2 200 200 200 0 200 3 3 451 480 503 464 454 3 784 1033 0 482 0 4 15 2196 2339 2452 2287 2217 4 3935 2452 2287 2217 0 Sub-Product 1 Q = 1000 SS = 100 LT = 1 Week 21 22 23 24 25 Gross Requirement 1261 1212 1233 1226 1370 Q = 1000 Schedule Receipt SS = 100 Proj. Available Bal./450 189 982 761 557 214 LT = 1 Net Requirement 811 1207 1221 1204 1343 Planned Order Receipt 1000 2000 1000 1000 1000 Planned Order Release 1000 2000 1000 1000 1000 Sub-Product 2 Q= 200 SS = 0 LT = 0 Week 21 22 23 24 25 Gross Requirement 320 125 131 132 148 Q = 200 Schedule Receipt SS = 0 Proj. Available Bal./150 150 30 105 174 42 94 LT = 0 Net Requirement 170 125 131 132 148 Planned Order Receipt 200 200 200 0 200 Planned Order Release 200 200 200 0 200 Sub-Product 3 Q= 2 periods SS = 25 LT = 1 Week 21 22 23 24 25 Gross Requirement 451 480 503 464 454 Q = 2 Periods Schedule Receipt SS = 25 Proj. Available Bal./200 533 25 523 25 25 LT = 1 Net Requirement 251 508 535 498 482 Planned Order Receipt 784 1033 482 Planned Order Release 784 1033 482 Sub-Product 4 Q= Lot for lot SS = 50 LT = 1 Week 21 22 23 24 25 Gross Requirement 2196 2339 2452 2287 2217 Q = L4L Schedule Receipt SS = 50 Proj. Available Bal./650 50 50 50 50 50 LT = 1 Net Requirement 1546 2339 2452 2287 2217 Planned Order Receipt 1596 2339 2452 2287 2217 Planned Order Release 1596 2339 2452 2287 2217 Capacity Planning Total MPS Undertime Cost $2/min Sub Product Qty. Needed Week 21 Week 22 Week 23 Week 24 Week 25 Buy Machine Cost $4,500/machine 1 15 1261 1212 1233 1226 1370 Resale Machine Cost $2,250/machine 2 2 320 125 131 132 148 No Overtime/Subcontracting 3 3 451 480 503 464 454 Holding Cost Based on Ending Inv. 4 15 2196 2339 2452 2287 2217 Cost Info of Sub-Products Sub-Product Type Route Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 1 1,2,3,4,5,6,7 Regular Cost ($/unit) 7 25 22 12 2 1,5,4,3,2,6,5 3 7,3,5,2,1 4 4,2,7,5,6 Total MPS - Based on MRP Number of Machines Sub Product Week 21 Week 22 Week 23 Week 24 Week 25 Totals Station Number of Machines 1 3000 1000 1000 1000 6001 1 10 2 200 200 200 0 200 802 2 20 3 784 1033 0 482 0 2302 3 10 4 3935 2452 2287 2217 0 10895 4 15 5 15 6 20 Lot Size Operation Work Center Run Time Setup Time Std. Setup Time Total 7 5 Sub-Product (Hours/Unit) (Hours/Lot) (Hours/Unit) (Hours/Unit) 1 1000 1/7 1 0.3333333333 3.333333333 0.0033333333 0.3366666667 Processing Times (Minutes/Unit) 2/7 2 0.15 2.166666667 0.0021666667 0.1521666667 Station Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 3/7 3 0.2333333333 3.5 0.0035 0.2368333333 1 20 19 13 0 4/7 4 0.35 1.583333333 0.0015833333 0.3515833333 2 9 22 18 8 5/7 5 0.25 1.166666667 0.0011666667 0.2511666667 3 14 18 21 0 6/7 6 0.1833333333 1.833333333 0.0018333333 0.1851666667 4 21 7 0 12 7/7 7 0.3 2.5 0.0025 0.3025 5 15 15 16 16 2 200 1/7 1 0.3166666667 3.166666667 0.0158333333 0.3325 6 11 11 0 15 2/7 5 0.25 2.083333333 0.0104166667 0.2604166667 7 18 0 3 10 3/7 4 0.1166666667 2.583333333 0.0129166667 0.1295833333 4/7 3 0.3 1.416666667 0.0070833333 0.3070833333 Downtime (Minutes/Machine) 5/7 2 0.3666666667 3.583333333 0.0179166667 0.3845833333 Total Work Center Time Between Failures Repair Time 6/7 6 0.1833333333 0.75 0.00375 0.1870833333 265 1 240 25 7/7 5 0.25 2.083333333 0.0104166667 0.2604166667 265 2 240 25 3 1/5 7 0.05 2.083333333 210 3 180 30 2/5 3 0.35 1.083333333 200 4 180 20 3/5 5 0.2666666667 2 205 5 180 25 4/5 2 0.3 2.666666667 255 6 240 15 5/5 1 0.2166666667 1.333333333 265 7 240 25 4 1/5 4 0.2 1 Setup Times 2/5 2 0.1333333333 2.166666667 Station Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 3/5 7 0.1666666667 4.5 1 200 190 80 0 4/5 5 0.2666666667 1.25 2 130 215 160 130 5/5 6 0.25 2.083333333 3 210 85 65 0 4 95 155 0 60 Product 1 Product 2 Product 3 Product 4 Product 5 5 70 125 120 75 6 110 75 0 125 Work Center 7 150 0 125 270 1 3S1(1) 2S3(5) S3(5)+S1(1) S2(1)+5S1(1) 6S1(1)+S2(1) 2 3S1(2) + S4(2) 9S4(2)+2S3(4) 4S4(2)+S3(4)+S1(2) S4(2)+5S1(2) 6S1(2) 3 3S1(3) 2S3(2) S3(2)+S1(3) S2(4)+5S1(3) 6S1(3)+S2(4) 4 3S1(4) + S4(1) 9S4(1) 4S4(1)+S1(4) S4(1)+S2(3)+5S1(4) 6S1(4)+S2(3) 5 3S1(5)+S4(4) 9S4(4)+2S3(3) 4S4(4)+S3(3)+S1(5) S4(4)+S2(2)+5S1(5) 6S1(5)+S2(2) 6 3S1(6)+S4(5) 9S4(5) 4S4(5)+S1(6) S4(5)+5S1(6) 6S1(6) 7 3S1(7) + S4(3) 9S4(3)+2S3(1) 4S4(3)+S3(1)+S1(7) S4(3)+5S1(7) 6S1(7) Product 1 Product 2 Product 3 Product 4 Product 5 No. of Machines Required Work Center Station 21 22 23 24 25 1 1 2 2 3 3 4 4 5 5 6 6 7 7 TotalTime/Prod Capacity Planning.V1 Available Time Total MPS - Based on MRP Station Number of Machines Available time per week Time bwt failure Repair time Repair frequency Available mins/machine Actual available time Sub Product Week 21 Week 22 Week 23 Week 24 Week 25 Totals Regular cost Week 21 Week 22 Week 23 Week 24 Week 25 Totals 1 10 2400 240 25 9.06 2173.584906 21736 1 3000 1000 1000 1000 0 6001 $7 $21,000 $7,000 $7,000 $7,000 $0 $42,000 2 20 2400 240 25 9.06 2173.584906 43472 2 200 200 200 0 200 802 $25 $1,400 $1,400 $1,400 $0 $1,400 $5,600 3 10 2400 180 30 11.43 2057.142857 20571 3 784 1033 0 482 0 2302 $22 $5,488 $7,231 $0 $3,374 $0 $16,093 4 15 2400 180 20 12.00 2160 32400 4 3935 2452 2287 2217 0 10895 $12 $27,545 $17,164 $16,009 $15,519 $0 $76,237 5 15 2400 180 25 11.71 2107.317073 31610 Total 7919 4685 3487 3699 200 20000 - $55,433 $32,795 $24,409 $25,893 $1,400 $139,930 6 20 2400 240 15 9.41 2258.823529 45176 7 5 2400 240 25 9.06 2173.584906 10868 Processing Times (Minutes/Unit) Sub-Product 1 Station Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 Available Machine Station 21 22 23 24 25 1 20 19 13 0 10 1 60200 20200 20200 20200 200 2 9 22 18 8 20 2 27130 9130 9130 9130 130 3 14 18 21 0 10 3 42210 14210 14210 14210 210 4 21 7 0 12 15 4 63095 21095 21095 21095 95 5 15 15 16 16 15 5 45070 15070 15070 15070 70 6 11 11 0 15 20 6 33110 11110 11110 11110 110 7 18 0 3 10 5 7 54150 18150 18150 18150 150 Setup Times Sub-Product 2 Station Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 Station 21 22 23 24 25 1 200 190 80 0 1 3990 3990 3990 190 3990 2 130 215 160 130 2 4615 4615 4615 215 4615 3 210 85 65 0 3 3685 3685 3685 85 3685 4 95 155 0 60 4 1555 1555 1555 155 1555 5 70 125 120 75 5 6250 6250 6250 250 6250 6 110 75 0 125 6 2275 2275 2275 75 2275 7 150 0 125 270 7 0 0 0 0 0 Sub-Product Type Route Sub-Product 3 1 1,2,3,4,5,6,7 Station 21 22 23 24 25 2 1,5,4,3,2,6,5 1 10272 13509 80 6346 80 3 7,3,5,2,1 2 14272 18754 160 8836 160 4 4,2,7,5,6 3 16529 21758 65 10187 65 4 0 0 0 0 0 5 12664 16648 120 7832 120 6 0 0 0 0 0 Station 1 21 22 23 24 25 Station 4 21 22 23 24 25 7 2477 3224 125 1571 125 Machines Required 35 18 12 13 2 Machines Required 52 25 24 23 1 Machines Available 10 35 18 12 13 Machines Available 15 52 25 24 23 Sub-Product 4 Capacity Required 74462 37699 24270 26736 4270 Capacity Required 111930 52134 50154 47914 1710 Station 21 22 23 24 25 Expected Run Time 2173 2173 2173 2173 2173 Expected Run Time 2160 2160 2160 2160 2160 1 0 0 0 0 0 Available Time 76055 39114 26076 28249 4346 Available Time 112320 54000 51840 49680 2160 2 31610 19746 18426 17866 130 Machines Purchased 25 0 0 1 0 Machines Purchased 37 0 0 0 0 3 0 0 0 0 0 Machines to Sell 0 17 6 0 11 Machines to Sell 0 27 1 1 22 4 47280 29484 27504 26664 60 Undertime 1593 1415 1806 1513 76 Undertime 390 1866 1686 1766 450 5 63035 39307 36667 35547 75 Machine Purchase Cost $112,500 $0 $0 $0 $0 Machine Purchase Cost $166,500 $0 $0 $0 $0 6 59150 36905 34430 33380 125 Machine Resale Cost $0 $38,250 $13,500 $0 $24,750 Machine Resale Cost $0 $60,750 $2,250 $2,250 $49,500 7 39620 24790 23140 22440 270 Undertime Cost $3,186 $2,830 $3,612 $3,026 $152 Undertime Cost $780 $3,732 $3,372 $3,532 $900 Total cost $115,686 -$35,420 -$9,888 $3,026 -$24,598 Total cost $167,280 -$57,018 $1,122 $1,282 -$48,600 Total Station Time Station 21 22 23 24 25 Station 2 21 22 23 24 25 Station 5 21 22 23 24 25 1 74462 37699 24270 26736 4270 Machines Required 36 25 15 17 3 Machines Required 61 37 28 28 4 2 77627 52245 32331 36047 5035 Machines Available 20 36 25 15 17 Machines Available 15 61 37 28 28 3 62424 39653 17960 24482 3960 Capacity Required 77627 52245 32331 36047 5035 Capacity Required 127019 77275 58107 58699 6515 4 111930 52134 50154 47914 1710 Expected Run Time 2173 2173 2173 2173 2173 Expected Run Time 2107 2107 2107 2107 2107 5 127019 77275 58107 58699 6515 Available Time 78228 54325 32595 36941 6519 Available Time 128527 77959 58996 58996 8428 6 94535 50290 47815 44565 2510 Machines Purchased 16 0 0 2 0 Machines Purchased 46 0 0 0 0 7 96247 46164 41415 42161 545 Machines to Sell 0 11 10 0 14 Machines to Sell 0 24 9 0 24 Undertime 601 2080 264 894 1484 Undertime 1508 684 889 297 1913 Machine Purchase Cost $72,000 $0 $0 $9,000 $0 Machine Purchase Cost $207,000 $0 $0 $0 $0 Machines required Machine Resale Cost $0 $24,750 $22,500 $0 $31,500 Machine Resale Cost $0 $54,000 $20,250 $0 $54,000 Station 21 22 23 24 25 Undertime Cost $1,202 $4,160 $528 $1,788 $2,968 Undertime Cost $3,016 $1,368 $1,778 $594 $3,826 1 35 18 12 13 2 Total cost $73,202 -$20,590 -$21,972 $10,788 -$28,532 Total cost $210,016 -$52,632 -$18,472 $594 -$50,174 2 36 25 15 17 3 3 31 20 9 12 2 4 52 25 24 23 1 Station 3 21 22 23 24 25 Station 6 21 22 23 24 25 5 61 37 28 28 4 Machines Required 31 20 9 12 2 Machines Required 42 23 22 20 2 6 42 23 22 20 2 Machines Available 10 31 20 9 12 Machines Available 20 42 23 22 20 7 45 22 20 20 1 Capacity Required 62424 39653 17960 24482 3960 Capacity Required 94535 50290 47815 44565 2510 Expected Run Time 2057 2057 2057 2057 2057 Expected Run Time 2258 2258 2258 2258 2258 Available Time 63767 41140 18513 24684 4114 Available Time 94836 51934 49676 45160 4516 Machines Purchased 21 0 0 3 0 Machines Purchased 22 0 0 0 0 Machines to Sell 0 11 11 0 10 Machines to Sell 0 19 1 2 18 Undertime 1343 1487 553 202 154 Undertime 301 1644 1861 595 2006 Machine Purchase Cost $94,500 $0 $0 $13,500 $0 Machine Purchase Cost $99,000 $0 $0 $0 $0 Machine Resale Cost $0 $24,750 $24,750 $0 $22,500 Machine Resale Cost $0 $42,750 $2,250 $4,500 $40,500 Undertime Cost $2,686 $2,974 $1,106 $404 $308 Undertime Cost $602 $3,288 $3,722 $1,190 $4,012 Total cost $97,186 -$21,776 -$23,644 $13,904 -$22,192 Total cost $99,602 -$39,462 $1,472 -$3,310 -$36,488 Station 7 21 22 23 24 25 Machines Required 45 22 20 20 1 Week 21 22 23 24 25 Machines Available 5 45 22 20 20 Total cost $1,001,481 -$242,569 -$47,383 $54,775 -$248,678 Capacity Required 96247 46164 41415 42161 545 Expected Run Time 2173 2173 2173 2173 2173 Available Time 97785 47806 43460 43460 2173 Total Sub_product Cost Total cost Machines Purchased 40 0 0 0 0 6001 $7 $42,007 Machines to Sell 0 23 2 0 19 802 $25 $20,050 Undertime 1538 1642 2045 1299 1628 2302 $22 $50,644 Machine Purchase Cost $180,000 $0 $0 $0 $0 10895 $12 $130,740 Machine Resale Cost $0 $51,750 $4,500 $0 $42,750 $761,067 Undertime Cost $3,076 $3,284 $4,090 $2,598 $3,256 Total cost $183,076 -$48,466 -$410 $2,598 -$39,494 Undertime 41473 Available time 1618378 Utlizied time 1576905 0.9743737248 Details Cost Information on Products Product 1 Product 2 Product 3 Product 4 Product 5 Regular Cost 100 80 25 150 200 Holding ($/unit/week) 15 12 11.5 20 18.5 Backorder($/unit/week) 25 18 10 50 75 Information on Process Undertime cost $2/min Hiring cost $12,000/person Layoff cost $25,000/person Overtime or Subcontract Not allowed Beginning inventory 0 Regular production unit/worker 30 Initial workforce 10 workers Cost Information of Sub-Products Sub-Product 1 2 3 4 Regular cost ($/unit) 7 25 22 12 Undertime cost $2/min Buy machine cost $4500/machine Resale machine cost $2250/machine Overtime or subcontracting Not allowed Holding cost Based on ending inventory Routing Sub-Product Type Route 1 1,2,3,4,5,6,7 2 1,5,4,3,2,6,5 3 7,3,5,2,1 4 4,2,7,5,6 Number of Machines Station Number of Machines 1 10 2 20 3 10 4 15 5 15 6 20 7 5 Processing Times (minutes per unit) Station Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 1 20 19 13 0 2 9 22 18 8 3 14 18 21 0 4 21 7 0 12 5 15 15 16 16 6 11 11 0 15 7 18 0 3 10 Setup Times (minutes per lot) Station Sub-Product 1 Sub-Product 2 Sub-Product 3 Sub-Product 4 1 200 190 80 0 2 130 215 160 130 3 210 85 65 0 4 95 155 0 60 5 70 125 120 75 6 110 75 0 125 7 150 0 125 270 Downtime (minutes per machine) Work Center Time Between Failures Repair Time 1 240 25 2 240 25 3 180 30 4 180 20 5 180 25 6 240 15 7 240 25 Product 1 Planning Level Policy Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 0 0 0 0 0 Regular production capacity per week 150 150 150 150 150 750 Actual production 148 148 148 148 148 740 $100 $74,000 Demand 150 147 147 148 148 740 Undertime Production 160 160 160 160 160 800 $2 $1,600 Back order 0 0 0 0 0 0 0 Workers needed 5 5 5 5 5 25 Workers Available 10 5 5 5 5 30 Workers Hired 0 0 0 0 0 0 Worker fired 5 0 0 0 0 5 $25,000 $125,000 End Inventory 0 0 0 0 0 0 Backlog 2 1 0 0 0 3 $25 $75 Average inventroy 0 0 0 0 0 0 Total cost $200,675 Chase Policy Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 0 0 0 0 0 Regular production capacity per week 150 150 150 150 150 750 $100 $75,000 Actual production 150 147 147 148 148 740 Demand 150 147 147 148 148 740 Undertime Production 0 240 240 160 160 800 $2 $1,600 Back order 5 5 5 5 5 25 Workers needed 10 5 5 5 5 30 Workers Available 0 0 0 0 0 0 Workers Hired 5 0 0 0 0 5 $25,000 $125,000 Worker fired 0 0 0 0 0 0 End Inventory 0 0 0 0 0 0 Backlog 0 0 0 0 0 0 Average inventroy Total cost $201,600 Product 2 Planning Level Policy Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 13 8 0 0 0 0 0 Regular production capacity per week 210 210 210 210 210 1050 0 0 Actual production 182 182 182 182 182 910 $80 $72,800 Demand 169 187 204 191 159 910 0 0 Undertime Production 2240 2240 2240 2240 2240 11200 $2 $22,400 Back order 0 0 0 0 0 0 0 0 Workers needed 7 7 7 7 7 0 0 0 Workers Available 10 7 7 7 7 0 0 0 Workers Hired 0 0 0 0 0 0 0 0 Worker fired 3 0 0 0 0 3 $25,000 $75,000 End Inventory 13 8 0 0 0 21 0 0 Backlog 0 0 14 23 0 37 $18 $666 Average inventroy 6.5 10.5 4 0 0 21 $12 $252 Total cost $ 1,71,118 Chase Policy Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 0 0 0 0 0 0 0 Regular production capacity per week 180 210 210 210 180 0 0 0 Actual production 169 187 204 191 159 910 $80 $72,800 Demand 169 187 204 191 159 910 0 0 Undertime Production 880 1840 480 1520 1680 6400 $2 $12,800 Back order 0 0 0 0 0 0 0 0 Workers needed 6 7 7 7 6 33 0 0 Workers Available 10 6 7 7 7 37 0 0 Workers Hired 0 1 0 0 0 1 12000 12000 Worker fired 4 0 0 0 1 5 $25,000 $ 1,25,000 End Inventory 0 0 0 0 0 0 0 0 Backlog 0 0 0 0 0 0 $0 $0 Average inventroy 0 0 0 0 0 0 $0 $0 Total cost $ 2,22,600 Product 3 Planning Level Policy Week 21 22 23 24 25 Total Cost Total Cost Week Forecast Beg Inv Prod. End Inv Backorder # Workers Hired Fired Cap (units) Under Cap Under Prod Beginning Inventory 0 0 0 15 43 58 0 0 21 131 0 110 0 21 4 0 6 120 10 800 Regular production capacity per week 120 120 120 120 120 600 0 0 22 106 0 110 0 4 4 0 0 120 10 800 Actual production 110 110 110 110 110 550 $25 $13,750 23 95 0 110 15 0 4 0 0 120 10 800 Demand 131 106 95 82 136 550 0 0 24 82 15 110 43 0 4 0 0 120 10 2000 Undertime Production 800 800 800 800 800 4000 $2 $8,000 25 136 43 110 17 0 4 0 0 120 10 4240 Back order 0 0 0 0 0 0 $75 $0 Totals 17 25 6 8640 Cost Total 168870 Workers needed 4 4 4 4 4 20 0 0 Costs 340 1250 150000 17280 Workers Available 10 4 4 4 4 26 0 0 Workers Hired 0 0 0 0 0 0 0 0 Worker fired 6 0 0 0 0 6 $25,000 $150,000 Week Forecast Beg Inv Prod. End Inv Backorder # Workers Hired Fired Cap Under Cap Under Prod End Inventory 0 0 15 43 `7 58 $11.50 $667.00 21 131 0 131 0 0 5 0 5 150 19 1520 Backlog 21 4 0 0 0 25 $10 $250 22 106 0 106 0 0 4 0 1 120 14 1120 Average inventroy 0 1.5 1.5 3.5 0 6.5 $12 $75 23 95 0 95 0 0 4 0 0 120 25 2000 Total cost $172,742 24 82 0 82 0 0 3 0 0 90 8 640 25 136 0 136 0 0 5 2 0 150 14 1120 Totals 2 6 6400 Chase Policy Costs 24000 150000 12800 Cost Total 186800 Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 0 0 0 0 0 0 0 Regular production capacity per week 150 120 120 90 150 630 0 0 Actual production 131 106 95 82 136 550 $25 $13,750 Demand 131 106 95 82 136 550 0 0 Undertime Production 1520 1120 2000 640 1120 6400 $2 $12,800 Back order 0 0 0 0 0 0 0 0 Workers needed 5 4 4 3 5 21 0 0 Workers Available 10 5 4 4 3 26 0 0 Workers Hired 0 0 0 0 2 2 $12,000 $24,000 Worker fired 5 1 0 0 0 6 $25,000 $150,000 End Inventory 0 0 0 0 0 0 $12 $0 Backlog 0 0 0 0 0 0 $10 $0 Average inventroy 0 0 0 0 0 0 $0 $0 Total cost $200,550 Initial Workforce size = 10 Beginning Inventory = 0 Undertime Cost = $2/min Hiring Cost = $12000/person Firing Cost = $25000/person No overtime or subcontracting Beginning inventory = 0 Regular production unit per worker = 30 units per week 1 period = 1 week = 5 days Manufacturing Units/day 480 Number of days a week 5 Number of weeks in an year 52 REGULAR COST ($/unit) 200 HOLDING COST ($/unit/week) 11.5 BACKORDER COST ($/unit/week) 75 Product 4 Planning Level Policy Week 21 22 23 24 25 Total Cost Beginning Inventory 0 7 11 11 8 Demand 82 85 89 92 95 443 Actual production 89 89 89 89 89 445 $66,750 Regular production capacity per week 90 90 90 90 90 Workers Needed 3 3 3 3 3 15 Workers Available 10 3 3 3 3 Workers Hired 0 0 0 0 0 Workers Fired 7 0 0 0 0 7 $140,000 End Inventory 7 11 11 8 2 Average Inventroy 3.5 9 11 9.5 5 38 $760 Undertime 80 80 80 80 80 400 $1,200 Total Cost $208,710 Chase Policy Week 21 22 23 24 25 Total Cost Beginning Inventory 0 0 0 0 0 Demand 82 85 89 92 95 443 Actual production 82 85 89 92 95 443 $66,450 Regular production capacity per week 90 90 90 120 120 Workers Needed 3 3 3 4 4 17 Workers Available 10 3 3 3 4 Workers Hired 0 0 0 1 0 1 $10,000 Workers Fired 7 0 0 0 0 7 $140,000 End Inventory 0 0 0 0 0 Average Inventroy 0 0 0 0 0 Undertime 640 400 80 2240 2000 5360 $16,080 Total Cost $232,530 Initial Workforce size = 10 Beginning Inventory = 0 Undertime Cost = $2/min Hiring Cost = $12000/person Firing Cost = $25000/person No overtime or subcontracting Beginning inventory = 0 Regular production unit per worker = 30 units per week 1 period = 1 week = 5 days Manufacturing Units/day 480 Number of days a week 5 Number of weeks in an year 52 REGULAR COST ($/unit) 150 HOLDING COST ($/unit/week) 20 BACKORDER COST ($/unit/week) 50 Product 5 Planning Level Policy Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 0 3 3 7 0 0 0 Regular production capacity per week 60 60 60 60 60 300 0 0 Actual production 44 44 44 44 44 220 $200 $44,000 Demand 45 40 42 40 53 220 0 0 Undertime Production 1280 1280 1280 1280 1280 6400 $2 $12,800 Back order 0 0 0 0 0 0 0 0 Workers needed 2 2 2 2 2 0 0 0 Workers Available 10 2 2 2 2 0 0 0 Workers Hired 0 0 0 0 0 0 0 0 Worker fired 8 0 0 0 0 8 $25,000 $200,000 End Inventory 0 3 3 7 0 13 0 0 Backlog 1 0 0 0 0 1 $75 $75 Average inventroy 0 1.5 1.5 3.5 0 13 $19 $241 Total cost $257,116 Chase Policy Week 21 22 23 24 25 Total Cost Total Cost Beginning Inventory 0 0 0 0 0 0 0 0 Regular production capacity per week 60 60 60 60 60 300 0 0 Actual production 45 40 42 40 53 220 $200 $44,000 Demand 45 40 42 40 53 220 0 0 Undertime Production 1200 1600 1440 1600 560 6400 $2 $12,800 Back order 0 0 0 0 0 0 0 0 Workers needed 2 2 2 2 2 10 0 0 Workers Available 10 2 2 2 2 18 0 0 Workers Hired 0 0 0 0 0 0 12000 0 Worker fired 8 0 0 0 0 8 $25,000 $200,000 End Inventory 0 0 0 0 0 0 0 0 Backlog 0 0 0 0 0 0 $0 $0 Average inventroy 0 0 0 0 0 0 $0 $0 Total cost $256,800 Initial Workforce size = 10 Beginning Inventory = 0 Undertime Cost = $2/min Hiring Cost = $12000/person Firing Cost = $25000/person No overtime or subcontracting Beginning inventory = 0 Regular production unit per worker = 30 units per week 1 period = 1 week = 5 days Manufacturing Units/day 480 Number of days a week 5 Number of weeks in an year 52 REGULAR COST ($/unit) 200 HOLDING COST ($/unit/week) 18.5 BACKORDER COST ($/unit/week) 75 Forecasting Research Method PRODUCT 4 Alpha 0.9 CROSTON SMOOTHING WITH ALPHA AND BETA VALUES Beta 0.9 History Horizon Product 4 X^(t,t+1) n(t) z^(t) n^(T) ERROR 1 18 22 0 22 1 4 2 22 1.06 23 22 20.8 20.94 3 25 8.29 1 24.7 2.98 16.71 4 27 22.35 1 26.77 1.2 4.65 5 32 30.87 1 31.48 1.02 1.13 6 35 34.58 1 34.65 1 0.42 7 39 38.56 1 38.56 1 0.44 8 41 40.76 1 40.76 1 0.24 9 43 42.78 1 42.78 1 0.22 10 47 46.58 1 46.58 1 0.42 MAD (1 to 10) 4.92 11 53 52.36 1 52.36 1 0.64 12 55 54.74 1 54.74 1 0.26 13 58 57.67 1 57.67 1 0.33 14 60 59.77 1 59.77 1 0.23 15 64 63.58 1 63.58 1 0.42 16 66 65.76 1 65.76 1 0.24 17 69 68.68 1 68.68 1 0.32 18 72 71.67 1 71.67 1 0.33 19 75 74.67 1 74.67 1 0.33 20 79 78.57 1 78.57 1 0.43 MAD (11 to 20) 0.36 21 78.57 22 78.57 23 78.57 24 78.57 25 78.57 MAD 4.92 FORMULA USED CROSTON SMOOTHING WITH ALPHA AND BETA VALUES Product 1 Forecasting History Demand 3 Week Error 5 Week Error 1 140 2 155 3 145 4 152 147 5.33 5 155 151 4.33 6 145 151 5.67 149.4 4.4 7 147 151 3.67 150.4 3.4 8 135 149 14.00 148.8 13.8 9 137 142 5.33 146.8 9.8 10 140 140 0.33 143.8 3.8 11 120 137 17.33 140.8 20.8 12 150 132 17.67 135.8 14.2 13 161 137 24.33 136.4 24.6 14 143 144 0.67 141.6 1.4 15 149 151 2.33 142.8 6.2 16 140 151 11.00 144.6 4.6 17 142 144 2.00 148.6 6.6 18 157 144 13.33 147 10 19 148 146 1.67 146.2 1.8 20 144 149 5.00 147.2 3.2 21 150 146.2 22 147 147.75 23 147 149.6666667 24 148 146 25 147 144 MAD MAD Given MAD = 10 7.88 8.573333333 Product 2 Forecasting HISTORY HORIZON Seasonal demand Seasonal Index Forecast using slope and intercept Forecasting using trend Error 1 145 0.86 178.63 153.59 8.59 2 164 0.97 177.70 169.54 5.54 3 178 1.06 176.77 186.36 8.36 4 200 1.19 175.84 206.61 6.61 5 215 1.28 174.91 224.06 9.06 6 205 1.22 173.98 211.60 6.60 7 170 1.01 173.05 175.81 5.81 8 142 0.84 172.13 142.94 0.94 9 119 0.71 171.20 121.00 2.00 10 147 0.87 170.27 146.41 0.59 11 162 0.96 169.34 161.56 0.44 12 180 1.07 168.41 177.53 2.47 13 199 1.18 167.48 196.77 2.23 14 220 1.31 166.55 213.34 6.66 15 208 1.23 165.62 201.42 6.58 16 175 1.04 164.69 167.32 7.68 17 140 0.83 163.76 135.99 4.01 18 121 0.72 162.83 115.08 5.92 19 146 0.87 161.90 139.22 6.78 20 160 0.95 160.97 153.59 6.41 21 160.04 169.01 22 159.12 187.01 23 158.19 204.01 24 157.26 191.01 25 156.33 159.02 MAD 5.16 Seasonal index 168.50 Grand Average (B) 169.80 Equation y = -0.9293x + 179.56 Slope -0.93 Intercept 179.56 Average seasonal Index (1) 1 146.00 0.86 2 162.00 0.95 3 179.01 1.05 4 199.51 1.17 5 217.51 1.28 6 206.51 1.22 7 172.50 1.02 8 141.01 0.83 9 120.01 0.71 10 146.01 0.86 11 162.00 0.95 12 179.00 1.05 13 199.50 1.17 14 217.50 1.28 15 206.50 1.22 16 172.51 1.02 17 141.00 0.83 18 120.00 0.71 19 146.01 0.86 20 162.01 0.95 21 179.31 1.06 22 199.57 1.18 23 218.99 1.29 24 206.25 1.21 25 172.72 1.02 Product 3 Forecasting Period Demand Forecast.ets Error 1 100 100.00 0.00 2 82 79.32 2.68 3 70 68.03 1.97 4 55 55.64 0.64 5 110 109.76 0.24 6 85 84.63 0.37 7 74 73.33 0.67 8 62 60.95 1.05 9 115 115.07 0.07 10 88 89.94 1.94 11 78 78.64 0.64 12 67 66.26 0.74 13 121 120.38 0.62 14 93 95.24 2.24 15 84 83.95 0.05 16 71 71.56 0.56 17 128 125.69 2.31 18 99 100.55 1.55 19 89 89.26 0.26 20 77 76.87 0.13 21 130.99 22 105.86 23 94.57 24 82.18 25 136.30 Calculated MAD= 1.11 Given: MAD = 10 Product 4 Forecasting PRODUCT 4 GRAPH HISTORY HORIZON PRODUCT 4 1 18 2 22 3 25 4 27 5 32 6 35 7 39 8 41 9 43 10 47 11 53 12 55 13 58 14 60 15 64 16 66 17 69 18 72 19 75 20 79 The graph displays an UPWARD TREND for this product. Using the trendline the equation for the trend is y = 3.1729x + 15.684 So, the values are F(t) = 15.684 α = 0.2 T(t) = 3.1729 β = 0.4 α 0.2 β 0.4 Period PRODUCT 4 F(t) T(t) FIT Error Absolute deviation Error^2 MAPE 1 18.00 15.68 3.17 α 0.2 2 22.00 18.69 3.10 β 0.4 3 25.00 21.83 3.12 4 27.00 24.96 3.12 5 32.00 27.87 3.04 6 35.00 31.13 3.13 7 39.00 34.40 3.19 8 41.00 37.87 3.30 9 43.00 41.13 3.28 10 47.00 44.14 3.17 47.31 0.31 0.31 0.09 0.65 11 53.00 47.25 3.15 50.39 -2.61 2.61 6.80 4.92 12 55.00 50.91 3.36 54.27 -0.73 0.73 0.53 1.33 13 58.00 54.42 3.41 57.83 -0.17 0.17 0.03 0.29 14 60.00 57.86 3.43 61.29 1.29 1.29 1.67 2.15 15 64.00 61.03 3.32 64.36 0.36 0.36 0.13 0.56 16 66.00 64.29 3.30 67.58 1.58 1.58 2.50 2.40 17 69.00 67.27 3.17 70.43 1.43 1.43 2.06 2.08 18 72.00 70.15 3.05 73.20 1.20 1.20 1.44 1.67 19 75.00 72.96 2.96 75.92 0.92 0.92 0.85 1.23 20 79.00 75.74 2.88 78.62 -0.38 0.38 0.14 0.48 21 78.70 2.92 81.61 22 84.53 23 87.44 24 90.36 25 93.27 MAD 1.00 MAD 1.00 MSE 1.48 MAPE 1.61 On calculating MAD of last 10 weeks MAD is equal to 0.999 which is less than given. LINEAR REGRESSION METHOD Period Demand Linear Regression Forecast Absolute Error Intercept 15.684 1 18 19 1 Slope 3.1729 2 22 22 0 3 25 25 0 4 27 28 1 5 32 32 0 6 35 35 0 7 39 38 1 8 41 41 0 9 43 44 1 10 47 47 0 11 53 51 2 12 55 54 1 13 58 57 1 14 60 60 0 15 64 63 1 16 66 66 0 17 69 70 1 18 72 73 1 19 75 76 1 20 79 79 0 21 - 82 22 - 85 23 - 89 24 - 92 25 - 95 Total Error 12 MAD 0.6 Product 5 Forecasting Period Time Actual Demand Forecast Error Absolute error 1 55 55.00 2 46 55.00 3 58 49.60 4 37 54.64 5 32 44.06 6 48 36.82 7 67 43.53 8 68 57.61 9 52 63.84 10 42 56.74 11 32 47.90 15.90 15.90 12 43 38.36 -4.64 4.64 13 37 41.14 4.14 4.14 14 61 38.66 -22.34 22.34 15 45 52.06 7.06 7.06 16 48 47.83 -0.17 0.17 17 53 47.93 -5.07 5.07 18 38 50.97 12.97 12.97 19 57 43.19 -13.81 13.81 20 40 51.48 11.48 11.48 21 45.00 22 40.00 23 42.00 24 40.00 25 53.00 alpha 0.60 MAD 9.76 Actual Demand vs. t t Actual Demand