Shelly Cashman Excel 2019 | Module 6: End of Module Project 2 Pet Stop

GETTING STARTED

1. Open the file SC_EX19_EOM6-2_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as SC_EX19_EOM6-2_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. 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.

3. With the file SC_EX19_EOM6-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

a. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Camille Beauclaire is a financial analyst for Pet Stop, an online pet supply company in Huntsville, Alabama. Camille is reviewing the recent sales of products and ad campaigns. She asks for your help in using Excel tables to analyze the data. Go to the October Sales worksheet, which lists the products sold in October 2021. Format the October Sales data (range A1:G27) as a table with headers using Olive Green, Table Style Medium 5 so that Camille can summarize and filter the data. Use October as the name of the table.

2. Add a column to the right end of the table and use Total Price as the column heading. In cell H2, enter a formula without using a function that uses structured references to multiply the price per unit (cell G2) by the units sold (cell F2) to find the total price. Fill the range H3:H27 with the formula in cell H2 if Excel does not do so automatically. (Hint: To avoid an #SPILL error, enable implicit intersection by placing the This Row specifier (@ character) in front of field names.)

3. Filter the table using a custom AutoFilter to display products with a Price per Unit amount greater than $25.00.

4. Go to the Proposed Discounts worksheet, which lists products and the discount prices that have been proposed for an upcoming sale in November. Camille notices that the Discounts table has a duplicate record. Clear the filter from the Discounts table to display all the records. Use the Remove Duplicates tool to identify duplicates in the Product ID column and remove the duplicate record.

5. Pet Stop plans to run promotional ads for all dog products with a discount price of more than $30.00. In cell H2, enter a formula using the AND function that includes structured references to display TRUE if a product has a [@[Pet Type]] of "Dog" and a [@[Discount Price]] value 30. Fill the range H3:H16 with the formula in cell H2 if Excel does not do so automatically.

6. Add a Total Row to the Discounts table, which automatically counts the number of Promo Ad values. Using the total row, display the sum of the Current Price and Discount Price amounts.

7. Camille wants to compare the discount prices visually. In the Discount Price column (range G2:G16), create a new Data Bars conditional formatting rule using Green Gradient Fill data bars.

8. Camille also wants to summarize the number of discounted products by product type and calculate their total and average discounted prices. In cell K2, enter a formula using the COUNTIF function that counts the number of Carrier products, using Discounts[Product Type] as the range and cell J2 as the criteria. Fill the range K3:K9 with the formula in cell K2. In cell L2, enter a formula using the SUMIF function that totals the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the sum_range. Fill the range L3:L9 with the formula in cell L2. In cell M2, enter a formula using the AVERAGEIF function that averages the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the average_range. Fill the range M3:M9 with the formula in cell M2.

9. Go to the October Ads worksheet, which lists all the digital ads released in October. Sort the data in the table in ascending order first by channel and then by release date.

10. Camille also wants to calculate ad amount subtotals for each channel. Convert the table to a range. Insert a subtotal at each change in the Channel value, using the Sum function to calculate the subtotals. Add subtotals to the Ad Amt values only, and include a summary below the data. Collapse the outline to display only the subtotals for each channel and the grand total.

11. Go to the Ad Lookup worksheet, which lists all the ads released in October and planned for November. Camille wants to look up the release date of each ad. In cell H3, enter a formula using the INDEX function. Use a structured reference to the AdLookup table (range A2:E36) as the array. For the row_num argument, use the MATCH function. Use the Ad ID (cell H2) as the lookup_value for the MATCH function. Use a structured reference to the ID column (range A2:A36) as the lookup_array for the MATCH function. Specify an exact match (0) for the MATCH function. Use the Release Date column number (column 4) as the column_num for the INDEX function.

12. Camille also wants to identify the number of ads that cost more than $1000. In cell H7, create a formula using the DCOUNT function to count the number of ads that cost more than $1000, using a structured reference to all the columns in the AdLookup table (range A1:E36) as the database, "Ad Amt" as the field, and the range G5:G6 as the criteria.

13. Finally, Camille wants to calculate the average ad amount for the Social media channel. In cell H12, create a formula using the DAVERAGE function to average the ad amounts for the Social media channel, using a structured reference to all the columns in the AdLookup table (range A1:E36) as the database, "Ad Amt" as the field, and the range G10:G11 as the criteria.

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.

Final Figure 1: October Sales Worksheet

Final Figure 2: Proposed Discounts Worksheet

Final Figure 3: October Ads Worksheet

Final Figure 4: Ad Lookup Worksheet