Exp19_excel_ch10_cap_auto_sales| exp19 excel ch10 cap auto sales

Exp19_Excel_Ch10_Cap_Auto_Sales| Exp19 Excel Ch10 Cap Auto Sales

Exp19_Excel_Ch10_Cap_Auto_Sales| Exp19 Excel Ch10 Cap Auto Sales

#Start   Excel. Download and open the file named Exp19_Excel_Ch10_Cap-AutoSales.xlsx. Grader has automatically added   your last name to the beginning of the filename.

#Use Get & Transform (Power   Query) to import the file e10c1Agents.csv.   
   Hint: Get Data is located on the Data tab in the Get & Transform Data   group.

Rename the worksheet Agents.
   Hint: Double click the worksheet tab to edit the sheet name.

#Use Get & Transform (Power   Query) to import the Vehicles table from the file e10c1Vehicles.accdb.
   Hint: Get Data is located on the Data tab in the Get & Transform Data   group.

Rename the worksheet Sales.
   Hint: Double click the worksheet tab to edit the sheet name.

#Load the file e10c1Customer_Leads.txt in the Power   Query Editor. Use the Power Query Editor to ensure the first row is used as   Headers. Close and load the table.
   Hint: To open the data in the Power Query Editor click Edit on the Import   window.

Rename the worksheet Customer_Leads.
   Hint: Double click the worksheet tab to edit the sheet name.
 

Add the Vehicles, e10c1Agents,   and e10c1Customer_Leads tables to the data model.
 

  Hint: Manage Data Model is located on the Data tab in the Data Tools group.

#Define the following   relationship:
   Table:
   e10c1Agents
   Field:
   AgentID
   Table:
   Vehicles
   Field:
   AgentID
   Hint: Relationships is located on the Data tab in the Data Tools group.

#Use Power Pivot to create a   PivotTable on Sheet1 starting in cell  B3. Add the Last field from the   e10c1Agents table to the Rows area and  Price from the Vehicles table to the   Values area.
 

  Hint: Power Pivot is an Excel add-in that must be loaded. Power  Pivot can be   loaded by accessing COM add-ins in Excel options. Once  loaded, the Power   Pivot tab is located on the Office ribbon.

Format the range C4:C20 with   Accounting Number Format.
 

  Hint: Accounting Number Format is located on the Home tab in the Number   group.

Insert a Clustered Column Pivot   Chart. Position the chart so the upper left corner is in the center of cell   D3.
 

  Hint: #PivotChart is located on the Analyze tab in the Tools group.

Add the Chart Title Sales.
   Hint: Chart elements can be added or edited on the Design tab in the Chart   Layouts group. 

Rename the worksheet SalesAnalysis.
   Hint: Double-click the worksheet tab to edit the sheet name.