Requirement 1 Using the formula in cell F20 (Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT (Always use cell references Requirement 2 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. Requirement 3 Use Excel's Solver to compute the number of pairs of shoes for each of the three models in order to maximize the contribution margin, given the constraints. Shoe Maximize: Number of Pairs Total Medina Ballard Fremont contribution Total contribution margin using margin SUMPRODUCT Sales price per pair 385 250 180 Variable expenses per pair 175 50 45 Contribution margin per pair 210 200 135 0 Contribution margin % 55% 80% 75% Machine Hours Machine Hours per pair of shoes Medina Ballard: Fremont Machine Hours used Cutting 0.15 0.15 0.20 0 Sewing 0.25 0.10 0.10 0 Packaging 0.25 0.10 0.101 0 Machine Hours used using SUMPRODUCT Subject to Constraints: Resources available Constraint as a Constraint single formula 1,800 2,100 2,400 626H26 G27H27 628-H28 Non- negative constraint