代做Business Operations Analysis帮做R编程

Instructions: Form. a group of 5 students. Use the link inside Canvas. Write a PPT to report your recommended solutions. Clearly explain the network design and assortment choices. Submit the PPT and the Excel files that your group creates.

Background of the Case Company

Founded in 1989, G company started with only one product—baby stroller. Over the past 30 plus years, G company expanded its product portfolio to include: cots, highchairs, children’s clothing, and nursing supplies. In 2024, G company emerges as a well-known brand in Shanghai, China.

At present, 84 stores are being supplied from a regional warehouse, from which the transportation time is 24 hours. In addition to the transportation time, each order requires picking, packing, and moving activities such that the total lead time for each order is roughly 72 hours (from ordering to receiving the goods). The transportation cost from the regional warehouse to stores is estimated to be CNY6 per kilometre per product.

G company plans to carry out a “520” pilot project: 5 hours’ maximum replenishment time, 2 replenishments a day, and 0 out-of-stock situations. The pilot project requires upfront warehouses serving as buffers for product flow from the regional warehouse to stores. The entire system will install an automatic ordering system that monitors the in-store inventory levels in real time.

The pilot project will deploy to 10 out of 84 stores in Shanghai. The focus is on children’s clothes, of which the assortment contains 5 variants shown in the following Table 1.

Table 1: Five Variants

G company currently is offering all five variants in their assortment. Ms Yan is leading the project and is thinking about whether to adjust the assortment. As the expectation of zero out-of-stock to be met, the mean demand at each store will be used for facility location planning. However, the impact of adjusting the assortment needs to be considered. The attractiveness of no purchase is scaled to be 1.

The transportation cost from the regional warehouse to the chosen upfront warehouse(s) will be excluded. For Tasks 2 and 3, the facility location model must minimize the total costs based on the mean demands at each store.

The transportation cost from any upfront warehouse to store is estimated to be CNY1 per kilometre per unit. There are 4 candidate locations to open an upfront warehouse.

Table 2: Candidate Locations for Upfront Warehouse

The second to fourth rows are measured in CNY while the fifth and sixth rows are GPS data.

The 10 stores in the pilot project are the following.

Table 3: GPS Data of Stores and Their (Daily) Average Store Traffic

Task 1) Use the calculator on www.nhc.noaa.gov/gccalc.shtml to compute the distance in kilometre. Some numbers are already filled. (Hint: As the GPS data is rounded in three decimal places, the calculator will produce a distance in integer. Feel free to double check the pre-filled numbers.)

Task 2) Develop a facility location model by assuming that all variants are included in the assortment. The mean demand for each location equals the average traffic multiplied by the choice probability of purchasing from the offered assortment. Use these mean demands as the inputs for the facility location problem. Solve the facility location problem using Excel.

Task 3) Develop a two-stage model. Stage 1: Apply the greedy Algorithm 2 (which has been discussed in the lecture) to choose a universal assortment for all stores. Stage 2: Compute the new probability of purchasing (the total probability of purchasing from the offered assortment). The mean demands are then updated as the following: the new probability multiplied by the average traffic of the store. Solve the facility location problem again.

Task 4) Contrast the solutions for Tasks 2 and 3 to determine the impact of adjusting the assortment.

Task 5) If we conduct a full search of all the possible assortments (rather than using the greedy algorithm 2), will the optimal solution change?

Write a PPT to explain your recommendations to Ms Yan. Submit your Excel file as the accompanying evidence. (Page limit is 30, including title page and references.)

A supply network means which upfront warehouses are chosen and which warehouse will supply to which store. You can present this network using a table (or map with arcs) with expected delivery quantities.

Hints:

- To increase the realism of your PPT, you can add photos of products or map of Shanghai city. Video is too large for uploading and downloading and hence is banned.

- The traffic in Table 3 is daily and hence, the annual income is the daily income multiplied by 365.

- Daily income of each store equals the average daily traffic multiplied by the “single-customer” income from the assortment model.

- Transportation cost also incurs daily while the fixed cost is annual.

- About the multiplication models: if an assortment attracts 90% of customers at store 4, the expected demand at store 4 equals 131*0.9, which will be used in the facility-location model; if an assortment generates 40CNY per customer on average, the daily net income of store 4 equals 131*40.