*********Please answer using cell reference and formulas *********

*********Please answer using cell reference and formulas *********

*********Please answer using cell reference and formulas *********

*********Please answer using cell reference and formulas *********

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 S Variable expenses per pair Contribution margin per pair Contribution margin percentage 4.000 3 85 175 210 55% 6.000 250 50 200 80% 1.500 180 45 135 75% $ 2,242,500 Magnolia has the following manufacturing constraints: Machine hours required Total Total per pair of shoes Machine machine Processes Medina Ballard Fremont hours used hours Cutting 0.15 0.15 0.20 0 1,800 Sewing 0.25 0.10 0.10 0 2.100 Packaging 0.25 0.101 0.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 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 13. 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 2. 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. Requirements 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. 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) and K. A copy of the Solver window appears in Excel hints section on the instructions worksheet. 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 6,000 250 1,500 180 $ $ $ Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage 4,000 385 175 210 55% 50 45 10 $ 2,242,500 200 80% 135 75% Magnolia has the following manufacturing constraints: Total Machine hours required Total per pair of shoes Machine machine Processes Medina Ballard Fremont hours used hours Cutting 0.15 0.15 0.20 0 1,800 Sewing 0.25 0.10 0.10 0 2,100 Packaging 0.25 0.10 0.10 0 I 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. 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. 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 S Variable expenses per pair Contribution margin per pair Contribution margin percentage 4.000 3 85 175 210 55% 6.000 250 50 200 80% 1.500 180 45 135 75% $ 2,242,500 Magnolia has the following manufacturing constraints: Machine hours required Total Total per pair of shoes Machine machine Processes Medina Ballard Fremont hours used hours Cutting 0.15 0.15 0.20 0 1,800 Sewing 0.25 0.10 0.10 0 2.100 Packaging 0.25 0.101 0.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 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 13. 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 2. 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. Requirements 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. 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) and K. A copy of the Solver window appears in Excel hints section on the instructions worksheet. 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 6,000 250 1,500 180 $ $ $ Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage 4,000 385 175 210 55% 50 45 10 $ 2,242,500 200 80% 135 75% Magnolia has the following manufacturing constraints: Total Machine hours required Total per pair of shoes Machine machine Processes Medina Ballard Fremont hours used hours Cutting 0.15 0.15 0.20 0 1,800 Sewing 0.25 0.10 0.10 0 2,100 Packaging 0.25 0.10 0.10 0 I 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. 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