#Exp22_Excel_Ch10_HOE – Commodities 1.1
#Exp22 Excel Ch10 HOE Commodities 1.1
#Excel Chapter 10 Hands-On Exercise – Commodities
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.
We are a professional custom writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework.
Yes. We have posted over our previous orders to display our experience. Since we have done this question before, we can also do it for you. To make sure we do it perfectly, please fill our Order Form. Filling the order form correctly will assist our team in referencing, specifications and future communication.
1. Click on the “Place order tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
2. Fill in your paper’s requirements in the "PAPER INFORMATION" section and click “PRICE CALCULATION” at the bottom to calculate your order price.
3. Fill in your paper’s academic level, deadline and the required number of pages from the drop-down menus.
4. Click “FINAL STEP” to enter your registration details and get an account with us for record keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
5. From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
Need help with this assignment?
Order it here claim 25% discount
Discount Code: SAVE25