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...
-
Cornwall and Hudson, a large retail department store, wants to handle just one brand of high-quality CD player. The list has been narrowed to two brands: Sony and Panasonic. To help make a decision,...
-
Penguin Pucks, Inc., has current assets of $3,000, net fixed assets of $6,000, current liabilities of $900, and long-term debt of $5,000. What is the value of the shareholders equity account for this...
-
Suppose that a probability of \(\frac{1}{16}\) is assigned to each point of the sample space of part (a) of Exercise 3.1 on page 65 . Find the probability distribution of the total number of units of...
-
The unadjusted trial balance of PS Music as of July 31, 2016, along with the adjustment data for the two months ended July 31, 2016, are shown in Chapter 3. Based upon the adjustment data, the...
-
1. As a Marketing researcher, you would like to establish the-cause-and-the-effect relationship between two variables X and Y (e.g., X causes Y). Explain why such a causal relationship is very hard...
-
Consider the following relation: CAR_SALE(Car#, Date_sold, Salesperson#, Commission%, Discount_amt) Assume that a car may be sold by multiple salespeople, and hence {Car#, Salesperson#} is the...
-
Over the past 6 years, a local firm has paid annual dividends of $2.18, $2.23, $2.35, $2.75, $3.07, and $3.24. What is the arithmetic average dividend growth rate? (Do not round intermediate...
-
Calculate for case (c). (c) Linear
-
Define posthumous reproduction and give an example of circumstances in which it might be utilized.
-
Describe the nature and significance of the biology plus approach to establishing parental rights for unwed fathers.
-
Describe approaches used by the courts to resolve disputes regarding disposition of cryopreserved embryos upon divorce.
-
Identify at least four kinds of liabilities arising out of the use of reproductive technologies.
-
We work on a communication campaign and we choose the Predator Shot energy drink product, a new brand that wants to hit the market in Thailand. We work communication campaigns where we do...
-
Find the center of mass of a thin triangular plate bounded by the y-axis and the lines y = x and y = 2 - x if (x, y) = 6x + 3y + 3.
-
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 . . . . . . . . . . ....
-
The \(x\) component of the velocity of a car changes from \(-10 \mathrm{~m} / \mathrm{s}\) to \(-2.0 \mathrm{~m} / \mathrm{s}\) in \(10 \mathrm{~s}\). (a) Is the car traveling in the positive or...
-
The day after the incident described in Problem 44, the instructor finds herself in the same situation. This time, she tries a harder physics exercise. She keeps running at a constant \(6.0...
-
(a) A car is speeding up in the negative \(x\) direction. In what direction do \(\vec{a}\) and \(\vec{v}\) point? (b) To which of the four graphs in Figures 3 . 2 and 3 . 3 does the situation...
Study smarter with the SolutionInn App