Magnolia Company produces leather shoes in three models: Medina, Ballard, and Fremont. Currently, Magnolia is manufacturing...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image 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 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. Using the formula in cell F20 (Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for (4 pts) a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT (Always use cell references and formulas where appropriate to receive full credit.) The objective and constraints are the green shaded cells. Create formulas in the blue shaded cells. (2 pts) 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. (3 pts) Once completed, save the project to the desktop. File name can be anything, but DO NOT include spaces. Return to auto-graded excel activity window, and progress to Step 3. Follow on screen instructions. Short-Term Business Decisions 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: Medina $ Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage Magnolia has the following manufacturing constraints: 4,000 385 $ 175 210 55% Medina Shoe Ballard 0.15 0.25 6,000 250 $ 50 200 80% Machine hours required per pair of shoes Ballard Total Fremont Contribution Margin 0.15 0.10 0.10 1,500 180 45 135 $ 2,242,500 75% Fremont Processes Cutting Sewing Packaging 0.25 Use the blue shaded areas on the ENTER ANSWERS tab for 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. Total Machine hours used 0 0 0 0.20 0.10 0.10 Total machine hours 1,800 2,100 2,400 Requirements 1 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: a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT 2 The objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells. 3 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. 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 a. Product #3 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. 2 The Solver is an Excel add-in. To load Solver onto the Data ribbon in the Analyze area: a. Click File > Options > Add-ins b. In the Manage box, select Excel Add-ins and click GO. 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. 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. 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. Requirement 1 Using the formula in cell F20 (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 Requirement 2 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. Requirement 3 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. HINTS To use Excel's Solver for computing the number of pairs of shoes for each of the three models refer to Excel hints section on the Instructions worksheet. Cell | Hint: G20 | Use the function =SUMPRODUCT() to calculate total contribution margin. Note that cell G20 is zero while all pairs of shoes are zero. Once Solver solves for the optimum number of shoes that will maximize total contribuition margin, G20 will be updated with the new Total contribution margin. The Solver solution maximizes contribution margin in cell G20, by changing the number of pairs of shoes sold (C17, D17, E17), using the constraints in columns J and K. A copy of the Solver window appears in Excel hints section on the Instructions worksheet. 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. Using the formula in cell F20 (Total contribution margin) of the Product Mix template, create formulas in the blue shaded cells for (4 pts) a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT (Always use cell references and formulas where appropriate to receive full credit.) The objective and constraints are the green shaded cells. Create formulas in the blue shaded cells. (2 pts) 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. (3 pts) Once completed, save the project to the desktop. File name can be anything, but DO NOT include spaces. Return to auto-graded excel activity window, and progress to Step 3. Follow on screen instructions. Short-Term Business Decisions 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: Medina $ Pairs of Shoes Sales price per pair Variable expenses per pair Contribution margin per pair Contribution margin percentage Magnolia has the following manufacturing constraints: 4,000 385 $ 175 210 55% Medina Shoe Ballard 0.15 0.25 6,000 250 $ 50 200 80% Machine hours required per pair of shoes Ballard Total Fremont Contribution Margin 0.15 0.10 0.10 1,500 180 45 135 $ 2,242,500 75% Fremont Processes Cutting Sewing Packaging 0.25 Use the blue shaded areas on the ENTER ANSWERS tab for 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. Total Machine hours used 0 0 0 0.20 0.10 0.10 Total machine hours 1,800 2,100 2,400 Requirements 1 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: a. Total contribution margin using SUMPRODUCT b. Machine hours used using SUMPRODUCT 2 The objective and constraints headings are the green shaded cells. Create formulas in the blue shaded cells. 3 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. 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 a. Product #3 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. 2 The Solver is an Excel add-in. To load Solver onto the Data ribbon in the Analyze area: a. Click File > Options > Add-ins b. In the Manage box, select Excel Add-ins and click GO. 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. 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. 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. Requirement 1 Using the formula in cell F20 (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 Requirement 2 The objective and constraints headings are the green shaded cells, fill in the formulas in the blue shaded areas. Requirement 3 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. HINTS To use Excel's Solver for computing the number of pairs of shoes for each of the three models refer to Excel hints section on the Instructions worksheet. Cell | Hint: G20 | Use the function =SUMPRODUCT() to calculate total contribution margin. Note that cell G20 is zero while all pairs of shoes are zero. Once Solver solves for the optimum number of shoes that will maximize total contribuition margin, G20 will be updated with the new Total contribution margin. The Solver solution maximizes contribution margin in cell G20, by changing the number of pairs of shoes sold (C17, D17, E17), using the constraints in columns J and K. A copy of the Solver window appears in Excel hints section on the Instructions worksheet.
Expert Answer:
Answer rating: 100% (QA)
The product ranking has to be done first to allocate the resources available here the machine hours ... View the full answer
Related Book For
Cornerstones of Cost Management
ISBN: 978-1285751788
3rd edition
Authors: Don R. Hansen, Maryanne M. Mowen
Posted Date:
Students also viewed these accounting questions
-
In doing cost analysis, you realize that there could be errors in the accounting records. For example, maintenance costs were recorded as zero in December. However, you know that maintenance was...
-
In Exercise 1, we assumed that there could be at most one distribution with moments E(Xk) = 1/3 for k = 1, 2, . . . . In this exercise, we shall prove that there can be only one such distribution....
-
The following are summary cash transactions that occurred during the year for Hope Healthcare Co. (HHC): Prepare the cash flows from operating activities section of HHCs statement of cash flows using...
-
Use lHpitals rule to find the limit. 31 + 3 43 14t - t + 3 lim
-
The expected return for the general market is 10.3 percent, and the risk premium in the market is 5.3 percent. Tasaco, LBM, and Exxos have betas of .864, .693, and .575, respectively. What are the...
-
In Exercise 1.18, test the goodness of fit between the observed class frequencies and the corresponding expected frequencies of a normal distribution with p = 65 and a = 21, using a 0.05 level of...
-
Some of the prior interviews suggest that the company is so cheap that they wont pay people what they are worth. Have you had similar experiences?
-
Advanced Products Corporation has supplied the following data from its activity-based costing system: During the year, Advanced Products completed one order for a new customer, Shenzhen Enterprises....
-
When discussing possible purchase of a fixed immediate annuity, not discussing the full range of available annuitization options and the implications of choosing one over another, particularly with...
-
Two blocks, of masses M = 2.0 kg and 2M, are connected to a spring of spring constant k = 200 N/m that has one end fixed, as shown in Figure. The horizontal surface and the pulley are frictionless,...
-
What is the current yield of the 7% IBM bond issue ($1000.00 bond) at the following bond prices.Fill out the table below. Current Rate Value of Bond Coupon 6.36% $70.00 6.59% $70.00 7.00% $70.00...
-
On the first day of the year 2019, a company issues a $50,000,000, 6%, 10-year bond that pays semi-annual interest, receiving cash of $52,000,000. Journalize the entry to record the first interest...
-
What are common allocation methods of pipeline imbalances in energy accounting?
-
What are the enterprise risks and opportunities for a company that has adopted a cryptocurrency strategy (e.g., Bitcoin)?
-
Washington Company has two divisions: the Adams Division and the Jefferson Division. The following information pertains to last year's results: Adams Division Jefferson Division Net (after-tax)...
-
Write a program that read a sequence of integer inputs and print (20 Points) a) The smallest and largest of the inputs. b) The number of even and odd inputs. c) Cumulative totals. For example, if the...
-
The balance sheets for Plasma Screens Corporation, along with additional information, are provided below: PLASMA SCREENS CORPORATION Balance Sheets December 31, 2024 and 2023 Assets Current assets:...
-
Refer to the table to answer the following questions. Year Nominal GDP (in billions) Total Federal Spending (in billions) Real GDP (in billions) Real Federal Spending (in billions) 2000 9,817 578...
-
Madison Company uses the following rule to determine whether direct labor efficiency variances ought to be investigated. A direct labor efficiency variance will be investigated anytime the amount...
-
Dohini Manufacturing Company had the following 12 months of data on purchasing cost and number of purchase orders. Required: 1. Determine the high point and the low point. 2. Calculate the variable...
-
Jocassee Furniture Manufacturing, Inc., has a division in the United States that produces and sells furniture for discount furniture stores. One type of dining room set is made in the International...
-
The most appropriate response to Nkomos Question 1 would be that the present value of future residual earnings is expected to be: A. zero. B. positive. C. negative. Mangoba Nkomo, CFA, a senior...
-
The most appropriate response to Nkomos Question 2 would be that the firms return on equity (ROE ) is: A. equal to the firms cost of equity. B. lower than the firms cost of equity. C. higher than the...
-
Based upon the information in Exhibit 2, the intrinsic value per share of the equity of Jackson Breweries is closest to: A. R97.67. B. R130.22. C. R186.03. Mangoba Nkomo, CFA, a senior equity analyst...
Study smarter with the SolutionInn App