https://docs.google.com/spreadsheets/d/1-dm54Iv_UfKoRdSzoR_3b-iGSY7g6RAbvyMaj1X9F8E/edit Volume, price per case, variable cost per case and total fixed costs are specified as numerical
Fantastic news! We've Found the answer you've been seeking!
Question:
https://docs.google.com/spreadsheets/d/1-dm54Iv_UfKoRdSzoR_3b-iGSY7g6RAbvyMaj1X9F8E/edit
Transcribed Image Text:
Volume, price per case, variable cost per case and total fixed costs are specified as numerical values. All other elements of the income statement contain the appropriate cell references and formulas. Fixed costs consist of machine-time-related costs ($7,000), labor- time-related costs ($10,000) and selling, general & administrative (SG&A) ($3,000). Machine-time and labor-time related costs are based on product-line standardized operating data and resource utilization. For example, sauces consume 12 minutes of machine time per case and budgeted sales volume is 200 cases, thus machine-time utilization is 200 * 12=2,400. Similar formulas are created for the remaining product lines as well as for labor-time-related fixed-product costs. These formulas link the operating data to the resource- utilization information and income statement so that any changes in sales volumes will be appropriately reflected in the income statement and resource utilization figures. You also are measuring the unused capacity, both in terms of minutes and costs. Actual machine time used is the sum of the machine times for the individual product lines. Unused machine time is machine time capacity less machine time used. The cost of unused machine capacity in the income statement is computed as the total fixed cost of machine-time less the sum of the costs allocated to the individual product lines. Similar calculations have been made for labor-time activities as well. You can see that there is a substantial amount of unused capacity in the current budget-16,600 minutes of machine time and 15,400 minutes of labor time. Requirements: The Controller has directed you to provide an analysis of the current situation. Specifically, he would like a briefing that addresses the following issues. 1. Determine the measures of productivity for each of the three product lines. The Controller can see that Casseroles generate the highest contribution margin per unit but they also consume a lot of resources, particularly labor time. He is unsure how to evaluate these relationships. Prepare a brief comment on the results to help the Controller. 2. Assuming that there are no market constraints at all, which product line would maximize profits for the firm? 3. Taking into consideration all of the constraints (market constraints as well as resource constraints), what product mix would maximize profits in the short run? 4. Does the firm have sufficient resources to meet the optimistic demand? Why or why not? 5. Assume that the optimistic demand materializes and Southern can hire 4,000 minutes of labor- time for a cost of $1,000. Determine the financial impact of this decision. Setting Up Your Solution: Productivity measures: You will need to create formulas to determine the CM / minute of machine time and CM / minute of labor time. Do this in the section of the Spreadsheet labeled Productivity Measures in columns D. F. and H respectively. Creating copies of the initial budget: • Begin with the Southern P&L worksheet and make a copy. Right-click on "Question1"> move or copy... > (move to end) > select create copy > OK . Rename the copied worksheet tab as Question 2. Right-click on the tab "Question 1 (2) > Rename > type Question 2. • Repeat the two steps above to create worksheets labeled Question 3, Question 4, and Question 5. Answering Question 1: Insert a comment on the Question 1 worksheet. Review > New Comment > write your commentary about the productivity measures. Answering Question 2: Configure the solver parameters to address the question taking into consideration machine and labor capacity constraints ONLY. Insert a new comment and prepare a brief analysis of the results. (Hint: which resource is the constraining resource?) Answering Question 3: Configure the parameters in the Solver tool to complete the analysis under the various conditions. Insert a new comment of each of the worksheet tabs. Answering Question 4: Configure the solver parameters so that the volume cells are equal to the market-share limit for each product line. Keep the solution (regardless of the results) and comment on the results. Answering Question 5: Include the revised data and rerun Solver. Add a comment about the results. Volume, price per case, variable cost per case and total fixed costs are specified as numerical values. All other elements of the income statement contain the appropriate cell references and formulas. Fixed costs consist of machine-time-related costs ($7,000), labor- time-related costs ($10,000) and selling, general & administrative (SG&A) ($3,000). Machine-time and labor-time related costs are based on product-line standardized operating data and resource utilization. For example, sauces consume 12 minutes of machine time per case and budgeted sales volume is 200 cases, thus machine-time utilization is 200 * 12=2,400. Similar formulas are created for the remaining product lines as well as for labor-time-related fixed-product costs. These formulas link the operating data to the resource- utilization information and income statement so that any changes in sales volumes will be appropriately reflected in the income statement and resource utilization figures. You also are measuring the unused capacity, both in terms of minutes and costs. Actual machine time used is the sum of the machine times for the individual product lines. Unused machine time is machine time capacity less machine time used. The cost of unused machine capacity in the income statement is computed as the total fixed cost of machine-time less the sum of the costs allocated to the individual product lines. Similar calculations have been made for labor-time activities as well. You can see that there is a substantial amount of unused capacity in the current budget-16,600 minutes of machine time and 15,400 minutes of labor time. Requirements: The Controller has directed you to provide an analysis of the current situation. Specifically, he would like a briefing that addresses the following issues. 1. Determine the measures of productivity for each of the three product lines. The Controller can see that Casseroles generate the highest contribution margin per unit but they also consume a lot of resources, particularly labor time. He is unsure how to evaluate these relationships. Prepare a brief comment on the results to help the Controller. 2. Assuming that there are no market constraints at all, which product line would maximize profits for the firm? 3. Taking into consideration all of the constraints (market constraints as well as resource constraints), what product mix would maximize profits in the short run? 4. Does the firm have sufficient resources to meet the optimistic demand? Why or why not? 5. Assume that the optimistic demand materializes and Southern can hire 4,000 minutes of labor- time for a cost of $1,000. Determine the financial impact of this decision. Setting Up Your Solution: Productivity measures: You will need to create formulas to determine the CM / minute of machine time and CM / minute of labor time. Do this in the section of the Spreadsheet labeled Productivity Measures in columns D. F. and H respectively. Creating copies of the initial budget: • Begin with the Southern P&L worksheet and make a copy. Right-click on "Question1"> move or copy... > (move to end) > select create copy > OK . Rename the copied worksheet tab as Question 2. Right-click on the tab "Question 1 (2) > Rename > type Question 2. • Repeat the two steps above to create worksheets labeled Question 3, Question 4, and Question 5. Answering Question 1: Insert a comment on the Question 1 worksheet. Review > New Comment > write your commentary about the productivity measures. Answering Question 2: Configure the solver parameters to address the question taking into consideration machine and labor capacity constraints ONLY. Insert a new comment and prepare a brief analysis of the results. (Hint: which resource is the constraining resource?) Answering Question 3: Configure the parameters in the Solver tool to complete the analysis under the various conditions. Insert a new comment of each of the worksheet tabs. Answering Question 4: Configure the solver parameters so that the volume cells are equal to the market-share limit for each product line. Keep the solution (regardless of the results) and comment on the results. Answering Question 5: Include the revised data and rerun Solver. Add a comment about the results.
Expert Answer:
Answer rating: 100% (QA)
Question 1 Productivity Measures To evaluate the productivity of each product line we can calculate the contribution margin per minute of machine time ... View the full answer
Related Book For
Managerial Accounting
ISBN: 9780073526706
12th Edition
Authors: Ray H. Garrison, Eric W. Noreen, Peter C. Brewer
Posted Date:
Students also viewed these accounting questions
-
Edit select queries for Coast Jewelers. a. Edit Query 1 by adding the field Postal Code and deleting the field Stateor Province. Save this query as Ch 11 Assignment 3a before you print it. b. Edit...
-
Answer 1-4 based on the link provided: Discuss year 2018 https://docs.google.com/spreadsheets/d/1sJucsYpSSaUijd2ptjdmWjluDICCTJ2_HjyzhMffEL0/edit#gid=679851468 Or, see attachment. Please see all the...
-
Consider a risky portfolio, A, with an expected rate of return of 0.18 and a standard deviation of 0.18, that lies on a given indifference curve. Which one of the following portfolios might lie on...
-
How would you apply this concepts to call center statistics? There are two numerical ways of describing quantitative variables, namely measures of location, and measures of dispersion. Measures of...
-
WhackAmOle has two million shares of common stock outstanding, 1.5 million shares of preferred stock outstanding, and 50,000 bonds. If the common shares are selling for $63 per share, the preferred...
-
A coin placed 30.0 cm from the center of a rotating horizontal turntable slips when its speed is 50.0 cm/s. (a) What force causes the centripetal acceleration when the coin is stationary relative to...
-
A new induction motor is put into operation. The initial vibration is acceptable, but after 1 hour of loaded operation, the 1 vibration has increased to an unacceptable level. The motor is...
-
The following financial data were reported by 3M Company for 2009 and 2010 (dollars in millions). Instructions (a) Calculate the current ratio and working capital for 3M for 2009 and 2010. (b)...
-
Consider various U.S. Treasury bonds that each pay a 5% annual coupon rate and have a face value of $1000. (a) Complete the table below. Show your work. Years to Maturity Yield-to-Maturity Current...
-
Determine how many users have received more than 5000 "cool" compliments. Create a variable user_count (an integer) which contains the number of user with more than 5000 "cool" compliments (using...
-
Supposed you are tasked with selecting a new supplier for a critical component of your company's product. The component is essential for maintaining product quality, and the supply chain has faced...
-
A(n) 70-g ice cube at O C is placed in 810 g of water at 28 o C. What is the final temperature of the mixture?
-
A chess board is an 8x8 matrix on which chess pieces are placed. A real chess game has a variety of pieces that have different rules defining how each can move and whom they can attack. For example,...
-
Please explain and compare and contrast manufacturing and services based on the following factors. Also provide similarities and differences of them. Please provide with some examples. Aggregate...
-
Discuss the advantages and disadvantages that entrepreneurial firms are most likely to experience if they are first movers? Explain briefly
-
Steph Curry is practicing for the 3-point shooting competition at the All-Star Game. Curry releases the basketball at 10 feet above the ground, and 80 feet away from the basketball hoop. Based on a...
-
Juwairiyyah is 2 years old and her father places her in his car which is on an incline. Initially he is sitting in the car with her but gets distracted and leaves her alone in the vehicle. She lifts...
-
Use a calculator to evaluate the expression. Round your result to the nearest thousandth. V (32 + #)
-
The check-clearing office of San Juan Bank is responsible for processing all checks that come to the bank for payment. Managers at the bank believe that variable overhead costs are essentially...
-
Refer to the data for Arbon Company in Exercise 4-16. Required: Assuming that the company uses the direct method rather than the step-down method to allocate service department costs, how much...
-
Royal Company manufactures 20,000 units of part R-3 each year for use on its production line. At this level of activity, the cost per unit for part R-3 follows: Direct materials . . . . . . . . . . ....
-
What is meant by lower of cost and net realizable value? What difficulties exist in the application of this measurement basis?
-
In essence, a liability is a future obligation to pay out money, arising from a past event. A. True. B. False.
-
Under IAS 37, a provision is a liability of uncertain timing or amount. A. True. B. False.
Study smarter with the SolutionInn App