need help fingering out how to solve my answers for the cells that are highlighted in blue.
Format Organize instructions Data ENTERANSWERS MNOP 1 2 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 52,242.500. However, some of the resources used in the manufacturing process are under tid, 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 Fremont Contribution Pairs of Shoes Sales prie per pair Variable expertes per pair Contribution margin per pair Contribution margin percentage Magnolia has the following manufacturing constraints: Machine our required Total Machine Processes Medina Ballard Fremont poursued Cutting 0.15 0.151 Sewing 0.25 0.10 Packaging * Use the blue shaded brems on the ENTER ANSWERS tab for inputs uve 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 Ung the forma 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 Excel SUPRODUCT Excel Solver mert Table Chart Text She Media Comment Formal Organi Data ENTERANSWERS Instructions O 34 Excel Skills SUMPRODUCT Excel Solver Excel Hints SUMPRODUCT Mlows you set up a simplified equation to ad up GUM, the results of cells multiplied by each other (PRODUCT). Examine the following Product Product Products Total using SUMPRODUCT -B364C3 C4.03 04 SUMPRODUCTION D3 844) Total sales to the quantity of Product sales price for Pet s the quantity of product e s ales price for product , plus the quantity of Product 3 times sales price for Product 23. The traditional formula diplayed in cell . The SUN PRODUCT formula is displayed in cell 7, which is the range of items to be multiplied, comma, the range of items to be multiplied by You can hight with the cursor the range of items in either array The Solver is an Excel add in To load Solver onto the Data ribbon in the Analyse area a. Click FleOptions Add-ins b. In the Manage box, select Excel Add-ins and click GO Magnolia Company produces the shoes in the model Medina, Ballard, and Fremont. Currently, Magnolia is manufacturing 4,000 pair of Medina, 6.00 pair of Ballard, and 1.500 pairs of Fremont during the year, for a Total.com > ORIER 25233133853 on the street the time to conteneurs constants on machinebours i ng and packaging BE 125% mert Table Chart Format Organic instructions Data ENTERANSWERS 6263628S526-52 Make Uncanned variables Monegative Simp LP the moth e r the Soler found a solution. All constraints and optimality conditions are satisfied Solver Solution Oonales Return to Soler Parameters Dialog Doline Reports DK Cancel Solver found a solution. All constraints and optimally conditions are satisfied. wherries is unai, cater Mas frames - keral prinal wiser means over has found a balc o n P art + instructions Oute ENTERANSWERS Requirement 1 Using the formula in cell F20 (Total contribution margins of the Productie template, create formulas in the Blue Shaded 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 in the formulas in the blue shaded areas Requirement Use Excel's Solver to compute the number of pairs of hoes for each of the three models order to m e the contribution Medina Ballard Fremont contribution 1100.00 0000000000 3450 710 200 2.20.500 Ballard Constraint as a negative Soliver for computing the number of pair of shoes for each of the three models refer to EN 3 G20 Use the function -SUMPRODUCT() to calculate total contribution margin. Note that cell G20 is while al s o shoes are Once Solverses for the Format Organize instructions Data ENTERANSWERS MNOP 1 2 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 52,242.500. However, some of the resources used in the manufacturing process are under tid, 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 Fremont Contribution Pairs of Shoes Sales prie per pair Variable expertes per pair Contribution margin per pair Contribution margin percentage Magnolia has the following manufacturing constraints: Machine our required Total Machine Processes Medina Ballard Fremont poursued Cutting 0.15 0.151 Sewing 0.25 0.10 Packaging * Use the blue shaded brems on the ENTER ANSWERS tab for inputs uve 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 Ung the forma 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 Excel SUPRODUCT Excel Solver mert Table Chart Text She Media Comment Formal Organi Data ENTERANSWERS Instructions O 34 Excel Skills SUMPRODUCT Excel Solver Excel Hints SUMPRODUCT Mlows you set up a simplified equation to ad up GUM, the results of cells multiplied by each other (PRODUCT). Examine the following Product Product Products Total using SUMPRODUCT -B364C3 C4.03 04 SUMPRODUCTION D3 844) Total sales to the quantity of Product sales price for Pet s the quantity of product e s ales price for product , plus the quantity of Product 3 times sales price for Product 23. The traditional formula diplayed in cell . The SUN PRODUCT formula is displayed in cell 7, which is the range of items to be multiplied, comma, the range of items to be multiplied by You can hight with the cursor the range of items in either array The Solver is an Excel add in To load Solver onto the Data ribbon in the Analyse area a. Click FleOptions Add-ins b. In the Manage box, select Excel Add-ins and click GO Magnolia Company produces the shoes in the model Medina, Ballard, and Fremont. Currently, Magnolia is manufacturing 4,000 pair of Medina, 6.00 pair of Ballard, and 1.500 pairs of Fremont during the year, for a Total.com > ORIER 25233133853 on the street the time to conteneurs constants on machinebours i ng and packaging BE 125% mert Table Chart Format Organic instructions Data ENTERANSWERS 6263628S526-52 Make Uncanned variables Monegative Simp LP the moth e r the Soler found a solution. All constraints and optimality conditions are satisfied Solver Solution Oonales Return to Soler Parameters Dialog Doline Reports DK Cancel Solver found a solution. All constraints and optimally conditions are satisfied. wherries is unai, cater Mas frames - keral prinal wiser means over has found a balc o n P art + instructions Oute ENTERANSWERS Requirement 1 Using the formula in cell F20 (Total contribution margins of the Productie template, create formulas in the Blue Shaded 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 in the formulas in the blue shaded areas Requirement Use Excel's Solver to compute the number of pairs of hoes for each of the three models order to m e the contribution Medina Ballard Fremont contribution 1100.00 0000000000 3450 710 200 2.20.500 Ballard Constraint as a negative Soliver for computing the number of pair of shoes for each of the three models refer to EN 3 G20 Use the function -SUMPRODUCT() to calculate total contribution margin. Note that cell G20 is while al s o shoes are Once Solverses for the
