excel 2019 grader dow jones and flight plan

complete by tommorrow

Exp19_Excel_Ch10_ML1_Dow_Jones_Instructions x
Grader – Instructions Excel 2019 Project

Exp19_Excel_Ch10_ML1_Dow_Jones

Project Description:

You are an intern for Hicks Financial, a small trading company located in Toledo, Ohio. Your intern supervisor wants you to create a report that details all trades made in February using current pricing information from the Dow Jones Index. To complete the task, you will import and shape data using Power Query. Then you will create data connections and visualizations of the data.

Steps to Perform:

Step

Instructions

Points Possible

1

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

0

2

Use Get & Transform Data tools (Power Query) to import the Dow Index information located in Table 2 from the URL https://finance.yahoo.com/quote/%5EDJI/components?p=%5EDJI. Before loading the data, use the Power Query Editor to remove the Change, % Change, and Volume columns. Format the Last Price column as Currency, name the query Dow and load the data into the existing worksheet.

15

3

Name the worksheet Current_Price.

5

4

Use Power Query to import trade data located in the workbook Exp19_Excel_Ch10_GRADER_ML1-TradeInfo.csv. Before loading the data, if necessary use the Power Query Editor to remove the NULL value columns and use the First Row As Headers. Split the company column using the left most space as the delimiter and rename the respective columns Symbol and Company Name.

15

5

Rename the worksheet Trades.

5

6

Add the Dow table and the Exp19_Excel_Ch10_GRADER_ML1-TradeInfo table to the Data Model.

0

7

Use Power Pivot to create the following relationship:
Table
Exp19_Excel_Ch10_GRADER_ML1-TradeInfo
Field
Symbol

Table
Dow
Field
Symbol

15

8

Use Power Pivot to create a PivotTable with the EXP19_Excel_Ch110_GRADER_ML1_TradeInfo Date field as a Filter, Last Price as a value, and the Dow table Company Name as Rows.

20

9

Create a Clustered Column PivotChart based on the PivotTable that compares the trading price of Apple and Coca-Cola stocks.

15

10

Add the chart title Trading Comparison, apply Accounting Number Format to cells C4:C5, and name the worksheet Price_Comparison.

5

11

Delete Sheet 1, if necessary.

5

12

Edit the connection properties to Refresh data when opening the file.

0

13

Save and close Exp19_Excel_Ch10_GRADER_ML1_Dow.xlsx and Exp19_Excel_Ch10_GRADER_ML1-TradeInfo.csv. Exit Excel. Submit the file as directed.

0

Total Points

100

Created On: 10/05/2020 1 Exp19_Excel_Ch10_ML1 – Dow Jones 1.3

Amy_Exp19_Excel_Ch10_GRADER_ML1-Dow.xlsx
Sheet1

1
2
3
4
5
6
7
8
9
A
B
C
D
E

Exp19_Excel_CH10_GRADER_ML1_Dow Jones_Final

Exp19_Excel_Ch10_GRADER_ML1-TradeInfo.csv
Date,Trade_#,Company,,,,,
2/27/2021,8280,CAT Caterpillar,,,,,
2/25/2021,3564,CVX Chevron,,,,,
2/25/2021,7709,BA Boeing,,,,,
2/11/2021,5494,BA Boeing,,,,,
2/7/2021,6555,HD Home Depot,,,,,
2/13/2021,8821,CSCO Cisco,,,,,
2/14/2021,4771,DIS Disney,,,,,
2/17/2021,9021,HD Home Depot,,,,,
2/28/2021,1342,BA Boeing,,,,,
2/6/2021,5092,AAPL Apple,,,,,
2/20/2021,9602,AAPL Apple,,,,,
2/16/2021,3952,GS Goldman Sachs,,,,,
2/20/2021,3432,MMM 3M,,,,,
2/10/2021,2953,KO Coca-Cola,,,,,
2/27/2021,9270,AXP American Express,,,,,

Exp19_Excel_Ch10_ML2_Flight_Plan_Instructions x
Grader – Instructions Excel 2019 Project

Exp19_Excel_Ch10_ML2_Flight_Plan

Project Description:

You are student at the Aviation Experts flight school in New York City and as part of your training, you are required to create a flight plan for a cross country trip. You have decided to import your destination information into Excel and use 3D Maps to create an animated tour of the cities you will fly over.

Steps to Perform:

Step

Instructions

Points Possible

1

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

0

2

Use the Get & Transform tools to import the text file Exp19_Excel_Ch10_GRADER_ML2-FlightPlan.

75

3

Rename the worksheet Flights.

25

4

Edit the connection properties to refresh when the file is opened.

0

5

Insert a 3D Map that uses Latitude and Longitude as Location dimensions.

0

6

Zoom in until the New York City fills the screen. Rename Scene1 as New York, set the Scene duration to 4 seconds, and the effect to Dolly.

0

7

Add a new Scene named Chicago and reposition the map so Chicago fills the screen.

0

8

Add a new Scene named Denver and reposition the map so Denver fills the screen.

0

9

Add a new Scene named Los Angeles and reposition the map so Los Angeles fills the screen.

0

10

Save and close Exp19_Excel_Ch10_GRADER_ML2_FlightPlan.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100

Created On: 10/15/2019 1 Exp19_Excel_Ch10_ML2 – Flight Plan 1.0

Amy_Exp19_Excel_Ch10_GRADER_ML2_FlightPlan.xlsx
Sheet1

Exp19_Excel_CH10_GRADER_ML2_Flight Plan_Final

Exp19_Excel_Ch10_GRADER_ML2-FlightPlan.txt
Flight City State Latitude Longitude
1 New York NY 40.73061 -73.935242
2 Chicago IL 41.881832 -87.623177
3 Denver CO 39.742043 -104.991531
4 Los Angeles CA 34.052235 -118.243683