Reference no: EM133547315
Supply Chain Management Strategy
Assignment Details
Required Tasks:
Read the given "case_study". Develop an Excel workbook with multiple worksheets, each showing the working and the solution to one of the quantitative questions or tasks of the assignment. To answer the qualitative task questions or those required to be answered in Word, a simple report should suffice.
Quantitative and Qualitative Case Study
For this assessment task, you are required to prepare an Excel workbook and a simple Word report.
The Excel workbook should comprise multiple worksheets showing the models, the regression analysis summary, the comparison of model performance, the decision tree, and the calculations you used to answer the task questions that require you to complete in Excel.
The simple Word report should be mainly for the economic order quantity (EOQ) analysis and the answers to the qualitative questions. There is no recommended format for the report. You can answer the case questions directly without giving a summary of the case or introduction. Nevertheless, a proper report cover is expected. Answer to each question should start on a new page. The analysis should be in-depth and comprehensive. Up-to-date references should be used to support arguments. Appendices can also be provided if necessary.
There is no recommendation for word length of the report, but you would need to write concisely yet comprehensively. Brevity can impact on the depth of the analysis and longwindedness can diminish clarity.
For Task 1, there should be FOUR worksheets in the Excel workbook. The first two show the following two demand forecasting methods that you are required to use to forecast the demand of one of the popular products - women's crew neck dress - that the case company U17 sells at its front stores in Melbourne.
- Moving average; and
- Static method.
You need to label the tabs of the two spreadsheets clearly to make them self-explanatory.
A worksheet showing the summary of the regression analysis for the static method should be included in the workbook. Again, label the tap clearly.
Furthermore, another worksheet comparing the performance of the two forecasting methods using the key performance indicators (KPIs) mentioned in Task 1 Question (c) should be provided. The suggested table format in Task 1 Question (c) should be used. Again, label the tab clearly.
Answer Task 1 Question (d) in the performance comparison table as well.
The Word report should address Task 1 Question (e) and (f). To answer Question (e), some EOQ and total cost calculations are needed. All the calculations involved must be shown clearly. To answer Question (f), it requires your own research and analysis.
For Task 2, there should be FOUR worksheets in the Excel workbook showing the following:
A decision tree each showing the parameters and the possible outcomes of the three sourcing options in the periods. All the calculations of returns and total net prevent values must be clearly shown.
Answer to Task 2 Question (d) in a comparison spreadsheet. The suggested table format in Task 2 Question (d) should be used.
Label the tab of each spreadsheet clearly.
Again, the Word report should address Task 2 Question (e) through your own research and analysis.
Task 1: Demand Forecasting for women's crew neck dress and procurement planning for Cute Boutique.
Description:
Demand for women's crew neck dress at one of the front stores of U17 for the last 24 months are as follows:
Year |
Month |
Demand |
2021
|
Jul
|
427
|
|
Aug
|
415
|
|
Sep
|
396
|
|
Oct
|
385
|
|
Nov
|
366
|
|
Dec
|
361
|
2022
|
Jan
|
375
|
|
Feb
|
380
|
|
Mar
|
388
|
|
Apr
|
384
|
|
May
|
387
|
|
Jun
|
396
|
|
Jul
|
415
|
|
Aug
|
408
|
|
Sep
|
399
|
|
Oct
|
382
|
|
Nov
|
375
|
|
Dec
|
373
|
2023
|
Jan
|
379
|
|
Feb
|
381
|
|
Mar
|
390
|
|
Apr
|
387
|
|
May
|
392
|
|
Jun |
398 |
Task Questions to be done in Excel:
Estimate the demand for 2023 (from July to September) using a 3-month moving average method.
Estimate the demand for 2023 (from July to September) using the static method.
Evaluate the MAD, MAPE and TS (i.e., the KPIs) in each case and compare them in the format as shown below.
ForecastingMethod
|
MAD
|
MAPE(%)
|
TSRange
|
Min.
|
Max.
|
Moving average method
|
|
|
|
|
Static method
|
|
|
|
|
Which method do you prefer? Why?
|
|
|
|
|
Based on the findings from (b), which of the two methods do you prefer? Why?
(Note: You can give your answer in the table of Question (c).)
Task Question to be done in Word:
Cute Boutique in Melbourne sells women's crew neck dress ordered from U17. The average demand is 100 units each month. Cute Boutique incurs a fixed order placement, transportation, and receiving cost of $200 each time an order is placed with U17. It also incurs a holding cost of 10 per cent of the unit cost of the product. The normal wholesale price is $30 per unit. Recently, U17 offers all-unit quantity discount to Cute Boutique to promote sales. The price schedule is as follows:
Order Quantity Per Lot Price Per Unit
1-200 $30
201-500 $28
Over500 $26
Q1 What is the economic order quantity (EOQ) of women's crew neck dress per lot for Cute Boutique if there is no volume discount?
Q2 What is the total annual cost for Cute Boutique assuming there is no volume discount from U17 and the price per unit is $30?
Q3 What is the optimal order quantity if Cute Boutique decides to take the volume discount? You need to show the calculations of EOQs and total costs at all the three prices.
Q4 What is the new total annual cost for Cute Boutique upon taking the volume discount?
Q5 What is the annual saving for Cute Boutique upon taking the quantity discount in comparison with the normal situation when there is no volume discount?
Conduct your own research and find out what the fast fashion industry usually does in forecasting demand. You may wish to analyse and discuss based on product value, product volume, and product flow as different product characteristics may require different methods to forecast the demand accurately. Give examples and use up-to-date references to support your arguments.
Task 2: Sourcing Decision for U17 Description:
U17 is a small apparel retailer of trendy and low-cost fast fashion for young people. Located in Melbourne, the company divides the year into four sales seasons of about three months each and brings in new merchandise for each season. The company has historically outsourced production to China, given the lower costs there. For one of its popular products, sourcing from the Chinese supplier costs 46 Chinese yuan renminbi (CNY) per unit (inclusive of all delivery costs), which at the current exchange rate of 4.6 CNY per AUD (i.e., 1 AUD = 4.6 CNY) gives a variable cost of about AUD10 per unit. The Chinese supplier, however, has a long lead time, forcing U17 to pick an order size well before the start of the season. This does not leave the company any flexibility if actual demand differs from the order size. A local supplier has come to the management with a proposal to supply product at a cost of AUD12 per unit but to do so quickly enough that U17 will be able to make supply in the season exactly matching demand. Management is concerned about the higher variable cost but finds the flexibility of the onshore supplier very attractive. The challenge is to evaluate the value of responsiveness provided by the local supplier.
Uncertainties Faced by U17
To better compare the two suppliers, management identifies demand and exchange rates as the two major uncertainties faced by the company. Over each of the next two periods (assume them to be a year each), demand may go up by 10 percent, with a probability of 0.5, or down by 10 percent, with a probability of 0.5. Demand in the current period is 1,000 units. Similarly, over each of the next two periods, the CNY may strengthen by 5 percent, with a probability of 0.5, or weaken by 5 percent, with a probability of 0.5. The exchange rate in the current period was 4.6 CNY per AUD.
Ordering Policies with the Two Suppliers
Given the long lead time of the offshore supplier, U17 commits to an order before observing any demand signal. Given the demand uncertainty over the next two periods and the fact that the profit margin from each unit (about AUD10 at a sale price of AUD20) is about the same as the loss if the unit remains unsold at the end of the season (loss of about AUD10), management decides to commit to an order that is somewhat higher than the expected demand. Given that the expected demand is 1,000 units over each of the next two periods, management decides to order 1,050 units from the Chinese supplier for each of the next two periods. If demand in a period turns out to be higher than 1,050 units, U17 will sell 1,050 units. However, if demand turns out to be lower than 1,050, the company will have leftover product for which it will not be able to recover any revenue. The short lead time of the local supplier allows U17 to keep bringing product in a small amount at a time, based on actual sales. Thus, if the local supplier is used, the company can meet all demand in each period without having any unsold inventory or lost sales. In other words, the final order from the local supplier will exactly equal the demand observed by U17.
A Potential Hybrid Strategy
The local supplier has also offered another proposal that would allow U17 to use both suppliers, each playing a different role. The Chinese supplier would produce a base quantity for the season and the local supplier would cover any shortfalls that result. The short lead time of the local supplier would ensure that no sales are lost. In other words, if U17 committed to a base load of 900 units with the Chinese supplier in each period and demand is 900 units or less, nothing would be ordered from the local supplier. If demand, however, is larger than 900 units (say, 1,100), the shortfall of 200 units would be supplied by the local supplier. Under a hybrid strategy, the local supplier would end up supplying only a small fraction of the season's demand. For this extra flexibility and reduced volumes, however, the local supplier proposes to charge AUD13 per unit if it is used as part of a hybrid strategy.
Task Questions to be done in Excel:
Draw a decision tree for each option reflecting the uncertainties over the next two periods. Identify each node in terms of demand and variable cost (may be affected by the fluctuations in exchange rate) and the transition probabilities. Use two decimal points in your notation of variable cost, if needed.
If management at U17 is to pick only one of the two suppliers, which one would you recommend? What is the NPV of the expected profit over the next two periods for each of the two choices? Assume an interest rate of k = 0.1 per period.
What do you think about the hybrid approach? Is it worth paying the local supplier extra to use it as part of a hybrid strategy? For the hybrid approach, assume that management will order a base load of 900 units from the Chinese supplier for each of the two periods, making up any shortfall in each period at the local supplier. Evaluate the NPV of the expected profits for the hybrid option assuming an interest rate of k = 0.1 per period.
Create a comparison sheet using the following format. Based on the findings from (b) and (c), which option should U17 pursue? Why?
Option
|
NPV of Total Expected Profit
|
Sourcing from the local supplier
|
|
Staying from the Chinese supplier
|
|
Hybrid sourcing
|
|
Which sourcing option would you recommend? Why?
|
|
Task Question to be done in Word:
Conduct your own research and find out how the decision tree methodology can be applied to the risk analysis in the fast fashion industry. As the type of risks in the upstream, midstream, and downstream of an apparel supply chain can vary, the tools for risk analysis (decision tree methodology can be one of them) may be different. You may wish to discuss the application in the different aspects of the supply chain operation based on the uncertainties involved.
Report Format:
Although a formal report is not required, the following should be noted:
In addition to the Report Cover (a page that properly provides the course number and title, assignment number, title of the case, student number, student name, and submission date), the report should include the following:
(a) Answers to Case Questions (Answer each of the questions on a new page.)
(b) References (List the references used in the investigation report in strict Harvard style.)
(c) Appendices (Optional. Show any additional information that you wish to provide but is too lengthy for the answer.)
The following should also be noted:
1. Use summary tables, charts, and figures where appropriate to aid your analysis and discussion. The figures and tables should be inserted in the sections and subsections immediately after they are mentioned.
2. All references, if any, must be provided in strict Harvard style.
3. Students can also refer to the document entitled "Written reports and essays: Guidelines for referencing and presentation in RMIT Business" for advice and tips on how to write proper reports and essays.
4. The suggested word length of the report is 1,500 to 2,000. Longer or shorter report (within reasonable margins) is acceptable as long as it is professionally presented, comprehensive, thorough, and easy to follow. Also, all the required tasks as stated above must be completed.
5. The assessment of the assignment will be based mainly on the thoroughness of the investigation, comprehensiveness of the analysis, and convincingness of the evidence to support arguments as presented in the report. The more effort students invested in investigating the function and writing the report, the higher the marks will be. Doing only the minimum will certainly not be good enough and might lead to poor result.