Page

1

GDPM 540

:

Quantitative Decision Modeling

Assignment #

2

Due:

March

15, 2021

Refer to the last page for a suggested format for assignments

Problem 2

(

50

marks)

Goldrite

Company manufacturers, assembles and rebuilds material handling equipment used in

wa

rehouses and distribution. One product, called a Liftmaster, is assembled from four components: a

frame, a motor, two supports, and a metal strap.

Goldrite

s

production sch

edule calls for 10

,000

Liftmasters to be made next month.

Goldrite

purchases the mot

ors from an outside supplier, but the

frames, supports, and straps may either be manufactured by the company or purchased from an outside

supplier. Manufacturing and purchase costs per unit are as follows:

Component

Manufacturing Cost

Pu

rchase Cost

Frame

$35

.00

$50

.00

Support

$11.00

$15.00

Strap

$5.0

0

$7.

0

0

Three departments are involved in the production of these components. The time (in minutes per unit)

required to process each component in each department and the

available capacity (in hours) for

the

three departments are as follows:

Department

Production Time per unit (minutes)

Time Available

Frame

Support

Strap

Hours

Cutting

3

1

0.3

9

00

Milling

2

1

0

50

0

Shaping

2

2

1

800

Except as noted, use only the

Excel Model and Sensitivity Report to

answer the questions below.

Some questions below only require you to report numbers that are easily read from your model

or sensitivity report. Other questions may require calculations, and you must show these

calculations to receive full marks. Question

s are independent.

(a)

Set up the above problem in Excel using the style that we used in class. Make sure that your

spreadsheet model is logical, well organized and easy to understand. Solve using Solver.