关键词 > Excel代写
Operations Management Final Exam, Summer 2023
发布时间:2023-06-24
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Operations Management
Final Exam, Summer 2023
The exam is for take-home. Discussions with others are not allowed. Sharing your answers and Excel files with others is strictly prohibited and will result in significant reduction in exam score.
1. Describe the cash/goods flow in the US drug supply chain consisting of the drug manufacturer, the distributor, and the pharmacy retailer. Then explain why the operating and net margin do not provide the true profitability of McKesson’s business. Finally propose and explain at least two profitability measures that are informative about McKesson’s true profitability. [10 pts]
2. Describe the key difference between Zara and H&M’s operating process. Use it to explain Zara’s relative advantage over H&M in both the gross margin and the operating margin in the fiscal year of 2021. Explain why it is not meaningful to directly compare the days of inventory of H&M and that of the Gap. [10 pts]
3. Provide evidence using Amazon’s financial data in 2020 to 2022 to show Amazon’s overinvestment in PPE relative to sales. Explain how Amazon’s operating efficiency measures (i.e., accounts receivable, inventory, and accounts payable) impact Amazon’s operating cash flow. Use it to further explain why Amazon does not adopt the strategy of using its relatively cheaper capital to pay its suppliers earlier, easing the suppliers’ working capital pressure. Propose and describe the details of a win-win payment scheme for Amazon to help mitigate its suppliers’ working capital pressure without hurting Amazon’s own operating cash flow. [20 pts]
4. Write a one-page report of why Greensill failed, what are the red flags of Greensill’s business model, and what are the lessons learnt for investment banking in terms of risk management of supply chain finance? [10 pts]
5. A store manager looks at the following operational and sales forecasting data. The manager needs to decide the quantity for each SKU to replenish to maximize the total expected profits. Note that the manager must make the replenishment quantity decisions before observing which demand scenario actually occurs. For each SKU under each demand scenario, the store earns the sales revenue consisting of two parts: 1) the selling price multiplied by the sales volume; and 2) the salvage value multiplied by the leftover inventory (if any). Further, the store pays the procurement costs which equal to purchase cost per unit multiplied by the purchase volume. Assume that the five demand scenarios have equal likelihood. Submit the Excel file with two spreadsheets solving the following two problems, respectively.
A) Help the manager build an Excel-based model to get the optimal replenishment quantities. [10 pts]
B) The upper-level management informs the store manager that no more than four SKUs can be replenished to control the fixed ordering costs. Help the manager build an Excel-based model to decide which SKUs to replenish and by how much. [10 pts]
SKU ID |
Starting Inventory before replenishment |
Demand Scenario I |
Demand Scenario II |
Demand Scenario III |
Demand Scenario IV |
Demand Scenario V |
Selling Price |
Salvage Value |
Purchase Cost Per Unit |
1001 |
77 |
40 |
17 |
54 |
78 |
99 |
$6 |
$2 |
$3 |
1002 |
1 |
75 |
11 |
99 |
53 |
13 |
$10 |
$4 |
$5 |
1003 |
57 |
81 |
32 |
53 |
9 |
56 |
$8 |
$6 |
$7 |
1004 |
33 |
41 |
97 |
47 |
62 |
11 |
$5 |
$2 |
$3 |
1005 |
24 |
18 |
100 |
39 |
90 |
25 |
$8 |
$5 |
$6 |
1006 |
24 |
79 |
69 |
58 |
59 |
3 |
$4 |
$2 |
$3 |
1007 |
9 |
43 |
98 |
41 |
43 |
94 |
$8 |
$5 |
$7 |
1008 |
19 |
22 |
60 |
92 |
26 |
52 |
$4 |
$2 |
$3 |
1009 |
98 |
81 |
34 |
5 |
66 |
57 |
$10 |
$5 |
$7 |
1010 |
43 |
53 |
98 |
54 |
80 |
19 |
$4 |
$2 |
$3 |
6. The Excel file “Final Exam_Flashion Data” contains the data of sales quantity as well as the relevant features for all the first exposure styles in the Footwear department of an online flashion retailer.
a) Perform the linear regression and create the regression table in a separate spreadsheet. [5 pts]
b) Use the pivot table to add the following two new columns: price discount (i.e., (MSRP-Price)/MSRP), # of competing styles in the same event. Perform the linear regression for the enriched data. [5 pts]
c) Can you further improve the forecasting accuracy (i.e., R-square) by refining the data? Perform the linear regression on the refined data. [5 pts]
Submit the Excel file addressing the above three questions.
7. Assume that the demand in each period is normally distributed with the average of 60 units and the standard deviation of 10 units. Fill in the three empty columns B, C, and D in the attached Excel file “Mexico_China.xlsx” with formulae to examine the performance of the tailored base-surge policy. Submit the filled-in Excel file. [15 pts]