Excel代写 | FIT 3158 Business Decision Modelling

这个作业是用Excel完成业务决策建模
FIT 3158 Business Decision Modelling

Assignment – Part 1
(30 marks in total contributing to 10% of in-semester assessment.)
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)
Section B
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.