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,

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