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

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)

If a plumber knows the rate he charges per hour, the cost of his tools and travel, the cost of purchasing items, and other subsidiaries, he can figure out how much he will make in a week via a formula.  If he were to choose to target a specific number and then work backwards to figure out how much he should charge for each hour, which Excel tool would he use to get the most direct and quickest result?conditional formattingtables with subtotalsgoal seekdatabase formulas

Multiple Choice QuestionA statistical method of identifying cost behavior that is computed using spreadsheet programs or calculators is:Multiple choice question.scatter diagramsthe high-low methodleast-squares regression

The purpose of the Goal Seek function in Excel is: Group of answer choices To evaluate what needs to be changed in the input variables in order to achieve a certain output variable value To evaluate what can be done to the output variable value in order to update the values of input variables To evaluate what needs to be changed in the input variables in order to maximise the value of the output variable To evaluate what needs to be changed in the output variable’s value in order to minimise mistakes To evaluate the best calculation in order to achieve a certain goal.

The logistics/operations manager of a mail order house purchases two products for resale: King Beds (K) and Queen Beds (Q). Each King Bed costs $500 and requires 100 cubic feet of storage space, and each Queen Bed costs $300 and requires 90 cubic feet of storage space. The manager has $75,000 to invest in beds this week, and her warehouse has 18,000 cubic feet available for storage. Profit for each King Bed is $300, and for each Queen Bed is $150. What is the objective function?Multiple ChoiceZ = $150K + $300QZ = $500K + $300QZ = $300K + $500QZ = $300K + $150QZ = $100K + $90Q