How to create a scenario summary report in excel 2016

Lab 1 of 6: Using Excel for Business Intelligence

Lab Overview

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

After completing the above tasks, you will write a short opinion paper with your findings and recommendations for Adventure Works.

Deliverables

You will submit two files for this lab.

· An Excel workbook titled Lab1_yourlastname.xlsx containing the following worksheets: (1) Sales Data, (2) Mountain-500 U.S. Orders, (3) Touring-3000 Australia Orders, (4) Sales by Product Category, (5) Sales by Country, (6) Sales Pivot by Product Country, (7) a second pivot table/pivot chart sheet, (8) Mountain-200 What-If, and (9) Scenario Summary.

· A Word document titled Lab1_yourlastname_Paper.docx containing a one-page summary of your findings and recommendations for Adventure Works.

When submitting the files, provide a comment explaining what you learned from completing this lab activity.

Category

Points

%

Step 1: Importing, Cleaning, and Converting Data

Sales data sheet contains data imported from the text file, professionally formatted, with added columns OrderTotal, FullName, and OrderCategory. Any spelling errors are corrected and duplicate records, or records with missing data, are removed.

10

14.4%

Step 2: Table Analysis

Mountain-500 U.S. Orders and Touring-3000 Australia Orders sheets contain records filtered as indicated. Sales by Product Category and Sales by Country sheets contain appropriate subtotals and pie charts.

15

21.4%

Step 3: Pivot Tables

Sales Pivot by Product Country sheet contains a pivot table and pivot chart as described in instructions. A second pivot sheet contains a pivot table and pivot chart using different fields selected by the student.

15

21.4%

Step 4: What If Analysis

Mountain-200 What If sheet contains baseline model, two one-variable data tables, and two two-variable data tables. Scenario Summary sheet contains a scenario summary report as described in instructions.

15

21.4%

Step 5: Opinion Paper

Provide at least two findings and recommendations.

15

21.4%

Total

70

100.0%

Lab Resources

Microsoft Excel 2016

You may use Microsoft Excel on your local PC or from the Virtual Lab-Citrix environment. The link is accessible from the Course Resources page in the Introduction and Resources module. View the Lab Resources section.

Lab Steps

Lab Videos

Please watch the videos below for guidance on completing the lab steps.

Important: The videos will demonstrate how to perform the operations required for the lab, but you will not receive full credit for the lab if you only recreate what is shown in the videos. You will learn to do each operation by following along with the video; then you will perform a similar operation on your own. Be sure to complete all steps in the written lab instructions below, using the videos as a guide; do not just follow along with the videos. Parts in the written instructions that go beyond what is shown in the videos will be marked with "On your own."

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.

a. 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."

b. 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.

c. Launch Excel 2016 and create a new blank workbook.

d. 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.

e. Rename the worksheet containing the imported data to sales data.

f. Format the worksheet professionally, making sure that column headings are bolded; all dollar values are formatted as currency; and column widths are set appropriately.

g. Clean the data by spell-checking all appropriate columns and deleting any rows containing missing observations, outliers, or duplicate entries.

h. Add a column titled OrderTotal that uses a formula to calculate the total price for each order, based on the UnitPrice and OrderQty columns.

i. Add a column titled FullName that contains the customer's full name by concatenating the FirstName and LastName columns.

j. 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.

k. 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!

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.

a. Make a copy of the Sales Data worksheet, naming it Mountain-500 U.S. Orders.

b. Convert the data to a table.

c. Filter the table to show only orders where the country is United States and the ProductModel is Mountain-500.

d. 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.

e. Make another copy of the Sales Data worksheet, naming it Sales by Product Category.

f. Convert the data to a table and sort A–Z by ProductCategory.

g. 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.

h. 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.

i. 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.

j. 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.

a. 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.

b. 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.

c. 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.

d. 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.

e. 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.

f. 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.

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.

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)

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.

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

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!

Step 5: Opinion Paper and Submit

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.