Please inclue the excel solver in the solution, thank you

This problem is a combination of binary integer programming and transportation problem

A firm has six sales centers in Quebec and has decided to open new warehouses to deliver its goods from the warehouses to the sales centers. There are two types of costs associated with the delivery: set-up costs (fixed costs) are capital costs which may usually be written off over several years, and transportation costs which depend on the distance covered. We assume that they have been put on some comparable basis, by taking the costs over a year.

There are 5 sites available for the construction of new warehouses to deliver goods to the six sales centers.

The following table (Table 1) gives the transportation costs (in thousand dollars) of delivering the entire demand of each sales center from a warehouse (not the unit costs). Certain deliveries that are impossible are marked with the infinity symbol ().

Sales Centers
Warehouse	1	2	3	4	5	6
1	100	80	50	50	60	100
2	120	90	60	70	65
3	140	120	80	80	75	130
4	160	125	100	100		150
5	190	150	130

Table 1: Transportation costs (in $1000) for satisfying entire demand of each sales center

The construction costs (fixed cost) for each warehouse as well as the capacity of each warehouse are listed in Table 2.

Warehouse	1	2	3	4	5
Cost ($1000)	4000	9000	10000	5000	9000
Capacity(tons)	280	250	100	180	250

Table 2: Fixed costs (in $1000) and capacity limits of the warehouse locations

There are estimations for demand of each sales center which are shown in Table 3.

Sales center	1	2	3	4	5	6
Demand (tons)	150	90	80	160	100	200

Table 3: Demand data

Considering that the demand of a sales center needs to be satisfied and a sales center may be delivered to from several warehouses.

Formulate algebraically the corresponding model that will determine which warehouses should be opened to minimize the total cost of construction and of delivery, while satisfying all demands. Define the decision variables, objective function, and constraints. (25 points)

Formulate this problem on a spreadsheet and use Excels Solver to determine which warehouse the firm should operate and how goods should be distributed from each warehouse to each sales center to minimize the total cost. (Provide the corresponding Excel Spreadsheet and the Answer Report). Include managerial statements that communicate the results of the analyses (i.e. describe verbally the results). (20 points