John Spillbury, the managing director and owner of iPiece Puzzles, a small but growing jigsaw puzzle manufacturer is planning to establish a stronger position for the company on the market. Computers, game consoles, apps and mobile phones not-withstanding, he feels that there is still a place for jigsaw puzzles. In particular, he is aiming for durable high-quality product lines manufacturing chipboard and wooden backed puzzles where elaborate and custom art designs can be printed with higher quality lithographic processes to attain a sheen finish with smooth edged pieces having good bonding between picture design and the backing material. He hopes these will standout against countless companies offering low-priced less durable puzzles with few custom designs that use simple bonding and coarse low-quality cardboard backing.

John Spillbury wishes to make all of his puzzle piece products using higher-grade chipboard and wooden backing with durable bonding that offers joy to piece together the challenging and elaborate designs for children and adults alike. From surveys targeting a cross-section of the market from school and university students to parents and hobbyist connoisseurs, he knows that most people are willing to buy good quality wooden or chipboard backed puzzles. He has also found that most families would prefer to pay a bit more to have lasting puzzles with beautiful artwork and photographic designs with higher quality finishing and lasting bonding as long-lasting family items associated with joyful memories of times spent together.

iPiece Puzzles are available in a wide range of art and photographic designs in most reputable stores for toys and stationery as well as at art galleries and museums. However, the company only makes three base products. A 14x20 500-piece puzzle, a 14x10 250-piece puzzle and a 6x10 50-piece puzzle. A vast array of art and photographic designs can be printed on the above sizes using iPiece Puzzles innovative lithographic printing processes and precision cutting technology. The per-unit profit of the items are $20 for the 500-piece puzzle, $12 for the 250-piece and $8 for 50-piece. These amounts include the cost of materials, labour and quality control as applicable.

John Spillbury believes that the company can sell any number of puzzles it produces, but the production is limited by resource availability. The recent COVID19 pandemic situation and cash-flow problems have caused the suppliers of these resources, in particular the sustainably sourced woodchips and wood suppliers to only sell in limited amounts to iPiece Puzzles. In particular, John can only source at most 2400kg of grade I Woodchips, 1300kg of grade II Soft Wood and 900kg of bonding adhesive each week from his suppliers. It is unlikely that these figures will change in the immediate future. However, the suppliers are willing to sell any amount John needs up to this limit and when he requires them without

the need to buy fixed quantities of resources in advance of the production making these costs variable costs rather than fixed costs to produce iPiece Puzzles.

Each 500-piece puzzle requires 0.6kg of grade I Woodchips to be used with 0.3kg of grade II Soft Wood and 0.2kg of bonding adhesive while each 250-piece puzzle requires 0.4kg of grade I Woodchips, 0.2kg of grade II Soft Wood and 0.1kg of bonding adhesive. The 50-piece requires 0.15kg of grade I Woodchips, 0.1kg of grade II Soft Wood and 0.1kg of bonding adhesive.

A recent market survey has also indicated significant higher demand for 50-piece puzzles for children, with families spending more time at home due to COVID19 related lockdown restrictions. Therefore, iPiece puzzles has determined that going ahead, at least 40% of the produced puzzles should be 50- piece puzzles. John is trying to come up with a weekly plan for iPiece puzzles product mix using LP. While John knows that other constraints including labour will also need specific consideration, he wants a simple first-pass model that is restricted to the above primary resource constraints on sustainably sourced wood and adhesive together with the recent demand constraint for 50-piece puzzles.

this is multi part question

Question: Considering the above, formulate the weekly production mix problem for iPiece Puzzles as an LP Model. Implement the formulation in Excel following goals/guidelines for good spreadsheet design and solve using Solver. Then answer the following questions in Section B assuming each question as independent of the other questions unless explicitly referred to.

the excel file is required

1. What is the optimal mix of weekly puzzle products and the optimal value for weekly profit?

2. Is the LP solution degenerate? Explain your answer.

3. Are there alternate optimal solutions to the problem? Explain your answer.

4. What are the marginal values of one unit of Grade II Soft Wood and Bonding Adhesive used?

5. If an additional 20kg of grade II Soft Wood is available through local sourcing for $35 more per kg than the normal price, should the company go ahead with the purchase? Explain your answer.

6. If iPiece Puzzles goes ahead with the purchase as detailed in Q5, what is the new optimal product mix and the new optimal profit value? Explain your answer.

7. Suppose iPiece Puzzles is able to source some additional bonding adhesive at $30 per kg over its normal cost in 200-kg packages, should John Spillbury go ahead with the purchase? Explain your answer.

8. If after negotiation the supplier in Q7 agrees to supply bonding adhesive in 50kg packages at the same additional price per kg, how many packages should be bought?

9. The usual supplier for grade II Soft Wood is only able to send 1000kg for a particular week instead of 1300kg due to a logistics issue. If iPiece Puzzles only gets the 1000kg, what is the new product mix to produce and the optimal solution? Explain your answer.

10. iPiece Puzzles has the opportunity to sell some of their available grade II Soft Wood at $45 per kg to a non-competitor company. If the company is willing to buy 50kg, should John Spillbury sell the grade II Soft wood? If not, what amount would iPiece Puzzles be willing to sell? Explain your answer.

11. iPiece Puzzles is planning to add a new 12x10 100-piece puzzle to its products mix with a design that requires 0.25kg of grade I Woodchips, 0.15kg of grade II Soft Wood and 0.1kg of bonding adhesive. If the company expects to sell a 100-piece puzzle with a unit profit of $10, should the design be approved? Explain your answer.

12. If the unit profit of 50-piece puzzles falls by $1 and the unit profit on 500-piece puzzles increases by $0.50 what effect will it have on the optimal solution? Explain your answer.

formulate LP model