Instructions

·         Answer the following product mix problem in Excel using the Solver.  Your final answer should show the number of Basic and XP computers to produce.

·         As stated, your solution should be in an Excel document, which you will submit by the due date.  I am giving you a week to complete the assignment.

·         You can work on the assignment in groups of up to 4 people (and you can also work on it individually if you prefer).  If you do work in a group, you can have just one member of the group submit the assignment, but be sure that the names of all members of the group are on the assignment. 

Problem

The PC Tech Company assembles and then tests two models of computers, Basic and XP. For the coming month, the company wants to decide how many of each model to assemble and then test. No computers are in inventory from the previous month, and because these models are going to be changed after this month, the company doesn’t want to hold any inventory after this month. It believes the most it can sell this month are 600 Basics and 1200 XPs. Each Basic sells for $300 and each XP sells for $450. The cost of component parts for a Basic is $150; for an XP it is $225. Labor is required for assembly and testing. There are at most 10,000 assembly hours and 3,000 testing hours available.  Each labor hour for assembling costs $11 and each labor hour for testing costs $15. Each Basic requires five hours for assembling and one hour for testing, and each XP requires six hours for assembling and two hours for testing. PC Tech wants to know how many of each model it should produce (assemble and test) to maximize its net profit, but it cannot use more labor hours than are available, and it does not want to produce more than it can sell.