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 14×20” 500-piece puzzle, a 14×10” 250-piece puzzle and a 6×10” 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.
Section A
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.
(3×2 marks, formulation and excel implementation)