2 Using Excel to make business decisions and calculate optimum product mix. 3 4 5 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. 6 The current product mix is: 7 Shoe Total Medina Ballard Fremont Contribution 8 Margin 9 Pairs of Shoes 4,000.00 6,000.00 1,500.00 10 Sales price per pair $ 385 $ 11 Direct Materials per pair 35 12 Direct Labor per pair 50 1335 250 $ 180 10 10 10 13 Variable overhead per pair 55 15 15 14 Variable Selling & Admin per pair 35 10 10 15 Contribution margin per pair 210 200 135 2,242,500 16 Contribution margin percentage 55% 80% 75% 17 18 Magnolia has the following manufacturing constraints: 19 Machine hours required per pair of shoes 20 21 22 Processes Cutting Instructions ENTERANSWERS1 Medina 0.15 ENTERANSWERS2 + Total machine Ballard Fremont Total Machine hours used hours available 0.15 0.20 0 1,800 A 1 2 Requirement 1 3456 B D G H 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. Pairs 7 Price per pair 8 9 Incremental variable costs: per unit 10 11 12 13 14 Total incremental variable cost 15 Contribution Margin 16 17 Incremental Net Operating Income 18 Decision 19 20 21 New Lost Requirement 2 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 G H 2 3 4 b. Machine hours used using SUMPRODUCT 6 8 Requirement 5 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. a. Total contribution margin using SUMPRODUCT Using the formula in cell F16 (Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for formulas where appropriate to Requirement 4 9 10 11 the constraints 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 Shoe Maximize: Total 12 Total Medina contribution Ballard Fremont contribution margin using margin SUMPRODUCT 13 Number of Pairs 14 Sales price per pair 385 250 180 15 Variable expenses per pair 175 50 45 16 Contribution margin per pair 210 200 135 0 17 Contribution margin % 55% 80% 75% 18 19) Machine Hours 20 Machine Hours per pair of shoes 21 22 Cutting Medina Ballard 0.15 0.15 Instructions ENTERANSWERS1 ENTERANSWERS2 Ready Accessibilitate Type here to search Fremont Machine Hours used Machine Hours used using Resources Subject to Constraints Constraint as a Non-negat SUMPRODUCT available Constraint single formula constrain 0.20 0 1,800 022-H22 D