assignment 7 21


Assignments must be completed on MindTap AND a completed workbook with your full completed solutions must be submitted via Sakai. You must submit both in order to receive any credit.

Save your time - order a paper!

Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

Order Paper Now

Hardcopies or copies emailed will not be graded.

There is no option for late submissions in MindTap. Failure to submit your submit your assignment on MindTap before the due date will result in zero credit.

Submit your worked data in one single MS Excel Workbook. Start your solution set by using the assignment shell provided on Sakai. Use appropriately labeled worksheets for each problem/section of a problem.

Pay very close attention to the final presentation of your work and make sure it is print-ready. Prepare all spreadsheets so that they are clear, attractive and easy for the untrained eye to follow and understand. While accurate content and precise execution of the techniques is critical, formatting, typographical and grammatical acuteness is also very important. General sloppiness and inconsistent formatting will lower your grade.

Note: Ignore MindTap warning about text-based answers. All open ended questions will be graded as well. In fact, your grade will depend equally on the accuracy of your analytical techniques and your interpretation of the results.

Assignment files should be named as follows:

  • Asg#_FirstInitialLastname
  • e.g. Assignment 1 for Michael Phelps would be named Asg1_MPhelps.xlsx

Question 1. 50 points

In PC Tech’s product mix problem that we solved this week, assume there is another PC model, the VXP, that the company can produce in addition to Basics and XPs.

The following inputs are available:

  • The most it can sell are 600 Basics, 1200 XPs, and 50 VXPs
  • Each Basic sells for $300 , each XP for $450, each VXP sells for $560
  • The cost of component parts for a Basic is $150; for an XP is $225; for a VXP is $275
  • There are at most 10,000 assembly hours and 3000 testing hours available.
  • Each Basic requires five hours for assembling and one hour for testing,; each XP requires six hours for assembling and two hours for testing; each VXP requires eight hours for assembling and three hours for testing
  • Each labor hour for assembling costs $11 and for testing costs $15.

From these available inputs, calculate the following inputs:

  • Unit Margins = (Selling Price) – (Labor hours for assembly) * (Cost per labor hour assembling)
    – (Labor hours for testing) * (Cost per labor hour testing)
    – (Cost of component parts)
  • Labor used = (Labor hours) * (Number to produce)
  • Profit = (Unit margin) * (Number to produce)
  • Total profit = (Net profit for Basic) + (Net profit for XP) + (Net profit for VXP)

A.Use an LP spreadsheet model to find the best mix of computer models that stays within the company’s labor availability and maximum sales constraints and maximizes total profit.
What is the optimal product mix? How many computers of each model should PC Tech produce?

B.You should find that the optimal solution is not integer-valued. If you round the values in the decision variable cells to the nearest integers, is the resulting solution still feasible?
(Note: You must manually round off the cell values. Simply changing the display of decimal points will not alter the cell value.)
If the resulting solution is no longer feasible, how might you obtain a feasible solution that is at least close to optimal?

Question 2. 50 points

A furniture company manufactures desks and chairs.

  • Each desk uses four units of wood, and each chair uses three units of wood.
  • A desk contributes $400 to profit, and a chair contributes $250.
  • Marketing restrictions require that the number of chairs produced be at least twice the number of desks produced.
  • There are 2000 units of wood available.
  • Calculate Profit = (Unit margin) * (Units produced) for all furniture.

A.Use Solver to maximize the company’s profit. How many desks and chairs should the furniture company produce?

DO NOT Answer Parts B and C of Question 2.