BrighTECH Mechatronics, a specialised electromechanical hardware manufacturer is producing a highly in-demand LED headlight for trains featuring heavy duty construction with solid state electronics. The headlights are produced at two manufacturing plants in Taiwan and Malaysia and currently, the company only sells the product through it’s distributors in the United States, United Kingdom, Brazil and South Africa. The manufacturing plant in Taiwan has the capacity to produce 2500 units per month and the Malaysian manufacturing plant can produce 3500 units per month. The cost of shipping a crate of 10 units for the coming month of November to distributors in each of the countries are estimated to be as given in the following table.
Manufacturing Plant Shipping Cost per Crate of 10 Units – November
United States United Kingdom Brazil South Africa
Taiwan $180 $140 $130 $110
Malaysia $150 $170 $100 $120

It has also been informed to BrighTECH that these costs will increase by 10% for the month of December considering the end of year seasonal variation to shipping and transportation costs. Each country has a standing order of 1250 headlight units for November and 1750 headlight units for December. For a particular month, BrighTECH can also ship 500 units more than the standing order to distributors in each country provided that a discount of $20 per unit is given on the excess amount. The distributors will hold this amount in inventory from that month to the next.
The per unit production costs for the month of November are estimated to be $210 at the manufacturing plant in Taiwan and $250 at the plant in Malaysia. It is also expected that these costs will be $230 per unit at both plants for the month of December.
BrighTECH Mechatronics wants to develop a plan that optimizes production and distribution for the next two months of November and December so that the demand at each country is met at the minimum cost.
1) Considering the network flows (decision variables) as representing the number of crates being shipped from each plant to each country for a particular month of interest and representing the amount of excess stock (number of crates) being held from November to December at each country, represent the decision problem using an appropriate network flow diagram. The discount given on the excess amount held on inventory by the distributors can be interpreted as a cost (for holding inventory) from the perspective of BrighTECH. Include all supply demand values at nodes and any applicable costs/flow bounds on arcs in your diagram.

(hint – Each node can be represented as a combination of country and month. For example, a Taiwan-NOV node to represent supply from Taiwan in November and a SouthAfrica-NOV node to represent demand at SouthAfrica in November etc.)

– Correct representation of flows – 2 marks
– Correct representation of nodes – 2 marks
– Correct supply/demand values at nodes – 2 marks
– Correct cost/flow bounds values as applicable in arcs – 2 marks
2) Implement the model in Excel following goals/guidelines for good spreadsheet design and using the balance of flow rules approach for network modelling. Include screenshots of the model in report as relevant.

– Correct representation of nodes – 2 marks
– Correct supply/demand values at nodes – 2 marks
– Correct representation of flows – 2 marks
– Correct cost values as applicable – 2 marks
– Correct flow bounds as applicable – 2 marks
– Correct net flow values for constraints – 2 marks
– Correct representation of objective function – 2 marks
– Adherence to goals/guidelines for good spreadsheet design – 2 marks

3) Solve using solver to find the optimal solution.
– Correct solver setup for objective Function and decision Variables – 2 marks
– Correct constraints in solver – 2 marks
– Correct optimal solution – 2 marks

# Assignment Part2 is to be submitted together with Part 1 and Part 3 in moodle by the due date. Delivery structure and submission instructions for the complete Assignment are as follows:

Your delivery for the Assignment should include:

1) Separate Excel workbooks for each part in the assignment containing:
a) Relevant Case Model for each part
b) Relevant Case Reports for the Case Model in each part (Answer/Sensitivity/Limits)

2) A single PDF/word document addressing the questions of all Parts (Part I – Part III) in the Assignment organised in a clear and logical manner according to Assignment Part and relevant section and question numbers under each part.
You are to upload your submission on the FIT3158 Moodle site under the “FIT3158 Assignment: Spreadsheet Modelling” submission link by the due date.
All submission must be in the form of;
Excel Workbooks, saved as: _Assignment_.xlsx (or xlsm) and a PDF/Word document saved as: _Assignment.docx (or .pdf).

WeCreativez WhatsApp Support
Stuck with your assignment? When is it due? Chat with us.
👋 Hi, how can I help?