Excel expert needed

Correct answers in red color

 

I.                    Questions 1 – 10 concern the following problem

 

Remember that, in order to maximize their profits, the Wingreen Humidor Company found that they should make 800 cherry humidors and 330 mahogany humidors every month.   However, management has been beset by customer complaints that Wingreen’s product has not been arriving on time.  A brief investigation has revealed what seem to be unusually high shipping costs and reports from regional shipping/ receiving managers that they are forced to temporarily warehouse product from one month to the next because the demand for humidors has apparently exceeded Wingreen’s shipping capacity.  You have been assigned the task of evaluating the shipping system in search of a solution.

 

Wingreen’s headquarters is located in Brooksville, FL, but they also have production facilities in Dade City and Ridge Manor. Shipments must arrive at five different regional distribution sites: Atlanta, Tallahassee, Jacksonville, and their two ritzy stores at Palm Beach and Disney World. 

 

Each store requires the following number of humidors:

Tallahassee                   125 humidors

Atlanta                         150 humidors

Palm Beach                  350 humidors

Disney                          380 humidors

Jacksonville                  125 humidors

 

Capacity at the production sites is 400 each.

           

You are given the following data regarding shipping costs:

                       

From Brooksville to Atlanta $1.50, to Tallahassee $1.00, to Jacksonville $2.00, to Palm Beach $0.55, to Disney World $0.45. From Dade City to Atlanta $1.60, to Tallahassee $1.10, to Jacksonville $2.10, to Palm Beach $0.50, to Disney World $0.40. From Ridge Manor to Atlanta $1.55, to Tallahassee $0.95, to Jacksonville $1.90, to Palm Beach $0.60, to Disney World $0.50.

 

Your task is to determine the shipping solution with the current system and the minimum cost of operation.  Submit your solution (show your work). (50 points).  Also, don’t forget to answer questions 1 – 10. (50 points).

 

1.         T          F          The minimum cost is $928.75.

 

2.         T          F          The amount shipped from Ridge Manor is a binding constraint.

 

3.         T          F          An optimized model will ship 100 humidors from Brooksville to Palm Beach.

 

4.         T          F          The objective coefficient of the Brooksville to Jacksonville shipping capacity may be increased infinitely without changing the optimum values of the decision variables.

 

5.         T          F          The objective coefficient of the Dade City to Disney World shipping capacity may be increased infinitely without changing the optimum values of the decision variables.

 

6.         Which of the following is not a binding constraint?

a.         Brooksville Shipped.

b.         Dade City Shipped.

c.         Ridge Manor Shipped.

d.         Jacksonville Received.

 

7.         In an optimized model, Ridge Manor will not ship any units to

a.         Atlanta

b.         Disney

c.         Tallahassee

d.         Jacksonville

 

8.         In an optimized model, how many humidors will be shipped from Dade City?

a.         250

b.         300

c.         350

d.         none of the above.

 

9.         The sensitivity report on the shipping to Palm Beach from Dade City indicates that

a.         it may be increased infinitely without changing the optimum model parameters.

b.         it may be increased by 5 units without changing the optimum model parameters.

c.         it may be increased by 40 units without changing the optimum model parameters.

d.         none of the above.

 

10.       The shadow prices indicate that the model is most sensitive to changes in the ____ constraint.

a.         Tallahassee

b.         Disney

c.         Jacksonville

d.         Palm Beach