Question 1
If you haven't done so already, please read the instructions for
this assignment first and download the spreadsheet. You will
need to work with the spreadsheet in order to answer the
questions below. For your convenience, you can also download it
here:
Here is the first question:
The spreadsheet contains data downloaded from a government
database. It is not very easy to read, so your first job is to
address some of the formatting.
The heading in cell A1 cannot be seen properly. Change the
alignment so that you can see what it says.
In which year was this data collected?
Your Answer
Question 2
Somehow the date in cell B2 has lost its formatting. Change the
format to a date. What date is now shown in B2? Use Year-Month-
Day format (like 2016-12-31).
Your Answer
Question 3
Apply the style Accent1 to the range A2:Z2. Apply the style
Heading 3 to the range A4:Z4. Which of the following looks most
like your data?
This:
This:
This:
This:
Your Answer
Question 4
There are also some corrections you need to make to the data.
One of the data entries is missing. You have been asked to Insert
a row after Case ID 49 (row 15) and enter the following data:
Case ID 51
Economic Position Full-time working
Occupation Type Managerial or Professional
Home Owned
Gender Male
Region Wales
Adults 2
Children 2
Jan Income 5924.00
Feb Income 5924.00
Mar Income 5924.00
Apr Income 5924.00
May Income 5924.00
Jun Income 5924.00
Jan Expenditure 2803.57
Feb Expenditure 2242.86
Mar Expenditure 2512.00
Apr Expenditure 2361.28
May Expenditure 2219.60
Jun Expenditure 2596.93
What is the total March Expenditure now? (cell Q2)
Your Answer
Question 5
An incorrect value has been entered for Case ID 5299. Use the
Find tool to find this Case ID and change the January Income to
$200. What is the total January Income now? (cell I2)
Your Answer
Question 6
There are several calculations missing which need to be added.
An additional column showing the total number of people per
household is required. Perform all the following steps and then
answer the question.
1. Insert a column after column H
2. In I4 type the heading Household
3. In I5 enter a calculation to add the number of adults in G5 to
the number of children in H5
4. Copy the formula down to fill the column
5. In cell I2, enter a calculation to get the total number of
people in all the households represented, or copy the
formula across from cell H2
What is the total Household value in cell I2?
Your Answer
Question 7
In cell V5 enter a calculation to get an average of income from
January to June (J5 to O5). Copy the formula down the column.
What is the Average Income for Case 15 (cell V8)?
Your Answer
Question 8
In cell W5 enter a calculation to add up the total income from
January to June. Copy the formula down the column. Widen the
column so that you can see the results. What is the total income
for Case 9 (cell W6)?
Your Answer
Question 9
In cell X5 enter a calculation to add up the total expenditure from
January to June (P5 to U5). Copy the formula down the column.
What is the total expenditure for Case 15?
Your Answer
Question 10
In cell Y5 enter a calculation to subtract Total Expenditure from
Total Income. Copy the formula down the column. What is the Net
for Case 15?
Your Answer
Question 11
Cost of living has been estimated at going up by 3.93% over the
next 6 months. We would like to forecast what the expenditure
will be over that period. In cell Z2 enter the value 3.93%. In Z5
enter a formula to calculate the forecast expenditure. To do this
you will need to calculate the increase in expenditure (current
total expenditure multiplied by the percentage increase) and add
it to the current total expenditure. Copy the formula down the
column. (Make sure that all the calculations are using the value in
Z2!).
What is the Forecast Expenditure for Case 20?
Your Answer
Question 12
Now select the Stats Worksheet. Enter simple formulas in B3 and
B4 to pull through the calculated Total Expenditure and Total Net
from the Data worksheet (cells X2 and Y2). If you have done it
correctly the pie chart should now show how income is
proportioned between expenditure and net.
According to the pie chart, what percentage of Income is made up
by Net? (Do not enter the % symbol in the answer box below, just
the number.)
Your Answer
Question 13
Still on the Stats sheet, enter a formula in B5 to add up the total
income for Quarter 1 using the calculated totals for January,
February and March in the Data worksheet. If you have done it
correctly the cell should change colour. What colour is the cell B5?
White
Black
Purple
Grey
Yellow
Your Answer
Question 14
The organisation has decided to have one Region for the Midlands
instead of two, so both East Midlands and West Midlands need to
be replaced with just Midlands. We then need to answer some
questions for the organisation.
In the Data worksheet, use Find and Replace to replace all
instances of East Midlands with Midlands. Repeat the operation,
this time replacing West Midlands with Midlands. Now filter the
data so that only Cases from the Midlands are visible.
What is the total number of Children recorded for the Midlands?
Your Answer
Question 15
Clear the previous filter. Add filters so that we only see cases for
Wales with 6 or more people in the household. How many
households in Wales have 6 or more people?
Your Answer
Question 16
Clear all filters. Sort the data by Total Income in descending order
(largest to smallest). What is the highest Total Income for a case?
Your Answer
Question 17
Change the sort to order the data so that you can easily identify
the lowest Average Income for Cases with an Intermediate
occupation. What is the lowest Average Income for people with an
Intermediate occupation?
Your Answer
Question 18
You are concerned there may be duplicates in the data set. Add
conditional formatting to the Case ID column to show all
duplicates in red. Sort the data by Case ID but instead of by
values, sort by colour. How many cases have been duplicated
(entered twice)?
Your Answer
Question 19
Delete one of each of the duplicate rows. What is the new total in
H2?
Your Answer
Question 20
To help represent the data graphically you have been asked to
create a few charts. You will need to go back to the Stats
worksheet.
Select the data from A8 to B12. Insert a Pie Chart to compare the
Average Incomes for different Economic Positions. Add a quick
layout that shows a percentage for each segment.
What is the percentage for Part-time working? Do not enter the %
symbol in the answer box below, just the number.
Your Answer
Question 21
Create a line chart showing the Total Income for each Month.
Ensure you select month names and Total Income values. Which
of these charts looks most like your line chart?
This:
This:
This:
Your Answer
Question 22
Insert a Stacked Column Chart to show the Jan, Feb and Mar
income for each Region. Which region has the second lowest
income for Jan-Mar (second smallest stack)?
Your Answer