Jefferson School District
CONSOLIDATE WORKBOOK DATA
GETTING STARTED
• Open the file SC_EX19_5b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_5b_FirstLastName_2.xlsx by changing the “1” to a “2”.
o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
o Support_EX19_5b_Fundraisers.xlsx
• With the file SC_EX19_5b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. Allison Kenyon is on the Finance Committee of the Jefferson School Foundation, an organization that raises funds for schools in the Jefferson School District. Allison is tracking sales from four cookie dough fundraisers held during the year. She asks for your help in projecting future sales and visualizing the sales data for an upcoming committee meeting.
The Elementary School, Middle School, and High School worksheets have the same structure and contain similar data. Group the Elementary School, Middle School, and High School worksheets to make changes to the three worksheets at the same time. The first change is to display today’s date.
In cell B3 of the Elementary School worksheet, enter a formula using the TODAY function to display today’s date.
2. Find each instance the text “Pistachio cranberry” on the three worksheets and then change it to Pumpkin spice to use the correct cookie flavor.
3. Use the text in cell H5 to fill the range I5:K5 with the remaining quarters in the year.
4. Allison wants to use the cell formatting in merged cell H8 in other places in the workbook. Create and apply a cell style as follows:
a. Create a cell style named Notes based on the formatting in merged cell H8.
b. Merge cells H6:K6 without centering the contents.
c. Apply the new Notes cell style to cell H6.
5. Allison thinks each school has a good chance of increasing the average number of fundraising items sold to 125 in Quarter 4 next year. For Quarter 1, she estimates 80 fundraising items sold, which is the average number sold each quarter this year.
Project the average number of items sold next year by filling the series for the first projection (range H7:K7) with a linear trend.
6. Allison also wants to know how the average number of items sold would increase if people bought 5% more fundraising items next year.
Project the average number of items sold next year for the second projection (range H9:K9) based on a growth series using 1.05 as the step value.
7. Allison wants to consolidate this year’s sales data for the elementary, middle, and high schools on the All Schools worksheet.
Ungroup the worksheets, go to the All Schools worksheet, and then consolidate the data as follows:
a. In cell C6, enter a formula using the SUM function and a 3D reference to total the number of chocolate chip cookie dough packages sold in Quarter 1 (cell B6) in Jefferson elementary, middle, and high schools.
b. Copy the formula in cell C6 to calculate the number of sales for the other flavors and quarters (range C6:F10), pasting the formula only.
c. In cell C15, enter a formula using the SUM function and a 3D reference to total the sales amounts for chocolate chip cookie dough packages sold in Quarter 1 (cell B15) in Jefferson elementary, middle, and high schools.
d. Copy the formula in cell C15 to calculate the sales for the other flavors and quarters (range C15:F19), pasting the formula only.
8. Allison wants to round the total sales values for each quarter so that they are easier to remember.
a. In cell C20, add the ROUNDUP function to display the total sales for Quarter 1 rounded up to 0 decimal places.
b. Fill the range D20:F20 with the formula in cell C20.
9. In cell G22, Allison wants to display the total fundraising sales from the previous year. This data is stored in another workbook. Insert the total as follows:
a. Open the file Support_EX19_5b_Fundraisers.xlsx.
b. In cell G22 of Allison’s workbook, insert a formula using an external reference to cell F20 in the All Schools worksheet in the Support_EX19_5b_Fundraisers.xlsx workbook.
10. Allison wants to visualize how the sales of each cookie dough flavor contributed to the total fundraising sales for the year.
Create a chart as follows to illustrate this information:
a. Create a 3-D Pie chart that shows how each cookie dough flavor (range B15:B19) contributed to the total sales (range G15:G19).
b. Move and resize the chart so that the upper-left corner is in cell B23 and the lower-right corner is in cell G38.
11. Format the 3-D Pie chart as follows to make it easier to interpret:
a. Use Fundraising Sales as the chart title.
b. Add data labels to the chart on the Inside End of each slice.
c. Display only the Category Name and Percentage values in the data labels.
d. Change the number format of the data labels to Percentage with 1 decimal place.
e. Explode the largest slice (Chocolate chip) by 5 percent.
f. Remove the legend, which repeats information in the data labels.
12. Prepare for printing the All Schools worksheet as follows:
a. Change the left and right margins to 0.5″.
b. Select the range B1:G39 as the print area.
c. Insert a footer that displays the Sheet Name in the center section.
13. Allison wants to compare sales for Quarters 1 and 4 but doesn’t want to clutter the All Schools worksheet with another chart. Create a new worksheet and chart for this comparison as follows:
a. Create a worksheet using Quarterly Comparison as the worksheet name.
b. In cell A1, type Fundraising Sales, and then resize column A to its best fit.
c. In cell B1, enter a formula using a worksheet reference to display the total sales amount from cell G20 on the All Schools worksheet, and then resize column B to its best fit.
d. Return to the All Schools worksheet, insert a Clustered Column chart based on the nonadjacent ranges B14:C19 and F14:F19, and then move the Clustered Column chart to the Quarterly Comparison worksheet.
e. Position the Clustered Column chart so that its upper-left corner is in cell A3.
14. Allison wants to use a copy of the Elementary School worksheet as a template to give to other schools.
Copy the worksheet as follows:
a. Create a copy of the Elementary School worksheet at the end of the workbook.
b. Rename the copy using Other School as the worksheet name.
c. On the Other School worksheet, clear only the contents from the cells containing data, not formulas, in the range B6:E10 and cell H2.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
