blue boxes is what teacher wants answered
125% T View Zoom Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize Instructions Data ENTERANSWERS O A B C D E G H K M N O P Short-Term Business Decisions W N 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 Total 8 Medina Ballard Fremont Contribution Margin Pairs of Shoes 4,000 6,000 1,500 10 Sales price per pair $ 385 $ 250 | $ 180 Variable expenses per pair 175 50 45 12 Contribution margin per pair 210 200 35 $ 2,242,500 Contribution margin percentage 55% 80% 75% 14 15 Magnolia has the following manufacturing constraints: 16 Total 17 Machine hours required per pair of shoes Total Machine machine Ballard hours 18 Processes Medina Fremont hours used available Cutting ).15 0. 15 0.20 1,800 20 Sewing 0.25 0.10 0. 10 2, 100 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 credit. If you copy/paste from the Instruction tab you will be marked wrong. 24 25 26 Requirements Possible points IN 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: 4 28 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. W N 33 34 Excel Skills 35 SUMPRODUCT 36 Excel Solver 37125% T View Zoom Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize + Instructions Data ENTERANSWERS Excel mints G 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\\ANALYS 2.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:Lice\ oot\\Office16\\Library\\EUROTOOL.XLAM Excel Add-in 63 ust Center Microsoft Power Map for Excel Solver Add-in C:\\ur Map Excel Add-in\\EXCELPLUGINSHELL.DLL COM Add - in C:\\._ot\\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\ oof\\ Office16\\Library\\Analysis \\ANALYSB2.XLL Description: Provides data analysis tools for statistic ering analysis Manage: Excel Add-ins 72 C. 73 Add-ins X 74 Add-ins available: 75 Analysis ToolPak Analysis ToolPak - VBA OK 76 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% T View Zoom Add Category Insert Table Chart Text Shape Media Comment Collaborate Format Organize + Instructions Data ENTERANSWERS O A C E F G H K M N O P 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 49 7 Formulas =(B3*B4)+(C3*C4)+(D3*D4) =SUMPRODUCT(B3:D3,B4:D4) 8 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 xcel Options 57 General Formulas View and manage Microsoft Office Add-ins. 58 59 Proofing Add-ins Location Language Active Application Add-ins 60 Analysis ToolPak C.\\..of\\Office 16\\Library\\Analysis\\ANALYS2.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:Liles \\Microsoft Shared\\ Add-ins Minute Actions Pane C.L.ice\ oof\\Office 16\\Library\\EUROTOOL.XLAM Excel Add-in 63 Trust Center Microsoft Power M Solver Add-in C:Aur Map Excel Add-in\\EXCELPLUGINSHELL. DLL COM Add-in Lot\\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 gineering 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 lnstructions ENTERANSWERS 1 Requirement 1 2 Using the formula in cell F20 (Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for 3 a. Total contribution margin using SUMPRODUCT 4 b. Machine hours used using SUMPRODUCT 5 (Always use cell references and 6 7 8 Requirement 2 9 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. 10 11 12 r Requirement 3 13 Use Excel's Solver to compute thqj number of airs of shoes for each of the three models in order to maximize the contribution margin, given the constraints. 14 15 Shoe Maximize: To al Total 16 Medina Ballard Fremont contribution fnontribution margin argin using SUMPRODUCT 17 Number of Pairs 4,000.00 6,000.00 1,500.00 18 Sales price per pair 385 250 180 19 Variable expenses per pair 175 50 45 20 Contribution margin per pair 210 200 135 2,242,500 11,500 21 Contribution margin % 55% 80% 75% 22 23 Machine Hours 24 Machine Hours per pair of shoes Subject to Constraints: Machine Hours Non- 25 Medina Ballard Fremont 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 626
