Need solution and explanation

Assignment.xlsx

Home >Business & Finance homework help >Operations Management homework help >Need solution and explanation

Q1

Below are two quick questions for station metrics calculation.
a. Station Metrics Lookup
Metrics (SQFT)	Station 1	Station 2	Station 3	Station 4	Station 5	Station 6	Station 7	Station 8
Building	149,775	117,478	108,069	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,505
Metrics	Station	SQFT
				*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 Benchmark by Throughput Breakdown
From (>=)	To (<)	Office Size
0	2,000	1,540
2,000	4,500	2,310
4,500	8,000	4,730
8,000	9,000	8,150
9,000	10,000	9,500
10,000	14,000	11,180
14,000	18,000	12,750
18,000	+	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; wave 2 leaves at 8:30; wave 3 leaves at 9: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	8:00	4	180	50
Late AM Shift	10:00	8	200	50
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.