Magnolia Company produces leather shoes in three models: Medina, Ballard, and Fremont with the following product mix and costs during the year. However, some of the resources used in the manufacturing process are underutilized, leading the manager to believe that there could be alternate decisions or an alternative product mix for shoes that would increase the Total contribution margin. The cul Magnolia has the following manufacturing constraints: Use the blue shaded areas on the ENTER ANSWERS tab tor inputs. 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. Rt Assume that Magnolia expects to produce and sell 4,000 pairs of Medina during the current year. One of Magnolia's sales representatives has found a new customer that is willing to buy 500 additional pairs for a price of $335 per pair. If they accept the customer's offer, it will decrease sales to regular customers by 200 pairs. Should they accept this special order? Show all work/calculations to justify your decision in 1 the ENTERANSWERS1 tab. Assume that Magnolia expects to produce and sell 4,000 pairs during the current year. A supplier has offered to manufacture and deliver 4,000 pairs for a price of $190 per pair. Should Magnolia accept this offer? How much will profit's increase or decrease? Show all 2 work/calculations to justify your decision in the ENTERANSWERS1 tab. Using the formula in cell F16 on the ENTERANSWERS2 tab (Total contribution margin) of the Product Mix template, create formulas in the 3 blue shaded cells for: a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT 4 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 5 the constraints. Assume that Magnolia expects to produce and sell 4,000 pairs of Medina during the current year. One of Magnolia's sales representatives has found a new customer that is willing to buy 500 additional pairs for a price of $335 per pair. If they accept the customer's offer, it will decrease sales to regular customers bv 200 pairs. Should thev accept this special order? Show all work/calculations to iustifv vour decision. Assume that Magnolia expects to produce and sell 4,000 units during the current quarter. A supplier has offered to manufacture and deliver 4,000 units for a price of $190 per unit. Should Magnolia accept this offer? How much will profit's increase or decrease? Show all work/calculations to justify your decision. Requirement 3 Using the formula in cell F16 (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 and formulas where Requirement 4 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. Requirement 5 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. \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline & \multicolumn{3}{|c|}{ Shoe } & & \multirow{2}{*}{\begin{tabular}{c} Maximize: \\ Total \\ contribution \\ margin using \\ SUMPRODUCT \\ \end{tabular}} & & & & \\ \hline & Medina & Ballard & Fremont & \begin{tabular}{c} Total \\ contribution \\ margin \end{tabular} & & & & & \\ \hline \multicolumn{10}{|l|}{ Number of Pairs } \\ \hline Sales price per pair & 385 & 250 & 180 & & & & & & \\ \hline Variable expenses per pair & 175 & 50 & 45 & & & & & & \\ \hline Contribution margin per pair & 210 & 200 & 135 & 0 & & & & & \\ \hline \multirow[t]{5}{*}{ Contribution margin %} & 55% & 80% & 75% & & & & & & \\ \hline & & & & & & & & & \\ \hline & \multicolumn{5}{|c|}{ Machine Hours } & \multirow[b]{3}{*}{\begin{tabular}{c} Resources \\ available \end{tabular}} & & & \\ \hline & \multicolumn{3}{|c|}{ Machine Hours per pair of shoes } & & & & \multirow[b]{2}{*}{ Constraint } & \multicolumn{2}{|c|}{ Subject to Constraints: } \\ \hline & Medina & Ballard & Fremont & \begin{tabular}{c} Machine \\ Hours used \end{tabular} & \begin{tabular}{l} Machine Hours \\ used using \\ SUMPRODUCT \end{tabular} & & & \begin{tabular}{c} Constraint as a \\ single formula \end{tabular} & \begin{tabular}{c} Non- \\ negative \\ constraint \end{tabular} \\ \hline Cutting & 0.15 & 0.15 & 0.20 & 0 & & 1,800 & F22