Scenario/Summary
Excel is a great tool that allows businesses to analyze data and make important decisions. In this lab, you will be using Microsoft Excel to complete some basic business intelligence tasks for Adventure Works Cycles, a fictional company that manufactures metal and composite bicycles for sale to commercial distributors in North America, Europe, and Asia. Adventure Works also sells bicycles and related products direct to consumers over the internet. Adventure Works is a multinational company headquartered in Bothell, Washington. The company is currently seeking to expand its market share by targeting high-volume customers, expanding availability of products on the web, and lowering production costs.
You have been hired as a data analyst for the company. Adventure Works managers have requested that you analyze 3 years of sales and cost data to help them answer specific business questions. In the course of this analysis, you will perform the following in Excel.
Importing, cleaning, and converting data
Table analysis
Pivot tables
What if analysis
Step 1: Importing, Cleaning, and Converting Data
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.
Download and save the file AdventureWorksOrders.txtLinks 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.
For this lab, you may use your own copy of Microsoft Excel 2016 or 2019 or you may use Excel 2019 in the Azure 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 or higher 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 to your local computer. Desktop or Documents is recommended. The File can then be copied into the Azure Virtual Lab environment.
Launch Excel 2016 or 2019 and create a new blank workbook.
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.
Rename the worksheet containing the imported data to sales data. Saving in Azure lab environment will allow you to copy the file from Excel in Azure and paste to your Desktop or Documents folder.
Format the worksheet professionally, making sure that column headings are bolded, all dollar values are formatted as currency, and column widths are set appropriately.
Clean the data by spell-checking all appropriate columns and deleting any rows containing missing observations, outliers, or duplicate entries.
Add a column titled OrderTotal that uses a formula to calculate the total price for each order based on the UnitPrice and OrderQty columns.
Add a column titled FullName that contains the customer’s full name by concatenating the FirstName and LastName columns.
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.
Save the workbook using the file name Lab1_yourlastname.xlsx. If you are using the virtual lab environment, you can then copy and paste the file to your local computer to prepare for submission.
Note: Save your work before continuing on to Step 2!
Step 2: Table Analysis
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.
Make a copy of the Sales Data worksheet, naming it Mountain-500 U.S. Orders.
Convert the data to a table.
Filter the table to show only orders where the country is United States and the ProductModel is Mountain-500.
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.
Make another copy of the Sales Data worksheet, naming it Sales by Product Category.
Convert the data to a table and sort A–Z by ProductCategory.
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.
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.
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.
Save the workbook file.
Note: Save your work before continuing on to Step 3!
Step 3: Pivot Tables
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.
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.
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.
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.
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.
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. Save the workbook file.
Note: Save your work before continuing on to Step 4!
Step 4: What-If Analysis
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.
Add a new blank worksheet to the workbook, and name it Mountain-200 What-If.
Starting in cell A1, enter the following as the baseline model for your analysis.
What-If Analysis for Mountain-200 Cost and Pricing
Unit Price $2,300
Quantity Sold 100
Revenue (Enter formula to calculate based on Unit Price and Quantity Sold)
Variable Cost per Unit $630
Total Variable Cost (Enter formula to calculate based on Quantity Sold and Variable Cost per Unit)
Fixed Cost $60,000
Total Cost (Enter formula to calculate based on Total Variable Cost and Fixed Cost)
Gross Profit (Enter formula to calculate based on Revenue and Total Cost)
Assign meaningful names to the cells in the right column using the labels in the left column.
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.
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.
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.
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.
Using the scenario manager, create three scenarios named high, midrange, and economy, with the values shown below for the changing cells.
Changing Cell High Midrange Economy
Unit Price $2,550 $2,300 $2,050
Quantity Sold 90 100 110
Variable Cost per Unit $565 $630 $695
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.
