Step

Instructions

Points Possible

1

Start Excel. Open the downloaded Excel file named BasicExcelSkillsDataFile.xlsx

0

2

On the Net Sales worksheet, in the range F4:F8, calculate the annual total sales for each state.

2.5

3

In the range B9:F9, calculate totals.

2.5

4

Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Colorado Total is of Total Sales, and then fill the formula down through the range G5:G8.

10

5

Apply the Total cell style to the range B9:F9.

4

6

Insert a 3-D Pie chart using the nonadjacent ranges A4:A8 and G4:G8. Change the chart style to Style 7. Reposition the chart so that the top left corner of the chart is centered in cell A10. Change the width of the chart to 6.5 inches.

5

7

Change the chart title to Annual Net Sales by State and add data labels to the chart that display only the Category Name and Percentage positioned in the Center. Change the data labels Font color to Black, Text 1 and apply Bold Italic. Remove the Legend from the chart.

4

8

In cell B5, enter a function that will display the average retail price. In cell B6, enter a function that will display the median retail price. In cell B7, enter a function that will calculate the lowest retail price. In cell B8, enter a function that will calculate the highest retail price. Format the range B5:B8 with Accounting Number Format.

10

9

On the Seattle Inventory worksheet, in cell G14, enter an IF function to determine the items to be ordered. If the Quantity in Stock is less than 50, then the cell should display Order. If not, then the cell should display OK. Copy the function down through cell G19.

10

10

Format the range A13:G19 as a table with headers and then apply Table Style Medium 5.

2