In this project, you will format numbers and create formulas using absolute cell references. You will insert statistical functions. You will also create and format charts, and insert WordArt.

Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel. Download and open the file named Student_Excel_02_SA_Sales_AS.xlsx. Save the file as Last_First_Excel_02_SA_Sales_AS.	0
2	Format the column labels with Wrap Text, Middle Align, and Center alignment.	3
3	In row 16, insert the column totals. Format the range B16:E16 with the Total cell style. AutoFit the width of columns A:F.	10
4	In cell F4, enter a formula that provides the Item Total as a Percent of Store Total. In the formula, use an absolute cell reference for the appropriate cell.	5
5	In cell F4, format the result with the Percent Style and display two decimal places. AutoFill the formula down through cell F15.	8
6	In the range B18:D18, use a function to calculate the average sales amount for each store.	9
7	In the range B19:D19, use a function to calculate the lowest sale amount for each store.	9
8	In the range B20:D20, use a function to calculate the highest sale amount for each store.	9
9	Select the nonadjacent ranges A3:A15 and E3:E15 and insert a 2-D Pie chart. Move the chart to a new sheet named Sales Chart.	10
10	Change the layout of the pie chart to Layout 5. Change the title to Garden Sales. Change the data labels to display both the Category Name and the Percentage. Change the position of the data labels to Outside End. Change the font size of the data labels to 12 and Bold. Note, Mac users, select the fifth layout (second column, second row) in the gallery.	10
11	In the April worksheet, insert a Line chart based on the range A3:D15. Move and resize the chart so that top left corner is just inside cell A22, and the lower right corner is inside cell F36.	10
12	Change the layout of the line chart to Layout 4. Change the chart style to Style 9. Note, Mac users, select the last layout in the gallery.	5
13	Insert three rows at the top of the worksheet. Insert the text Toms Place Sales as WordArt using the Gradient Fill, Gray. Change the font size to 34. Move the WordArt to the top of the worksheet, and center the text box in the range A1:E3. (ensure that the top right corner is at the edge of the E:F border)	9
14	Scale to fit the Height of the page to 1 page.	2
15	Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales Chart; April. Save and close Last_First_Excel_01_SA_Visitors_HW.xlsx. Exit Excel. Submit the file as directed.	1

Garden Tool Sales
April
Item	Westside Store	East Valley Store	Main Store	Item Total	Percent of Store Total
Shovel	$ 3,398	$ 2,124	$ 4,361	$ 9,883
Rake	4,264	2,016	4,914	11,194
Axe	1,325	943	1,993	4,261
Pick	1,557	845	1,928	4,330
Sledge Hammer	1,232	576	1,530	3,338
Wheelbarrow	1,336	747	1,469	3,552
Pruner	4,423	328	4,518	9,269
Garden Hose	5,222	2,516	6,079	13,817
Bulb Planters	2,572	1,793	3,187	7,552
Trowel	2,561	1,653	3,386	7,600
Garden Fork	3,367	2,599	3,979	9,945
Aerator	1,541	961	1,763	4,265
Store Total
Average
Minimum
Maximum