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 specied 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 condence interval for the projections. Resize the forecast chart to cover the range C2:E23. Note that the increasingly wider condence 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
c. From the Forctis Products CSV le, retrieve all of the columns except the Unit Price column.
8. Dene 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
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
10. Open to the Data Model in Power Pivot, and then create the following hierarchies in the
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
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
Get Free Quote!
388 Experts Online