DE assignment

DECandidateAssignment.xlsx

Home >Engineering homework help >Civil Engineering homework help >DE assignment

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
Opereation	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 Amazon 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

Describe a project/product you have designed. Please include related background from original project intent all the way to implementation. Provide diagrams or data visualizations to help you illustrate details if needed.