CO1098 – Information Management

15 Nov

CO1098 – Information Management Practical 6
1 | P a g e
Practical 6
Excel 2013 – Assessment 1
This Practical is part of the continuous assessment and is worth 30% of the module mark for
CO1098, and as such has to be an individual piece of work. The document file produced in this
exercise must be handed in via Blackboard by Friday, 25th November, 6pm. Failure to do so on
time will result in marks being deduced in line with University policy (see Study Guide for
You can obtain 100 marks in total: 20 marks for Part 1 (for importing and editing the data set), and 80 marks for Part 2 (40
for calculations, 20 for summary statistics, 20 for charts).
Please submit the finished document on Blackboard. In addition, as per departmental policy for
assessed coursework, you will need to submit the Cover Sheet for Coursework. Please download
the file coversheet_practical6.doc, fill in the data required (we will accept your name typed in the
Signature line as your electronic signature) and submit it on Blackboard with your assignment.
Make sure you download Practical6data.txt into your folder before you start.
Looking at vehicle related crime data (Data by the Home Office).
Part 1: Importing and Editing
1. Importing a text file into Excel.
a. Open a new workbook in Excel.
b. Select cell A1
c. In the Data Tab, Get External Data group, select From Text
d. Select the file Practical6data.txt and click Import
e. The Text Import Wizard will help you through the process:
i. In Step 1 of 3 make sure you select Delimited
before moving on to the next step.
ii. In Step 2 of 3 choose Tab under Delimiters
CO1098 – Information Management Practical 6
2 | P a g e
iii. In Step 3 of 3 click Finish
iv. In the Import Data dialogue simply click OK to import into the existing
f. Edit the spreadsheet:
i. Insert four rows at the top of the worksheet (pushing all the data imported
ii. In A1 provide Your_Name and in A2 provide your username
iii. Provide the following titles for each data column in row 4: ‘Year’, ‘Force’,
‘Aggregated vehicle taking’, ‘Theft from vehicle’, ‘Theft of vehicle’, and
‘Vehicle interference’.
iv. We will be looking at the data from 2010/11, so delete all the other data
v. Rename the Worksheet tab from Sheet1 to ‘Crime Data’.
g. Save the Workbook as Crime.xlsx into your folder (and remember to save regularly
Part 2: Calculations and Charts
2. Calculations
a. Calculating totals
i. Provide a new title in H4, namely ‘All vehicle offences’
ii. Calculate the total of the first four columns in column H
b. Ratios
i. Provide the following titles: in I4 ‘% Aggregated vehicle taking’, in J4
‘%Theft from vehicle’, in K4 ‘%Theft of vehicle’, and in L4 ‘%Vehicle
ii. In columns I, J, K and L calculate what percentage of All vehicle offences
the category of crime represents, i.e. for column I calculate the percentage of
Aggregated vehicle taking represents of All vehicle offences for that force,
etc. – format those results as percentage to 2 decimal places
iii. In G1 input ‘Total’ and then in H1 calculated the total number of all the
vehicle offences in all the forces.
iv. In M4 input ‘Percentage of total’, and in that column using relative and
absolute references calculate what percentage all vehicle offences for each
force contribute in relation to the Total
CO1098 – Information Management Practical 6
3 | P a g e
3. Summary statistics
a. Using functions calculate the Average (in P5), and find the minimum (in P6) and
maximum values (in P7) for Aggregated vehicle taking (column C)
b. Repeat for Theft from vehicle (column D), placing the values in equivalent cells in
column Q, Theft of vehicle (column E) in column R, and Vehicle interference
(column F) in column S
c. Provide meaningful titles for these calculations in column O and row 4.
4. Charts
a. Display averages calculated in step 3. above in a Pie Chart, providing labels and title
(place towards top of current worksheet).
e. Provide a Clustered Bar Chart for the Percentage of total calculations (Step 2.b.iv
above), again providing labels and title.
Save your work, and once you have completed the practical hand in your work via
BlackBoard. Remember the coversheet.

Leave a comment

Posted by on November 15, 2016 in academic writing, Uncategorized


Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: