Supply Chain Management Course . Need help with this problem please. Show everything graph, formula , excel .......

1.

A furniture manufacturer produces two types of tables (country and contemporary) using three types of machines. The time required to produce the tables on each machine is given in the following table.

MachineCountryContemporaryTotal Machine Time Available per WeekRouter1.52.01,000Sander3.04.51,900Polisher2.51.51,400

Country tables sell for $350 and contemporary tables sell for $450. Management has determined that at least 20% of the tables made should be country and at least 30% should be contemporary. How many of each type of table should the company produce if it wants to maximize its revenue? (LetX₁be the number of country tables to produce andX₂the number of contemporary tables to produce.)

(a)Formulate an LP model for this problem to maximize revenue (in dollars).

MAX................................................................

Subject to:Router...........................................

Sander...............................................................

Polisher................................................................

Minimum country tables..........................................

Minimum contemporary tables.........................................

X₁,X₂0

(b) Create a spreadsheet model for this problem and solve it using Solver. What is the optimal solution? (Round your answers to the nearest integer.)

(X₁,X₂) =................................

(c) How will your spreadsheet model differ if there are 25 types of tables and 15 machine processes involved in manufacturing them?

• The spreadsheet model would have only more constraints.
• The spreadsheet model would have only more variables.
• The spreadsheet model would have more variables and constraints.
• The spreadsheet model would not change.

Show Work with excel formula and graph.

2.

Bearland Manufacturing produces 4 different types of wood paneling. Each type of paneling is made by gluing and pressing together a different mixture of pine and oak chips. The following table summarizes the required amount of gluing, pressing, and mixture of wood chips required to produce a pallet of 50 units of each type of paneling:

Resources Required per Pallet of Paneling TypePaneling TypeTahoePacificSavannahAspenGlue (quarts)505010050Pressing (hours)1505010050Pine chips (pounds)400200300500Oak chips (pounds)750500250500

Assume the company has 6,000 quarts of glue;7,400hours of pressing capacity;30,000 poundsof pine chips; and62,500 poundsof oak chips available in the next production cycle. Further assume that each pallet of Tahoe, Pacific, Savannah, and Aspen panels can be sold for profits of$1,250,$700,$800,and$450,respectively. Finally, for marketing purposes, the company wants to produce at least 4 pallets of each type of paneling. (LetX₁,X₂,X₃,andX₄be the number of pallets of Tahoe, Pacific, Savannah, and Aspen paneling produced.)

(a)Formulate an LP model for this problem to maximize profit (in dollars).

MAX: .................................................................

Subject to:Glue (quarts) .................................................................

Pressing (hours) .................................................................

Pine chips (pounds) .................................................................

Oak chips (pounds) .................................................................

Tahoe (number pallets) .................................................................

Pacific (number pallets) .................................................................

Savannah (number pallets) .................................................................

Aspen (number pallets) .................................................................

(b)Create a spreadsheet mode for this problem, and solve it using Solver. What is the optimal solution?

(X₁,X₂,X₃,X₄) =................................

Show Work with excel formula and graph.

3.

Par, Inc., is a small manufacturer of golf equipment and supplies. Par's distributor believes a market exists for both a medium-priced golf bag, referred to as a standard model, and a high-priced golf bag, referred to as a deluxe model. The distributor is so confident of the market that, if Par can make the bags at a competitive price, the distributor will purchase all the bags that Par can manufacture over the next three months. A careful analysis of the manufacturing requirements resulted in the following table, which shows the production time requirements for the four required manufacturing operations and the accounting department's estimate of the profit contribution per bag.

The director of manufacturing estimates that 630 hours of cutting and dyeing time,600 hoursof sewing time,708 hoursof finishing time, and135 hoursof inspection and packaging time will be available for the production of golf bags during the next three months. (LetSbe the number of standard bags. LetDbe the number of deluxe bags.)

Suppose that Par's management encounters the following situations.

If each of these situations is encountered separately, what is the optimal solution and the total profit contribution?

(a)The accounting department revises its estimate of the profit contribution for the deluxe bag to $18 per bag.

The optimal solution occurs at(S,D) =........................with a profit of$............................

(b)A new low-cost material is available for the standard bag, and the profit contribution per standard bag can be increased to $20 per bag. (Assume that the profit contribution of the deluxe bag is the original $9 value.)

The optimal solution occurs at(S,D) =........................with a profit of$............................

(c)New sewing equipment is available that would increase the sewing operation capacity to725hours. (Assume that10S+ 9Dis the appropriate objective function.)

The optimal solution occurs at(S,D) =........................with a profit of$............................

Show Work with excel formula and graph please .