Sales and Inventory Items

A key activity for marketing professionals is to analyze projected sales and inventory information. This is especially important for retail environments. This exercise utilizes the skills covered in this chapter to analyze sales and inventory data.

Prepare the Worksheet

1. Open the file named SC1 Data and then Save As SC1 Sales and Inventory
2. In the Sheet1 worksheet, enter the word Totals in cell C14.
3. Format all the cells in Sheet1 to Century font style and a 12-point font size.
4. Set the column width for Columns A through G to 13.5.
5. Edit the entry in cell B2 to read “Item Number.”
6. Use AutoFill to fill the Item Numbers from B3 into the range B4:B13.  The item numbers should increase by one as they are filled through the range.
7. Copy the contents of cell A3 and paste them into the range A4:A8.
8. Delete Column F.

Apply Text and Cell Formatting

1. Format the range A1:F2 so the text is Bold.
2. Set the alignment in the range A2:F2 to Wrap Text.
3. Prepare A1:F1 for the title text by changing the fill color of the cells in the range A1:F1 to Red, Accent 2, Darker 25%.
4. Make the following font changes to the range A1:F1: set the font color to white, add italics, and set the font size to 14.
5. Merge and center the cells in the range A1:F1.
6. Enter the title for this worksheet in the range A1:F1. The title should appear on two lines. The first line should read Status Report. The second line should read Sales and Inventory by Item.
7. Increase the height of Row 1 so the entire title is visible.
8. Format the values in the range C3:C13 with dollar signs and two decimal places.
9. Format the values in the range E3:F13 with comma style, zero decimal places.

Add the SUM Function

1. In cell E14, use AutoSum to calculate the sum of the values in the range E3:E13.
2. In cell F14, use AutoSum to calculate the sum of the values in the range F3:F13.
3. Apply All Borders to the range A1:F14.
4. Add a thick bottom border to the cells with the column labels (A2:F2) and the cells for the last item number (A13:F13).
5. Add a thick line border around the perimeter of the range A1:F14.

Finalize the Worksheet

1. Insert a new blank worksheet in the workbook (this will be Sheet4).
2. Delete Sheet3.
3. Move Sheet4 ahead of Sheet2 so the order of the worksheets is Sheet1, Sheet4, and Sheet2.
4. Rename the Sheet1 worksheet tab to “Status Report.”
5. Change the orientation of the Status Report worksheet so it prints landscape instead of portrait.
6. Add a header to the Status Report worksheet that shows the date (needs to update) in the upper left corner and your name in the center.
7. Add a footer to the Status Report worksheet that shows the page number in the lower right corner with the word “Page” before the number.
8. Center the worksheet both horizontally and vertically on the sheet.
9. Check the spelling of the worksheet and make any necessary changes. Save the SC1 Sales and Inventory workbook.
10. Submit the SC1 Sales and Inventory workbook as directed  by your instructor.

Attribution

Copied from Beginning Excel 2019 and licensed under CC BY.