these are the instuctions

the answer blanks are below

Magnolia Company produces leather shoes in three models: Medina, Baltard, 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 Totat 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 c Magnolia has the followieg manufactaring constraints: fiequirements Possible points 4 a. Total contribution margin using suMphoDuct b. Machine hours unedusing SuMphoouct 2 The objectrve and constraints heudings are the gieen shaded cells, Create formulas in the blueshaded chilis. 1. Ute frepl's Solver to compule the number of pairs of shoes for each of the three models in order to maximile the contribution murbir, given the constraints. 2 3 Excelskilh 1 sumproouct 2 excelsolme Getelthints \begin{tabular}{|c|c|c|c|c|c|c|} \hline 4 & A & b & c & a. & E & 1 \\ \hline \multicolumn{7}{|c|}{ Product 8 Product n} \\ \hline 3 & & Product n & Product n & Product =3 & Total & Total ualne SUMPRODUCT \\ \hline 1 & Ouantity & 3 & 10 & 35 & & \\ \hline 4 & Sales Price & 100 & 200 & 300 & & \\ \hline \multicolumn{7}{|c|}{ 5i Totol Solet } \\ \hline & Total Salen & & & & 7000 & 7,000 \\ \hline 1) & Fermulat & & & & =(63(4)+i()+(4)+(0304) & =5UMPhobuct(23 01,04-04) \\ \hline E & & & & & & \\ \hline \end{tabular} Total sales is equal to the quantity of Product #1 times sales price for Product =1. plus the quantity of Product #2 times sales peice for Product 12 , plus the quantity of a. Product il times sales price for Product m. The traditional formula is driplayed in cell E7. The sumphooucr 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 cao highlight with the cursor the range of items in either array. 2 The Solver is an Ercel add-in. To load Solver onto the Data ribbon in the Analyce areat a. Click. File > Options > Add ins d. Solver appeurs on the Data Fibbon. cliak on the solver tool Set up the objective (maximize Total contnbution margin/ subject to the constraints sthe number of pairs of shoes is zero of more, and the constraints on machine hours for cutting sewing, and packegise) more, and the comstraints on machine hours for cutting sewing, and packagingl It tavi fale to deticiog. 7s saving R- Submitting Solution 1 sawe file to desktop. a. Create folder on desktop, and label COMPLETE EXCEL PROIECTS 2 upload and submit your file to Carws to be graded. a. Navigate back to the activity window - screen where you downloaded the instial spreadsheet. b. Click choose button under step 3; locate the file you just saved and click Open c. Click Upload button understep 3 d. Chack Submit button under step 4 Vhewine thevilts 1 Click on flesults tab in MyAccountindab 2 click on the Assigament you were working on I click on Project linky this will bring up your Score summary 4. Within Score Surnary window, click on Download (upper right) to download the spreadsheet with feedback Requirement 1 Using the formula in cell 120 (Total contnbution 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 (Atways 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 2 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. Mequirement 3 Use Excel's solver to compute the number of pains of shoes for each of the three models in order to maximize the contribution margin, liven the constraints. \begin{tabular}{|c|c|c|c|c|c|} \hline & \multicolumn{3}{|c|}{ Shoet } & \multirow[b]{2}{*}{\begin{tabular}{c} Total \\ contribution \\ margin \end{tabular}} & \multirow[b]{2}{*}{\begin{tabular}{|c|} Maximize: \\ Total \\ contribution \\ margin using \\ sumproouct \\ \end{tabular}} \\ \hline & Medina & Ballard & fremont & & \\ \hline \multicolumn{6}{|l|}{ Number of Pairs } \\ \hline sales price per pair & 245 & 250 & 150 & & \\ \hline Variable expenses per pair & 175 & 50 & 45 & & \\ \hline Contritistion margin per pair & 210 & 200 & 135 & 0 & \\ \hline Contribution margin x & 356 & nons & & & \\ \hline \end{tabular} lavis. Crilininit