Question 7 a) Create the spreadsheet below using Excel. Include the rows and columns identifiers, text formatting and column highlighting as shown. [10 marks] A B C D E F UK Prison Population (2016 - 2020) National Number Number Number of Non- Year Total UK Prison Population Remanded Sentenced Criminal Prisoners Population 2016 65648100 9,288 74,316 1,530 83604 2017 66040200 9,638 74,803 1,422 84441 toUAW 2018 66435600 9,285 72,619 869 81904 2019 66796800 9,145 72,798 767 81943 2020 67081000 11,388 67,352 774 78740 b) What actions or steps in Excel can you take to order the spreadsheet by the number of prisoners remanded in ascending order? [4 marks] c) State the specific actions or steps in Excel that will produce a list/display of those years where the population of the UK is less than 66.7 million? [4 marks] d) Which type of graph will be suitable for representing the total number of recorded prisoners between 2016 and 2020? [1 mark] e) In which column might replication have been used? [1 mark] f) How many non-criminal prisoners have been recorded between 2016 and 2020? Write the formula you have used alongside the correct numerical answer.Question 8 a) What type of graph is suitable to show the correlation between the number of non-criminal prisoners and the number of remanded prisoners over the recorded five years? [1 mark] b) Create an appropriate graph to show the correlation between the total population of the United Kingdom and the total number of prisoners over the recorded five years. [5 marks] c) What type of correlation is present between the total population of the United Kingdom and the total number of prisoners over the recorded five years? [1 mark] [Total Marks for Question 8 = 7] Question 9 a) Calculate the median number of prisoners for each type of custody, stating the formula you would use for determining the median for the number of non-criminal prisoners. [5 marks] b) Calculate the average number of prisoners for each type of custody, stating the formula you would use for determining the mean for the number of remanded prisoners. [5 marks] c) Calculate the standard deviation of the total number of prisoners dataset (Column F) using Excel. Use the standard deviation range function, STDEV.P, to check your answer. [7 marks] d) Provide a definition for the standard deviation of any dataset. You must cite any sources used. [10 marks]Question 10 a) Write the Excel function that gives the total number of prisoners for the years in which the number of remanded prisoners was greater than 9150. [4 marks] b) Write the Excel function that gives the average number of sentenced prisoners for the years in which the number of non-criminal prisoners was greater than 1000. [4 marks] c) Write the Excel function that finds the sum of the total UK population for the years where the total number of remanded prisoners was less than 9150. [4 marks] d) Write the Excel function that searches the spreadsheet to find '2019' and also the corresponding total number of prisoners for that year. [6 marks] [Total Marks for Question 10 = 18] Question 11 a) Produce an appropriate fully labelled chart to compare the number of prisoners in each custody type between 2016 and 2020. [10 marks] b) Interpret the chart produced in 11a). Discuss TWO probable justifications for the behaviour of the data. [4 marks] c) Produce a suitable and fully labelled chart to reflect each custody type's proportion of the total amount of prisoners recorded in 2019.USING NUMERACY, DATA & IT - PART 2: TO BE COMPLETED BY PEOPLE AND SOCIETY PATHWAY STUDENTS ONLY [120 MARKS] This part covers Learning Outcomes 3 and 4 of the module. Given below is the total prisoner count in the United Kingdom prison system by type of custody. B C D E UK Prison Population (2016 - 2020) National Number Number Number of Non- Year Population Remanded Total UK Prison Sentenced Criminal Prisoners Population 2016 65648100 9,288 74,316 1,530 83604 2017 66040200 9,638 74,803 1,422 84441 2018 66435600 9,285 72.619 869 81904 2019 66796800 9,145 72,798 767 81943 2020 67081000 11,388 67,352 774 78740 Question 6 a) Which year had the lowest number of sentenced prisoners in those recorded five years? [1 mark] b) In which year did the UK Prison system have the least number of remanded prisoners over those five years? [1 mark] c) What is the median UK population between 2016 and 2020? [2 marks] d) What is the range between the number of remanded prisoners over the recorded five years? [3 marks] e) Which years did the number of sentenced prisoners rise above sixty times that of the number of non-criminal prisoners? [3 marks] f) By comparing the total number of UK prisoners and the total population of the UK, work out which year had the greatest number of prisoners per capita. Show your working to justify your answer. [4 marks]