Business Finance
University of North Texas Module 11 Fabric Station Data Analysis Project

University of North Texas

Question Description

I’m trying to learn for my Excel class and I’m stuck. Can you help?

It has to be done by reading the instructions and do the exercise in excel file. Please do not just rename my name in other's file because I will get a zero grade if they find it. Thank you. It has to be done in coming 3 days ( by 11/22/2019)

Unformatted Attachment Preview

New Perspectives Excel 2016 | Module 11: SAM Project 1a Fabric Station DATA ANALYSIS WITH BUSINESS INTELLIGENCE GETTING STARTED • Open the file NP_EX16_11a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX16_11a_FirstLastName_2.xlsx by changing the “1” to a “2”. o • • To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer: o Support_NP_EX16_11a_History.csv o Support_NP_EX16_11a_2018-2019.csv o Support_NP_EX16_11a_Fabric.accdb With the file NP_EX16_11a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. To complete this project, you need to add the Power Pivot tab to the ribbon as follows: o From the File tab, click the Options button. In the Data section of the Advanced tab, click the checkbox next to Enable Data Analysis add-ins: Power Pivot, Power View, and Power Map, and click OK. *Power Pivot, Power View, and Power Map are not available on Microsoft Office Home and Business 2016 and Microsoft Office 365 Business. PROJECT STEPS 1. As an intern at a Fabric Station store in Chicago, Illinois, you are working with Jordan Sanderson to produce a sales report. Four other cities have Fabric Station stores, and Jordan wants to analyze sales for the past two years and project future sales for all the stores. To create the report, you need to import data from various sources and use the Excel Business Intelligence (BI) tools. Switch to the Sales History worksheet. Use Power Query to create a query and load data from a CSV file into a new table as follows: a. Create a new query that imports data from the Support_NP_EX16_11a_History.csv file, available for download from the SAM website. New Perspectives Excel 2016 | Module 11: SAM Project 1a b. 2. 3. Edit the query to remove the Units Sold and Notes columns, then close and load the query data to a table in cell A3 of the existing worksheet. (Hint: Use the Close & Load to… button.) Insert a chart to identify trends in the sales history data as follows: a. Insert a Scatter chart based on the values in the range B3:C26. b. Move and resize the chart so that it covers the range D3:J20. c. Add the axis title Sales ($mil) to the vertical axis. d. Use Sales Trend as the chart title. e. Add a Logarithmic trendline to the chart. Switch to the Current Sales worksheet. Use Power Query as follows to create a query that loads daily sales data from a CSV file and transforms the data to display only monthly totals: a. Beginning in cell A3, create a new query that imports data from the Support_NP_EX16_11a_2018-2019.csv file, available for download from the SAM website. b. Remove all columns except the OrderDate and Sales columns. c. Based on the OrderDate column, add a new Date column to the query using the End of Month option. (Hint: Select the OrderDate column before selecting options on the Add Column tab.) d. Use Month as the name of the new column. To display the total sales for each month, group the dates in the Month column and create a new column displaying the sum of the Sales values as follows: 4. e. Transform the data in the Month column by grouping the data by month. f. Use Monthly Sales as the name of the new column. g. Use Sum as the operation. h. Use Sales as the column to sum within the Month group. i. Load the query data to a table in the existing worksheet beginning in cell A3. j. Format the data in the range B4:B27 using the Currency number format with 2 decimal places and the $ symbol. k. If an extra blank row appears at the end of the new table, remove it. Create a forecast sheet to track the changes in monthly sales and project next year’s monthly sales as follows: a. Based on the data in the range A3:B27, create a forecast sheet. b. Using the Options menu in the Create Forecast Worksheet dialog box, manually set the seasonality to 12. c. Use 12/31/2020 as the Forecast End date. (Hint: After clicking the Create button, click the Got it! button if the FORECAST SHEET dialog box opens.) New Perspectives Excel 2016 | Module 11: SAM Project 1a 5. d. Use Monthly Sales Forecasts as the name of the new sheet. e. Resize and move the forecast chart to cover the range C2:E24. Switch to the Fabric Sold by Store worksheet. Create a query and load data from an Access database to display information about fabric purchased from Fabric Station stores as follows: a. Create a new query that imports data from the Support_NP_EX16_11a_Fabric.accdb Access database, available for download from the SAM website. (Hint: Don't open the support files as this may cause an error.) b. Preview the 2018-2019 Orders table, and then only create a connection to the data. (Hint: Use the Load To button arrow to open the Load To dialog box.) c. Add the data to the Data Model. Create a PivotTable that shows the fabric sold by category in each of the five stores during 2018 and 2019 as follows: 6. 7. 8. d. In cell A3, insert a PivotTable using the workbook’s Data Model. e. Use the following fields from the 2018-2019 Orders table in the PivotTable areas: o ItemQty field: Values box o Category field: Rows box o StoreCity field: Columns box Format the PivotTable to make the data easier to interpret as follows: a. Use Items Sold as the custom name of the Sum of ItemQty field. b. Format the Sum of ItemQty values using the Number format with 0 decimal places and a 1000 separator. Insert a Timeline Slicer to show items sold in January, 2019 as follows: a. Insert a Timeline Slicer that uses the OrderDate field from the 2018-2019 Orders table. b. Move and resize the Timeline Slicer to cover the range A12:G21. c. Use the Timeline Slicer to display items sold only in January, 2019. Switch to the Fabric Sold by Date worksheet. Create a PivotTable as follows that shows each category of fabric sold by date: a. In cell A3, insert a PivotTable using the workbook’s Data Model. b. Use the following fields from the 2018-2019 Orders table in the PivotTable areas: o OrderDate field: Rows box o ItemQty field: Values box o Category field: Columns box New Perspectives Excel 2016 | Module 11: SAM Project 1a 9. Create hierarchies for the order dates and product categories as follows so you can easily find information about a specific fabric sale on any date: a. Use the Manage option on the Power Pivot tab to display the Data Model in Diagram View. b. Create a new hierarchy and use Date as the hierarchy name. c. Add the OrderDate(Year), OrderDate(Quarter), OrderDate(Month) and OrderDate fields in that order to the new Date hierarchy. (Hint: Do not select the OrderDate(Month Index) field.) d. Create another new hierarchy and use Product as the hierarchy name. e. Add the Category and Subcategory fields in that order to the new Product hierarchy, and close the Power Pivot window. Revise the PivotTable to use the hierarchies you created as follows: f. Remove the fields from the Columns and Rows boxes. g. Use the following hierarchies from the 2018-2019 Orders table in the PivotTable areas: h. 10. 11. o Date hierarchy: Rows box o Product hierarchy: Columns box Leave the ItemQty field in the Values box. Format the new PivotTable to match the one on the Fabric Sold by Store worksheet as follows: a. Use Items Sold as the custom name of the Sum of ItemQty field. b. Format the Sum of ItemQty values using the Number format with 0 decimal places and a 1000 separator. Use Drill Down to display the daily sales for January, 2019. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. New Perspectives Excel 2016 | Module 11: SAM Project 1a Final Figure 1: Sales History Worksheet New Perspectives Excel 2016 | Module 11: SAM Project 1a Final Figure 2: Monthly Sales Forecasts Worksheet New Perspectives Excel 2016 | Module 11: SAM Project 1a Final Figure 3: Current Sales Worksheet New Perspectives Excel 2016 | Module 11: SAM Project 1a Final Figure 4: Fabric Sold by Store Worksheet New Perspectives Excel 2016 | Module 11: SAM Project 1a Final Figure 5: Fabric Sold by Date Worksheet Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file New Perspectives Excel 2016 | Module 11: SAM Project 1a Fabric Station DATA ANALYSIS WITH BUSINESS INTELLIGENCE Ganesh Gurung dit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. M website. Fabric Station: Sales 1997 - 2019 20191119172435np_ex16_11a_ganeshgurung_1 Sales History 11/19/2019 Fabric Station: Sales of Selected Products 20191119172435np_ex16_11a_ganeshgurung_1 Current Sales 11/19/2019 Fabric Station: Fabric Sold by Store Fabric Station: Fabric Sold by Date ...
Purchase answer to see full attachment
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

Final Answer

Attached.

Author:

Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file

New Perspectives Excel 2016 | Module 11: SAM Project 1a

Fabric Station

DATA ANALYSIS WITH BUSINESS INTELLIGENCE
Ganesh Gurung

dit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

M website.

Fabric Station: Sales 1997 - 2019
Year Business Year Sales ($mil)
1997
1
1.062
1998
2
2.246 14
1999
3
4.197
2000
4
7.175 12
2001
5
8.221
2002
6
8.065 10
2003
7
6.124
2004
8
7.097 8
2005
9
8.214
2006
10
8.312 6
2007
11
9.207
2008
12
7.021 4
2009
13
8.927
2010
14
8.908 2
2011
15
9.648
2012
16
10.448 0
0
2013
17
11.239
2014
18
11.787
2015
19
11.742
2016
20
11.739
2017
21
12.019
2018
22
12.224
2019
23
12.881

Sales Trend

5

10

15

20

25

20191124222659np_ex16_11a_ganeshgurung_1 Sales History 11/24/2019

Forecast(Monthly Sales) Lower Confidence Bound(Monthly Sales)
400000
350000
300000
250000
200000
150000
100000
50000

10/31/2019

9/30/2019

8/31/2019

7/31/2019

6/30/2019

5/31/2019

4/30/2019

3/31/2019

2/28/2019

1/31/2019

12/31/2018

11/30/2018

9/30/2018

10/31/2018

8/31/2018

7/31/2018

6/30/2018

5/31/2018

4/30/2018

3/31/2018

2/28/2018

0

1/31/2018

Month
Monthly Sales
1/31/2018
117763.47
2/28/2018
86569.15
3/31/2018
96682.76
4/30/2018
138393.98
5/31/2018
131233.87
6/30/2018
169729.51
7/31/2018
150653.33
8/31/2018
163001.73
9/30/2018
208074.11
10/31/2018
148101.68
11/30/2018
191886.68
12/31/2018
286427.48
1/31/2019
120291.57
2/28/2019
100956.73
3/31/2019
118690.4
4/30/2019
139805.65
5/31/2019
174809.54
6/30/2019
191214.1
7/31/2019
164521.19
8/31/2019
197536.4
9/30/2019
202215.11
10/31/2019
182863.6
11/30/2019
186582.52
12/31/2019
315102.7
1/31/2020
3/2/2020
3/31/2020
5/1/2020
5/31/2020
7/1/2020
7/31/2020
8/31/2020
10/1/2020
10/31...

joantutor (2707)
UT Austin

Anonymous
I was on a very tight deadline but thanks to Studypool I was able to deliver my assignment on time.

Anonymous
The tutor was pretty knowledgeable, efficient and polite. Great service!

Anonymous
I did not know how to approach this question, Studypool helped me a lot.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags