Need solution with explanation

Need solution with explanation – in excel sheet

Q1

Lookup

)

8,069

5

Metrics Station SQFT

Benchmark by Throughput Breakdown

Office Size

0

2,000

4,500

8,000

9,000

10,000

14,000

18,000

																																																												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 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	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

; wave 2 leaves at 8:30; wave 3 leaves at 9:00…

8:00 4 180 50

8

50

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.