Bottleneck types Number of occurance

D 220
A 196
E 109
C 71
B 30
Total 626

Suggestion: Checklist of different types of process failures is constructed.

A Pareto chart indicates that bottleneck D & A accounted for 66.45% of the process failure.

This chart isn't available in your version of Excel.

Editing this shape or saving this workbook into a different file format will
permanently break the chart.
 Cumulative percentage
35.14%
66.45%
83.87%
95.21%
100.00%

ocess failures is constructed.

nted for 66.45% of the process failure.

e format will
 Need to fill Need to fill Need to fill Fomular Fomular Fomular
Year Quarter (x) Sales (y) AVG temp x*y x^2 Seasonal factor 1000

1 350 10 350 1 0.1765 900

2 510 15 1020 4 0.2323
2015 800
3 750 25 2250 9 0.3665
4 420 12 1680 16 0.2247 700

5 370 8 1850 25 600

6 480 14 2880 36
2016

Sales
500
7 860 28 6020 49
8 500 11 4000 64 400
9 450 7 4050 81 300
10 550 16 5500 100
2017 200
11 820 26 9020 121
12 570 14 6840 144 100
Total 78 6630 186 45460 650 0
0

Question
1 647 Using three-quarter moving average
2 578 five-quarter
Using weighted moving average,
three-quarter moving average
3 641 F= W1*D1 + W2*D2+ W3*D3+….

4 554
a 445
b 16.53846
5 Liner register: F = a+b*t
forecast sales for the
1st quarter of 2018 660

forecast sales for the Liner register: F = a+b*t

6 2nd quarter of 2018 677
0.1765 = (Sale of Q1/2015+ Sale of Q1/2016 + Sale of Q1/2017)/ Total Sale of 3 years
0.2323
7 Seasonal factor
0.3665
0.2247
Year (x) Sales (y) X*Y X^2 AVG X AVG Y a
1 2030 2030 1 2 2210 1850
8 2 2210 4420 4 => Y= a+ bx= 1850+ 180*4
(Use Slope 3 2390 7170 9
calculation) 6 6630 13620 14
Forecast for total https://www.calculator.net/slope-calculat
sales in 2018 2570
 The seasonally
adjusted forecast for
9 578 = Seasonal factor of last quarter x Forcecast of total sale 2018
the last quarter of
2018 is

slope 23 AVG temp (x) Sales (y) X*Y X^2 AVG X

10
Draw chart intercept 196.953 10 350 3500 100 15.5
=> Fomular forecast sales 656 15 510 7650 225
25 750 18750 625
12 420 5040 144
8 370 2960 64
14 480 6720 196
28 860 24080 784
11 500 5500 121
7 450 3150 49
16 550 8800 256
26 820 21320 676
14 570 7980 196
Total 186 6630 115450 3436
 1000

900

800

700

600 f(x) = 16.5384615384615 x + 445

Sales

500

400

300

200

100

0
0 2 4 6 8 10 12 14
Period

17)/ Total Sale of 3 years

b
180
=> Y= a+ bx= 1850+ 180*4

culator.net/slope-calculator.html?type=1&x11=1&y11=2030&x12=2&y12=2210&x=65&y=28
AVG Y b a
552.5 23 196.9529837

=> Y= a+bx = 169+ 20*23

Chart Title
1000
900
800 f(x) = 22.9385171790235 x + 196.952983725136
700
600
500
400
300
200
100
0
5 10 15 20 25 30
Note: Only delete in Yellow & white range. Blue is fomular

Need to fill Need to fill Need to fill

Quantity Cost Quantity Cost Quantity Cost

1 2 3
Period
Begin inventory 0
Regular 0 0
1 Overtime 0 0
Subcontract 0 0
Regular 0 0
2 Overtime 0 0
Subcontract 0 0
Regular 0
3 Overtime 0
Subcontract 0
Regular
4 Overtime
Subcontract
Total production 0 0 0
Demand

Fomular
Need to fill

Production Plan
Fomular Fomular Fomular Fomular Fomular

Overti End
Period Demand Reg. Prod me Sub Inventory
1 0 0 0 0 0
2 0 0 0 0 0
3 0 0 0 0 0
4 0 0 0 0 0
Total 0 0 0 0 0

Fomular

Reg. Cost 0
Overtime cost 0
Subcontract cost 0
Carrying cost 0
Total cost 0
Need to fill Formular Fomular
Quantity Cost Need to fill

Unused Max
4
Capacity Capacity Type Max capacity
0 0 Regular
0 0 0 Overtime
0 0 0 Subcontract
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
0 0 0
 Need to fill Need to fill Need to fill
Quantity Cost Quantity Cost Quantity Cost

1 2 3
Period
Begin inventory 10 0 3 6
Regular 60 50 15 53 0 56
1 Overtime 65 65 68 10 71
Subcontract 80 83 20 86
Regular 75 50 0 53
2 Overtime 75 65 0 68
Subcontract 80 60 83
Regular 75 50
3 Overtime 75 65
Subcontract 60 80
Regular
4 Overtime
Subcontract
Total production 70 230 300
Demand 70 230 300

Fomular
Need to fill

Production Plan
Fomular Fomular Fomular Fomular Fomular

Period Demand Reg. Prod OvertimSub End Inventory

1 70 75 75 20 110
2 230 75 75 60 90
3 300 75 75 60 0
4 80 75 5 0 0
Total 680 300 230 140 200

Fomular

Reg. Cost 50
Overtime cost 65
Subcontract cost 80
Carrying cost 3
Total cost 41750
Need to fill Formular Fomular
Quantity Cost Need to fill

Unused Max
4
Capacity Capacity Type Max capacity
9 10 Regular 75
59 0 75 Overtime 75
74 0 75 Subcontract 60
89 40 60
56 0 75
71 0 75
86 0 60
53 0 75
68 0 75
83 0 60
75 50 0 75
5 65 70 75
80 60 60
80 170 680
80
Note: Only delete in Yellow & white range. Blue is fomular

Need to fill Need to fill Need to fill

Quantity Cost Quantity Cost Quantity Cost

1 2 3
Period
Begin inventory 300 0 3 6
Regular 600 20 300 23 100 26
1 Overtime 25 28 31
Subcontract 28 31 34
Regular 1200 20 23
2 Overtime 25 28
Subcontract 28 31
Regular 1300 20
3 Overtime 200 25
Subcontract 28
Regular
4 Overtime
Subcontract
Total production 900 1500 1600
Demand 900 1500 1600

Fomular
Need to fill

Production Plan
Fomular Fomular Fomular Fomular Fomular

Overti End
Period Demand Reg. Prod me Sub Inventory
1 900 1000 100 0 500
2 1500 1200 150 250 600
3 1600 1300 200 500 1000
4 3000 1300 200 500 0
Total 7000 4800 650 1250 2100

Fomular

Reg. Cost 20
Overtime cost 25
Subcontract cost 28
Carrying cost 3
Total cost 153550
Need to fill Formular Fomular
Quantity Cost Need to fill

Unused Max
4
Capacity Capacity Type Max capacity
9 300 Regular
29 0 1000 Overtime
100 34 0 100 Subcontract
37 500 500
26 0 1200
150 31 0 150
250 34 250 500
23 0 1300
28 0 200
500 31 0 500
1300 20 0 1300
200 25 0 200
500 28 0 500
3000 750 7000
3000