Organizing the Band

Spreadsheets and databases provide tools for organizing and evaluating data. The York Middle School (YMS) serves the following neighborhoods: Euclid, Parma, Tremont, Cuyahoga, and Lakewood.

The YMS Marching Band needs new uniforms and is selling candy bars to raise funds.

Here are the band members and the neighborhoods they live closest to:

7th Graders:

Sebastian—Tremont
Christin—Cuyahoga
Pablo—Cuyahoga
Matthew—Tremont
Blake—Tremont
Lisa—Cuyahoga
Charles—Cuyahoga
8th Graders:

Dominic—Parma
Lauren—Lakewood
Stella—Parma
Pedro—Euclid
Rochelle—Euclid
Deshaun—Cuyahoga
Max—Lakewood
Mary—Lakewood
Frank—Lakewood
Edward—Cuyahoga
Lillian—Lakewood
Henry—Parma
Carla—Parma
Bekah—Lakewood
Marcella—Lakewood
9th Graders

Gray—Euclid
Daniel—Euclid
Erin—Tremont
Petra—Tremont
Amber—Euclid
Omega—Euclid
Macon—Tremont
Ken—Tremont
Each member is selling three different types of candy bars: the Traditional, the Crunch Magic, and the Caramel Joy. The students have decided to sell within their own neighborhoods to minimize the driving required and to avoid oversaturating any one market. Each student is required to sell 45 candy bars at $2 per candy bar. The student takes the orders and then delivers the candy bars at the end of the sale period.

Rosario, the band leader, hands out order sheets so all the bandmembers can enter the appropriate sales information. Rosario collects the completed order sheets and then brings the appropriate number of candy bars to the respective neighborhoods so students can deliver them to the customers/supporters.

At the end of the fundraiser, every student sold 45 candy bars in the following amounts:

Petra (13 Traditionals, 29 Crunch Magics, 3 Caramel Joys)
Ken (16 Traditionals, 29 Crunch Magics)
Sebastian (7 Traditionals, 22 Crunch Magics, 16 Caramel Joys)
Matthew (17 Traditionals, 17 Crunch Magics, 11 Caramel Joys)
Henry (25 Traditionals, 20 Caramel Joys)
Carla (10 Traditionals, 29 Crunch Magics, 6 Caramel Joys)
Blake (3 Traditionals, 29 Crunch Magics, 13 Caramel Joys)
Christin (21 Traditionals, 18 Crunch Magics, 6 Caramel Joys)
Pablo (28 Crunch Magics, 17 Caramel Joys)
Lisa (13 Traditionals, 19 Crunch Magics, 13 Caramel Joys)
Charles (17 Traditionals, 22 Crunch Magics, 6 Caramel Joys)
Deshaun (15 Traditionals, 18 Crunch Magics, 12 Caramel Joys)
Dominic (11 Crunch Magics, 34 Caramel Joys)
Stella (7 Traditionals, 21 Crunch Magics, 17 Caramel Joys)
Pedro (8 Traditionals, 17 Crunch Magics, 20 Caramel Joys)
Rochelle (16 Traditionals, 13 Crunch Magics, 16 Caramel Joys)
Gray (14 Traditionals, 31 Caramel Joys)
Daniel (7 Traditionals, 30 Crunch Magics, 8 Caramel Joys)
Amber (14 Traditionals, 17 Crunch Magics, 14 Caramel Joys)
Bekah (33 Traditionals, 3 Crunch Magics, 9 Caramel Joys)
Marcella (11 Traditionals, 19 Crunch Magics, 15 Caramel Joys)
Mary (20 Traditionals, 19 Crunch Magics, 6 Caramel Joys)
Lauren (19 Traditionals, 18 Crunch Magics, 8 Caramel Joys)
Omega (9 Traditionals, 25 Crunch Magics, 11 Caramel Joys)
Erin (28 Traditionals, 15 Crunch Magics, 12 Caramel Joys)
Edward (10 Traditionals, 21 Crunch Magics, 14 Caramel Joys)
Lillian (8 Traditionals, 27 Crunch Magics, 10 Caramel Joys)
Max (11 Traditionals, 34 Caramel Joys)
Macon (15 Traditionals, 19 Crunch Magics, 11 Caramel Joys)
Frank (13 Traditionals, 17 Crunch Magics, 15 Caramel Joys)
Rosario needs to organize the data to ensure that she delivers the correct number of candy bars to each neighborhood, where the associated students will pick up their candy bars and deliver them to their customers.

Craft a spreadsheet that organizes the data, and then create a pivot table that shows Rosario which, and how many, candy bars to deliver to each neighborhood.

If you were going to present the data showing candy bar sales by type, which chart would display the data the most effectively? Create the graph and defend your decision.

find the cost of your paper

Sample Answer

 

 

 

 

Organizing and Analyzing Candy Bar Sales Data

Creating the Spreadsheet

Step 1: Set up the spreadsheet:

  • Column A: Student Name
  • Column B: Grade
  • Column C: Neighborhood
  • Column D: Traditional Candy Bars Sold
  • Column E: Crunch Magic Candy Bars Sold
  • Column F: Caramel Joy Candy Bars Sold

Step 2: Input the data:

Full Answer Section

 

 

 

 

 

 

  • Enter the data for each student as provided in the prompt.

Creating the Pivot Table

Step 1: Select the data range:

  • Highlight the entire data range.

Step 2: Insert a Pivot Table:

  • Go to the “Insert” tab and click “Pivot Table.”
  • Choose a location for the Pivot Table.

Step 3: Drag and drop fields:

  • Drag “Neighborhood” to the “Rows” area.
  • Drag “Traditional Candy Bars Sold,” “Crunch Magic Candy Bars Sold,” and “Caramel Joy Candy Bars Sold” to the “Values” area.

Step 4: Format the Pivot Table:

  • Adjust column widths and number formats as needed.
  • Consider adding a “Total” column to see the total number of candy bars for each neighborhood.

Choosing the Best Chart

To visually represent the candy bar sales by type, a pie chart would be the most effective.

Why a Pie Chart?

  • Clear comparison: A pie chart visually represents the proportion of each candy bar type sold relative to the total sales.
  • Easy interpretation: The size of each slice directly corresponds to the percentage of sales for that candy bar type.
  • Focus on the whole: It effectively shows how the different types of candy bars contribute to the overall sales.

To create the pie chart:

  1. Select the data: Highlight the columns containing the total sales for each candy bar type.
  2. Insert a pie chart: Go to the “Insert” tab and click “Pie Chart.”
  3. Customize the chart:
    • Add a chart title: “Total Candy Bar Sales by Type”
    • Label each slice with the candy bar type and percentage.
    • Consider using different colors for each slice to enhance visual appeal.

By analyzing the spreadsheet and pivot table, Rosario can efficiently plan the delivery of candy bars to each neighborhood. The pie chart provides a clear visual representation of the sales breakdown, aiding in future sales strategies and inventory management.

This question has been answered.

Get Answer