Operations Mgmt Problems -Excel --See Attachment

Details: Complete problems 4.1, 4.3, 4.5, 4.25, and 4.27 in the textbook

Submit one Excel file. Put each problem result on a separate sheet in your file. 

Problems Note: PX means the problem may be solved with POM for Windows and/or Excel OM.

4.1 The following gives the number of pints of type B

blood used at Woodlawn Hospital in the past 6 weeks:

WEEK OF PINTS USED

a) Forecast the demand for the week of October 12 using a

3-week moving average.

b) Use a 3-week weighted moving average, with weights of .1, .3,

and .6, using .6 for the most recent week. Forecast demand for

the week of October 12.

c) Compute the forecast for the week of October 12 using exponential

smoothing with a forecast for August 31 of 360 and a 5 .2. PX

WEEK OF	PINTS USED
August 31	360
September 7	389
September 14	410
September 21	381
September 28	368
October 5	374

4.2

YEAR	1	2	3	4	5	6	7	8	9	10	11
DEMAND	7	9	5	9	13	8	12	13	9	11	7

a) Plot the above data on a graph. Do you observe any trend,

cycles, or random variations?

b) Starting in year 4 and going to year 12, forecast demand using

a 3-year moving average. Plot your forecast on the same graph

as the original data.

c) Starting in year 4 and going to year 12, forecast demand using

a 3-year moving average with weights of .1, .3, and .6, using .6

for the most recent year. Plot this forecast on the same graph.

d) As you compare forecasts with the original data, which seems

to give the better results? PX

4.3 Refer to Problem 4.2. Develop a forecast for years 2

through 12 using exponential smoothing with a 5 .4 and a forecast

for year 1 of 6. Plot your new forecast on a graph with the

actual data and the naive forecast. Based on a visual inspection,

which forecast is better? PX

4.5 The Carbondale Hospital is considering the purchase

of a new ambulance. The decision will rest partly on the anticipated

mileage to be driven next year. The miles driven during the

past 5 years are as follows:

YEAR	MILAGE
1	3,000
2	4,000
3	3,400
4	3,800
5	3,700

4.25 The following gives the number of accidents that

occurred on Florida State Highway 101 during the past 4 months:

MONTH NUMBER OF ACCIDENTS

MONTH	NUMBER OF ACCIDENTS
January	30
February	40
MARCH	60
APRIL	90

Forecast the number of accidents that will occur in May, using

least-squares regression to derive a trend equation.PX

4.27 George Kyparisis owns a company that manufactures

sailboats. Actual demand for George’s sailboats during each of

the past four seasons was as follows:

	YEAR
SEASON	1	2	3	4
WINTER	1,400	1,200	1,000	900
SPRING	1,500	1,400	1,600	1,500
SUMMER	1,000	2,100	2,000	1,900
FALL	600	750	650	500

AR

George has forecasted that annual demand for his sailboats

in year 5 will equal 5,600 sailboats. Based on this data and the

multiplicative seasonal model, what will the demand level be for

George’s sailboats in the spring of year 5?

Details: Complete problems 6.12, S6.11, S6.20, S6.23, S6.27, and S6.35 in the textbook. √

Submit one Excel file. Put each problem result on a separate sheet in your file. 

6.12 Mary Beth Marrs, the manager of an apartment

complex, feels overwhelmed by the number of complaints she

is receiving. Below is the check sheet she has kept for the past

12 weeks. Develop a Pareto chart using this information. What

recommendations would you make?

WEEK GROUNDS

PARKING/

DRIVE

WEEK	GROUNDS	PARKING DRIVES	POOL	TENANT ISSUES	ELECTRICAL PLUMBING
1	√√√	√√	√	√√√
2	√	√√√	√√	√√	√
3	√√√	√√√	√√	√
4	√	√√√√	√	√	√√
5	√√	√√√	√√√√	√√
6	√	√√√√	√√
7		√√√	√√	√√
8	√	√√√√	√√	√√√	√
9	√	√√	√
10	√	√√√√	√√	√√
11		√√√	√√	√
12	√√	√√√	√√	√

S10 POOL

TENANT

ISS

S6.11 Twelve samples, each containing five parts, were taken

from a process that produces steel rods. The length of each rod in

the samples was determined. The results were tabulated and sample

means and ranges were computed. The results were:

SAMPLE	SAMPLE MEAN (in.)	RANGE (in.)
1	10.002	0.011
2	10.002	0.014
3	9.991	0.007
4	10.006	0.022
5	9.997	0.013
6	9.999	0.012
7	10.001	0.008
8	10.005	0.013
9	9.995	0.004
10	10.001	0.011
11	10.001	0.014
12	10.006	0.009

a) Determine the upper and lower control limits and the overall

means for x-charts and R-charts.

b) Draw the charts and plot the values of the sample means and

ranges.

c) Do the data indicate a process that is in control?

d) Why or why not? PX

UES

ELECTRICAL/

S6.20 Jamison Kovach Supply Company manufactures paper

clips and other office products. Although inexpensive, paper clips

have provided the firm with a high margin of profitability. Sample

size is 200. Results are given for the last 10 samples:

SAMPLE	1	2	3	4	5	6	7	8	9	10
DEFECTIVES	5	7	4	4	6	3	5	6	2	8

a) Establish upper and lower control limits for the control chart and

graph the data.

b) Is the process in control?

c) If the sample size were 100 instead, how would your limits and

conclusions change? PX

S6.23 The school board is trying to evaluate a new math

program introduced to second-graders in five elementary schools

across the county this year. A sample of the student scores on

standardized math tests in each elementary school yielded the following

data:

SCHOOL	NO. OF TEST ERRORS
A	52
B	27
C	35
D	44
E	55

SCHOOL NO. OF TEST ERRORS

Construct a c-chart for test errors, and set the control limits

to contain 99.73% of the random variation in test scores.

What does the chart tell you? Has the new math program been

effective? PX

S6.27 Meena Chavan Corp.’s computer chip production process

yields DRAM chips with an average life of 1,800 hours and

s = 100 hours. The tolerance upper and lower specification limits

are 2,400 hours and 1,600 hours, respectively. Is this process capable

of producing DRAM chips to specification? PX

S6.35 One of New England Air’s top competitive priorities

is on-time arrivals. Quality VP Clair Bond decided to personally

monitor New England Air’s performance. Each week for the past

30 weeks, Bond checked a random sample of 100 flight arrivals for

on-time performance. The table that follows contains the number of

flights that did not meet New England Air’s definition of “on time”:

SAMPLE

(

LATELIGHTS

SAMPLE (WEEK)	LATE FLIGHTS	SAMPLE (WEEK)	LATE FLIGHTS
1	2	16	2
2	4	17	3
3	10	18	7
4	4	19	3
5	1	20	2
6	1	21	3
7	13	22	7
8	9	23	4
9	11	24	3
10	0	25	2
11	3	26	2
12	4	27	0
13	2	28	1
14	2	29	3
15	8	30	4

SAMPLE

(WEEK)

a) Using a 95% confidence level, plot the overall percentage of late

flights (p) and the upper and lower control limits on a control

chart.

b) Assume that the airline industry’s upper and lower control limits

for flights that are not on time are .1000 and .0400, respectively.

Draw them on your control chart.

c) Plot the percentage of late flights in each sample. Do all samples

fall within New England Air’s control limits? When one falls outside

the control limits, what should be done?

d) What can Clair Bond report about the quality of service? PX

Details; Complete problems 7.5, 7.7, and 7.11 in the textbook.

Complete supplement problems S7.3, S7.5, S7.7, S7.11, S7.15, and S7.28 in the textbook.

7.5 Borges Machine Shop, Inc., has a 1-year contract for

the production of 200,000 gear housings for a new off-road vehicle.

Owner Luis Borges hopes the contract will be extended and

the volume increased next year. Borges has developed costs for

three alternatives. They are general-purpose equipment (GPE),

flexible manufacturing system (FMS), and expensive, but efficient,

dedicated machine (DM). The cost data follow;

7.7 Using the data in Problem 7.5, determine the best process

for each of the following volumes: (1) 75,000, (2) 275,000,

and (3) 375,0

7.11 Tim Urban, owner/manager of Urban’s Motor Court

in Key West, is considering outsourcing the daily room cleanup

for his motel to Duffy’s Maid Service. Tim rents an average of

50 rooms for each of 365 nights (365 × 50 equals the total rooms

rented for the year). Tim’s cost to clean a room is $12.50. The

Duffy’s Maid Service quote is $18.50 per room plus a fixed cost

of $25,000 for sundry items such as uniforms with the motel’s

name. Tim’s annual fixed cost for space, equipment, and supplies

is $61,000. Which is the preferred process for Tim, and why? PX

S7.3 If a plant has an effective capacity of 6,500 and an

efficiency of 88%, what is the actual (planned) output?

S7.5 Material delays have routinely limited production

of household sinks to 400 units per day. If the plant efficiency is

80%, what is the effective capacity?

S7.7 Southeastern Oklahoma State University’s business

program has the facilities and faculty to handle an enrollment

of 2,000 new students per semester. However, in an effort

to limit class sizes to a “reasonable” level (under 200, generally),

Southeastern’s dean, Holly Lutze, placed a ceiling on enrollment

of 1,500 new students. Although there was ample demand for

business courses last semester, conflicting schedules allowed only

1,450 new students to take business courses. What are the utilization

and efficiency of this system?

S7.11 The three-station work cell illustrated in Figure S7.7

has a product that must go through one of the two machines at

station 1 (they are parallel) before proceeding to station 2.

( Station 1 Machine A Capacity: 20 units/ per hour )

( Station 1 Machine A Capacity: 20 units/ per hour )

( Station3 Machine B Capacity: 12 units/ per hour ) ( Station2 Machine B Capacity: 5 units/ per hour )

12 ( Station1 Machine B Capacity: 20 units/ per hour )

a) What is the bottleneck time of the system?

b) What is the bottleneck station of this work cell?

c) What is the throughput time?

d) If the firm operates 10 hours per day, 5 days per week, what is

the weekly capacity of this work cell

􀀁S7.15 A production process at Kenneth Day Manufacturing

is shown in Figure S7.9. The drilling operation occurs separately

from, and simultaneously with, the sawing and sanding operations.

A product needs to go through only one of the three assembly

operations (the operations are in parallel).

a) Which operation is the bottleneck?

b) What is the bottleneck time?

c) What is the throughput time of the overall system?

d) If the firm operates 8 hours per day, 20 days per month, what

is the monthly capacity of the manufacturing process?

Figure S7.9 Info .

Sawing/6 Units per hour Sanding /6 Units per hour Assembley 07 units per hour

Welding/ 2 Units per hour Assembley .07 units per hour

Assembley .07 units per hour

Drilling/ 2.4 Units per hour

S7.28 James Lawson’s Bed and Breakfast, in a small historic

Mississippi town, must decide how to subdivide (remodel)

the large old home that will become its inn. There are three

alternatives: Option A would modernize all baths and combine

rooms, leaving the inn with four suites, each suitable for two to

four adults. Option B would modernize only the second floor; the

results would be six suites, four for two to four adults, two for

two adults only. Option C (the status quo option) leaves all walls

intact. In this case, there are eight rooms available, but only two

are suitable for four adults, and four rooms will not have private

baths. Below are the details of profit and demand patterns that

will accompany each option:

ANNUAL PROFIT UNDER VARIOUS DEMAND PATTERNS
ALTERNATIVES	HIGH	P	AVERAGE	P
(Modernize all)	$90,000	.5	$25,000	.5
(Modernize 2nd)	$80,000	.4	$70,000	.6
(Status quo)	$60,000	.3	$55,000	.7

Which option has the highest expected monetary value? PX

Details: Complete Problems 10.25, 10.29

Submit one Excel file. Put each problem result on a separate sheet in your file. 

10.25 Peter Rourke, a loan processor at Wentworth Bank,

has been timed performing four work elements, with the results

shown in the following table. The allowances for tasks such as this

are personal, 7%; fatigue, 10%; and delay, 3%.

OBSERVATIONS (MINUTES)
TASK ELEMENT	PERFORMANCE RATING (%)	1	2	3	4	5
1	110	.5	.4	.6	.4	.4
2	95	.6	.8	.7	.6	.7
3	90	.6	.4	.7	.5	.5
4	85	1.5	1.8	2.0	1.7	1.5

a) What is the normal time?

b) What is the standard time? PX

10.29. The Dubuque Cement Company packs 80-pound

bags of concrete mix. Time-study data for the filling activity are

shown in the following table. Because of the high physical demands

of the job, the company’s policy is a 23% allowance for workers.

a) Compute the standard time for the bag-packing task.

b) How many observations are necessary for 99% confidence,

within {5% accuracy?

OBSERVATIONS (SECONDS)
ELEMENT	1	2	3	4	5	PERFORMANCE RATING
Grasp and place bag	8	9	8	11		7		110
Fill bag	36	41	39	35	112a	85
Seal bag	15	17	13	20	18	105
Place bag conveyor	8	6	9	30b	35b	90

a) What is the normal time?

b) What is the standard time? PX

Details: Complete problems 9.11, 9.13, and 9.15 in the textbook.

Submit one Excel file. Put each problem result on a separate sheet in your file. 

9.11 Stanford Rosenberg Computing wants to establish an

assembly line for producing a new product, the Personal Digital

Assistant (PDA). The tasks, task times, and immediate predecessors

for the tasks are as follows:

TASK TIME (sec)

IM

TASK	TIME (SEC.)	IMMEDIATE PREDECESSORS
A	12	──—
B	15	A
C	8	A
D	5	B,C
E	20	D

MEDIATE

PREDECESSORS

A 12 —

B 15 A

C 8 A

D 5 B, C

E 20 D

Rosenberg’s goal is to produce 180 PDAs per hour.

a) What is the cycle time?

b) What is the theoretical minimum for the number of workstations

that Rosenberg can achieve in this assembly line?

c) Can the theoretical minimum actually be reached when workstations

are assigned? PX

9.13 Sue Helms Appliances wants to establish an assembly

line to manufacture its new product, the Micro Popcorn

Popper. The goal is to produce five poppers per hour. The tasks,

task times, and immediate predecessors for producing one Micro

Popcorn Popper are as follows:

TASK	TIME (MIN)	IMMEDIATE PREDECESSORS
A	10	—
B	12	A
C	8	A,B
D	6	B,C
E	6	C
F	6	D,E

a) What is the theoretical minimum for the smallest number of

workstations that Helms can achieve in this assembly line?

b) Graph the assembly line and assign workers to workstations.

Can you assign them with the theoretical minimum?

c) What is the efficiency of your assignment? PX

9.15 The following table details the tasks required for

Indiana-based Frank Pianki Industries to manufacture a fully

portable industrial vacuum cleaner. The times in the table are in

minutes. Demand forecasts indicate a need to operate with a cycle

time of 10 minutes.

ACTIVITY	ACTIVITY DESCRIPTION	IMMEDIATE PREDECESSORS	TIME
A	Attach wheels to tub	—	5
B	Attach motor to lid	—	1.5
C	Attach battery pack	B	3
D	Attach safety cutoff	C	4
E	Attach filters	B	3
F	Attach lid to tub	A, E	2
G	Assemble attachments	—	3
H	Function test	D,F,G	3.5
I	Final inspection	H	2
J	Packing	I	2

a) Draw the appropriate precedence diagram for this production

line.

b) Assign tasks to workstations and determine how much idle

time is present each cycle.

c) Discuss how this balance could be improved to 100%.

d) What is the theoretical minimum number of workstations? PX ( Station 2 Capacity: 5 units/ per hour ) ( Station 3 Capacity: 12 units/ per hour )