BUSA 2205: Fundamentals of Computer Applications (OER): Unit 3: Spreadsheets as Databases

Unit 3 Module 1 Application Assignment

1. Download and open the Database I Excel file, save it to your desktop
2. Rename the sheet Table Formatting
3. Change Wholesale and Retail price to Currency
4. Highlight the data in the spreadsheet and select any style except yellow, be sure to include your table has a header row
5. Insert a Total Row at the bottom of the set of data using the Table Style Option on the Design Tab
6. Create a copy of the sheet and rename it Conditionally Formatted
7. On the sheet labeled Table Formatting, using the Remove Duplicates tool on the Design Tab, remove the duplicates based on Product Name
8. On the sheet labeled Table Formatting, Insert a Slicer based on Item Class choosing Drive Line
9. Return to the sheet labeled Conditionally Formatted, highlight all of the data and Convert it to Range
10. Highlight data and apply conditional formatting on the Last Inventory Date for dates in the last month
11. Save the file as FirstInitialLastNameBUSA2205Unit3Mod1.xlsx (DBridgesBUSA2205Unit3Mod1.xlsx)

1. Download Unit 3 Mod 2 Application Assignment File
    
2. Add Average Satisfaction Score in cell K1
    
3. Calculate Average Satisfaction for each location using the average function
    
4. Set the Average Satisfaction Score to one (1) decimal point
    
5. Copy Sheet 1 nine (9) times, rename each sheet using the following names:
   1. Main Data
   2. Customer Service Satisfaction
   3. Likelihood of recommending
   4. Order Fulfillment Satisfaction
   5. Packaging Satisfaction
   6. Production Satisfaction
   7. Refund Satisfaction
   8. Sales Rep Satisfaction
   9. Shipping Materials Satisfaction
   10. Shipping Satisfaction
        
6. On the Customer Satisfaction Sheet,
   1. Convert data to a table using any style other than yellow
   2. Filter the data by Customer Service Satisfaction for values of 4 and greater
       
7. On the Likelihood of recommending sheet,
   1. Convert data to a table using any style other than yellow
   2. Filter the data by Likelihood of recommending to others for values for values 4 and greater
       
8. On the Order Fulfillment Satisfaction sheet,
   1. Convert data to a table using any style other than yellow
   2. Filter the data by Order Fulfillment Satisfaction for values for values 4 and greater
       
9. On the Packaging Satisfaction sheet,
   1. Convert data to a table using any style other than yellow
   2. Filter the data by Packaging Satisfaction for values for values 4 and greater
       
10. On the Product Satisfaction sheet,
    1. Convert data to a table using any style other than yellow
    2. Filter the data by Product Satisfaction for values for values 4 and greater
        
11. On the Refund Satisfaction sheet,
    1. Convert data to a table using any style other than yellow
    2. Filter the data by Refund Satisfaction for values for values 4 and greater
        
12. On the Sales Rep Satisfaction sheet,
    1. Convert data to a table using any style other than yellow
    2. Filter the data by Sales Rep Satisfaction for values for values 4 and greater
        
13. On the Shipping Materials Satisfaction sheet,
    1. Convert data to a table using any style other than yellow
    2. Filter the data by Shipping Materials Satisfaction for values for values 4 and greater
        
14. On the Shipping Satisfaction sheet,
    1. Convert data to a table using any style other than yellow
    2. Filter the data by Shipping Satisfaction for values for values 4 and greater
        
15. On the Main Data Sheet, insert a column between A and B naming it State. Enter the state that each city resides in.
    1. Tampa, FL
    2. Atlanta, GA
    3. Birmingham, AL
    4. Nashville, TN
    5. Kansas City, KS
    6. Louisville, KY
    7. Baltimore, MD
    8. Louisville, KY
    9. Dover, DE
    10. Charlotte, NC
    11. Charleston, SC
    12. Savannah, GA
    13. Knoxville, TN
    14. Lexington, KY
    15. Cincinnati, OH
    16. Chicago, IL
         
    17. Sort the data by State
         
    18. Insert Subtotals that change at each State and Average the Average Satisfaction Score
         
    19. Close all of the groups buy clicking the minus signs on the far left
         
    20. Highlight the row that contains the state with the Highest Average Satisfaction Score
         
16. Save the file as FirstInitialLastNameBUSA2205Unit3Mod2.xlsx

Unit 3 Module 3 Application Assignment

1. Download the attached file, CPSC-LOA-Data-MM-DD-2016.csv, and save to the desktop

2. Open Excel

3. Import CPSC-LOA-Data-MM-DD-2016.csv, this is a delimited comma separated file

4. Create a pivot table (named Primary Violation) showing Sum of LOT size and Primary Violation, then sort greatest to least

5. Create a second pivot table (named Country) showing Sum of LOT size and country, the sort greatest to least.

6. Save file as FirstInitialLastNameBUSA2205Unit3Mod3.xlsx (DBridgesBUSA2205Unit3Mod3.xlsx)

NOTE: there should be three worksheets total in this workbook when it is turned in.