How much should the company be willing to pay to acquire additional capacity in the ‘Assembly minutes’ area?

SKU: Ques60 Category:

What are the optimal solution and the total profit

 

Order Now

On the tab labeled “Original Problem”, I have provided the modeling of Problem #17 in Chapter 3.    Carefully read the statement of the problem in the textbook and then review the model to familiarize yourself with the integration of the LP into Excel.   Reference the ‘Par.xls’ example from the text.

First requirement is to fill in the formulas in the GREEN cells for the Objective function and the constraints.  There is no need to change the values in the RED cells.

Next, load the solver by filling in the ‘Set Target Cell‘, ‘By Changing Cells‘, and ‘Subject to the Constraints‘ boxes.

Once you have loaded the solver and solved the problem, you are required to obtain the sensitivity analysis.  If you do this correctly, the ‘Sensitivity Report’ will show up in a new tab in the bottom of the sheet.

From there, proceed to the Solutions tab where you will find five questions.  To answer each question, you will be required to consult the sensitivity analysis and locate the correct information from the analysis to answer each question.  All answers should come from solving the model (after loading the solver) and obtaining and consulting the sensitivity data, not by resolving the model multiple times!

In your response to each question, you should begin with a written sentence(s) that explains where specifically in the sensitivity analysis you found the information you need to answer each question followed by a sentence answering each respective question.  Each question is worth 20 points:  10 points for explaining where in the sensitivity analysis you found the information necessary to answer the question (mechanical component) and 10 points for a clear, concise, grammatically correct answer to the question (written component).  Questions should be answered on the Solutions tab and typed in unbolded print right after the statement of the question.

Good luck, and HAVE FUN!

 

Microsoft Excel 12.0 Sensitivity Report

Worksheet: [MGMT430-Whetsel.xls]Original Problem

Report Created: 6/9/2014 8:00:06 PM

excel1

Porsche Club

DRBDRW
Number to Make16000Total Profit
Unit Profits$200$280$320,280

 

ConstraintsConstraintsConstraintsConstraintsConstraints
Steel available202532,00040,000
Manufacturing minutes4010064,000120,000
Assembly minutes60409600096,000

 

Question 1 : 

What are the optimal solution and the total profit?

 

Question 2 : 

If the cost to make the DRW decreases to $260 per unit, how would the optimal solution change?

 

Question 3: 

How much should the company be willing to pay to acquire additional capacity in the ‘Assembly minutes’ area?  Explain in detail.

 

Question 4 : 

How much should the company be willing to pay to acquire additional ‘Steel’?  Explain in detail

 

Question 5: 

How much will the objective function, ‘Total Profit’, change if there was one additional hour of Manufacturing capacity available?

 

 
 

Writing your homework and assignments all on your own is a difficult task. So, Assignments4u has made it easy for students by helping them in writing their assignments. Assignments4u will assist you in completing your tasks, and you can approach us with all your assignment, homework, and essay writing requirements.

We have over 4350+ experienced writers working as experts in different streams of study. Get all your academic doubts clarified and take pride in learning subjects like history, math’s or law. Our assignment help and essay help is available in countries like USA, Australia, New Zealand, Singapore and many more. Get accounting assignment help, corporate nursing assignment help, marketing assignment help or else statistics assignment help in exchange for a nominal price.

Reviews

There are no reviews yet.

Be the first to review “How much should the company be willing to pay to acquire additional capacity in the ‘Assembly minutes’ area?”

Your email address will not be published. Required fields are marked *

Sorry no more offers available

When assignments gets tough, get tougher

Want a fresh solution like this one? 
We are available 24/7
ORDER NOW
+1
Share
Tweet
Pin
Call Back