I need solution in excel using solver part b,c,d thanks

Question 1 Beverly Thompson is part of the supply chain team at FreshGo, a pure-play online grocer in the UK that offers deliveries to 80% of the zip codes in the country. Beverly is an expert in network design, and she is in charge of determining the flows that will optimize the cost operation. The current network has a hub-and-spoke structure, with a big central hub (Hub A) that serves four distribution centers (DCs). All customer orders are served from one of these four DCs. FreshGo has divided its market in 8 delivery areas. Orders in the same delivery area are all served from one single DC. Table 1. Distance from Hub A to DCs (in miles). A . DC1 DC2 DC3 DC4 Huba 191 166 |82 27 Table 2. Distance from DCs to delivery areas (in miles). Area 4 Area 8 Area 2 43 18 Area 1 75 90 27 42 Area 3 65 59 86 Area 5 45 69 Area 6 78 123 DC1 DC2 DC3 DC4 24 Area 7 2 7 21 55 11 51 10 76 36 83 89 21 55 1e 45 15 11 Table 3. DC capacity (in boxes). DC1 DC2 Capacity 1250 1340 1060 895 DC3 DC4 Table 4. Expected daily demand for each delivery area (in boxes). Area 1 Area 2 Area 3 Area 4 Area 5 Area 6 Area 7 Area 8 Demand 359 258 583 988 244 299 503 707 Considering that the transportation cost between Hub A and DCs is 0.024 pounds per box per mile and the transportation cost between DCs and delivery areas is 0.072 pounds per box per mile. Part A What is the optimal daily cost of operation (in pounds) for this network to satisfy all the expected demand? FreshGo is considering opening one additional hub. In other words, Hub A and the four DCs (from Question 1) will remain open, and FreshGo is considering to add one (only one) more hub to its network. Beverly Thompson has identified three potential locations, each one with a different daily fixed cost (see Table 6). The new potential hubs have a maximum capacity of 2000 boxes each. Transportation costs per box per mile from Question 1, are also valid for the new hub. Table 5. Distances from potential new hubs to DCs (in miles). HubB Hub Hub D DC1 169 122 56 DC2 12 50 23 DC3 66 34 65 DC4 13 56 80 Table 6. Fixed daily cost for each potential new hub. Hub B Hub C Hub D 1000 800 500 Fixed cost Part B Which additional hub should FreshGo open to minimize the daily cost of operation? (Remember that Hub A remains open.) ? What would be the optimal daily cost of operation for this network (including the fixed cost of running the new hub)? Part C Beverly Thompson has been tasked to analyze another option. Instead of introducing a new hub, she should explore the option of delivering directly to consumers from Hub A (in addition to delivering from the four DCs). Table 7. Distance from Hub A to delivery areas (in miles). Area 1 Area 2 Area 3 Area 4 Area 5 Area 6 Area 7 Area 8 Hub 1 186 49 193 224 152 122 294 226 The cost of serving from Hub A directly to the delivery areas would be 0.072 pounds per box per mile. What would be the optimal daily cost of operation for this network? Part D FreshGo management team recognizes that Beverly Thompson did a great analysis, but they are reluctant to introduce any major changes to their network right now, in the middle of the COVID pandemic. However, they want to better understand what is their current level of service. (To answer this question, just consider FreshGo's original network from Question 1.) What is the level of service FreshGo is currently providing, measured in percentage of demand within 30 miles of DC? Question 1 Beverly Thompson is part of the supply chain team at FreshGo, a pure-play online grocer in the UK that offers deliveries to 80% of the zip codes in the country. Beverly is an expert in network design, and she is in charge of determining the flows that will optimize the cost operation. The current network has a hub-and-spoke structure, with a big central hub (Hub A) that serves four distribution centers (DCs). All customer orders are served from one of these four DCs. FreshGo has divided its market in 8 delivery areas. Orders in the same delivery area are all served from one single DC. Table 1. Distance from Hub A to DCs (in miles). A . DC1 DC2 DC3 DC4 Huba 191 166 |82 27 Table 2. Distance from DCs to delivery areas (in miles). Area 4 Area 8 Area 2 43 18 Area 1 75 90 27 42 Area 3 65 59 86 Area 5 45 69 Area 6 78 123 DC1 DC2 DC3 DC4 24 Area 7 2 7 21 55 11 51 10 76 36 83 89 21 55 1e 45 15 11 Table 3. DC capacity (in boxes). DC1 DC2 Capacity 1250 1340 1060 895 DC3 DC4 Table 4. Expected daily demand for each delivery area (in boxes). Area 1 Area 2 Area 3 Area 4 Area 5 Area 6 Area 7 Area 8 Demand 359 258 583 988 244 299 503 707 Considering that the transportation cost between Hub A and DCs is 0.024 pounds per box per mile and the transportation cost between DCs and delivery areas is 0.072 pounds per box per mile. Part A What is the optimal daily cost of operation (in pounds) for this network to satisfy all the expected demand? FreshGo is considering opening one additional hub. In other words, Hub A and the four DCs (from Question 1) will remain open, and FreshGo is considering to add one (only one) more hub to its network. Beverly Thompson has identified three potential locations, each one with a different daily fixed cost (see Table 6). The new potential hubs have a maximum capacity of 2000 boxes each. Transportation costs per box per mile from Question 1, are also valid for the new hub. Table 5. Distances from potential new hubs to DCs (in miles). HubB Hub Hub D DC1 169 122 56 DC2 12 50 23 DC3 66 34 65 DC4 13 56 80 Table 6. Fixed daily cost for each potential new hub. Hub B Hub C Hub D 1000 800 500 Fixed cost Part B Which additional hub should FreshGo open to minimize the daily cost of operation? (Remember that Hub A remains open.) ? What would be the optimal daily cost of operation for this network (including the fixed cost of running the new hub)? Part C Beverly Thompson has been tasked to analyze another option. Instead of introducing a new hub, she should explore the option of delivering directly to consumers from Hub A (in addition to delivering from the four DCs). Table 7. Distance from Hub A to delivery areas (in miles). Area 1 Area 2 Area 3 Area 4 Area 5 Area 6 Area 7 Area 8 Hub 1 186 49 193 224 152 122 294 226 The cost of serving from Hub A directly to the delivery areas would be 0.072 pounds per box per mile. What would be the optimal daily cost of operation for this network? Part D FreshGo management team recognizes that Beverly Thompson did a great analysis, but they are reluctant to introduce any major changes to their network right now, in the middle of the COVID pandemic. However, they want to better understand what is their current level of service. (To answer this question, just consider FreshGo's original network from Question 1.) What is the level of service FreshGo is currently providing, measured in percentage of demand within 30 miles of DC