Project: Jay Stoller is a sales analyst at PCC Sport, a chain of sporting goods stores in the western United States. Jay is working on a sales report that will detail customer purchases of PCC Sport's line of cycling products. He has CSV files with two years of sales data. Jay wants you to compare website sales with brick-and-mortar store sales over that period. He also wants you to explore whether growing interest in women's cycling apparel is reflected in clothing sales at the company.

Modify the Workbook

1. Open the PCC.xlsx workbook. Save the workbook as XX_PCC Sport, substituting the XX with your initials.
2. In the Documentationworksheet, enter your name and the date.
3. In the Sales Trend worksheet, use the Query Editor to connect to the PCC SalesCSV file.
   1. Remove all columns except the Sales Date and Revenue columns.
   2. Create a column named Month that stores the end of month date for each transaction.
   3. Group the data by the Month column, creating a new column named Monthly Revenue that displays the sum of the revenue values.
   4. Change the name of the query to Monthly Revenue and then load the query to an Excel table, starting in cell A4 of the Sales Trend worksheet.
   5. Format the monthly revenue values in the range B5:B28 as currency with no decimal places.
4. In the Sales Trend worksheet, insert a scatter chart with straight lines of the data in the range A4:B28 resized to cover the range C4:N22. Create and format the chart and add axis titles as shown in the screen print on the next page.
5. Add a linear trendline to the chart to highlight the general trend of the monthly sales and for the two-period moving average. Compare your completed chart with the image below.

1. Based on the data in the range A4:B28, create a forecast sheet named Revenue Forecast that projects monthly sales through 12/31/2020 assuming a 12-month seasonal period and including a 95 percent confidenceintervalfor projections. Resize the forecast chart to cover the range C2:E23. Note that the increasingly wider confidence band around the projected values indicated that the forecast is less precise farther into the future. Move the worksheet after the Sales Trend sheet. Compare your worksheet to the image below.

1. Create the following three queries, only creating a connection,and loading the data in the Data Model:
   1. Access the PCC SalesCSV file. Delete the Changed Type step that the Query Editor generates, and then insert a new step that sets the data type of the Sales Date column to Date, the Units Sold column to a Whole Number, and the Revenue column to Decimal Number. Retrieve all the columns in the file.
   2. From the PCC StoresCSV file, retrieve all the columns except Street, Phone, and Manager.
   3. From the PCC ProductsCSV file, retrieve all the columns except the Unit Price column.
2. Using the Power Pivot tools, define the following table relationships: Connect the PCC Sales and PCC Stores tables through the StoreID field. Connect the PCC Sales and PCC Products tables through the ProductID field.
3. Create the following hierarchies in the specified order:
   1. In the PCC Storestable, create the Location hierarchy containing the StoreType, Region, State, and City State fields.
   2. In the PCC Products table, create the Product hierarchy containing the Group, Subgroup, and Product Description fields.
4. In the Product Revenue worksheet, starting in cell A4, insert a PivotTable that displays the sum (Values area) of the Revenue field from the PCC Sales table. Bring the Sales Date field in the ROWS area, and the Group field from the Products table in the COLUMNS area. Format the revenue values as currency with no decimal places. Name the Pivot Table PCC Product Revenue.
5. In the PCC Product Revenuetable, Group the Date field containing the Sales Datedisplay(Year), Sales Date (Quarter), Sales Date (Month), and Sales Date fields. Collapse the dates. Compare your table with the image below.

1. Insert a Pivot Table in cell A4 of the Products and Locationsworksheet, breaking down the total Revenueto the VALUESarea, the Product hierarchy to theCOLUMNSarea, and the Location hierarchy to the ROWSarea. Filter and drill down into the table so that it displays sales of men's and women's jackets for the three Colorado stores in the Mountain region. Format the sum of the Revenue field as currency with no decimal places. Compare your results with the image below.

1. In the Subtotals by Region sheet, a PivotTable has been created showing the sales for each state by region. Your boss, Jay, wants to see the total sales by region and within each region which state has the highest sales.
   1. Add Subtotals to the bottoms of the groups.
   2. Remove the Grand Total row.
   3. Change the report layout to the Tabular Form.
   4. Under Report Layout also select "Repeat All Item Labels."
   5. Sort the states within each region so the highest revenue appears at the top of the list in each region. Compare your table with the image to the right.

1. Next, Jay want to see what percentage sales each state has of the total and he wants them ranked and filtered to the top five performers. A PivotTable has been created on the Top Five States sheet summarizing the sales by state.
   1. Add a calculated field to this PivotTable that figures the percentage of each state's revenue of the Grand Total of revenue. Name the field Percentage. [Hint: you will divide Revenue bythe Grand Total of $5,767,329 using no dollar sign or commas (5767329).] Change the field name to Percentage and display the numbers as percentages with two decimals. (Note: You can also calculate the Percentage of Grand Total using the Value Field Settings dialog box. However, for this test you need to demonstrate your ability to create a calculated field and therefore must use this method. When your test is graded, it will be checked to verify you created a calculated field.)
   2. Drag another copy of the Revenue field to the Values area, placing it at the bottom of the list. Change the name to "Rank" and select the appropriate settings to rank this field largest to smallest.
   3. Sort the PivotTable in descending order by rank.
   4. Filter the table to show the top 5 ranked states.
   5. To further highlight the rank, using Conditional Formatting, add Blue Data Bars to the Percentage column. Compare your table with the image to the right.

1. Finally, Jay would like to compare the nutritional group revenue of bars and beans to drinks and gels in the Southwest region. A PivotTable has been prepared on the Southwest Nutrition Sales sheet showing the revenue for all these items for the Southwest region.
   1. Create a calculated item that will add together Bars and Beans and a second calculated item that will add together Drinks and Gels.
   2. Your PivotTable is now showing duplicate revenue information; filter the table to show only the Bars and Beans item, and the Drinks and Gels item.
   3. Show only the Grand Total for columns.Compare your table with the image below.

1. Save and close the XX_PCC Sportworkbook.

Task 3 - Module 12

Data File Needed: Sales Invoice.xlsx

Project: Hillsboro Investments is a new manufacturing organization. You have been asked to develop an Excel Sales Invoice. A colleague has already set up an Excel spreadsheet containing information. You need to name ranges, add data validation, and input error messages. You also need to protect one of the worksheets but allow for certain ranges to be edited. Finally, you will create a macro to clear the invoice after the order is submitted.

Modify the Workbook

1. Open the Sales_Invoice.xlsx data file and save it as XX_ Invoice.xlsx, substituting the XX with your initials Unprotect All Sheets and return to the Invoice Sheet.
2. Select the required range(s) and set up the following Validation rules:

Range Name	Validation Rules	Error Alert
Bill To: (Cell C8)	Create a validation rule that will allow the user to select a customer from a list of names. (Note a list of customers is in the Customers sheet.)	Set up a Stop alert informing the user to choose a customer from the list.
Phone: (Cell G8)	Create a validation rule that will allow the user to only enter a 10-digit phone number, using dash 2 dash separators. (Hint: limit the text length to 12 characters.)	Set up a Warning alert informing the user to enter the phone number in the following format: xxx-xxx-xxxx.
Item # (Cell B14:B38)	Create a validation rule that will allow the user to select an item number from a list. (Note a list of items is in the Product Detail sheet.)	Set up a Stop alert informing the user to choose an item from the list.

1. Test each validation rule:
   1. In the Bill To: Choose Tailspin Toys.
   2. Add Phone Number: 432-555-0178.
2. In the range B14:B17 the customer ordered 5 of each of the following items:
   1. # Z4579
   2. # Z4580
   3. # Z4581
   4. # Z4582
3. Create a formula to calculate the TOTAL in cell L41.
4. Unlock the ranges C8, G8, L9, B14:B38, H14:H38, and then protect the worksheet allowing users to only select locked or unlocked cells. No password protection needed. Save the workbook.
5. Save the workbook again as XX_ Invoice_Macro and change the file extension to a Macro Enabled file.
6. Create a macro called ClearInvoicethat will complete the following steps for the Invoice sheet:
   1. Delete the information in cells: C8, G8, L9, B14:B38, and H14:H38.
   2. Select cell H9. Stop Recording.
   3. A simple mistake was made in the recording. It was requested that after the Macro ended the cursor should be placed in cell C8. Open Visual Basic Editor and edit Range to C8.
   4. In Cells O1:P3 draw and assign a button for the ClearInvoice Macro. Edit the text of the button to an appropriate name. (Hint: you'll have to unprotect your sheet to create the button.)
7. Test your macro.
8. Protect the worksheet again.
9. Save the workbook.
10. Submit the following files to the Test 2 Assignment drop box in D2L:
    1. XX_CorpCosts.xlsx
    2. XX_PCC Sport.xlsx
    3. XX_ Invoice.xlsx
    4. XX_ Invoice_Macro.xlsm