It needs to be in excel. It uses solver to minimize costs. Please help

Business Case 1: MOTUR Eockground Each year, Westvaco sells more than $2 billion worth of manufactured paper, paperboard, and specialty chemicals. Production occurs at five domestic paper mills and four chemical plants. In addition, Westalty co has many converting locations, which manufacture liquid packaging. envelopes, folding cartons and comugated boxes. A typical paper mill might produce 1200 tons of paper per day to fill orders from 250 customers. Sending 100 truckload shipments per day would not be unusual for a mall served by 20 motor carriers. Sending 100 truckload shipmentsper day would not be unusual for a mill served by 20 motect to dally volume commitments and equipment availability. Each carrier has a different and somewtiat complex rate commitments and equipment availabiity, Each carrier has a diflerent and somewhat complex rate structure. Given a pool of orders that must be shipped on a given day, the mill's problem is to assign truckloads to carriers to minimize its total shipping cost. Your Role: Analyst in the Trensportation Division The corporate transportation division has a dual role at Westvaco. It supports the operating locations by negotiating freight rates and service commitments with rail, truck, and ocean carriers. In addition, it serves as an internal consulting group for reviewing operations in the field and making recommendations on streamlining tasks, making organizational changes to support changing customer requirements, and supporting the implementation of new technology. Data \& information Gathered: You have met with Marta and she has shared the following information. Scope: Marta is not tasked with planning loads. That information comes to her from a different department and she must then assign carriers to loads. She has a contract for each carrier that gives the rates to each destination served (state or zip code range). She also has a list of the trailers available for each carrier. She will select a carrier for a given shipment based on the knowledge of the best carriers for a given traffic lane, subject to availability. Some carriers have minimum daily volume commitments that must be met. After the carrier is selected for a given load, Marta updates the information in the mill's mainframe computer and displays this information in the shipping area. The selected carrier's trailer is spotted and brought to the loading dock and loading commences. The shipment information is then phoned of faxed to the carrier. An example: Marta has shared with you the following printout of data that she recently used to assign carriers to loads at Westvaco's paper mill in Wickliffe, Kentucky. The values in the "Carrier" columns The information she received from the load planner tells her that 32 truckloads are needed to distribute last night's production. In the shipping area, 33 drivers from 6 carriers are waiting for their trucks to be loaded. One truck will not be needed today. The carrier PSST has 4 trucks in the shipping area, and Westvaco has a contractual obligation to use all 4 of these trucks today. The mileage numbers in the table represent the total number of miles for the trip from Wickliffe to the final destination. Marta explains that, for example, four truckloads are needed to go to Atlanta, Georgia. These truckloads can be assigned to a single carrier, or they can be split among several carriers. When Marta did the assignment for this each of the following four sample days (shown below), she arrived at the costs of the trips generated by the manual system (shown at the bottom). Marta claims that annual shipping costs are usually around $10 million. The Task You have been tasked with consulting with the company's transportation planner, Marta Heres. She has recently been hired into the position and was surprised and concerned that carrier selection at Westvaco was done manually by transportation planners in the past. She is convinced there is a faster, cheaper and better way to do the carrier selections. Using relevant information, you should: 1) Develop a process or model to determine least-cost assignment of truckloads to carriers while meeting necessary requirements. 2) Based on the four example days given, estimate the daily savings using your model compared to the old method. Estimate using both an average dollars saved and average percent savings. 3) Estimate yearly savings using your proposed method. 4) Make a recommendation, with justification, as to whether your new approach is worthwhile to continue. Business Case 1: MOTUR Eockground Each year, Westvaco sells more than $2 billion worth of manufactured paper, paperboard, and specialty chemicals. Production occurs at five domestic paper mills and four chemical plants. In addition, Westalty co has many converting locations, which manufacture liquid packaging. envelopes, folding cartons and comugated boxes. A typical paper mill might produce 1200 tons of paper per day to fill orders from 250 customers. Sending 100 truckload shipments per day would not be unusual for a mall served by 20 motor carriers. Sending 100 truckload shipmentsper day would not be unusual for a mill served by 20 motect to dally volume commitments and equipment availability. Each carrier has a different and somewtiat complex rate commitments and equipment availabiity, Each carrier has a diflerent and somewhat complex rate structure. Given a pool of orders that must be shipped on a given day, the mill's problem is to assign truckloads to carriers to minimize its total shipping cost. Your Role: Analyst in the Trensportation Division The corporate transportation division has a dual role at Westvaco. It supports the operating locations by negotiating freight rates and service commitments with rail, truck, and ocean carriers. In addition, it serves as an internal consulting group for reviewing operations in the field and making recommendations on streamlining tasks, making organizational changes to support changing customer requirements, and supporting the implementation of new technology. Data \& information Gathered: You have met with Marta and she has shared the following information. Scope: Marta is not tasked with planning loads. That information comes to her from a different department and she must then assign carriers to loads. She has a contract for each carrier that gives the rates to each destination served (state or zip code range). She also has a list of the trailers available for each carrier. She will select a carrier for a given shipment based on the knowledge of the best carriers for a given traffic lane, subject to availability. Some carriers have minimum daily volume commitments that must be met. After the carrier is selected for a given load, Marta updates the information in the mill's mainframe computer and displays this information in the shipping area. The selected carrier's trailer is spotted and brought to the loading dock and loading commences. The shipment information is then phoned of faxed to the carrier. An example: Marta has shared with you the following printout of data that she recently used to assign carriers to loads at Westvaco's paper mill in Wickliffe, Kentucky. The values in the "Carrier" columns The information she received from the load planner tells her that 32 truckloads are needed to distribute last night's production. In the shipping area, 33 drivers from 6 carriers are waiting for their trucks to be loaded. One truck will not be needed today. The carrier PSST has 4 trucks in the shipping area, and Westvaco has a contractual obligation to use all 4 of these trucks today. The mileage numbers in the table represent the total number of miles for the trip from Wickliffe to the final destination. Marta explains that, for example, four truckloads are needed to go to Atlanta, Georgia. These truckloads can be assigned to a single carrier, or they can be split among several carriers. When Marta did the assignment for this each of the following four sample days (shown below), she arrived at the costs of the trips generated by the manual system (shown at the bottom). Marta claims that annual shipping costs are usually around $10 million. The Task You have been tasked with consulting with the company's transportation planner, Marta Heres. She has recently been hired into the position and was surprised and concerned that carrier selection at Westvaco was done manually by transportation planners in the past. She is convinced there is a faster, cheaper and better way to do the carrier selections. Using relevant information, you should: 1) Develop a process or model to determine least-cost assignment of truckloads to carriers while meeting necessary requirements. 2) Based on the four example days given, estimate the daily savings using your model compared to the old method. Estimate using both an average dollars saved and average percent savings. 3) Estimate yearly savings using your proposed method. 4) Make a recommendation, with justification, as to whether your new approach is worthwhile to continue