Excel

YOF EWS06 A1 - Painted Treasures Gift Shop Review 1.1

Project Description:

The sales team at the Painted Treasures Gift Shop would like to review its staff’s sales after some of the new staff has finished a sales training program. You have been given the data for May 2014 and more data will be added in the future. The sales team wants you to review the data, perform some data analyses, and answer some specific questions. This will help the sales team determine how well the staff is performing, and who needs further training.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step | Instructions | Points Possible |

1 | Start Excel. Open the downloaded Excel file named e03ws06_grader_a1.xlsx. Save the file with the name e03ws06ShopSales_LastFirst replacing LastFirst with your name. Enable content if necessary. | 0.000 |

2 | On the Sales worksheet, insert a table with headers that uses the range $A$9:$I$43. With the data table selected, create named ranges using the top row as the names. | 5.000 |

3 | Copy range A9:I9, and then paste the range in cell A1. In cell G2, type Receptionist. In cell I2, type Check. | 5.000 |

4 | Create an advanced filter using the data in range A1:I2. Filter the list in-place to display the filtered data on the Sales worksheet. Copy the filtered data, including the header row, and paste it in cell A1 of the Filtered worksheet. | 5.000 |

5 | On the Sales worksheet, remove the filter from the table. In cell J9, type Subtotal. In cell J10, enter a formula that multiplies Units and Retail_Price. Format the Subtotal column as accounting number format. | 9.000 |

6 | Create a named range for the Subtotal column that uses the column heading as the name. | 5.000 |

7 | Use the SUBTOTAL function to complete the following. In cell H4, insert a formula that counts the number of cells in the Category field that are not empty. In cell H5, insert a formula that sums the cells in...