Short-Term Business Decisions Using Excel to calculate optimum product mix 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 underutilised, 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 Medina Ballard Fremont Contribution Margin $ 4,000 185 6,000 250S S 1,500 180 Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage 135 $ 2.242.500 Magnolia has the following manufacturing constraints: Machine hours required Total per pair of shoes machine Total Machine Processes Medina Ballard Fremont Cutting 0.15 0.15 0.20 0.25 0.10 0.10 Packaging 0.25 0.10 0.10 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. Possible points 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. 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 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 Medina Ballard Fremont Total contribution margin contribution margin using SUMPRODUCT 180 Number of Pairs Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin 250 50 200 80% 1350 55 Machine Hours Machine Hours per pair of shoes Subject to constraints: Medina Machine Hours used using SUMPIKODUCT Constraint as a single formula negative constraint Cutting 0.15 0.25 0.25 Ballard Fremont Machine Hours used 0.15 0.20 0.10 0.100 0. 100 .100 Resources available 1.800 2.100 2.400 Constraint G2-26 G27H27 G28H28 Short-Term Business Decisions Using Excel to calculate optimum product mix 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 underutilised, 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 Medina Ballard Fremont Contribution Margin $ 4,000 185 6,000 250S S 1,500 180 Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage 135 $ 2.242.500 Magnolia has the following manufacturing constraints: Machine hours required Total per pair of shoes machine Total Machine Processes Medina Ballard Fremont Cutting 0.15 0.15 0.20 0.25 0.10 0.10 Packaging 0.25 0.10 0.10 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. Possible points 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. 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 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 Medina Ballard Fremont Total contribution margin contribution margin using SUMPRODUCT 180 Number of Pairs Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin 250 50 200 80% 1350 55 Machine Hours Machine Hours per pair of shoes Subject to constraints: Medina Machine Hours used using SUMPIKODUCT Constraint as a single formula negative constraint Cutting 0.15 0.25 0.25 Ballard Fremont Machine Hours used 0.15 0.20 0.10 0.100 0. 100 .100 Resources available 1.800 2.100 2.400 Constraint G2-26 G27H27 G28H28