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 and 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 Total contribution Total Medina Ballard tionrgin using mont contribution margin SUMPRODUCT Number of Pairs Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin % 385 175 210 55% 250 50 200 80% 180 45 135 75% Machine Hours Machine Hours per pair of shoes Subject to Constraints: Machine Hours used using SUMPRODUCT MedinaBallard Fremont Machine Hours Resources Constraint as a single Non-negative available formula used Constraint G26-H26 G27Options>Add-ins In the Manage box, select Excel Add-ins and click GO. 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 and 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 Total contribution Total Medina Ballard tionrgin using mont contribution margin SUMPRODUCT Number of Pairs Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin % 385 175 210 55% 250 50 200 80% 180 45 135 75% Machine Hours Machine Hours per pair of shoes Subject to Constraints: Machine Hours used using SUMPRODUCT MedinaBallard Fremont Machine Hours Resources Constraint as a single Non-negative available formula used Constraint G26-H26 G27Options>Add-ins In the Manage box, select Excel Add-ins and click GO