Uncategorized
Need solution with explanation
Need solution with explanation – in excel sheet
Q1
Below are two quick questions for station metrics calculation. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
a. | Station | Metrics | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Metrics ( | SQFT | Station 1 | Station 2 | Station 3 | Station | 4 | Station 5 | Station 6 | Station 7 | Station | 8 | |||||||||||||||||||||||||||||||||||||||||||||||||
Building | 149,775 | 117,478 | 1 | 0 | 151,011 | 154,941 | 119,005 | 139,518 | 189,719 | |||||||||||||||||||||||||||||||||||||||||||||||||||
Office | 15,365 | 12,979 | 15,538 | 14,016 | 19,890 | 10,303 | 19,175 | 12,214 | ||||||||||||||||||||||||||||||||||||||||||||||||||||
Operation | 134,410 | 104,499 | 92,531 | 136,995 | 135,051 | 108,702 | 120,343 | 177, | 50 | |||||||||||||||||||||||||||||||||||||||||||||||||||
*Build two dropdown lists in cell B12 and C12 to refer to the Station and Metric selection in the table above. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*Enter the formula in D12 to populate the associated sqft in table above using the Metric and Station selection from the left. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
b. Standard | Office Size | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
From (>=) | To (<) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2,000 | 1,540 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4,500 | 2,310 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
8,000 | 4,730 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
9,000 | 8,150 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
10,000 | 9,500 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
14,000 | 11, | 180 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
18,000 | 12,750 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
+ | 14,720 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Actual Size Throughput | *D27 ranges from 0 to infinite as a integer. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Recommended Office Size | *Enter the formula in D28 to populate the recommended sqft based on the table above. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*Try not to use nested if statement if you can find a better easier way to do this. |
Q2
Below is an example of ABC Delivery Station’s dispatch model. Dispatch means the delivery vehicles are leaving the station with certain number of packages on it and go out in a pre-defined route sequence to deliver packages to customer’s door step. | ||||
There are two major delivering windows everyday and they are supported by different shifts of associates. Every window has a number of waves for dispatch. Per wave, the number of delivery vans is constant. Every wave is placed at least 30 mins apart for the ease of operation. For example, wave 1 leaves at | 8:00 | |||
Assume vans will have to return to the station by 9PM everyday and the maximum route length a van can support is 10 hrs. Noted that # of packages that can be delivered per route will decrease when the maximum route length is not achievable. | ||||
Dispatch Time Span | First Wave Time Depart Time | # of Dispatch Wave | Max # of packages per Route | # of Vans per Wave |
Early AM Shift | ||||
Late AM Shift | 10:00 | 200 | ||
Please create a dispatch model to evaluate this system and answer the following questions | ||||
a. What is the maximum daily dispatch capacity in packages given the model assumptions above? | ||||
b. Do you see any operation concerns for the sample dispatch model in a.? How would you improve it? | ||||
c. Assume you only identified 300 van parking spots onsite and all vans have to park onsite overnight after their delivery. Assume vans will not be reused between 2 shifts and max # of vans per wave is 50. How would you redesign the dispatch model for this station to achieve the maximum dispatch daily capacity? |
Q3
Please think big and engineer a fungible parent container to improve the layout, absorbing the variation in cube of oversize and standard packages. The containers will be used as the final sortation, Associates moving the packages from a conveyor and into this container. Then, the same container is then staged, and loaded. In 2021, ABC network cube per package increased by 19% (0.37 to 0.54 cut/ pkg) with individual sites variation up to 0.28 cut/ pkg. This resulted in average pick cycle time increase of 9 minutes 31 seconds (+22%) directly contributing to late wave departures. The goal is to create a container that holds both oversize packages unsorted and standard packages that are sorted into bags. This container needs to absorb the variation of package cube, reducing the variation in downstream processes. As the Associate moves packages from the conveyor and sort directly in the same container that will be staged. Please ensure to include a high level model with fillin data. Provide diagrams or data visualizations to help you illustrate details if needed. |