Sales Mix for Mustang Baseball League

Each year, the members of teams in the Mustang Baseball league sell candy items to raise money to support the purchase of uniforms, game operating expenses, field maintenance expenses, and to fund other needs. When a company or organization sells more than one product, it must sell the right combination of products in order to maximize its profits. This combination is called the "sales mix," and it is an important factor in determining CVP relationships.

The Mustang Baseball teams sell almond chocolate bars, chocolate bridge mix, and chocolate-covered peanuts. The league manager, Angie Bowen, is arranging for this year's candy order. The company that supplies her products requires that the teams sell a minimum of 500 products. The cost of each item is $1.00, and the league is free to mark up items as it sees fit.

Angie needs your help to determine the right sales mix, and how it affects the cost-volume-profit relationship. Based on last year's sales, Angie arrived at the following proportions: 40% chocolate bars, 30% bridge mix, and 30% peanuts. Angie wants you to explore what would happen to the operating income and the break-even point if the sales mix were as follows:

Do the following:

1. Click the link to download the Candy file. Download the Candy file to your Internet Assignments Disk.
2. Log off of the Internet and then close your Web browser.
3. If necessary, start Excel and make sure that your Internet Assignments Disk is in the appropriate drive. Open the Candy workbook from the disk and save it as Sales Mix Candy.
4. Enter the new workbook name, your name, and the date in the Title sheet.
5. Give the Total Candy Sold value in cell C4 the range name "Units_Sold." Use the Create Names command to create names for the Percent of Sales values found in the D5:F6 range of the Sales worksheet.
6. Using Scenario Manager, create the five scenarios listed above. The changing cells in each scenario are the percent of sales values found in the D6:F6 range and the Units Sold value found in cell C4. Add a description of each scenario.
7. Use the Names Create command to create names for the outcome values in the range C20:C21.
8. Create a Scenario Summary report for the five scenarios. Include the operating income and the break-even point in the scenario summary report.
9. Print the scenario summary report in landscape orientation.
10. Analyze the report. Which sales mix results in the highest operating income and the lowest break-even point? Based on this, which of the three candy items is the most profitable to the company?
11. Save your changes and close the Sales Mix Candy workbook.