Exp22_excel_ch10_hoe – commodities 1.1 | Computer Science homework help

#Exp22_Excel_Ch10_HOE – Commodities 1.1ÿ
#Exp22 Excel Ch10 HOE Commodities 1.1ÿ
#Excel Chapter 10 Hands-On Exercise – Commodities
#Exp22_Excel_Ch10_HOE_Commodities
ÿÿ
Project Description:
You are a financial analyst for a brokerage firm. Your manager wants you to analyze commodity sales patterns of the top five brokers for the first quarter. Unfortunately, the data required to complete the analysis are distributed among several key data sources. You received basic broker information through an email and transaction information from an Access database, and you will need to retrieve real-time NASDAQ trading information from the Web. You do not want to simply copy and paste the data into the worksheet; you want to connect and transform data in Excel so that the constantly changing values are always up to date. You also want to create data visualizations to provide geospatial information and a business dashboard.
ÿÿÿÿÿ
Start Excel. Download and open ÿÿthe file named Exp22_Excel_Ch10_HOE_Commodities.xlsx. ÿÿGrader has automatically added your last name to the beginning of the ÿÿfilename.
ÿ
You have a list of client ÿÿinformation stored as a CSV file. You want to use Get & Transform Data ÿÿ(Power Query) to import the file, so the information will update as new clients ÿÿare added.ÿ
ÿÿUse Get & Transform (Power Query) to import the e10h1Client_Info.csv. Load the data. Rename the newly created ÿÿworksheet Clients.
ÿ
All commodity transactions are ÿÿstored in an Access database. You will use the Get & Transform tools to ÿÿimport this data while maintaining a connection to the database. You want to ÿÿuse the Power Query Editor to shape the data.ÿ
ÿÿUse Get & Transform (Power Query) to import the transactions table from ÿÿthe e10h1Transactions.accdb ÿÿdatabase. Load the data to a new worksheet.
ÿ
The database table you imported ÿÿcontains data that was incorrectly formatted. You will use the Power Query ÿÿEditor to reformat the data. In addition, a coworker created a list of broker ÿÿcontact information as a tab-delimited file in Notepad. You will use the ÿÿPower Query Editor to shape the data by splitting the columns and providing ÿÿunique data labels before importing it to Excel.ÿ
ÿÿUse the Power Query Editor to change the data type of the date field in the ÿÿTransaction query from Date/Time to Date format and the Purchase_Price and ÿÿSelling_Price fields to Currency.ÿ
ÿÿUse Get & Transform (Power Query) to load the e10h1Broker_Info.txt file in the Power Query Editor. Use Tab as ÿÿthe delimiter. Split the Name column using Space as the delimiter splitting ÿÿthe column at the Left-most occurrence. Rename the newly split columns Name.1 ÿÿand Name.2 First and Last respectively.ÿ
ÿ
Split the City, State column ÿÿusing Comma as the delimiter. Use Power Query to Trim the excess space off ÿÿthe newly created City, State.2 column. Split the City, State.2 column using ÿÿSpace as the delimiter. Then click or press the X located to the left of the ÿÿstep Changed Type3 in the Applied ÿÿSteps box located in the Query Settings pane.ÿ
ÿÿRename the City, State.1 column City, the City, State.2.1 column State, and the City, State.2.2 column Zip Code. Close & Load the ÿÿtransformed data and then rename the worksheet Brokers.ÿ
ÿ
Transactional information for ÿÿQuarter 4 sales are stored in a separate Access database. You will use Power ÿÿQuery to import the Quarter 4 data as a query and then append the ÿÿinformation, from the existing Transactions table.ÿ
ÿÿUse Get & Transform to load the 2024_Q4_Transactions table from the e10h1Q4_Append.accdb database in the ÿÿPower Query Editor. Then append the existing Transactions query with the data ÿÿfrom the 2024_Q4_Transactions query.ÿ
ÿÿUse the Power Query Editor to change the data type of the Date field to Date. ÿÿClose and load the appended query.
ÿ
You want to finalize the data in ÿÿyour report so it will not update if the external source is modified. You ÿÿwill ensure the external connection properties are set to not refresh the ÿÿdata when the file is opened. This change will ensure that when your report ÿÿis distributed there will be consistent data with no external connection ÿÿerrors.ÿ
ÿÿEdit the connection properties for each of the external connections so ÿÿbackground refresh and refresh the connection on refresh all are disabled.ÿ
ÿ
You have decided to enhance your ÿÿreport by using Power Pivot to create a PivotTable and PivotChart. You will ÿÿfirst enable the Power Pivot add-in, add the existing data to a data model, ÿÿand then create relationships. You want to use Power Pivot to analyze the ÿÿdata that was imported using Get & Transform. Because the data has ÿÿalready been imported, you will add the existing data to a data model.ÿ
ÿÿAdd the transaction information located in the range A1:I76 on the ÿÿTransactions worksheet to the data model.ÿ
ÿÿAdd the broker information located in the range A1:G6 on the Brokers ÿÿworksheet to the data model.ÿ
ÿÿAdd the client information located in the range A1:F19 on the Clients ÿÿworksheet to the data model.
ÿ
After adding all imported data ÿÿto the data model, you will define the relationships between the transactions ÿÿdatabase table, the broker information, and client information.ÿ
ÿÿCreate a relationship between the Transactions table Account field and ÿÿe10h1Client_Info Account field.ÿ
ÿÿCreate a relationship between the Transactions table Broker_ID field and ÿÿe10h1Broker_Info Broker_ID.
ÿ
You want to summarize the sales ÿÿof each agent in a PivotTable based on commodity. As your last step, you will ÿÿuse the relational data in the data model to create a PivotTable and ÿÿPivotChart.ÿ
ÿÿCreate a PivotTable starting in cell A1 based on the existing data model in ÿÿPower Pivot. Add the Last field to the Rows box, Date field to the Filter ÿÿbox, and Commodity to the Columns box.ÿ
ÿÿAdd the Selling_Price field to the Values box and apply Accounting Number ÿÿformat to the range B5:E10.ÿ
ÿÿAdd a Clustered Column PivotChart based on the PivotTable. Position the chart ÿÿso the upper right corner is in cell F3. Add the chart title Sales Data. Ensure the chart title appears ÿÿat the top of the chart.ÿ
ÿÿRename the worksheet Sales_Analysis.
ÿ
You want to create a 3D Map tour ÿÿthat displays your current client locations. You want to visualize locations ÿÿand client salaries.ÿ
ÿÿCreate a 3D map using the City field from the e10h1Client_Info file as the location. Add Earnings as the Height ÿÿdimension and the Account field as the Category dimension.ÿ
ÿÿRemove the legend and save the 3D map.
ÿ
After adding the dimensional ÿÿvisualizations to the 3D Map, you want to create a tour to better view the ÿÿdata from different angles.ÿ
ÿÿCenter the map so the United States is in the center of the map area for ÿÿscene 1. Add a new scene that repositions the map to show the Eastern ÿÿseaboard. Edit scene 1 to use the Fly Over effect for a duration of 4 ÿÿseconds. Edit scene 2 to use the Push In effect.ÿ
ÿ
Save and close Exp22_Excel_Ch10_HOE_Commodities.xlsx. ÿÿExit Excel. Submit the file as directed.