BraydonFarm LLC. of Troy, AL supplies fresh flowers and plants to four stores of Marvins Building Materials A retail chain selling home improvement goods across the state of Alabama.The locations of the five Marvins stores are on a separate sheet provided by the proctor during test time:

During peak season, a full truck load (FTL) of flowers can only serve three of the five stores.BraydonFarm has only one truck to carry out deliveries from its farm to retail outlets. Instead of charting out the two delivery routes on the state map, the farm owner has turned to SCOB of Troy University and ask you to set up an Excel model and find the optimal routes that will minimize the total delivery mileage.

Requirements:

1. Each route to be represented by a sequence of store numbers, like 1-5. Flowers will be loaded up onto the truck at the Troy loading dock and then driven to the two or three stores for unloading and return to the loading dock for replenishment.
2. Use theINDEX(array,row_num,[col_num])function in Excel to translate route (sequence of two numbers) into distances.
3. Use Google Map or other mapappsto find the distances between stores and between the loading dock and stores
4. In Excel Solver settings, please specify the objective, decision variables, and constraints and then solve the problem by finding the optimal solution.

Grading Criteria:

1. Set up your Excel model that must have:
   1. A 5X5 matrix containing the distances between stores and between loading dock and stores. The distances between loading dock and stores should be stored along the diagonal of the matrix (5 pt).
   2. An assignment vector that contains the store numbers, which will be the decision variables in the Excel model (2 pt).
   3. The total mileage that is the total of the two trip mileages, which will be in the objective cell (3 pt).
2. Solved the Model using Excel Solver and present the routes to the driver in a way that he can follow (8 pt).
3. What is themaximal possible total mileage inservicing the same set of stores?Estimate the maximal $fuel cost savings (3 pt).
4. Summarize your feelings and thoughts after solving such a real business problem as compared to those problems from test bank (2 pt).
5. Submit your Excel file before deadline (2 pt)