answer part of excel. (the #'s in red are my answers. And I think they are incorrect.
Instructions ENTERANSWERS L011: A B c o E r e H I J K L M N o P (1in 1 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 4 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. 6 The current product mix is: 7 Shoe Total 8 Medina Ballard Fremont Contribution Margin 9 Pairs of Shoes 4,000 6,000 1,500 10 Sales price per pair S 385 S 250 S 180 \"1 Variable expenses per pair 175 50 45 12 Contribution margin per pair 210 200 135 5 2,242,500 13 Contribution margin percentage 55% 80% 75% 1A 15 Magnolia has the following manufacturing constraints: 16 ,7 Machine hours required 13:13} - mac Ine per Pa" 0f shoes Total Machine hours 18 Processes Medina Ballard Fremont hours used available 19 Cutting 0.15 0.15 0.20 - 1,800 20 Sewing 0.25 0210 0.10 - 2,100 21 Packaging 0.25 0.10 0.10 , 2,400 22 Use the blue shaded areas on the ENTER ANSWERS tab for inputs. 23 ALWAYS use cell references and formulas where appropriate to receive full credlt. If you copy/paste from the Instruction tab you will be marked wrong. 24 25 26 Requirements Possible points 27 Using the formula in cell no on the ENTERANSWERS tab {Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for: 4 28 a. Total contribution margin using SUMPRODUCI' 29 b. Machine hours used using SUMPRODUCT 30 The objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells. 2 31 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 32 33 34 Excel Skills 35 SUMPRODUCT 36 Excel Solver 37 125% T View Zoom Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize + Instructions Data ENTERANSWERS O O P A C E F G H K M N 34 Excel Skills 35 SUMPRODUCT 36 Excel Solver 37 38 39 Excel Hints 40 SUMPRODUCT allows you set up a simplified equation to add up (SUM) the results of cells multiplied by each other (PRODUCT). Examine the following: 41 42 A B D 43 44 N Product #1 Product #2 Product #3 Total Total using SUMPRODUCT 45 3 Quantity 5 10 15 46 4 Sales Price 100 200 300 UT 48 6 Total Sales 7,000 7,000 7 Formulas =(B3*B4)+(C3*C4)+(D3*D4) =SUMPRODUCT(B3:D3,B4:D4) 50 8 51 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 Product #3 52 a. 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. 53 The Solver is an Excel add-in. To load Solver onto the Data ribbon in the Analyze area: 54 a. Click File > Options > Add-ins 55 b. In the Manage box, select Excel Add-ins and click GO. 56 excel Options 57 General View and manage Microsoft Office Add-ins. 58 Formulas Proofing Add-ins 59 active Application Add-ins Location 60 anguage Analysis ToolPak C:\\..of\\ Office 16\\Library\\Analysis\\ANALYSE2.XLL Excel Add-in Advanced 61 Customize Ribbon Inactive Application Add-ins Analysis ToolPak - VBA C:\\.Office16\\Library\\Analysis\\ATPVBAEN.XLAM Excel Add-in 62 Quick Access Toolbar Date (XML) C:L.iles\\Microsoft Shared\\ Add-ins More Actions Pane C:Lice\ oof\\ Office16\\Library\\EUROTOOL.XLAM Excel Add-in 63 Trust Center Microsoft Power Map for Excel Solver Add-in C:Aur Map Excel Add-in\\EXCELPLUGINSHELL.DLL COM Add-in L.on\\Office16\\Library\\SOLVER\\SOLVER.XLAM Excel Add-in 64 cument Related Add-ins 66 Disabled Application Add-ins No Disabled Application Add-in Add-in: Analysis ToolPak Publisher: Microsoft Corpo 68 Compatibility: No compatibility information available Location: C:\\Program Files (x86)\\Microsoft Office\ oot\\Office16\\Library\\Analysis \\ANALYS2.XLL escription: Provides data analysis tools for statistical and ngineering analysis 70 Manage: Excel Add-ins Text 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 cont 125% T Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize View Zoom + Instructions Data ENTERANSWERS O A B C E G H K M N O P SC$17:SE$17 > = 0 Add 105 SG$26:SG$28 125% T View Zoom Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize + Instructions Data ENTERANSWERS G Excel mints M N O P 40 SUMPRODUCT allows you set up a simplified equation to add up (SUM) the results of cells multiplied by each other (PRODUCT). Examine the following: 41 42 A B D E 43 44 Product #1 Product #2 Product #3 Total Total using SUMPRODUCT 45 Quantity 10 15 46 Sales Price 100 200 300 47 UI A W 48 6 Total Sales 7,000 7,000 49 7 Formulas =(B3*B4)+(C3*C4)+(D3*D4) =SUMPRODUCT(B3:D3,B4:D4) 50 8 51 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 Product #3 52 a. 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. 53 The Solver is an Excel add-in. To load Solver onto the Data ribbon in the Analyze area: 54 a. Click File > Options > Add-ins 55 b. In the Manage box, select Excel Add-ins and click GO. 56 57 Excel Options General 58 Formulas View and manage Microsoft Office Add-ins. 59 Proofing Add-ins Name - 60 Language Active Application Add-ins Advanced Analysis ToolPak C:\\.of\\Office16\\Library\\Analysis\\ANALYSB2.XLL Excel Add-in 61 Customize Ribbon Inactive Application Add-ins Analysis ToolPak - VBA C:\\.Office 16\\Library\\Analysis \\ATPVBAEN.XLAM Excel Add-in 62 Quick Access Toolbar C:\\_iles\\Microsoft Shared\\Smart Tag\\MOFLDLL Add -ins Euro Currency Tools Microsoft Actions Pan C:\\_ice\ oot\\Office16\\Library\\EUROTOOL.XLAM Excel Add-in 63 ust Center Microsoft Power Map for Excel Solver Add-in C:\\_r Map Excel Add-in\\EXCELPLUGINSHELL.DLL COM Add - in C:\\.of\\Office16\\Library\\SOLVER\\SOLVER.XLAM Excel Add-in 64 Document Related Add-ins 65 No Document Related Add-in Disabled Application Add-ins No Disabled Application Add-ins Add-in: Publisher: Analysis ToolPak Microsoft Corporation Compatibility. No compatibility information available Location: C:\\Program Files (x86)\\Microsoft Office\ oot\\Office 16\\Library\\Analysis \\ANALYSE2.XLL Description: Provides data analysis tools for statistic ering analysis Manage: Excel Add-ins Go .. 72 C. 73 Add-ins X 74 Add-ins available: 75 Analysis ToolPak OK 76 Analysis ToolPak - VBA Euro Currency Tools Cance 77 Text 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 cont 125% EE T View Zoom Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize Instructions Data ENTERANSWERS O B C D E G H K 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 and Requirement 2 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. 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 15 Shoe Maximize: Total Total 16 Medina Ballard Fremont contribution contribution margin margin using SUMPRODUCT Number of Pairs 4,000.00 6,000.00 1,500.00 Sales price per pair 385 250 180 Variable expenses per pair 175 50 45 Contribution margin per pair 210 200 135 2,242,500 11,500 Contribution margin % 55% 80% 75% Machine Hours Machine Hours per pair of shoes Subject to Constraints: 25 Medina Ballard Fremont Machine Hours Non- Machine used using Resources Constraint as a negative Hours used SUMPRODUCT available Constraint single formula constraint 26 Cutting 0.15 0.15 0.20 1800 1,800 G26
