Magnolia Company produces leather shoes in three models: Medina, Ballard, and Fremont. Currently, Magnolia is manufacturing 4,000 pairs of Medina, 6,000 pairs of Ballard, and 1,500 pairs of Fremont during the year, for a Total contribution margin of $2,242,500. However, some of the resources used in the manufacturing process are underutilized, leading the manager to believe that there could be an alternative product mix for shoes that would increase the total contribution margin. Magnolia can sell all shoes produced. The current product mix is: Shoe Medina Ballard Fremont Total Contribution Margin Pairs of Shoes 4,000 6,000 Sales price per pair $ 385 $ 250 $ 1,500 180 Variable expenses per pair 175 50 45 Contribution margin per pair 210 200 Contribution margin percentage 55% 80% 135 $2,242,500 75% Magnolia has the following manufacturing constraints: Machine hours required per pair of shoes Processes Cutting Sewing Packaging Total Machine Medina Ballard Fremont hours used machine hours available 0.15 0.15 0.20 0 1,800 0.25 0.25 0.10 0.10 0.10 0 0.10 0 2,100 2,400 Use the blue shaded areas on the ENTER ANSWERS tab for inputs. ALWAYS use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong. Requirements # #### #### Using the formula in cell F20 on the ENTERANSWERS tab (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 The objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells. 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.