Help me use solver in excel to solve this, i need step by step instructions through excel please?

Problem 1 Production Mix Problem Formulation, Excel Solver Solution and Interpretation

Bills Sport Company manufactures three types of footballs: High School, College and All-pro. Each football requires cutting, dying, sewing and inspection. Based on available equipment, the tables below show the machine hours required for each activity by type of football. The last column also shows the total machines hours available each month.

	High School	College	All Pro	Hours Available
Cutting	.6	.2	.2	1,500
Dying	.1	.3	.5	900
Sewing	.2	.4	.4	1,000
Logo Embroidery	.1	.1	.1	1,000

The market for all products is large and the company believes that it can sell all the footballs that it makes. The next table shows the revenue per unit sold and the material cost per unit sold.

Product	Revenue per Unit ($)	Material Cost per Unit ($)
High School	52	10
College	54	20
All-Pro	70	30

In addition, the variable labor costs are $15/hour for cutting, $20/hour for dying, $20/hour for sewing and $10 hour for embroidery.

a) Using MS-Excel, formulate an LP problem to maximize profits (hint, the objective function should compute the sum of the profits of each football type). Name the worksheet Football Problem.

b) Use MS Excel Solver to find the optimal number of High School, College and All Pro footballs to produce. Create a sensitivity analysis. In a text box on the problem worksheet, explain in words what the optimal solution values are and what the maximum profit value is.

Rename the sensitivity report worksheet Football Sensitivity. On this worksheet, write answers to the questions below in a textbox.

c) Which equipment resources have been fully utilized (e.g. what constraints are binding)? Is there any equipment which has not been fully utilized? If so, what is the slack?

How much should management be willing to pay for one additional machine hour of time for cutting, dying, sewing and embroidery?

e) Interpret the range of optimality of each the objective function coefficients.

f) If the profit contribution of the high school football was increased by $12, what would be the new optimal solution (decision variables) values? Explain how to you computed the new profit without resolving the problem.

Problem 2 Diet Problem Formulation, Excel Solver Solution and Interpretation

A dietician wants to make a snack for an athletic team by mixing ounces of Product A and Product B. He wants to meet the following nutritional requirements at minimum cost:

Nutrient	Contribution / Ounce (gr) Product A	Contribution / Ounce (gr) Product B	Minimum Required (gr)
Carbohydrates	2	5	20
Protein	6	1	12
Calories	90	50	450

Product A costs $.20 per ounce and Product B costs $.10 per ounce.

a) Using MS-Excel, formulate an LP problem to minimize costs. Name the worksheet Diet Problem.

b) Use MS Excel Solver to find the optimal number of ounces of product A and Product B. Create a sensitivity analysis. In a text box on the problem worksheet, explain what the optimal solution values are and what the optimum cost per snack is.

Rename the sensitivity report worksheet Diet Sensitivity. On this worksheet, write answers to the questions below in a textbox.

c) Are there any requirements exceeded (e.g.)? If so, which ones and by how much?

d) Interpret the range of optimality of each the objective function coefficients.

Interpret the shadow prices for each of the constraints.