Capstone Design Project

profileAbdul-M
Problem123.xls

Problem # 1

0.6
Problem # 1
Using Exponential smoothing method to forecast the demand for next 10 years.
α =0.6 (Assumed value)
Year 400 Gallon Tank ES α =0.6 (Assumed)
2011.0 5100 5100
2012.0 4810 5100
2013.0 5470 4926
2014.0 5540 5252
2015.0 5700 5425
2016.0 6080 5590
2017.0 6467 5884
2018.0 5884 6234
2019.0 6234 6024
2020.0 6024 6150
2021.0 6150 6074
2022.0 6074 6119
2023.0 6119 6092
2024.0 6092 6109
2025.0 6109 6099
2026.0 6099 6105
2027.0 6101
Year 500 Gallon Tank ES α =0.6 (Assumed)
2011.0 4470 4470
2012.0 4310 4470
2013.0 4450 4374
2014.0 4540 4420
2015.0 4680 4492
2016.0 4900 4605
2017.0 5507 4782
2018.0 4782 5217
2019.0 5217 4956
2020.0 4956 5112
2021.0 5112 5018
2022.0 5018 5075
2023.0 5075 5041
2024.0 5041 5061
2025.0 5061 5049
2026.0 5049 5056
2027.0 5052
Year 600 Gallon Tank ES α =0.6 (Assumed)
2011.0 2490 2490
2012.0 2520 2490
2013.0 2360 2508
2014.0 2560 2419
2015.0 2310 2504
2016.0 2980 2387
2017.0 2693 2743
2018.0 2743 2713
2019.0 2713 2731
2020.0 2731 2720
2021.0 2720 2727
2022.0 2727 2723
2023.0 2723 2725
2024.0 2725 2724
2025.0 2724 2725
2026.0 2725 2724
2027.0 2724
Year 800 Gallon Tank ES α =0.6 (Assumed)
2011.0 1250 1250
2012.0 1310 1250
2013.0 1330 1286
2014.0 1180 1312
2015.0 1130 1233
2016.0 1210 1171
2017.0 1493 1194
2018.0 1194 1374
2019.0 1374 1266
2020.0 1266 1331
2021.0 1331 1292
2022.0 1292 1315
2023.0 1315 1301
2024.0 1301 1310
2025.0 1310 1305
2026.0 1305 1308
2027.0 1306

Problem # 2

Problem # 2
Estimate center wise sales forecast for next 10 years.
Center Wise forcast sales for 400 Gallon tank
Centroid % of total Sales Forecast of sales (Center wise)
2018.0 2019.0 2020.0 2021.0 2022.0 2023.0 2024.0 2025.0 2026.0 2027.0
Detroit 9.0 561.0 542.0 553.0 547.0 551.0 548.0 550.0 549.0 549.0 549.0 549.9
Toledo 10.0 623.0 602.0 615.0 607.0 612.0 609.0 611.0 610.0 610.0 610.0 610.9
Cleaveland 12.5 779.0 753.0 769.0 759.0 765.0 762.0 764.0 762.0 763.0 763.0 763.9
Youngstown 12.2 760.0 735.0 750.0 741.0 747.0 743.0 745.0 744.0 745.0 744.0 745.4
Athens 11.0 686.0 663.0 676.0 668.0 673.0 670.0 672.0 671.0 672.0 671.0 672.2
Collumbos 8.8 549.0 530.0 541.0 535.0 539.0 536.0 538.0 537.0 537.0 537.0 537.9
Erie 4.0 249.0 241.0 246.0 243.0 245.0 244.0 244.0 244.0 244.0 244.0 244.4
Pittsburg 6.5 405.0 392.0 400.0 395.0 398.0 396.0 397.0 396.0 397.0 397.0 397.3
Morgantown 4.0 249.0 241.0 246.0 243.0 245.0 244.0 244.0 244.0 244.0 244.0 244.4
Lekington 7.0 436.0 422.0 430.0 425.0 428.0 426.0 428.0 427.0 427.0 427.0 427.6
Fort-Wine 9.0 561.0 542.0 553.0 547.0 551.0 548.0 550.0 549.0 549.0 549.0 549.9
Boffalo 6.0 374.0 361.0 369.0 364.0 367.0 366.0 367.0 366.0 366.0 366.0 366.6
6233.5974400000005 6023.835136 6149.6925184 6074.1780889599995 6119.486746624 6092.301552025599 6108.61266878464 6098.825998729216 6104.698000762471 6101.174799542518
Center Wise forcast sales for 500 Gallon tank
Centroid % of total Sales Forecast of sales (Center wise)
2018.0 2019.0 2020.0 2021.0 2022.0 2023.0 2024.0 2025.0 2026.0 2027.0
Detroit 9.0 470.0 446.0 460.0 452.0 457.0 454.0 456.0 454.0 455.0 455.0
Toledo 10.0 522.0 496.0 511.0 502.0 507.0 504.0 506.0 505.0 506.0 505.0
Cleaveland 12.5 652.0 619.0 639.0 627.0 634.0 630.0 633.0 631.0 632.0 632.0
Youngstown 12.2 636.0 605.0 624.0 612.0 619.0 615.0 617.0 616.0 617.0 616.0
Athens 11.0 574.0 545.0 562.0 552.0 558.0 555.0 557.0 555.0 556.0 556.0
Collumbos 8.8 459.0 436.0 450.0 442.0 447.0 444.0 445.0 444.0 445.0 445.0
Erie 4.0 209.0 198.0 204.0 201.0 203.0 202.0 202.0 202.0 202.0 202.0
Pittsburg 6.5 339.0 322.0 332.0 326.0 330.0 328.0 329.0 328.0 329.0 328.0
Morgantown 4.0 209.0 198.0 204.0 201.0 203.0 202.0 202.0 202.0 202.0 202.0
Lekington 7.0 365.0 347.0 358.0 351.0 355.0 353.0 354.0 353.0 354.0 354.0
Fort-Wine 9.0 470.0 446.0 460.0 452.0 457.0 454.0 456.0 454.0 455.0 455.0
Boffalo 6.0 313.0 297.0 307.0 301.0 304.0 302.0 304.0 303.0 303.0 303.0
5216.75776 4955.839744 5112.3905536 5018.46006784 5074.818359296 5041.0033844224 5061.29236934656 5049.118978392064 5056.423012964761 5052.040592221143
Center Wise forcast sales for 600 Gallon tank
Centroid % of total Sales Forecast of sales (Center wise)
2018.0 2019.0 2020.0 2021.0 2022.0 2023.0 2024.0 2025.0 2026.0 2027.0
Detroit 9.0 244.0 246.0 245.0 245.0 245.0 245.0 245.0 245.0 245.0 245.0
Toledo 10.0 271.0 273.0 272.0 273.0 272.0 273.0 272.0 272.0 272.0 272.0
Cleaveland 12.5 339.0 341.0 340.0 341.0 340.0 341.0 340.0 341.0 341.0 341.0
Youngstown 12.2 331.0 333.0 332.0 333.0 332.0 332.0 332.0 332.0 332.0 332.0
Athens 11.0 298.0 300.0 299.0 300.0 300.0 300.0 300.0 300.0 300.0 300.0
Collumbos 8.8 239.0 240.0 239.0 240.0 240.0 240.0 240.0 240.0 240.0 240.0
Erie 4.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0
Pittsburg 6.5 176.0 178.0 177.0 177.0 177.0 177.0 177.0 177.0 177.0 177.0
Morgantown 4.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0 109.0
Lekington 7.0 190.0 191.0 190.0 191.0 191.0 191.0 191.0 191.0 191.0 191.0
Fort-Wine 9.0 244.0 246.0 245.0 245.0 245.0 245.0 245.0 245.0 245.0 245.0
Boffalo 6.0 163.0 164.0 163.0 164.0 163.0 164.0 163.0 163.0 163.0 163.0
2713.19552 2731.071488 2720.3459072 2726.78125568 2722.920046592 2725.2367720448 2723.84673677312 2724.680757936128 2724.1803452383233 2724.4805928570063
Center Wise forcast sales for 800 Gallon tank
Centroid % of total Sales Forecast of sales (Center wise)
2018.0 2019.0 2020.0 2021.0 2022.0 2023.0 2024.0 2025.0 2026.0 2027.0
Detroit 9.0 124.0 114.0 120.0 116.0 118.0 117.0 118.0 117.0 118.0 118.0
Toledo 10.0 137.0 127.0 133.0 129.0 132.0 130.0 131.0 130.0 131.0 131.0
Cleaveland 12.5 172.0 158.0 166.0 162.0 164.0 163.0 164.0 163.0 163.0 163.0
Youngstown 12.2 168.0 154.0 162.0 158.0 160.0 159.0 160.0 159.0 160.0 159.0
Athens 11.0 151.0 139.0 146.0 142.0 145.0 143.0 144.0 144.0 144.0 144.0
Collumbos 8.8 121.0 111.0 117.0 114.0 116.0 115.0 115.0 115.0 115.0 115.0
Erie 4.0 55.0 51.0 53.0 52.0 53.0 52.0 52.0 52.0 52.0 52.0
Pittsburg 6.5 89.0 82.0 86.0 84.0 85.0 85.0 85.0 85.0 85.0 85.0
Morgantown 4.0 55.0 51.0 53.0 52.0 53.0 52.0 52.0 52.0 52.0 52.0
Lekington 7.0 96.0 89.0 93.0 90.0 92.0 91.0 92.0 91.0 92.0 91.0
Fort-Wine 9.0 124.0 114.0 120.0 116.0 118.0 117.0 118.0 117.0 118.0 118.0
Boffalo 6.0 82.0 76.0 80.0 78.0 79.0 78.0 79.0 78.0 78.0 78.0
1373.78944 1266.199936 1330.7536384 1292.02141696 1315.260749824 1301.3171501056 1309.68330993664 1304.663614038016 1307.6754315771905 1305.8683410536858

Problem # 3

Problem # 3
Determine the Economical center
Solution:
We need to assume values of cost from any center to client location.
Lets assume that client location is A. Also for determining most economical option lets assume
fixed no of items
Centroid Cost to Location A/Item Items assumed Cost
Detroit 10.0 100.0 1000.0
Toledo 15.0 100.0 1500.0
Cleaveland 20.0 100.0 2000.0
Youngstown 45.0 100.0 4500.0
Athens 63.0 100.0 6300.0
Collumbos 85.0 100.0 8500.0
Erie 12.0 100.0 1200.0
Pittsburg 35.0 100.0 3500.0
Morgantown 45.0 100.0 4500.0
Lekington 89.0 100.0 8900.0
Fort-Wine 74.0 100.0 7400.0
Boffalo 54.0 100.0 5400.0
Most economiccal cost center for our assumed scnario is Detroit