This paper demonstrates, via a short interactive case analysis, an effective method for teaching Theory of...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
This paper demonstrates, via a short interactive case analysis, an effective method for teaching Theory of Constraints (TOC) using Excel's Solver tool. Southern Frozen Foods produces prepared frozen foods in three product lines and is concerned about how the current product mix consumes its fixed resources. Management wants to determine how to more efficiently utilize its capacity and improve profitability. What is Southern's most profitable product mix? Do they have the capacity to meet demand? What is the value of adding capacity? Excel's Solver Tool is an extremely powerful yet simple to use tool, providing the accountant with the ability to explore the relationships between product mix, profitability, and resource utilization. The basic steps in setting-up the spreadsheet are to create a contribution margin income statement, specify standard operating data, establish resource utilization, and identify the firm's constraints. The fundamental concept of Theory of Constraints (TOC), articulated by Eliyahu M. Goldratt and Jeff Cox in their 1984 book The Goal, is actually quite simple. TOC states that every organization is faced with at least one factor that limits the firm's ability to earn profits. Given this core concept, the manager who wants more profits must manage the constraint(s) faced by the enterprise. Students are asked to take the role of assisting the controller for Southern Frozen Foods in the evaluation of several internal and external constraints. Although the case is very concise, numerous analyses can be explored in a reasonable time period. The case can be used in any managerial accounting course once students have a fundamental understanding of contribution margin and cost-volume-profit analysis. SOUTHERN FROZEN FOODS, INC.: IMPROVING The Controller for Southern Frozen Foods asks you to help figure out a way to improve profits. Currently, the firm is operating at a small net operating loss and the CEO would clearly like to see some improvement. Specifically, she wants to know the most profitable product mix, whether the company has the capacity to meet expected demand, and the value of adding capacity. Using paper and a pencil to complete the analysis would take hours. Using Excel's Solver can save huge amounts of time and produce better reports to management. The company produces three frozen-food product lines: sauces and soups are produced in five-pound boil-in- bag packages and casseroles in four-pound aluminum pans. The product lines vary significantly in their consumption of machine and labor time. So far, you have prepared a basic spreadsheet (see the Figure 1 below). The spreadsheet includes monthly budgeted RESOURCE UTILIZATION AND PROFITABILITY sales volume, revenues, variable cost and contribution margin by product line. The company faces several constraints within which it must operate. In this case it is limited to 40,000 minutes of machine time and 50,000 minutes of labor time each. period. Additionally, the company serves a limited market: It cannot expect to sell more than 500 cases of sauces, 400 cases of soup and 700 cases casseroles. Minimum expected demand (pessimistic demand) is also noted in the analysis. The budgeted income statement shows Southern generating a loss of $200 for the month, with a product mix of 200 cases of sauces, 300 soups and 500 casseroles. Standard contribution margin analysis suggests that casseroles, at $22 contribution margin per case, are the most profitable product. Sauces generate $14 per case and soups $20. Sales volume (cases) Revenue Variable costs Contribution margin Fixed costs: Machine-time-related Labor-time-related Segment margin SG&A expenses Operating income Income taxes Operating income after-tax Segment sales mix (revenue) Standard operating data Machine-time utilization per case (minutes) Labor-time utilization per case (minutes) Resource utilization Total machine-time utiization Total labor-time utilization Productivity Measures CM/ minute of machine time CM/ minute of tabor time Constraints Machine time capacity Labor time capacity Pessimistic Demand Market-share limit Sauces $6,000 3,200 $2,800 420 1.000 $1,380 14% Southern Frozen Foods Proforma Monthly Segment Analysis Soups 2,400 5,000 200 300 $30.00 $12,600 $42.00 $25,000 16.00 6,600 22.00 $14.00 $6,000 $20.00 12 25 200 500 1,050 1.920 $3,030 29% 6,000 9.600 Figure 1 20 32 Casseroles 200 400 14,000 $11,000 2.625 4.000 $4,375 57% 15,000 20,000 500 $50.00 28.00 $22.00 30 40 400 700 Unused Capacity 2.905 3,080 16,600 15,400 Total 1,000 $43,600 23.800 $19,800 7,000 10.000 $2,800 3,000 (200) (200) 23.400 34,600 40,000 50,000 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 "Question 1 > 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. This paper demonstrates, via a short interactive case analysis, an effective method for teaching Theory of Constraints (TOC) using Excel's Solver tool. Southern Frozen Foods produces prepared frozen foods in three product lines and is concerned about how the current product mix consumes its fixed resources. Management wants to determine how to more efficiently utilize its capacity and improve profitability. What is Southern's most profitable product mix? Do they have the capacity to meet demand? What is the value of adding capacity? Excel's Solver Tool is an extremely powerful yet simple to use tool, providing the accountant with the ability to explore the relationships between product mix, profitability, and resource utilization. The basic steps in setting-up the spreadsheet are to create a contribution margin income statement, specify standard operating data, establish resource utilization, and identify the firm's constraints. The fundamental concept of Theory of Constraints (TOC), articulated by Eliyahu M. Goldratt and Jeff Cox in their 1984 book The Goal, is actually quite simple. TOC states that every organization is faced with at least one factor that limits the firm's ability to earn profits. Given this core concept, the manager who wants more profits must manage the constraint(s) faced by the enterprise. Students are asked to take the role of assisting the controller for Southern Frozen Foods in the evaluation of several internal and external constraints. Although the case is very concise, numerous analyses can be explored in a reasonable time period. The case can be used in any managerial accounting course once students have a fundamental understanding of contribution margin and cost-volume-profit analysis. SOUTHERN FROZEN FOODS, INC.: IMPROVING The Controller for Southern Frozen Foods asks you to help figure out a way to improve profits. Currently, the firm is operating at a small net operating loss and the CEO would clearly like to see some improvement. Specifically, she wants to know the most profitable product mix, whether the company has the capacity to meet expected demand, and the value of adding capacity. Using paper and a pencil to complete the analysis would take hours. Using Excel's Solver can save huge amounts of time and produce better reports to management. The company produces three frozen-food product lines: sauces and soups are produced in five-pound boil-in- bag packages and casseroles in four-pound aluminum pans. The product lines vary significantly in their consumption of machine and labor time. So far, you have prepared a basic spreadsheet (see the Figure 1 below). The spreadsheet includes monthly budgeted RESOURCE UTILIZATION AND PROFITABILITY sales volume, revenues, variable cost and contribution margin by product line. The company faces several constraints within which it must operate. In this case it is limited to 40,000 minutes of machine time and 50,000 minutes of labor time each. period. Additionally, the company serves a limited market: It cannot expect to sell more than 500 cases of sauces, 400 cases of soup and 700 cases casseroles. Minimum expected demand (pessimistic demand) is also noted in the analysis. The budgeted income statement shows Southern generating a loss of $200 for the month, with a product mix of 200 cases of sauces, 300 soups and 500 casseroles. Standard contribution margin analysis suggests that casseroles, at $22 contribution margin per case, are the most profitable product. Sauces generate $14 per case and soups $20. Sales volume (cases) Revenue Variable costs Contribution margin Fixed costs: Machine-time-related Labor-time-related Segment margin SG&A expenses Operating income Income taxes Operating income after-tax Segment sales mix (revenue) Standard operating data Machine-time utilization per case (minutes) Labor-time utilization per case (minutes) Resource utilization Total machine-time utiization Total labor-time utilization Productivity Measures CM/ minute of machine time CM/ minute of tabor time Constraints Machine time capacity Labor time capacity Pessimistic Demand Market-share limit Sauces $6,000 3,200 $2,800 420 1.000 $1,380 14% Southern Frozen Foods Proforma Monthly Segment Analysis Soups 2,400 5,000 200 300 $30.00 $12,600 $42.00 $25,000 16.00 6,600 22.00 $14.00 $6,000 $20.00 12 25 200 500 1,050 1.920 $3,030 29% 6,000 9.600 Figure 1 20 32 Casseroles 200 400 14,000 $11,000 2.625 4.000 $4,375 57% 15,000 20,000 500 $50.00 28.00 $22.00 30 40 400 700 Unused Capacity 2.905 3,080 16,600 15,400 Total 1,000 $43,600 23.800 $19,800 7,000 10.000 $2,800 3,000 (200) (200) 23.400 34,600 40,000 50,000 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 "Question 1 > 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)
The paper demonstrates an effective method for teaching the Theory of Constraints TOC using Excels S... View the full answer
Related Book For
Operations and Supply Chain Management
ISBN: 978-0078024023
14th edition
Authors: F. Robert Jacobs, Richard Chase
Posted Date:
Students also viewed these business writing questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Read the case study "Southwest Airlines," found in Part 2 of your textbook. Review the "Guide to Case Analysis" found on pp. CA1 - CA11 of your textbook. (This guide follows the last case in the...
-
Melanie Martinez sighed as she reviewed the profit-loss statement for past month. Her shop, Shear Heaven, started out strong nine months ago, but profits have declined over the past couple of months....
-
Concentric with the circle x 2 + y 2 + 2x 8y + 8 = 0 and passes through (2, 3)
-
The information listed below was obtained from the accounting records of Williams Company as of December 31, 2013, the end of the companys fiscal year. (a) On August 1, 2013, the company borrowed...
-
Luke Roye is uncertain about the control features of a bank account. Explain the control benefits of (a) A checking account (b) A bank statement.
-
Apex Electrical Supply, Inc., purchased inventory for \($2,000\) and also paid \($125\) freight to have the inventory delivered. Apex Electrical Supply, Inc., returned \($500\) of the goods to the...
-
The president of Healthy Heart Exercise Equipment Co. has been concerned about the growth in costs over the last several years. The president asked the controller to perform an activity analysis to...
-
Rundle Corporation makes custom - order furniture to meet the needs of persons with disabilities. On January 1 , Year 2 , the company had the following account balances: $ 8 8 , 0 0 0 for both cash...
-
On December 31, Y3, Aylmer Industries Inc. purchased 85% of the outstanding shares of Belmont Inc. The purchase price is indicated on the excel file. The entry has been recorded on the books of...
-
How does the implementation of a centralized database system impact the efficiency and data accuracy in a large retail organization?
-
First Firm's market cap is $5,940. This includes cash of $542. The firm has 299 shares outstanding. First Firm uses its cash to pay a dividend. If a retired lady has 61 shares, and needs a total of...
-
How may share repurchases by a company be used to support the share price of that company? Explain.
-
Assume perfect capital markets. First Firm is considering making an investment of $183 today. If it did so, the one-year investment would pay out $241 in 12 months. First Firm will fund part of the...
-
For the current fiscal year, Purchases were $210,000, Purchases Returns and Allowances were $3,600 and Freight In was $15,000. If the beginning merchandise inventory was $140,000 and the ending...
-
On the planet Vulcan, Spock decides to reproduce the approach of Eratosthenes to measure the circumference of Vulcan, which we assume to be a perfectly spherical planet. His home town is directly...
-
(1) What should the team leader do to get the team's communications back on track? (2) What is the best way to handle problem team members during meetings? (3) Does the team need more skills...
-
1. As a general strategy, would you recommend that Carl take an aggressive approach to capacity expansion or more of a wait-and-see approach? 2. Should Carl go with the option for one facility that...
-
What are characteristics of efficient, responsive, risk- hedging, and agile supply chains? Can a supply chain be both efficient and responsive? Risk- hedging and agile? Why or why not?
-
Good doctor patient relations begin with both parties being punctual for appointments. This is particularly important in my specialty pediatrics. Mothers whose children have only minor problems dont...
-
Alan Industries is expanding its product line to include three new products: A, B, and C. These are to be produced on the same production equipment, and the objective is to meet the demands for the...
-
Determine the maximum eccentric load \(P\) the 2014-T6aluminum-alloy strut can support without causing it either to buckle or yield. The ends of the strut are pin connected. a $150 mm 150 mm 100 mm...
-
The W14 \(\times 26\) structural A-36 steel member is used as a column that is assumed to be fixed at its top and pinned at its bottom. If the 15-kip load is applied at an eccentric distance of 10...
-
The W14 \(\times 26\) structural A-36 steel member is used as a 20 -ft-long column that is assumed to be fixed at its top and fixed at its bottom. If the 15-kip load is applied at an eccentric...
Study smarter with the SolutionInn App