In the second case, the constrains of customer demand and truck capacity are furtherconsidered to make the scenario more practical. Hence, capacities of these 3 self-pickuptrucks are given on Table 2(a) and the locations and demands of 30 customers areshown on Table 2(b). You are required to solve the below optimization problem byusing MS Excel Solver to provide instruction to the transportation team.Table 2(a). List of truck locationsSelf-pickupTruckLocation Capacity(in kg)X YTruck A 1 1 50Truck B 10 10 80Truck C 20 20 80Total = 210 kgTable 2(b). List of customer locationsCustomer Location Demand(in kg) Customer Location Demand(in kg) Customer Location Demand(in kg)X Y X Y X YC1 1 3 4.5 C11 16 12 7.5 C21 31 25 2C2 2 35 5 C12 17 32 6.5 C22 31 35 5C3 5 5 8 C13 18 17 5 C23 31 44 8C4 5 47 6.5 C14 21 33 7.5 C24 37 42 6.5C5 5 17 6.5 C15 22 12 8 C25 38 6 4.5C6 9 5 4.5 C16 24 27 9.5 C26 42 41 5.5C7 10 19 7.5 C17 24 8 7.5 C27 42 49 8C8 11 43 8 C18 27 37 9.5 C28 42 7 7.5C9 13 25 7.5 C19 28 12 4.5 C29 47 26 4.5C10 15 45 5 C20 29 8 9.5 C30 49 8 6.5Report preparation (in Word):1. The mathematical model of the above problem with descriptions2. Screen captures of your settings in (i) Excel sheet(s) and (ii) Solver withdescriptions3. Visualization of (i) the optimal truck locations with customer locations and (ii)optimal total travelling distanceSubmit your report about the above tasks in ONE file in .doc/.docx/.pdf. Pleasename the file with your student ID, e.g. 12345678d-ASM1.doc/.docx/.pdf.Submission Deadline: The end of the day of 29 September 2023 (Week 4)

A data analyst is trying to establish the cheapest way for a company to deliver inventory from a number of depots to a range of retail stores, based on quantities held at each depot, the distances between the depots and the capacity to hold required inventories in each store.If he were using an Excel spreadsheet to do this task, which function would be most appropriate to use to reach his desired objective? SolverScenario managerGoal seekMultiple regression

Introduction:As a logistics engineer for an e-commerce consolidation company, you are now taskedwith implementing a self-pickup service. This service will utilize cargo vans/trucks,allowing customers to pick up their orders. The transportation team is responsible forparking these trucks at various points throughout Hong Kong. Your role is to determinethe optimal parking locations for these self-pickup trucks. The goal is to maximizecustomer convenience, which is achieved by minimizing the travel distances betweencustomers and the parked trucks. In this assignment, you are going to learn how todetermine the location of self-pickup trucks to serve customers, and the add-in function,namely Solver in MS Excel, will be used to assist in the optimization process. Overall,there are two major tasks as follows:A. Section 1: You are required to follow the instructions to build the solver about thelocation optimization of self-pickup trucks to minimize the total travelling distancebetween trucks and customers.B. Section 2: You are required to build the mathematical model for the advancedlocation optimization problem with the demand and truck capacity considerations.By solving the above optimization problems, it is expected to obtain the locations (i.e.coordinates) of three self-pickup trucks. Thus, the optimal and clear instructions to thetransportation team can be made.Distance Calculations:Distance measurement is an important issue in this assignment, which can be used tocalculate the distance between two points. In this assignment, the Manhattan Distanceis applied to measure the distance between locations of customers and trucks.Equation of the Manhattan Distance:𝐷(𝐴, 𝐵) = |𝐴𝑥 − 𝐵𝑥| + |𝐴𝑦 − 𝐵𝑦|Section 1: A Self-Learning ExampleYou are required to determine the optimal locations of THREE self-pickup trucks sothey can effectively serve 30 customers. The decision is solely based on the Manhattandistance, and therefore, the appropriate locations to park the trucks can be obtainedwhile the overall travelling distance between customers and trucks can be minimized.You should also notice that self-pickup trucks should evenly share the number ofcustomers, and you have to determine which customers should be associated with thespecific self-pickup trucks. In order to build the above optimization problem, the site ismapped into a 50x50 grid, and the corresponding locations of the 30 customers to beserved are shown in Table 1(a). The truck locations are shown in Table 1(b).A = (Ax, Ay)B = (Bx, By)The Hong Kong Polytechnic UniversityDepartment of Industrial & Systems Engineering2/6Table 1(a). List of customer locationsCustomer Location Customer Location Customer LocationX Y X Y X YC1 1 3 C11 16 12 C21 31 25C2 2 35 C12 17 32 C22 31 35C3 5 5 C13 18 17 C23 31 44C4 5 47 C14 21 33 C24 37 42C5 5 17 C15 22 12 C25 38 6C6 9 5 C16 24 27 C26 42 41C7 10 19 C17 24 8 C27 42 49C8 11 43 C18 27 37 C28 42 7C9 13 25 C19 28 12 C29 47 26C10 15 45 C20 29 8 C30 49 8Table 1(b). List of truck locationsSelf-pickupTruckLocationX YTruck A 1 1Truck B 10 10Truck C 20 20Mathematical Model:Min. ∑ ∑ [(|𝑥𝑖 − 𝑥𝑘| + |𝑦𝑖 − 𝑦𝑘|) ∙ 𝑧𝑖𝑘]3𝑘=130𝑖=1(1)Subject to:∑ 𝑧𝑖𝑘3𝑘=1= 1, ∀𝑖 (2)∑ 𝑧𝑖𝑘30𝑖=1= 303 = 10, ∀𝑘 (3)0 ≤ 𝑥𝑘, 𝑦𝑘 ≤ 50 (4)𝑧𝑖𝑘 ∈ {0, 1} (5)(𝑥𝑖, 𝑦𝑖) denotes the coordinates of customers 𝑖 = {C1, … , C30}, while (𝑥𝑘, 𝑦𝑘) are thedecision variables representing the coordinates of trucks 𝑘 ={Truck A, Truck B, Truck C} , referred to Equation (4). Moreover, 𝑧𝑖𝑘 is a binarydecision variable, representing the assignment of truck 𝑘 to customer 𝑖, referred toEquation (5).Overall, Equation (1) is the objective function of this optimization problem to minimizethe total travelling distance between trucks and customers. Equation (2) restricts thateach customer is handled by only one truck. Equation (3) ensures the equal number ofcustomers assigned to each truck. Equation (4) limits the coordinate of trucks withinthe 50*50 grid. Equation (5) is the binary constraint for the truck assignment20:42Section 2: Exercise (7.5%)In the second case, the constrains of customer demand and truck capacity are furtherconsidered to make the scenario more practical. Hence, capacities of these 3 self-pickuptrucks are given on Table 2(a) and the locations and demands of 30 customers areshown on Table 2(b). You are required to solve the below optimization problem byusing MS Excel Solver to provide instruction to the transportation team.Table 2(a). List of truck locationsSelf-pickupTruckLocation Capacity(in kg)X YTruck A 1 1 50Truck B 10 10 80Truck C 20 20 80Total = 210 kgTable 2(b). List of customer locationsCustomer Location Demand(in kg) Customer Location Demand(in kg) Customer Location Demand(in kg)X Y X Y X YC1 1 3 4.5 C11 16 12 7.5 C21 31 25 2C2 2 35 5 C12 17 32 6.5 C22 31 35 5C3 5 5 8 C13 18 17 5 C23 31 44 8C4 5 47 6.5 C14 21 33 7.5 C24 37 42 6.5C5 5 17 6.5 C15 22 12 8 C25 38 6 4.5C6 9 5 4.5 C16 24 27 9.5 C26 42 41 5.5C7 10 19 7.5 C17 24 8 7.5 C27 42 49 8C8 11 43 8 C18 27 37 9.5 C28 42 7 7.5C9 13 25 7.5 C19 28 12 4.5 C29 47 26 4.5C10 15 45 5 C20 29 8 9.5 C30 49 8 6.5Report preparation (in Word):1. The mathematical model of the above problem with descriptions2. Screen captures of your settings in (i) Excel sheet(s) and (ii) Solver withdescriptions3. Visualization of (i) the optimal truck locations with customer locations and (ii)optimal total travelling distanceSubmit your report about the above tasks in ONE file in .doc/.docx/.pdf. Pleasename the file with your student ID, e.g. 12345678d-ASM1.doc/.docx/.pdf.Submission Deadline: The end of the day of 29 September 2023 (Week 4)

The unbalanced transportation problem where the total supply exceeds total demand is the case where: In a transportation problem with three locations and two destinations, the objective function is as follows: Min 20X11 + 18X21 + 23 X31 + 16X12 + 14X22 + 12X32. How much does it cost to ship one unit from location 2 to destination 1?

Suppose Jimbo’s Moving Truck business is considering expanding. To decide if expanding isbeneficial to the business, Jimbo wants to conduct a cost-benefit analysis.a) Specify the options or set of alternatives under consideration.b) To implement the expansion Jimbo requires five new employees. Each employee isexpected to be paid $32,000. The cost of interviewing and hiring new employees is$10,000. To ensure the employees are useful to Jimbo’s business he needs to purchasea second truck at the cost of $45,000. The additional resources will increase Jimbo’srevenue by $250,000. In addition to this, Jimbo is expected to receive an additionalbenefit from free advertising on the side of the new truck which he values at $30,000.Conduct the CBA for Jimbo. Determine if Jimbo should expand his business.

Truck LoadingYou oversee truck loading for a warehouse shift. There are 3 trucks ready to load:Truck #Truck CapacityAverage Box SizeTruck 115,000 cubic ft0.3 cubic ftTruck 225,000 cubic ft0.3 cubic ftTruck 335,000 cubic ft0.6 cubic ft20,83333,00062,500187,500191,666Based on this information, how many total boxes will fit in all 3 trucks?