Reference no: EM132956927

Question - Consider the following weekly resource allocation problem faced by the production manager of XYZ Company. XYZ sells three types of computers; the PC (type X), the notebook computer (type Y), and the workstation (type Z). The net profit from each is Type X: $350; Type Y: $470; and Type Z: $610 where net profit equals the sales price of each computer minus the direct costs of purchasing components, producing computer cases, and assembling and testing the computer. Assume that all production during any given week can and will be sold immediately. For a particular week, 120 hours are available on the Type-A test equipment, where X and Y's are tested, and 48 hours are available on the Type-B test equipment, where Z's are tested. The testing of each computer takes one hour, regardless of type. Production is constrained by the availability of 2000 labor hours for product assembly. Each Type X takes 10 labor hours, type Y takes 15 labor hours and type Z takes 20 labor hours. Other activities have virtually unlimited resources. (i) Construct, by hand, an LP model of this problem.(ii) Use the SOLVER in Excel to solve this problem. Interpret your output.

Solve this problem using Solver in Excel and provide answers to the following questions:

a. How many PCs, Notebook computers, and workstations should be produced?

b. What is the total profit?

c. Is this an optimal solution?

d. Please submit your supporting Excel spreadsheet.

e. If you were given additional funding, where would you invest that money to increase your profit and why? (Hint: testing equipment? If so, which ones A, B, or both? production labor? Both testing equipment and production labor? make sure to articulate the reason(s) for your investment decision.