OMGT2229 Strategic Supply Chain Assignment 3: Harvey Norman Forecasting, EOQ and Sourcing Decision Tree, Singapore

This OMGT2229 Assignment 3 uses Harvey Norman’s 12 Singapore stores and a TP-Link Tapo home security camera. Task 1 combines moving-average and static demand forecasting with an all-unit quantity discount EOQ problem, and Task 2 asks for two-period decision trees and NPVs comparing a Chinese supplier, a local supplier and a hybrid sourcing option under demand and CNY exchange-rate uncertainty. Both tasks end with a research infographic and a personal reflective journal.

Submission Requirements

• Excel workbook: separate, clearly labelled worksheets for the forecasting models, regression analysis summary, performance comparison, decision trees and NPV calculations. All calculations stay in Excel and must not be repeated in the Word report.
• Word report: a proper report cover, the EOQ analysis and the qualitative answers, each question starting on a new page, with references and optional appendices.

Task 1: Demand Forecasting and Procurement Planning for Harvey Norman (20 marks)

Monthly demand for the TP-Link Tapo 2K Wi-Fi Home Security Camera across the 12 Harvey Norman stores in Singapore:

Questions to be done in Excel

• (a) Estimate demand for July, August and September 2026 using a 3-month moving average. (1 mark)
• (b) Estimate demand for July, August and September 2026 using the static method. (3 marks)
• (c) Evaluate MAD, MAPE and the tracking signal (minimum and maximum) for each method and compare them in a table. (2 marks)
• (d) Based on the findings, which of the two methods do you prefer and why? (1 mark)

Question to be done in Word

(e) Average monthly demand has been 640 units, the fixed ordering cost is $2,500 per order, the holding cost is 15% of unit cost and the standard wholesale price is $70. TP-Link offers an all-unit quantity discount: 1 to 2,500 units at $70, 2,501 to 5,000 units at $69, and over 5,000 units at $68.

1. What is the EOQ per lot with no volume discount? (1 mark)
2. What is the total annual cost with no discount at $70 per unit? (1 mark)
3. What is the optimal order quantity if the discount is taken? Show EOQs and total costs at all three prices. (3 marks)
4. What is the new total annual cost upon taking the discount? (1 mark)
5. What is the annual saving compared with no volume discount? (1 mark)

(f) Research how consumer electronics retailers such as Harvey Norman forecast demand, and present the approaches as a diagram or infographic (not an essay) with referenced text boxes, analysing forecasting by product value, volume and flow with product examples (3 marks). Add a reflective report or journal on how you developed the diagram, your research process and what you learned (3 marks).

Task 2: Sourcing Decision for Harvey Norman (20 marks)

Harvey Norman (Pertama Merchandising, 12 stores in Singapore and 34 in Malaysia) sources the camera from China at CNY 385 per unit (about SGD 70 at 5.5 CNY per SGD) with a long lead time, or could buy a comparable model from a local supplier at SGD 80 with fast delivery. Expected annual demand is 7,500 units; in each of the next two periods demand may rise or fall by 10% (probability 0.5 each) and the CNY may strengthen or weaken by 5% (probability 0.5 each). The selling price is SGD 120 (margin SGD 50). The offshore option orders 8,500 units per period, with unsold units donated at no revenue; the local option matches demand exactly. A hybrid option orders a base of 6,500 units from China each period, with any shortfall supplied locally at SGD 90 per unit.

Questions to be done in Excel

• (a) Draw a decision tree for each option over the next two periods, identifying each node by demand and variable cost and showing transition probabilities. (6 marks)
• (b) If only one supplier is chosen, which would you recommend? Calculate the NPV of expected profit over two periods for each, with k = 0.1 per period. (4 marks)
• (c) Evaluate the hybrid approach and its NPV of expected profit (k = 0.1). Is it worth paying the local supplier extra? (2 marks)
• (d) Create a comparison sheet of the three options and recommend one, with reasons. (2 marks)

Question to be done in Word

(e) Research how decision tree methodology and other tools are used for risk analysis across the upstream, midstream and downstream consumer electronics supply chain, and present them as a diagram or infographic with referenced text boxes and product examples (3 marks). Add a reflective report on your research approach, sources, challenges in applying the decision tree and insights gained (3 marks).

Note: Period 0 profit is excluded from the NPV comparison because it is the same across all three options. AI-generated images and reflections are not accepted.