Using the data and instructions provided below:-

i) Create a spreadsheet that calculates the correlation between the share price and profit. When done, explain what the correlation means in terms of the relationship between profit and share price.

ii) Then, calculate a forecast share price if the profit is $12.3m You must please attach a copy of the excel spreadsheet you used.

Jan- Profit 16, Share Price 12.3

Feb- Profit 14, Share Price12.7

Mar- Profit 15, Share Price12.1

Apr- Profit 12, Share Price11.9

May- Profit 12, Share Price10

Jun- Profit 14, Share Price13

Jul- Profit 11, Share Price9.7

Aug- Profit 11.3, Share Price11

Sep- Profit 12, Share Price10.1

Oct- Profit 10.9, Share Price9.7

Nov- Profit 10.5, Share Price9.7

Dec- Profit 11, Share Price9.9

i): Correlation result is:

This means that:

ii): The forecast share price at a profit of $12.3m is

Input the data above into an Excel spreadsheet and then follow these instructions: -

i)'Correlation' calculation instructions: -

The format for the 'correlation' function in .xls (Excel) is:

=CORREL(array1, array2)

Where 'array1' is the first column of data and 'array2' is the second column of data In Excel, your formula should look like this:

=CORREL(B2:B13, C2:C13)

ii)'Forecast' calculation instructions: -

The format for the 'forecast' function in .xls (Excel) is:

=FORECAST(known data value, array1,array2) In Excel, your formula should look like this:

=FORECAST(12.3, B2:B13,C2:C13)