READ INSTRUCTIONS THESE ARE DIFFERENT NUMBERS!! NEED ONLY REQUIREMENTS 1, 2, &3

Magnolia Company produces leather shoes in three models: Medina, Ballard, and FremontCurrently, Magnolia is manufacturing 4,000 pairs of Medina6,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 marginMagnolia can sell all shoes produced

2 Using Excel to calculate optimum product mix. 3 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 4 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 mox i Shoe Medina Ballard Fremont Total Contribution. Margin Pairs of Shoes 1,500 4,000 385 $ Sales price per pair 100 Variable expenses per pair 175 45 210 135 $2,242,500 Contribution margin per pair Contribution margin percentage 55% 75% 14 15 Magnolia has the following manufacturing constraints 16 17 18 Processes Medina Total Machine Fremont hours used 0.20 0 Cutting dan Instructions ENTERANSWERS A 19 10 11 12 13 S 6,000 250 $ 50 200 30% Machine hours required per pair of shoes Ballard 0.15 0.15 D Total machine hours 1.000 KARA Machine hours required 17. per pair of shoes 18 Processes Medina Ballard Total Machine hours used 0 0 19. Cutting 0.15 Fremont 0.20 0.10 0.15 1,800 201 Sewing 0.25 0.10 7,100 Packaging 0.25 0.10 0.10 0 2,400 22 te the blue shaded areas on the ENTER ANSWERS tab for inputs. 23 ALWAYS une rell references and formalas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong. 26 Requirements thing the formula in cell F20 on the ENTERANSWERS tab (Fotal contribution margin) of the Product Mix template, create formulas in the blue shaded cells for Total contribution margin using SUMPRODUCT 20 29 Machine hours used using SUMPRODUCT 30 3 the objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells 31 3 the 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 32 53 14 talk 1 SUMPRODUCT 2 Excel Solver B Excel Mint Total machine hours Formulas Data Review mert Home Daw Page Layout 3 x f O D 1 Requirement 1 3 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 3 4 h Machine hours used using SUMPRODUCT (Always use cell references 5 6 7 0 Requirement 2 9 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. 10 11 12 Requirement 3 13 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. 14 Shoe 15 Maximize Total Total Medina: Ballard Fremont contribution contribution margin using margin SUMPRODUCT Number of Pairs 4.000.00 6,000.00 1,500.00 Sales price per pair 385 250 100 175 50 45 Variable expenses per pair Contribution margin per pair Contribution margin 210 200 135 2,242,500 11.500 55% 80% 75% nitructions un File 626 2022855 16 17 10 19 21 ENTERANSWERS 4 MI M Number of Pairs Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin Cutting Sewing Packaging Total Fremont contribution 1,300.00 180 45 135 Medina Ballard 4000.00 6,000.00 385 250 175 50 210 200 55% 90% Machine Hours per pair of shoes Medina Rallard Fremont 0.15 0.15 0.25 0.10 0.25 0.30 Total contribution margin using margin SUMPRODUCT 2,242,500 11,500 Machine Hours used using SUMPRODUCT 75% Machine Hours 0.20 0.10 0.10 Machine Hours used 1800 1750 1750 Resources available: 1,800 2,100 2,400 Constraint G250 H25 G270 H27 G28 H28 Subject to Constraints: Constraint as a single formula Non- negative constraint