Please HELP I don't know what to do. Could you please answer all of question 1 (AandB) and all of question 3 (requirements:3,4,&5). THANKYOU IN ADVANCE!!!

2 2 Using Excel to make business decisions and 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. Fixed overhead costs are $273,000 for Medina, $223,000 for Ballard, and $153,000 for Fremont 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 current product mix is: Magnolia has the following manufacturing constraints: Use the blue shaded areas on the ENTER ANSWERS tab for inputs. 26 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. 27 28 29 Requirements 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 the FNTFRANSWFRS1 tah 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 work/calculations to 31 2 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 blue 3 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 the 5 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 by 200 pairs. Should they accept this special order? Show all work/calculations to justify your 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. A B C D F G I J K 2 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 (Always use cell references and 6 Requirement 4 7 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. 8 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 9 constraints. 10 \begin{tabular}{|l|l|r|r|r|r|r|} \hline 11 & & \multicolumn{3}{|c|}{ Shoe } & & Maximize: \\ \hline 12 & & & & & Total Total contribution margin using contribution margin SUMPRODUCT \\ \hline 13 & Number of Pairs & Ballard & & & \\ \hline 14 & Sales price per pair & & & & \\ \hline 15 & Variable expenses per pair & 175 & 50 & 180 & & \\ \hline 16 & Contribution margin per pair & 210 & 200 & 45 & & \\ \hline 17 & Contribution margin \% & 55% & 80% & 135 & 0 & \\ \hline 18 & & & & 75% & & \end{tabular} \begin{tabular}{l} \hline 18 \\ \hline 19 \\ \hline 20 \\ \hline \end{tabular}