Explore BrainMass

# need help in putting my solution in excel

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

i am not good in excel working... can anyone please put my two problems in excel file....here the file with solution attached.

Q. 7 Solution
(a) Draw the precedence diagram
Precedence diagram can be drawn as below:

(b) What is the workstation cycle time?

Workstation time =7 ½ hours/ 300 per day
= 450 /300 = 1.5 min or 90 seconds
c. What is the theoretical minimum number of workstations?

No. of workstations = (70+40+.................+ 25)/ 90
= 410 / 90 = 4.555 = 5
(d). Assign tasks to workstations using the longest operating time.

Job is in station 1: a and d, time require is 70+10 = 80 second and
Idle time of station 1 = 90-80 =10 second

Job is in station 2: b and c, time require is 40+45 = 85 second and
Idle time of station 2 = 90-85 =05 second

Job is in station 3: g and j , time require is 60+25 = 85 second and
Idle time of station 3 = 90-85 =05 second

Job is in station 4: e and h , time require is 30+50 = 80 second and
Idle time of station 4 = 90-80 =10 seconds

Job is in station 5: f, I, k, and l , time require is 20+15+20+25 = 80 second and
Idle time of station 5 = 90-80 =10 seconds
e. What is the efficiency of your line balance?

Efficiency = 410 / 5*90 = 91.11%

(f) . Suppose demand increases by 10 percent. How would you react to this? Assume that you can operate only 7 1/2 hours per day.
If we will increase demand by 10% . that mean now demand is 300+30 = 330 per day
New cycle time = 450 / 330 = 1.36 min = 82 seconds
No. of work stations = 410 / 82 = 5
Mean no change in theoretical work station. But in actual, the work station increase because station 2 and station 3 cannot work (cycle time is less than time required at 2 and 3 work station.). So that, we require additional work station.
Q. 11
The flow of materials through eight departments is shown in the table below. Even though the table shows flows into and out of the different departments, assume that the direction of flow is not important. In addition, assume that the cost of moving material depends only on the distance moved.
DEPARTMENTS
1 2 3 4 5 6 7 8
1 20
2 15 25 4
3 5 40 5
4 5 10
5 1 20 30
6 20
7 3 10
8 5
a. Construct a schematic layout where the departments are arranged on a 2 — 4 grid with each cell representing a 10 — 10-meter square area.
b. Evaluate your layout using a distance-times-fl ow measure. Assume that distance is measured rectilinearly (in this case, departments that are directly adjacent are 10 meters apart and those that are diagonal to one another are 20 meters apart).
(a)
Initial Layout on a 2-4 grid
Dept. 1 Dept. 2 Dept. 3 Dept. 4
Dept. 5 Dept. 6 Dept. 7 Dept. 8

Base on given data you have to find Nonadjacent Loads as given below:
1 2 35
1 5 1
2 3 30
2 7 4
3 4 45
3 5 5
4 5 30
4 7 3
5 6 30
7 8 15

Nonadjacent Loads are between 2-7 dept., 3-5 dept., 4-5 dept. and 4.7 dept... Total is 52.
Our objective here is to try to find minimum non adjacent load.

Total load distance base on give distance
To-From Total load Distance between depts. Total distance
1 2 35 10 = 35*10 = 350
1 5 1 10 = 1*10 = 10
2 3 30 10 30*10 = 300
2 7 4 20 4*20 = 80
3 4 45 10 45*10 = 450
3 5 5 30 5*30 = 150
4 5 30 30 30*30 = 900
4 7 3 20 3*20 = 60
5 6 30 10 30*10 = 300
7 8 15 10 15*10 = 150
Total ` 2750

Dept. 4 Dept. 3 Dept. 2 Dept. 1
Dept. 5 DEPT. 6 DEPT. 7 Dept. 8

To-From Total load Distance between depts. Total distance
1 2 35 10 = 35*10 = 350
1 5 1 40 = 1*40 = 40
2 3 30 10 30*10 = 300
2 7 4 10 4*10 = 40
3 4 45 10 45*10 = 450
3 5 5 20 5*20 = 100
4 5 30 10 30*10 = 300
4 7 3 30 3*30 = 90
5 6 30 10 30*10 = 300
7 8 15 10 15*10 = 150
Total ` 2120

We minimize total load distance from 2750 to 2120 by reshifting the departments.