Load the data onto your computer

1. Download the sales data document: CCS-Sales-DataDownload CCS-Sales-Data.
   This is a comma-separated values (.csv) file containing more than 1,000 sales records. Be sure to remember where you save it; you’ll need to find it in the next step.
2. Start your spreadsheet program. Use the import feature to import CCS-Sales-Data.csv into your spreadsheet program. Note: CCS-Sales-Data.csv is a comma-delimited file. During the import, you may need to specify that commas delimit the file. For more information on how to import files into Microsoft Excel, visit the following Microsoft Office Support webpage: Text Import WizardLinks to an external site..
3. Using Save As, save the file as an .xlsx file type (an “Excel workbook”), and name it with your last name (e.g., callahan.xlsx).
4. Add column headers to match the data. In other words, the first row of the Excel spreadsheet should include the column headings Store Number, Sales Region, Item Number, Item Description, Unit Price, Units Sold, and Month-Year.

Manipulate the data

Next, you will use Microsoft Excel’s formula functions to sort, group, and manipulate the data. For guidance using the functions described in the following steps, refer to the Learning Resources: Spreadsheets page in this module. Before attempting the following steps, you may wish to complete the Practice: Using Spreadsheets activity, which is also found in this module.

Note: Steps 5 through 13 below should be completed on one worksheet in the Excel spreadsheet. Label the worksheet “5-to-13.” Steps 14, 15, and 16 should be on separate worksheets of their own. For Step 17, each pivot table and the corresponding chart should be on its own worksheet.

5. Add a new column to the spreadsheet named Sale Amount. Compute the sale amount for each row (each sales record by multiplying unit price and units sold). Use an appropriate Excel formula to accomplish this.
6. What are the total sales for the quarter? Use the SUM function to accomplish this.
7. Find the minimum sale amount at each store and the maximum sale amount at each store. In other words, compute the lowest dollar amount of sales at each store and the highest dollar amount of sales at each store. You can accomplish this either by constructing pivot tables or by using built-in Excel formulas such as MINIFSLinks to an external site. and MAXIFSLinks to an external site..
8. Use the SUMIF function to find the total sales for each store.
9. Use the SUMIF function to find the total sale for each region.
10. Assume that we have the following profit margins: a desktop CPU (or laptop) profit margin is 25%, a 101 keyboard profit margin is 10%, a 17-inch monitor is 10%, and a PC mouse is 5%. How much profit did we make this quarter? What is our total profit margin?Round the profit margin to the nearest hundredth of a percentage.
11. Based on the profits computed in Step 10, which is the best performing region in terms of profit, and what were the quarterly profits for that region?
12. Based on the profits computed in Step 10, which is the best performing store in terms of profit, and what were the profits for that store?
13. Suppose we are able to increase our profit margins so that the new profit margins are indicated as follows: a desktop CPU (or laptop) profit margin is 26%, a 101 keyboard profit margin is 15%, a 17-inch monitor is 15%, and a PC mouse is 15%. What is the new profit amount? Compared to the profit you computed in Step 10, how much (in dollar figures as well as in percentage of change) does our total profit increase? Percentage of change from the original value to the new value is computed as follows:
14. Based on total quarterly sales, what percentage of total sales does each region contribute? Submit your answer on a separate worksheet.
15. Based on total quarterly sales, what percentage of total sales does each store contribute? Submit your answer on a separate worksheet.
16. What are the total sales by month? Submit your answer on a separate worksheet.

Sort the data

17. Create pivot tables for the following: (i) Total Sales by Region, (ii) Total Sales by Store, (iii) Total Sales by Item Number, and (iv) Total Sales by Month. For each pivot table, also construct a chart (graph) of your choice for the data. For example, for (i), you can construct a bar graph with region on the x-axis and total sales on the y-axis. For (ii), you may choose to construct a pie chart depicting total sales by store. Each pivot table and the corresponding chart should be on its own worksheet. Do your answers from Steps 8 and 9 above match the answers you arrived at by using pivot tables?