GETTING STARTED
 Save the file SC_EX365_2021_2a_FirstLastName_1.xlsx as
SC_EX365_2021_2a_FirstLastName_2.xlsx
 Edit the file name by changing “1” to “2”.
 If you do not see the .xlsx file extension, do not type it. The file extension will
be added for you automatically.
 With the file SC_EX365_2021_2a_FirstLastName_2.xlsx open, ensure that
your first and last name is displayed in cell B6 of the Documentation worksheet.
 If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS

1. Shanice Traynor is the warehouse manager for the Roadrunner Online
   warehouse in Detroit, Michigan. The warehouse specializes in automobile parts,
   which consumers buy from Roadrunner Online. Shanice has started to create an
   Excel workbook to track items in the warehouse inventory. She asks you to
   format the workbook to make the information clearer and easier to understand
   before she finishes entering the data.
   Go to the Bin worksheet. Change the name of the worksheet tab to Bin List to
   use a more descriptive name.
2. Bottom Align the contents of the range C3:C13 to match the alignment of the
   other cells on the worksheet.
3. Format the list of bins to emphasize that the information belongs together as
   follows:
   a. Apply Outside Borders to the range A1:C13.
   b. Change the border color to Blue-Gray, Accent 3 (7th column, 1st row of
   the Theme Colors palette).
4. Apply the Short Date number format to cell B15 to use a more common date
   format.
5. In Page Layout view, enter Warehouse Bins in the center section of the header
   to provide a meaningful header when Shanice prints the worksheet.

Cashman Excel 365/2021 | Module 2: SAM Project 1a 6. Go to the Inventory List worksheet. To make it stand out, format the worksheet
title in cell A1 as follows:
a. In cell A1, change the font to Calibri Light.
b. Change the font size to 20 point.
c. Change the font color to Indigo, Text 2.
d. Bold the worksheet title.
e. Merge and center the worksheet title across the range A1:I1.

7. Resize the width of column B using AutoFit to display the full text of the
   descriptions.
8. Shanice asks you to find any duplicate entries and then delete them. Respond
   to her request as follows:
   a. In the range A5:A16, use Conditional Formatting Highlight Cells Rules to
   highlight duplicate values in Light Red Fill with Dark Red Text.
   b. Delete the row containing the second duplicate value.
9. Shanice wants to keep a running count of the items in inventory.
   In cell B3, enter a formula that uses the COUNTA function to count the values in
   the range B5:B15 to determine the number of items in inventory.
10. Shanice wants to display the entire text in cell F4, but format it so it requires
    less horizontal space. Make this formatting change as follows:
    a. Set the width of column F to 9.00, which is wide enough to display the
    characters in each word of the column heading in cell F4.
    b. Wrap the contents of cell F4 to display the text on two lines.
11. Apply shading to the range A2:B3 using Aqua, Accent 4, Lighter 60% (8th
    column, 3rd row of the Theme Colors palette) to separate the range from the
    rest of the worksheet.
12. To format the value as a dollar amount, decrease the number of decimal places
    in cell A3 to 2.
13. Apply the Accent3 cell style to the range A4:I4 to indicate those cells contain
    column headings and to the range E16:E18 to indicate those cells contain row
    headings.
14. Apply the Accounting number format to the range G5:H15 to show that the
    values are dollar amounts.
15. Shanice wants to know at a glance which items have the most inventory value.
    In the range H5:H15, use Conditional Formatting to create a Data Bars rule
    with the Gradient Fill Blue Data Bar color option.
16. Shanice also wants to highlight the items that need to be reordered.
    In the range I5:I15, use Conditional Formatting Highlight Cells Rules to format
    cells containing a value equal to Reorder in Light Red Fill with Dark Red
    Text.
17. In cell G16, enter a formula using the AVERAGE function to display the average
    cost of the parts in the warehouse (range G5:G15).
18. In cell G17, enter a formula using the MIN function to display the cost of the
    cheapest part located in the warehouse (range G5:G15).

Cashman Excel 365/2021 | Module 2: SAM Project 1a 19. In cell G18, enter a formula using the MAX function to display the cost of the
most expensive part located in the warehouse (range G5:G15).

20. Check the Spelling in the workbook to identify and correct any spelling errors.
21. Change the tab color of the Inventory List worksheet to Blue-Gray, Accent 3
    (7th column, 1st row of the Theme Colors palette), to match the color of the Bin
    List worksheet tab.