Microsoft Excel 2010 Chapter 2 – Lab Test A

Performing Sales Analysis

Purpose:  To demonstrate the ability to enter and copy formulas, and apply formatting to a worksheet.

Problem:  You are an accountant for a collectibles store that has locations all throughout the state. You are trying to determine if stores are meeting their sales quotas. You know the sales amount, returns amount and sales quota. You will calculate the rest using functions.

Instructions:  Perform the following tasks to create the worksheet shown in Figure E2A-1.

  1. Enter and format the worksheet title Corital Collectibles and worksheet subtitle Yearly Sales Analysis in cells A1 and A2. Change the theme of the worksheet to the Concourse theme. Apply the Title cell style to cells A1 and A2. Change the font size in cell A1 to 28 points. Merge and center the worksheet title and subtitle across columns A through F. Change the background color of cells A1 and A2 to the Indigo, Accent 5, Lighter 60% color. Change the font color of cells A1 and A2 to the Yellow Standard color. Draw a thick box border around the range A1:A2.
  2. Change the width of column A to 15.00 points. Change the widths of columns B through F to15.00 points. Change the heights of row 3 to 33.00 points and row 11 to 30.00 points.
  3. Enter the column titles in row 3 and row titles in the range A10:A13, as shown in Figure E2A-1.Center the column titles in the range A3:F3. Apply the Heading 1 cell style to the range A3:F3.Apply the Total cell style to the range A10:F10. Bold the titles in the range A10:A13. Change the font size in the range A3:F13 to 12 points.
  4. Enter the data for the Store Location (A4:A9), Sales (B4:B9), Returns (C4:C9), and Sales Quota (E4:E9) columns using the column info from Figure E2A-1.
  5. Use the following formulas to determine the Net Sales in column D and the Above Quota in column F for the first store location. Copy the two formulas down through the remaining store locations.
    1. Net Sales (cell C4) =  Sales – Returns
    2. Above Quota (F4) = Net Sales – Sales Quota
  6. Determine the totals in row 10.
  7. Determine the average, maximum, and minimum values in cells B11:B13 for the range B4:B9, and then copy the range B11:B13 to C11:F13
  8. Format the numbers as follows: (a) assign the Currency style with a floating dollar sign to the cells containing numeric data in the ranges B4:F4 and B10:F13, and (b) assign a number style with two decimal places and a thousand’s separator (currency with no dollar sign) to the range B4:F9.
  9. Use conditional formatting to change the formatting to dark red font  Indigo, Accent 5, Lighter 80% background in any cell in the range F4:F9 that contains a value less than 0.
  10. Change the worksheet name from Sheet1 to Sales Analysis and the sheet tab color to the Yellow standard color. Change the document properties, as specified by your instructor. Change the worksheet header with your name, course number, and other information as specified by your instructor.
  11. Spell check the worksheet. Preview and then print the worksheet in landscape orientation. Save the workbook using the file name, Excel Chapter 2 – Lab Test A in the compapps/excel/chapter 2 folder on your Z:/ drive.

Figure E2A-1