Business Intelligence Modelling

Question 1: LP Model and Sensitivity Analysis – CarpetKing

CarpetKing is one of the largest manufacturers of carpet in both the state of Queensland and
in Victoria’s regional centre of Geelong. CarpetKing manufactures and sells residential and
commercial broadloom carpeting, carpet tiles, and area rugs to retailers, commercial
contractors, and distributors throughout Australia. CarpetKing has had a legendary reputation
for both quality woven and tufted carpet for at least several decades. Traditional skills
alongside the most modern technology, unique to CarpetKing, have placed it in the forefront
of the rugs industry.
CarpetKing currently owns and operates 24 state-of-the-art ALPHA looms and 81 BETA
looms. BETA looms produce standard tufted carpeting. ALPHA looms cannot only produce
standard tufted carpeting, but they also allow for the incorporation of designs (such as flowers
and company logos) into the carpeting. The company has received several big orders for its
17 types of carpets/rugs and anticipates that, with the closure of several local rug
manufacturers, there might be an influx in demand for its carpet. Its owner, Joan Clarke, is
concerned that their current production capacity may not be able to handle the situation. She
has asked for your assistance in planning a weekly production schedule for the next year
(under the assumption that demand is constant).
The following table summarises the expected demand for each type of carpet that must be
produced in the coming year along with their production rates and costs on each type of loom,
and the cost of subcontracting for each order. Note that the first four orders involve special
production requirements that can be achieved only on a ALPHA loom or via subcontracting.
As a contingency plan, CarpetKing has sourced out a local manufacturer whom they could
trust to subcontract to – should the need arise. You can assume that any portion of an order
may be subcontracted, but this local manufacturer could produce no more than 600,000
metres per year (i.e., 12,000 metres per week) of any combination of carpets.

 

To maximise efficiency and to keep pace with demand, the company operates 24 hours a day,
7 days a week. Each machine is down for routine maintenance for approximately 2 hours per
week. It is customary for the company to shut down all its production activities for 2 weeks
at the end of the year for the Christmas and New Year holidays.
a) Formulate a Linear Programming (an LP) formulation for this problem. Save your
formulation in the Word document [LastName-StudentId.docx]. (6 marks)
b) Create a spreadsheet model for this problem. Store the model in your Excel
workbook [LastName-StudentId.xlsx] and name your spreadsheet ‘Rugs’. (4
marks)
c) Solve the problem – using MicroSoft Excel Solver. Generate the Sensitivity report
for the problem and name your spreadsheet ‘Rugs Sensitivity Rep’. (1 mark)
Using the MicroSoft Excel Solver sensitivity report, provide answers (in the Word document)
to the following questions: (You must include explanations with your answers.)
d) What is the optimal production plan and the associated cost? (2 marks)
e) Is the solution degenerate – and why or why not? (1 mark)
f) Is the solution unique? (1 mark)
g) What would happen to the total cost if one of the ALPHA machines broke and could
not be used at all during the week? (1 mark)
h) What would happen to the total cost if an additional ALPHA machine was
purchased and available for use during the week? (1 mark)
i) What would happen to the total cost if one of the BETA machines broke and could
not be used at all during the week? (1 mark)
j) What would happen to the total cost if an additional BETA machine was purchased
and available for use during the week? (1 mark)
k) How much money does it cost to produce carpet order 2? How much would the
total cost decrease if order 2 were eliminated? (Provide an explanation). (1 mark)
l) If the carpets in orders 5 to 16 all sell for the same amount, which type of carpet
should CarpetKing encourage its sales force to sell more of? (Provide an
explanation). (1 mark)
m) If the cost of buying the carpet in order 1 increased to $3.06 per metre, would the
optimal solution change? (Provide an explanation.) (1 mark)
n) If the cost of buying the carpet in order 16 decreased to $1.81 per metre, would the
optimal solution change? (Provide an explanation). (1 mark)
o) If CarpetKing decides to produce all their carpets and not to outsource any, what
would be your recommendation to management? (1 mark)
p) The company considers introducing a new carpet, carpet 18, about which we have
the following figures:

Question 2 – Network Modelling of ForexGuru (3 + 5 + 3 + 2 + 2 = 15 marks)
ForexGuru is a global investment company, providing start-up capital to promising new
ventures around the world. Due to the nature of its business, ForexGuru holds funds in a
variety of countries and converts between currencies as needs arise in different parts of the
world. Several months ago, the company moved US$24 million into Japanese yen (JPY)
when one U.S. dollar (USD) was worth 74 yen. Since that time, the value of the dollar has
risen, and it now requires almost 77 yen to purchase one dollar.
Besides its holdings of yen, ForexGuru also currently owns 9 million European EUROs and
45 million Swiss Francs (CHF). ForexGuru’s chief economic forecaster is predicting that all
of the currencies it is presently holding will continue to lose strength against the dollar for the
rest of the year. As a result, the company would like to convert all its surplus currency
holdings back to U.S. dollars until the economic picture improves.
The bank ForexGuru uses for currency conversions charges different transaction fees for
converting between various currencies. The following table summarizes the transaction fees
(expressed as a percentage of the amount converted) for US dollars (USD), Australian dollars
(AUD), British pounds (GBP), European Euros (EURO), Indian Rupees (INR), Japanese yen
(JPY), Singapore dollars (SGD), and Swiss Francs (CHF).

Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.

[order_calculator]