Using and Managing Data and Information

1
Using and Managing Data and Information
BA3020QA
Assignment 1 (July cohort 2019-20)
Note to students: There are FIVE tasks and you are expected to answer all of them.
Answer each task on a separate worksheet of the same Excel file.
The deadline for this assignment is Monday 21st September 2020 before 3pm. The
submission is via turnitin.
Feedback on the students’ work is expected within 15 working days of submission.
Use Excel as a calculator to evaluate the following calculations. For each calculation you
should provide the formula you have used.
a. 61.2 – 37.8 x 0.33
b. 5.48 + 8.8 x 1.9 /√87
c. (339 – 173 / 4) ÷ 48 / √63
d. (864 – (143 ÷ 36)/33
) ÷ √3768 − 121/3 x √630 − 9(61)
e. (478 – (13)2
)
105 x ( √1081)
Use the Excel formatting facilities (show formulae) to answer the following questions:
a. Express 7.07% as a fraction in its simplest form
b. Express 0.0436 as a percentage (2 decimal places)
c. Express 0.688 as a fraction in its simplest form
d. Tony scored 48/70 in an examination. What percentage did he achieve (1 decimal
place)?
e. The following table shows sales for a company in the last two years:
Year 2018 2019
Sales (£000) 234 188
What is the percentage change of sales for the company (2 decimal places)?
2
Use Excel financial facilities to perform the following scenarios:
You need to show the financial formulae you have used in each case.
a. Calculate the total amount earned in 5 years for saving a single amount of £10,000 in
a bank account that pays 3.5% per year interest (annual compounding).
b. Calculate the annual premiums paid at the end of each year for a 7-year loan of
£200,000 that costs 2.5% per year.
c. Calculate the total amount earned after 5 years for regular monthly cash investments
of £200 paid into an interest account at the beginning of each month and pays 1.25%
interest per year.
d. Calculate the monthly repayments (payable at the end of each month) for a period of
5-year for a loan of £30,000 costing 4.5%.
e. What is the single amount you need to save now (2020) to raise £25,000 by the year
2026 if the savings rate is 3%?
Use an Excel worksheet to setup a table of values and equations for the following scenario:
Scenario
You own a coffee shop in Holborn. The management cost of the shop is fixed at £6,000 per
month. In addition, the owners have calculated that the average cost of coffee is £1.70. These
are assumed to be the only costs.
Coffees are sold for an average price of £4.20.
Requirement:
Estimate the break-even point by producing a table of values for sales ranging from 0 (zero)
to 3,000 coffees increasing by 300.
You need to create a spreadsheet containing the information above and a table of values.
3
You are given the following employees’ information after their first six months at work. The
data is shown as entered on an Excel spreadsheet. The grey row and column are those of an
A B C
1 Employee Holidays taken
(days)
Training completed
(days)
2 1 10 2
3 2 6 1
4 3 7 3
5 4 12 2
6 5 11 1
7 6 8 2
8 7 8 4
9 8 6 3
10 9 9 2
11 10 8 1
12 11 11 3
13 12 13 2
14 13 6 2
15 14 7 1
16 15 12 1
17 16 10 2
18 17 11 3
19 18 9 2
20 19 7 2
21 20 14 1
22 21 7 4
23 22 11 1
24 23 6 2
25 24 12 4
26 25 8 1
a. Write down a function that will return the number of employees who have taken 10 or
more days as holiday. (1.5 marks)
b. Write down a function that will return the number of employees who have completed
less than 2 training days. (1.5 marks)
c. Write down a logical function with an “IF” statement to test whether an employee has
taken 10 or more days as leave and completed less than 3 training days. You should
use “YES” if the employee has taken 10 or more days as leave and completed less
than 3 training days. (2 marks)
Note: The functions you return in questions a. to c. should be written using relative
references to the spreadsheet rows and columns above.