need to do in excel

17 questions remaining Case Problem 15.2 It's Snowing Again! Gastonia, North Carolina, is not accustomed to significant snow- falls. In the past few years, however, the snow has been relentless. Determined to be prepared this year, the city manager has requested a capacity plan from GDOT (Gastonia Department of Transportation) The roads for which the city is responsible include 10 miles of 4-lane highways, 15 miles of 3-lane highways, 40 miles of 2-lane primary roads, and 50 miles of 1-lane secondary roads. The standard cover age for keeping roads clear during inclement weather is three passes over highways during a 12-hour time period, two passes over primary roads, and one pass over secondary roads. Gastonia has 3 snowplows with a maximum speed of 35 mph that can clear a width of one lane at a time. During a 3-in, snowfall, the plows are running at 90% effi- ciency. The efficiency (measured by speed) decreases with the amount of snowfall to 75% during a 6-in, snowfall, 50% for a left snowfall, and 30% for a 2-ft or greater snowfall. During a normal 8-hour day, workers take one hour total for breaks and lunch. The same ratio is used for 12-hour shifts. This downtime is especially important during inclement weather, as the drivers need to warm up and refocus, and excess snow and salt needs to be removed from the equipment. Pre- pare a snow removal capacity plan for Gastonia roads that includes: a. An estimate of the capacity to clear roads in a 12-hour period expressed in miles per type of snowfall. b. An estimate of the total workload (in miles traveled) over a 12-hour period to keep roadways clear, and work load per type of road. (Hint: You'll need to double the number of lanes to account for north and south movement.) c. An estimate of the current load percent, and a recommenda tion for adjusting the workload when loud percent exceeds 100% d. If no other adjustments were allowed, how many snowplows would GDOT need to clear each type of snowfall? e. What additional information would you need to make an informed decision on adjusting load or expanding capacity for GDOT's snow plowing services? se Problem 15.3 Hosuki bki, a small car maker, competes with larger manufacturers by ding cars to order. The company has invested heavily in tech sity and close partnerships with suppliers. Customers enter their es and choose their options on the company website. Hosuki's system responds with an estimated cost and completion date. the customer approves the order. Hosuki sets to work Quick response for Hosuki is dependent on collabomthe man 3 C