Adventure Works has supplied you with sales data extracted from their corporate database in the form of a text file. In this section, you will import, clean, and convert the data to prepare it for analysis.

1. Download and save the file AdventureWorksOrders.txt (Links to an external site.). Be sure to save the file rather than opening it directly from this link (it will not open in Excel because it is a text file). If you are not given the option of saving the file when you click on the link, right-click it and select "Save link as" or "Save target as."
2. For this lab, you may use your own copy of Microsoft Excel 2016, or you may use Excel 2016 in the Citrix virtual lab environment. You are recommended to use the virtual lab to become familiar with it, as it will be required for some future labs. You should definitely use the virtual lab if your own copy of Excel is not the 2016 version, or if you are not using the Microsoft Windows operating system (the version of Excel for the Mac OS is significantly different from the version for Windows).
   
   If you will be using the virtual lab environment, log into it as directed in the Lab Resources section above and copy the AdventureWorksOrders.txt file to your virtual home drive (the F: drive in the virtual environment, labeled with your DSI number). See the Lab Resources section on the Introduction & Resources area under Modules > Course Resources page for tutorials on accessing the Citrix virtual lab environment, installing the required Citrix receiver software, finding and launching applications, and copying files to and from your virtual home drive.
3.  Launch Excel 2016 and create a new blank workbook.
4.  Using the Get External Data From Text command on the Data tab of the ribbon, import the data from the AdventureWorksOrders.txt file into Excel. The file is a delimited file in comma separated value (CSV) format.
5. Rename the worksheet containing the imported data to sales data.
6. Format the worksheet professionally, making sure that column headings are bolded; all dollar values are formatted as currency; and column widths are set appropriately.
7. Clean the data by spell-checking all appropriate columns and deleting any rows containing missing observations, outliers, or duplicate entries.
8. Add a column titled OrderTotal that uses a formula to calculate the total price for each order, based on the UnitPrice and OrderQty columns.
9. Add a column titled FullName that contains the customer's full name by concatenating the FirstName and LastName columns.
10. Add a column titled OrderCategory that categorizes orders as large, medium, or small based on the OrderTotal, with large orders having OrderTotal greater than $1,500; medium orders having an OrderTotal between $500 and $1,500; and small orders having an OrderTotal less than $500.
11. Save the workbook using the file name Lab1_yourlastname.xlsx. If you are using the virtual lab environment, you should save it to a folder on your virtual home drive.

NOTE: Save your work before continuing on to Step 2!

Adventure Works management would like to examine individual orders for two specific product models: the Mountain-500 in the United States, and the Touring-3000 in Australia. They would also like to see how their sales break down by product category and by country. In this section, you will accomplish this using table analysis techniques, including filtering, sorting, subtotals, and charts.

1. Make a copy of the Sales Data worksheet, naming it Mountain-500 U.S. Orders.
2. Convert the data to a table.
3. Filter the table to show only orders where the country is United States and the ProductModel is Mountain-500.
4. On your own: Make another copy of the Sales Data worksheet, naming it Touring-3000 Australia Orders. Convert the data to a table and filter to show only orders where the country is Australia and the ProductModel is Touring-3000.
5. Make another copy of the Sales Data worksheet, naming it Sales by Product Category.
6. Convert the data to a table and sort A–Z by ProductCategory.
7. Convert the data back to a normal range and add subtotals to show the sum of OrderTotal for each product category. Group to hide the individual orders and show only the subtotals for each product category.
8. Create a pie chart showing how total sales are distributed by product category, making sure the chart has a descriptive title and that the pie slices are clearly labeled. Place the chart to the right of the table near the top of the worksheet.
9. On your own: Perform a similar analysis using subtotals and a pie chart to show how sales are distributed by country. Do this on another worksheet named Sales by Country.
10. Save the workbook file.

NOTE: Save your work before continuing on to Step 3!

Adventure Works management would like to see a more in-depth analysis of sales by country within each product category and subcategory, with the ability to drill down to see sales of specific product models within a subcategory when desired. In this section, you will use pivot tables and pivot charts to accomplish this.

1. Create a pivot table based on the Sales Data worksheet, placing the pivot table on a new worksheet named Sales Pivot by Product Country. Choose ProductCategory for the Filter, ProductSubcategory and ProductModel for the Rows, Country for the Columns, and Sum of OrderTotal for the Values.
2. Initially, set the ProductCategory filter to (All) and collapse the row fields so that only the product subcategory totals are shown, with none of the detail for the product models within the subcategories.
3. Create a stacked column pivot chart based on this pivot table. Give the chart a descriptive title and ensure all categories and data sets are clearly labeled. Place the chart to the right of the pivot table near the top of the worksheet.
4. Explore the data by changing the filter setting to look at each product category, and expanding or collapsing rows to look at product model details within specific categories. Please return the pivot table and chart to their initial settings before submitting the assignment.
5. On your own: Construct a different pivot table and chart based on your own choices of fields to analyze. Use at least one filter field, at least two row fields, at least one column field, and at least one value field. At least three of these five field choices must be different from the fields used in the first pivot table (for example, you could choose to still use ProductCategory for the filter and Country for the columns, but you would then need to make different field choices for the rows and values). You may use the same or a different chart type. Place your pivot table and chart together on a new worksheet, and give the worksheet tab a descriptive name.
6. Save the workbook file.

Adventure Works management is considering various pricing options for its most popular bike model, the Mountain-200. In this section, you will perform a what-if analysis to predict revenues, costs, and profits for this model under different conditions, using one-variable data tables, two-variable data tables, and scenarios.

a. Add a new blank worksheet to the workbook, and name it Mountain-200 What-If.

b. Starting in cell A1, enter the following as the baseline model for your analysis.

c. Assign meaningful names to the cells in the right column, using the labels in the left column.

d. On the same worksheet, create a one-variable data table that varies the Unit Price from $2,000 to $2,500 in increments of $50, and shows the corresponding values for revenue, total cost, and gross profit. Apply conditional formatting to highlight gross profit values greater than $120,000.

e. On the same worksheet, create a two-variable data table that varies the unit price from $2,000 to $2,500 in increments of $50 and also varies the quantity sold from 75 to 125 in units of 5, showing the corresponding values for gross profit. Apply conditional formatting to highlight gross profit values greater than $120,000.

f. On your own: Create a one-variable data table that varies the variable cost per unit from $500 to $750 by increments of $25, showing corresponding values for total cost and gross profit.

g. On your own: Create a two-variable data table that varies the variable cost per unit from $500 to $750 by increments of $25, and varies the fixed cost from $50,000 to $70,000 by increments of $2,000, showing the corresponding values of gross profit.

h. Using the scenario manager, create three scenarios named high, midrange, and economy, with the values shown below for the changing cells.

i. Generate a scenario summary report for the above scenarios, with revenue, total cost, and gross profit as the result variables. Place the scenario summary report worksheet immediately after the Mountain-200 What-If worksheet in the workbook.

j. Save the workbook file. 

NOTE: Save your work before continuing on to Step 5!

Review all the analyses performed for this lab activity, and write a one-page opinion paper summarizing at least two key findings that you believe are significant for Adventure Works Cycles, and making at least two recommendations for actions that management should take in response. Your paper should be in APA format, professionally written, and should be free of grammatical errors, typos, and misspellings. Save your paper in a Microsoft Word file named Lab1_yourlastname_Paper.docx.

Submit both your Excel workbook and your Opinion Paper document.