Please provide solution in EXCEL format.
PRODUCTION. Minnesota Fabrics produces three sizes of comforters (full, queen, and king size) that it markets to major retail establishments throughout the country. Due to contracts with these establishments, Minnesota Fabrics must produce at least 120 of each size comforter daily. It pays $0.50 per pound for stuffing and $0.20 per square foot for quilted fabric used in the production of the comforters. It can obtain up to 2700 pounds of stuffing and 48,000 square feet of quilted fabric from its suppliers.
Labor is considered a fixed cost for Minnesota Fabrics. It has enough labor to provide 50 hours of cutting time and 200 hours of sewing time daily. The following table gives the unit material and labor required as well as the selling price to the retail stores for each size comforter.
Quilted Cutting Sewing
Stuffing Fabric Time Time Selling
(pounds) (sq. ft.) (minutes) (minutes) Price
Full 3 55 3 5 $19
Queen 4 75 5 6 $26
King 6 95 6 8 $32
a. Determine the daily production schedule that maximizes total daily gross profit (= selling price - material costs). How much of the available daily material and labor resources would be used by this production schedule?
b. What is the lowest selling price for queen size comforters that Minnesota Fabrics could charge while maintaining the optimal production schedule recommended in part a?
c. Suppose Minnesota Fabrics could obtain additional stuffing or quilted fabric from supplementary suppliers. What is the most it should be willing to pay for:
i. An extra pound of stuffing? Within what limits is this valid?
ii. An extra square foot of quilted fabric? Within what limits is this valid?
iii. An extra minute of cutting time? Within what limits is this valid?
iv. An extra minute of sewing time? Within what limits is this valid?
d. Suppose the requirement to produce at least 120 king size comforters were relaxed. How would this affect the optimal daily profit?