Use in excel solver using the simplex LP features with excel for part of the problem. Please show screenshots of the formulas and excel workbook! This will help a ton. Part of the problem is also a handwritten network model that has to be built.

Please don't answer unless using excel and showing screenshots or this will waste my question! Thank you

Mr. Analytyks the VP of Operations and Supply Chain at Blue Bee Beverages (BBB) is planning the operations for the next year. BBB has three plants and has 60 temporary employees working at the plants. The company plans on expanding its production capacity by adding at most three more plants. ABC Consulting has identified five potential locations from which BBB can select up to three additional sites. The table below shows the current and potential sites, the variable cost (production cost per unit), and the fixed cost (production setup cost).

Site	Status	Fixed Cost	Variable Cost/carton	Annual Capacity in Cartons
1	Existing	$10,000	$15	490,000
2	Existing	$11,000	$16	410,000
3	Existing	$8700	$9	485,000
4	Potential	$8000	$17	582,000
5	Potential	$10,500	$12	585,000
6	Potential	$13,000	$9	675,000
7	Potential	$9500	$16	643,000
8	Potential	$11,125	$8	552,500

The company's supply chain consists of 3 distribution centers (DCs). Industrial distributors will do the last mile distribution of shipping the beverages to the retailstores. Thus, industrial distributors help BBB to achieve some economies of scale. Mr. Analytyks needs to worry only about the network optimization as the last mile distribution is done at the industrial distributor's cost. This means Mr. Analytyks scope is limited to determining how much to produce in each plant, the manpower required for production, and transportation of the finished products to the DCs. The table below shows the cost of transporting a carton of the beverage to the DCs from the plant locations.

Site	Status	DC1	DC2	DC3
1	Existing	$36	$34	$9
2	Existing	$28	$14	$36
3	Existing	$17	$26	$38
4	Potential	$26	$24	$19
5	Potential	$34	$8	$34
6	Potential	$21	$24	$20
7	Potential	$32	$17	$26
8	Potential	$55	$40	$19

The table below shows the forecasted demand of the product for the next 12 months at the DC locations. Mr. Analytyks is not so happy with the demand fluctuations. This means Ms. Resorzes, the head of Human Resources, may need to hire and fire some of the employees each month. She expressed her displeasure at frequenthiring and layoffwhen they spoke on the phone. Analytyks, a graduate of UNCW assured her that he can offset the HR requirements through inventory. He gave a lecture to Resorzes on how inventory, a necessary evil, can help smoothen production requirements.

Month	DC1	DC2	DC3
1	45000	45000	35000
2	45000	50000	30000
3	45000	50000	30000
4	50000	70000	80000
5	90000	100000	110000
6	140000	100000	110000
7	145000	115000	115000
8	155000	120000	115000
9	145000	110000	110000
10	45000	45000	35000
11	45000	45000	35000
12	45000	45000	35000

Currently, there are 60 workers employed at the existing plants. Each employee can produce up to 800 cartons each month.The maximum numberof workers each factory can handle is 100. The monthly cost of each employee is $3500. The hiring and firing costs are $3500 and $5000 respectively. The cost of carrying inventory is $700/carton/month. At the beginning of month 1 there was no inventory.

Mr. Analytyks has tasked you to come up with two optimization models.(i) to do the site selection, and

(ii) to find the production plan.

Model 1 Site Selection: Considerthe production and variable costs of the current and potential sites.The variable cost includesthe transportation and production cost. You shouldensure that the current sites are open always and maximum of three additional sites from the potential locations may be opened.

Analytyks suggested you to use the aggregate 12 months demand for each DC (e.g. DC1: 995,000) in the optimization model. What is the total logistics cost (production + transportation) for the forecasted demand. You are requiredto provide a robust solution. Meaning if the demand increasesby 15% and 20% from the current level. In these scenarios which among the potential sites will be selected. Based on the robust solution, what is your suggestion for the potential site.

Hint: Model 1 is a fixed chargemodel. Run the model with the base demand, note down the results; just change the demand values

Model 2 Human resources planning: Based on the currentmonthly forecast demanddata (use the monthly sum of demand of all DCs, e.g., Month 1, 125K), find the production plan for the next year. You are required to find the number of employees hired and fired each month and the total inventory of the items carried each month. What is the total cost of your production plan.