Case Study 2 36 Years Gold Prices Q2 Solution

As you need to find out the correlation among five years of Gold prices, first group the data on annual basis and filter the data to depict values only of 2011 to 2015. Then calculate the correlation using Data Analysis Toolpak.

Place cursor in any cell where source data is located, then click Insert Tab on Menu bar and click PivotTable button. Create PivotTable dialogue box is displayed. Check whether the data is correctly selected and choose New Worksheet to place PivotTable report. Click OK.

Drag Date to Row Labels and US Dollar to Values. Click the list arrow on Sum of US Dollars. From the popup menu click Value Field Settings. Value Field Settings dialogue appears. From Summarize value field by options select Average, then click Number Format, Format Cells dialogue box is displayed, select number and click OK. Once again click OK.

Now place cursor on any cell on Row Labels, from Group Ribbon options click Group Selection tab, Grouping dialogue box appears on the screen. By default excel includes Starting at and Ending at dates. Move to By options. To group your data by Years and Months, click on Years and Months and click OK. Your grouping is done.

You can see a new filed Years is added to Row Labels. From Row Labels drag Years to Column Labels.

To shortlist the desired years click the list arrow on Column Labels, from drop down list, uncheck select all, and select years 2011 to 2015. Click OK.

Now click Data tab on Menu bar, and click Data Analysis, Data Analysis dialogue box appears, from Analysis Tools select Correlation and click OK. Select source data using Input Range, don’t forget to include labels. Click Labels in First Row option. Choose output range and click OK. Cross order correlation for the five years is calculated. You can see an inverse relationship in Gold Prices during the study period.