Question 5 (Total of 31 marks)

Jill Bright is a production manager at Bill's Gate Computers. The company purchases a basic computer at wholesale prices and then adds a display, extra memory cards, extra USB ports, or a CD-ROM or DVD-ROM drive. The computers, with the added features, are then resold at retail prices. The company produces three models: Simplex, Omniplex and Multiplex. Each Simplex generates $200 in profit, while for each Omniplex the profit is $300. The profit for a Multiplex is $250. The Simplex model has fewer add-ons, so it requires only 60 minutes of installation time. The Omniplex has more add-ons and requires 150 minutes of installation time. Multiplex has more add-ons than Simplex, but not as many as Omniplex, and it requires 90 minutes of installation time. There are five employees available to do the installation work.

After several weeks of operation, the sales department does some market research. Based on this research, it is decided that the combined sales of Simplex and Multiplex models cannot exceed 20 units. Moreover, no more than 16 Omniplex computers can be sold in a week. Bill's Gate employees also notice that they are getting many returns because of a problem with one of the add-ons. They realize that they need to test their finished products before shipping them. The task of testing the computers is assigned to only one of the installers. To accommodate this change, the other four installers agree to work 10 hours per week, so that the total available installation time remains 40 hours per week. The employee who does the testing also works 10 hours per week. It takes her 18 minutes to test a Simplex computer. Each Omniplex and each Multiplex both require 24 minutes testing time.

Jill must decide the rate of production per week for each computer model in order to maximize the company's weekly profit. Jill has chosen to use linear programming and she has asked for your help.

Questions that follow will guide you through the steps to obtain the solution and discuss the results.

(a)(12 marks) Formulate a linear programming model for this problem. filling in the template below. Type up the full mathematical model in Word and include it here.

For full marks fill in the template provided below, clearly indicating:

The decision variables. Define them precisely.

The objective. Using your decision variables, formulate the objective function.

The constraints. Using your decision variables, formulate these constraints.

Decision Variables

Objective and Objective Function

Constraints

(b)(7 marks) Use EXCEL Solver to obtain a solution to the linear programming problem from part (a), together with an Answer Report and a Sensitivity Report.

Provide a screenshot of your solved EXCEL spreadsheet and the Answer and Sensitivity reports below.

(c)(6 marks) One of Bill's Gate competitors has launched a new advertising campaign and Jill worries that the profit per each Simplex model may decrease to $140.

Would the solution obtained in part (b) still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and Bill's Gate Computers profit change, if at all? Attach a new set of results if necessary.

(d)(6 marks) The other four installers agree to work 11 hours per week, so that the total available installation time increases to 44 hours per week

Would the solution obtained in part (b) still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and Bill's Gate Computers profit change, if at all? Attach a new set of results if necessary.