Description

Forctis Sport Ray Stohler is a sales analyst at Forctis Sport, a chain of sporting goods stores located in the western United States. Ray is working on a sales report that will detail customer purchases of Forctis Sport’s line of cycling products. He has CSV ­les with two years of sales data. Ray 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 re‑ected in clothing sales at the company. Complete the following:

1. Open the Forctis workbook located in the Excel11 > Case3 folder included with your Data Files, and then save the workbook as Forctis Sport in the location speci­ed by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Sales Trend worksheet, use the Query Editor to connect to the Forctis Sales CSV ­le

located in the Excel11 > Case3 folder, and then do the following:

a. Remove all columns except the Sales Date and Revenue columns.

b. Create a column named Month that stores the end-of-month date for each transaction.

c. Group the data by the Month column, creating a new column named Monthly Revenue that

displays the sum of the revenue values for each of the 24 months in the data.

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

e. 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:L22. Format the chart so that it is easy to read and interpret.

5. Add a linear trendline to the chart to highlight the general trend of the monthly sales over the

past two years, even with the seasonal variation in sales.

6. Based on the data in the range A4:B28, create a forecast sheet named Revenue Forecast that projects monthly sales up to 12/31/2020 assuming a 12-month seasonal period and including a 95 percent con­dence interval for the projections. Resize the forecast chart to cover the range C2:E23. Note that the increasingly wider con­dence band around the projected values

indicates that the forecast is less precise farther into the future. Move the worksheet after the

Sales Trend sheet.

7. Create the following queries, only creating a connection and loading the data in the Data Model:

a. Access the Forctis Sales CSV ­le. 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 Whole Number, and the Revenue column to Decimal Number.

Retrieve all of the columns in the ­le.

b. From the Fortis Stores CSV ­le, retrieve all of the columns except Street, Phone, and

Manager.

c. From the Forctis Products CSV ­le, retrieve all of the columns except the Unit Price column.

8. De­ne the following table relationships: Connect the Forctis Sales and Forctis Stores table

through the StoreID ­eld. Connect the Forctis Sales and Forctis Products through the

ProductID ­eld.

Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

EX 726 Excel | Module 11 Analyzing Data with Business Intelligence

9. In the Product Revenue worksheet, starting in cell A4, insert a PivotTable that displays the sum of

the Revenue ­eld from the Forctis Sales table broken down by the Sales Date ­eld in the ROWS

area and the Group ­eld in the COLUMNS area. Format the revenue values as currency with no

decimal places.

10. Open to the Data Model in Power Pivot, and then create the following hierarchies in the

speci­ed order:

a. In the Forctis Sales table, create the Date hierarchy containing the Sales Date (Year), Sales

Date (Quarter), Sales Date (Month), and Sales Date ­elds.

b. In the Forctis Stores table, create the Location hierarchy containing the StoreType, Region,

State, and City State ­elds.

c. In the Forctis Products table, create the Product hierarchy containing the Group, Subgroup,

and Product Description ­elds.

11. Insert a PivotTable in cell A4 of the Products and Locations worksheet, breaking down the total

revenue by the Product hierarchy (COLUMNS area) and the Location hierarchy (ROWS area).

Drill down into the table so that it displays sales of men’s and women’s jackets for the three

Colorado stores. Format the sum of the Revenue ­eld as currency with no decimal places.

12. Insert a Power View sheet with the name and title Sales Report and then add the following

visualizations:

a. In the left half of the sheet, display a table containing the StoreType, Region, City State, and

Revenue ­elds from the Forctis Stores and Forctis Sales tables. Format the revenue values as

currency with no decimal places. (Hint: Select any revenue value in the Revenue column,

and then apply the Currency format from the Number group on the DESIGN tab.)

b. In the upper-right quarter of the sheet, insert a bar chart displaying the values of the Revenue