Reference no: EM133112735
BA3005 Using and Managing Data and Information - London Metropolitan University
Task 1
Use Excel to perform the following calculations.
For each case you should present the calculations and explain how you would use Excel as a basic calculator to perform the calculations and show the formulae you have used.
(1 decimal place)
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)
Task 2: Use Excel financial formulae to perform the following scenarios. For each scenario, you need to present the problem and explain how you would use Excel to solve it. You should also show the appropriate formula you have used.
a. Calculate the total amount accrued after 4 years for saving a single amount of £8,000 in a bank account that pays interest of 2% per year. Assume that interest is calculated at the beginning of every year.
b. Calculate the monthly repayments for a 10 year loan of £25,000 with 3% interest per year. Repayments are made at the beginning of each month.
c. Calculate the total amount accrued after 5 years for regular monthly cash investments of £100 paid into a savings account at the beginning of each month and pays 3.75% interest per year.
d. Calculate the monthly repayments (payable at the end of each month) for a period of 30 years, for a mortgage of £270,000 at a fixed interest rate of 2.8% per year.
e. What is the single amount you need to save today so that you will have £10,000 in 5 years, if the saving rate is 3.5%? Assume that interest is applied to the account at the end of every year.
Task 3: Use an Excel worksheet to setup one table to compare the UK Government spending for the two years. Calculate the amounts for each department (in £bn) for the following scenario:
Scenario
Total UK Government spending for the financial year 2021 was £905 bn. The sum is split between each of the departments as follows:
Pensions 18.5%
Healthcare 19.6%
Education 10.4%
Others 51.5%
Total UK Government spending for the financial year 2022 was estimated to be £1,062 bn. The sum is split between each of the departments as follows:
Pensions 15.9%
Healthcare 20.1%
Education 9.4%
Others 54.6%
By comparing the UK Government spending (£bn) in education, work out the percentage change for 2022 (show formula)
Task 4:
You are given the following employees' information after their first six months at work. Enter the data as shown on an Excel spreadsheet and complete the following table:
|
Employee
|
Holidays taken (days)
|
Training completed (days)
|
Logical Function (Insert Formula- part c)
|
|
A
|
3
|
2
|
|
|
B
|
11
|
1
|
|
|
C
|
9
|
0
|
|
|
D
|
7
|
3
|
|
|
E
|
4
|
2
|
|
|
F
|
7
|
2
|
|
|
G
|
9
|
3
|
|
|
H
|
10
|
0
|
|
|
I
|
8
|
1
|
|
|
J
|
6
|
2
|
|
|
Number of Employees
|
(Insert formula- part a)
|
(Insert formula- part b)
|
|
Number of Employees (Insert formula- part a) (Insert formula- part b)
a. Write down a function that will return the number of employees who have taken more than 8 days as holiday. (use COUNTIF function)
b. Write down a function that will return the number of employees who have completed less than 2 training days. (use COUNTIF function)
c. Write down a logical function with an "IF" statement to test whether an employee has taken more than 8 days as holiday and completed less than 2 training days. You should show "YES" if the employee has taken more than 8 days as holiday and completed less than 2 training days.