Question: DO ALL FORMULAS ON EXCEL WORK THEM OUT STEP BY STEP! FOLLOW DIRECTIONS As a logistics specialist, you are responsible for meeting customer demand. One

DO ALL FORMULAS ON EXCEL WORK THEM OUT STEP BYDO ALL FORMULAS ON EXCEL WORK THEM OUT STEP BY STEP! FOLLOW DIRECTIONS

As a logistics specialist, you are responsible for meeting customer demand. One of your customers places orders for you to supply four products by the end of October. You have limited capacity for raw material and other facilities. You are required to satisfy customer demand by manufacturing the products and/or buying from a supplier. After consulting with the production department, you obtained the following information. Product A Product B Product C Product D Order quantity 3000 2500 3500 2000 Hours required on machine X per 4 3 2 5 unit Hours required on machine Y per 2 5 3 unit Raw material in kilos 3 2 4 4 Time needed for Packaging per unit 24 minutes 36 minutes 30 minutes 45 minutes 4 Available monthly hours per month: 10,000 on machine X, 8000 on machine Y, and 6,000 for packaging. Purchased items from your supplier are already packaged. Available raw material per month: 9,000 kilos Product A Product B Product C Product D Cost per unit to make in $ 60 83 85 90 Cost per unit to buy in $ 70 95 120 110 Formulate this problem, that is, define variables, write the objective function and constraints. (30) Design a Microsoft Excel initial worksheet. (20) Solve this problem. You are required to print and attach the initial worksheet, the final worksheet, Solver parameters window, and the solution written by you (Typed or handwritten). (30) Suppose your supplier wants you to buy at least 2000 units of product C, what changes would you make to your formulation? (10) Suppose you have raw material in abundance (unlimited quantity), what changes would you make to your formulation? (5) Suppose machine X breaks down for a few days and you lose 2,000 machine hours in October what changes would you make to your formulation

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!