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.

Southern Frozen Foods Proforma Monthly Segment Analysis Unused Sauces Soups Casseroles Capacity Total Sales volume (cases) 200 300 500 1,000 Revenue $6,000 $30.00 $12,600 $42.00 $25,000 $50.00 $43,600 Variable costs 3,200 16.00 6,600 22.00 14,000 28.00 23,800 Contribution margin $2,800 $14.00 $6,000 $20.00 $11,000 $22.00 $19,800 Fixed costs: Machine-time-related 420 1,050 2,625 2,905 7,000 Labor-time-related 1,000 1,920 4,000 3,080 10,000 Segment margin $1,380 $3,030 $4,375 $2,800 SG&A expenses 3,000 Operating income (200) Income taxes Operating income after-tax (200 Segment sales mix (revenue) 14% 29% 57% Standard operating data Machine-time utilization per case (minutes) 12 20 30 Labor-time utilization per case (minutes) 25 32 40 Resource utilization Total machine-time utilization 2,400 6,000 15,000 16,600 23,400 Total labor-time utilization 5,000 9,600 20,000 15,400 34,600 Productivity Measures CM / minute of machine time CM / minute of labor time Constraints Machine time capacity 40,000 Labor time capacity 50,000 Pessimistic Demand 200 200 400 Market-share limit 500 400 700 Figure