summary sales data can be compared to inventory investments to determine whether the product mix is optimal

Step
Instructions
Points Possible

1
Start Excel. Download and open the file named Excel_Ch02_Prepare_WeeklySales.xlsx. Grader has automatically added your last name to the beginning of the filename.
0

2
To be of value, information must be communicated effectively. Effective communication of information generally requires the information is formatted in a manner that aids in proper interpretation and understanding. Format cell range B6:H6 with the Accounting Number Format. Format cell range B7:H8 with the Comma Style. Format cell range C29:C30, C33, and C36:C38 with the Percent Style, increase decimal once.
0.3

3
Negative numbers often require more than parentheses or a hyphen to call attention to the fact that a value is less than zero. Format cell C31 and cell C34 with the Currency with 0 decimal places. Format cell range B20:H22 with Number, 2 decimal places, Use 1000 Separator (,) with negative numbers select the red negative number format (1,234.10).
0.2

4
Excel stores a date and time as a number in which the digits to the left of the decimal place are the number of complete days since January 1, 1900. Date and time formatting allows Excel date and time values to be displayed in a fashion that allows human interpretation. Cell B4 is an unformatted date in Excel. Format cell B4 with the Short Date format. Using the fill handle, copy the date through cell range B4:H4. AutoFit column widths as necessary.
0.2

5
On the HourlySales-Sunday worksheet, the data in cell range A6:A7 is unformatted time. Format cell range A6:A7 with Time format (1:30 PM style). Using the fill handle, copy the contents and format of cells A6:A7 through cell range A6:A28.
0.2

6
Cell alignment can be used to improve the appearance of your worksheet. On the WeeklySales worksheet, right align cell range A5:A25. Left align cells A5, A10, A15, and A19. Indent cells A5, A10, A15, and A19. Right align cells I4:J4. Bottom align cells B4:J4.
0.3

7
Sometimes it is helpful to display information at an angle or even vertically rather than in the standard horizontal left to right. Rotating textual content can be very helpful in presenting information in a space-efficient yet readable manner. Angle cell range B4:J4 Counterclockwise orientation. Bold and Center cell range B4:J4.
0.2

8
Fill color can be used to categorize information, to band rows or columns as a means of assisting the reader to follow information across or down a worksheet, or to highlight values. Format cell ranges B4:J4 and A4:A25 with Gold, Accent 4, Lighter 40% (eighth column, fourth row) fill color. Format cells A9, A14, and A18 with No Fill.
0.4

9
After changing the background color of cells, you notice the borders are no longer visible. To view the cell borders, you will add cell borders. Format cell ranges B4:J4 and A5:J25 with the All Borders border style. **The book shows A5:A25. Please use A5:J25 as given in these instructions for completing the prepare case exercise. Format cells A5, A10, A15, A19 with a Thick Bottom Border. Format cell J9 with a Top and Double Bottom Border.
0.3

10
Format cell ranges B9:I9, B14:I14, B18:I18 and B26:I26 with the Top and Bottom Border. Format cell range B27:I27 with a Bottom Double Border.
0.4

11
Formatting a cell(s) can involve several steps. To save yourself time and have consistent formatting throughout your worksheet you can copy formats from one cell to another with the Format Painter. Using the Format Painter, copy the format in cell B22, and copy the format to cell range B23:H25. By double-clicking the Format Painter, copy the format of cell B6 to the cell ranges B11:H11, B16:H16, and B20:H20. Turn off the Format Painter. AutoFit column width if necessary.
0.4

12
When a cell is copied to the Clipboard, there is much more than a simple value ready to be pasted to another location. Formats, formulas, and values are all copied and can be selectively pasted to other locations in a workbook. Copy cell B7. Using Paste Special feature, paste only the format of cell B7 to cell ranges B12:H13 and B17:H17.
0.2

13
Not only should worksheets be professionally formatted, they should also contain correct spelling. Even professionally formatted worksheets can be confusing if they contain misspelled words or phrases. Check the spelling of the worksheet.
0.2

14
Adding pictures to a worksheet can add visual interest and can also help with the branding of your company. In cell A1, insert the Excel_Ch02_Prepare_red_bluff.jpg picture. Align the picture with Snap to Grid. Resize the picture so the right border is between Columns B and C, and the bottom border is between rows 1 and 2.
0.2

15
To save yourself time formatting, you may choose to use built-in cell styles, which allow for rapid and accurate changes to the appearance of a workbook with very little effort. On the HourlySales-Sunday worksheet tab, apply the Themed Cell Style 20% – Accent1 to cells B4 and H4. Apply the Themed Cell Style 40% – Accent1 to cells E4 and J4. Apply the Heading 4 cell style to cell ranges A3:B3, B5:O5, A5:A33. **On this item, the book shows B4:O5 instead of B5:O5. Please use what is provided on this instruction sheet. Apply the Total cell style to cell range B29:O29.
0.8

16
Workbook themes allow you to change the formatting of the worksheets throughout the entire workbook, in a quick and uniform manner. On the HourlySales-Sunday worksheet, apply the Parallax theme. Click the HourlySales-Monday worksheet and note the theme was applied to that worksheet as well. On the HourlySales-Monday worksheet, apply the Headlines theme. The Parallax theme has been removed and the Headlines theme is now applied to all worksheets in the workbook. On the WeeklySales worksheet, notice the background colors that were set by using cell formatting are also affected by the new workbook theme. Also notice for any cell in which a font was not explicitly set, the font has changed. If the theme is not available, browse for the downloaded theme file. Use the Page Layout tab, Themes group.
0.2

17
Instead of reaching for a calculator or a piece of paper, you will use formulas to perform mathematical calculations. Formulas in Excel perform calculations within a worksheet, workbook or multiple workbooks. By creating a formula, you can save yourself time by copying the formula to other locations within the worksheet or workbook. On the WeeklySales worksheet, in cell B9 create a formula to add cells B6, B7 and B8. Copy the formula in cell B9 to cell range C9:H9.
0.4

18
A function is a built-in program that performs operations on data. The SUM function is a commonly used function that adds all numeric information in a specified range. In the previous step, you created a simple formula to add cells. You can also add cells using the SUM functions. Use the AutoSum button to create totals in the following cell ranges: B14:H14, B18:H18, B26:H26, I6:I9, I11:I14, I16:I18, and I20:I26. In cell B27, use the AutoSum button to total the noncontiguous cells in B26, B18, B14, and B9. Copy the formula in cell B27 through the cell range C27:I27.
0.25

19
In cell J25, calculate the commissions John Schilling earned on golf lessons. To calculate the commission, multiply the total revenue from golf lessons for the week in cell I25 by the commission paid for golf lessons is in cell C33.
0.2

20
In cell J6, calculate the commissions earned selling golf clubs in the Pro Shop by multiplying the total golf club sales in cell I6 by the commission percentage on golf club sales in cell C29. In cell J7, calculate the commission earned on Pro Shop accessories by multiplying the sum of golf ball and accessory sales in cells I7 and I8 by the commission rate in cell C30. (Use parenthesis where appropriate.)
0.4

21
In cell J9, using the AutoSum function total the commissions from cell J6 and J7.
0.2

22
Using the AutoSum button to total a range of cells that includes noncontiguous data may not give you the result you desire. Instead, consider inserting a function using AutoSum after selecting source cells when the source range does not contain contiguous data. On the HourlySales-Sunday worksheet tab, you will first total the Sunday hourly sales in row 29. Select cell range B29:B6. Total the data by using the AutoSum function. Using the fill handle, copy the formula through cell range B29:O29.
0.4

23
Next, you will calculate averages and counts for the HourlySales-Sunday worksheet. The AVERAGE function returns the average (mean) from a specified range of cells. The COUNT function returns the number of cells in a cell range that contain numbers. In cell B30, use a function to calculate the average of B6:B28. Use the fill handle to copy the formula to cell range B30:O30. In cell B31, use a function to count the cells with numbers in cell range B6:B28. Use the fill handle to copy the formula to B31:O31.
0.4

24
You want to analyze the hourly sales data even further by finding the smallest and largest values. The MIN function examines all numeric values in a specified range and returns the minimum value. The MAX function examines all numeric values in a specified range and returns the maximum value. In cell B32, use a function to calculate the maximum value of cell range B6:B28. Use the fill handle to copy the formula to cell range B32:O32. In cell B33, use a function to the minimum value of cell range B6:B28. Use the fill handle to copy the formula to B33:O33.
0.4

25
The functions inserted into the HourlySales-Sunday worksheet can be used to calculate the same values in the HourlySales-Monday worksheet. They simply need to be copied between worksheets. On the Sunday worksheet, copy rows 29:33. Paste the copied data to the Monday worksheet to rows 29:33.
0.2

26
Formatting of a worksheet can not only improve the worksheet’s appearance, but can also assist in the analysis of data. Conditional formatting allows the specification of rules that apply formatting to a cell as determined by the rule outcome. It is a way to dynamically change the visual presentation of information in a manner that adds information to the worksheet. You wan to highlight the top sales figures in each category, so you will create a conditional format. On the WeeklySales worksheet, in cell range B9:H9, add a conditional format that highlights the top 1 cell in the range. Format the top cell in the range with Green Fill with Dark Green Text.
0.2

27
You want to highlight the low sales figures in each category. You will creating another highlight cells conditional format. In cell range B9:H9, add a conditional format that highlights the Bottom 1 cell in the range. Format the bottom cell in the range with Light Red Fill with Dark Red Text.
0.2

28
Next, using the Format Painter, copy the conditional formatting in cell range B9:H9 to cell ranges B14:H14, B18:H18, and B26:H26.
0.3

29
The Accounting Number Format does not include the option to display negative numbers in red as displayed in C8. To display the negative numbers in red for the cell range B6:I27, create conditional formatting. The conditional format will Highlight Cells that are Less Than zero, then format the cells less than zero with Red Text.
0.2

30
Conditional formatting can also be used to highlight whether a value satisfies criteria such as a benchmark. You will use conditional formatting with icon sets to highlight above-minimum commissions. In cell J9, create a conditional format with Icon Sets. The conditional format will format all cells based on their value. Under Edit the Rule Description, change the Icon Style arrow to 3 Arrows (Colored). You may have to scroll up. Under Icon, next to the yellow arrow icon, change the icon to red down arrow. Change the bottom Icon, next to the red arrow icon, to No Cell Icon. Change both Type boxes to Number. Change the top Value box to $C$31
0.2

31
Use the conditional format you just created in cell J9 for the golf lessons commission in cell J25. Copy the conditional format from cell J9 to cell J25. Edit the conditional format in cell J25 so the top Value box references cell $C$34 the minimum commission for the golf pro. If necessary, Autofit the column width of Column J.
0.05

32
In using a formula to determine which cells to format, the conditional format always starts with an equal sign, which is followed by a conditional test. If that condition is TRUE then the formatting is applied. If the condition is FALSE, then it will not apply the formatting. You will create a conditional format which will format weekly sales totals to be displayed in a bold and green font if they meet or exceed benchmarks determined as follows: In cell I9, create a Highlight Cells conditional format that uses the following formula rule =I9/I27>=C36 Set the Format to Bold and color Green. In cell I14, create a Highlight Cells conditional format that uses the following formula rule =I14/I27>=C37 Set the Format to Bold and color Green. In cell I18, create a Highlight Cells conditional format that uses the following formula rule =I18/I27>=C38 Set the Format to Bold and color Green.
0.3

33
It may be necessary to remove the conditional formatting without affecting other cell formatting or cell contents. Remove conditional formatting from the following cell ranges B10:I10, B15:I15, and B19:I19.
0

34
A worksheet can contain information that may not be necessary, or even desirable, to display. You want to hide the data that is being used to determine commissions. Hide rows 29:38 of the WeeklySales worksheet.
0.2

35
Gridlines are very helpful in visualizing and navigating a workbook during development, but some users feel gridlines clutter a worksheet. Remove the Gridlines from the WeeklySales worksheet.
0.2

36
Show Formulas is very helpful in understanding how a worksheet is structured. It is an essential aid in correcting errors or updating the function of a worksheet. You want to create a worksheet that displays the formulas instead of the calculated results. Make a Copy of the WeeklySales worksheet. Name the worksheet Formulas Move the Formulas worksheet to the right of the left of the Documentation worksheet. On the Formulas worksheet, display the formulas. Change the page orientation to Landscape. Scale the page layout for both Height and Width to 1 page.
0.3

37
On the Documentation worksheet tab, and then complete the following. In cell A8, type today’s date in mm/dd/yyyy format. In cell B8, type your name in Firstname Lastname format. In cell C8, type Green background for high sales In cell C9, type Red background for low sales In cell C10, type Added a Formulas worksheet
0.3

38
Add the file name to the left page footer on all worksheets. Set the workbook to print all worksheet.
0.2

39
Save and close Excel_Ch02_Prepare_WeeklySales_LastFirst.xlsx. Exit Excel. Submit the file as directed.
0