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. Using the formula in cell F20 (Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for (4 pts) a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT (Always use cell references and formulas where appropriate to receive full credit.) The objective and constraints are the green shaded cells. Create formulas in the blue shaded cells. (2 pts) 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. (3 pts) Once completed, save the project to the desktop. File name can be anything, but DO NOT include spaces. Return to auto-graded excel activity window, and progress to Step 3. Follow on screen instructions. 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 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 Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage 4,000 385 175 10 55% 6,000 1.500 250 $ 180 5045 200 135 80% 75% 2 $ 2,242,500 Magnolia has the following manufacturing constraints: Cutting Machine hours required Total Total per pair of shoes Machine machine Processes Medina Ballard Fremont hours used hours 0.15 0.15 0.200 1,800 Sewing 0.25 0.10 0.10 0 2.100 Packaging 0.25 0.10 0.10 0 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 1 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 2 The objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells. 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. Total sales is equal to the quantity of Product #1 times sales price for Product #1, plus the quantity of Product #2 times sales price for Product #2, plus the quantity of a. Product #3 times sales price for Product #3. The traditional formula is displayed in cell E7. The SUMPRODUCT formula is displayed in cell F7, which is the range of items to be multiplied, comma, the range of items to be multiplied by. You can highlight with the cursor the range of items in either array. 2 The Solver is an Excel add-in. To load Solver onto the Data ribbon in the Analyze area: a. Click File > Options > Add-ins b. In the Manage box, select Excel Add-ins and click GO. 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. 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. 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. 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. HINTS To use Excel's Solver for computing the number of pairs of shoes for each of the three models refer to Excel hints section on the Instructions worksheet. Cell Hint: G20 Use the function =SUMPRODUCT() to calculate total contribution margin. Note that cell G20 is zero while all pairs of shoes are zero. Once Solver solves for the optimum number of shoes that will maximize total contribuition margin, G20 will be updated with the new Total contribution margin. The Solver solution maximizes contribution margin in cell G20, by changing the number of pairs of shoes sold (C17, D17, E17), using the constraints in columns J and K. A copy of the Solver window appears in Excel hints section on the Instructions worksheet
