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.
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
- Using Excel To Calculate Optimum Product Mix Magnolia Company Produces Leather Shoes In Three Models Medina Ballard 1 (49.93 KiB) Viewed 40 times
- Using Excel To Calculate Optimum Product Mix Magnolia Company Produces Leather Shoes In Three Models Medina Ballard 2 (47.86 KiB) Viewed 40 times
Drone Buss 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 Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per palt Contribution margla percentage Total Medina Ballard Fremont Contribution Margin 4.000 6,000 1,500 $ 385$ 250 $ 180 175 50 45 210 200 135 2.242,500 55% NOX 78% Manolla has the following manufacturing constraints: Machine hours required Total per pair of shoes Total Machine machine Processes Medina Balland Fremont hours used hours Cutting 0.15 0.15 0.20 0 1,100 Sewing 0.25 0.10 0.10 0 2.100 Packaging 0.25 0,10 0,10 0 2.400 Like the blue shaded areas on the ENTER ANSWERS tab for inputs. ALWAYS Le cell references and formulas where appropriate to receive full credit. If you copy/paste from the instruction tab you will be married work 26 Itequirements 17 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 29 . Machine hours used using SUMPRODUCT 30 2 The objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells. 1 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.
Requirement1 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 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 Medina Hallard Maximize Total contribution margin using SUMPRODUCT Total contribution margin Fremont Number of Pairs Sales price per pair Variable expenses per pair Contribution margin per pale Contribution margin 385 175 250 50 200 80% 180 45 135 25% 210 0 55% Machine Hours Machine Hours per pair of shoes Medina Ballard Fremont Machine Hours used using SUMPRODUCT Subject to constraints Non Constraint as a negative single formula constraint Cutting Sewing Packaging 0.15 0.25 0.25 0.15 0.10 0.20 0.10 0,10 Machine Hours und 9 0 0 Resources available 1.800 2.100 2.400 Constraint G2626 02722 G2H28 0.10