Assignment: Using and Managing Data and Information - Calculations

This assignment provides a comprehensive analysis of data and information management, covering various aspects such as calculations, financial analysis, and the application of Excel functions. The document begins with basic calculations involving arithmetic operations, percentages, and fractions. It then delves into financial concepts, including compound interest, annuity calculations, and loan repayments. Furthermore, the assignment explores the estimation of break-even points and the use of Excel functions like COUNTIF and IF statements to analyze employee data, including leave days and training completion. The document presents step-by-step solutions, formulas, and examples to illustrate each concept, providing a practical guide for understanding and applying data management principles in a business context. This is a valuable resource for students studying data and information management.

Using and Managing Data
and Information

Table of Contents
TASK 1............................................................................................................................................1
Calculations.................................................................................................................................1
a. 21.2-16.8*0.67.........................................................................................................................1
b. 5.48 + 3.8 * 1.9 / √27..............................................................................................................1
c. (369 – 173 / 4) ÷ 68 / √83........................................................................................................1
d. (2864 – (143 x 36)/32 ) ÷ √2568 − 121/4 x √630 − 5(89)......................................................1
e. (34.78 – (5.63)^2 ) ÷ ( √365) / 5^2..........................................................................................1
TASK 2............................................................................................................................................2
a. Express 46.7% as a fraction in its simplest form....................................................................2
b. Express 0.01896 as a percentage ............................................................................................2
c. 0.7777 as a fraction in its simplest form.................................................................................2
d. Adam score..............................................................................................................................2
e. Percentage change of sales......................................................................................................3
TASK 3............................................................................................................................................3
a. Calculating the total amount earned........................................................................................3
b. Calculating the annual premiums paid at end of each year.....................................................3
c. Calculating the total amount earned after 5 years for regular monthly cash investment........4
d. Presenting the monthly repayments........................................................................................4
e. Single amount that need to be save for 2026...........................................................................4
TASK 4............................................................................................................................................5
Estimating the break even point..................................................................................................5
Computation of BEP...................................................................................................................5
TASK 5 ...........................................................................................................................................6
a. Function use to return the number of employees who have taken 10 or more days...............6
b. Function use to return the number of employees who have completed less than 2 training
days.............................................................................................................................................7
c. If function statement................................................................................................................7

TASK 1
Calculations
a. 21.2-16.8*0.67
As per the BODMAS rules, first solve multiplication values and then subtraction
So, 21.2-11.256
= 9.94
b. 5.48 + 3.8 * 1.9 / √27
As per the BODMAS rule, first solve Division value and then multiplication and addition
So, 5.48 + 3.8 * 1.9/5.19
= 5.48 + 3.8 * 0.366
= 5.48 + 1.39
= 6.87
c. (369 – 173 / 4) ÷ 68 / √83
As per the BODMAS rule, solve bracket and then division and then subtraction
= (369 – 43.25)÷ 68 / 9.11
= 325.75 ÷ 7.46
= 43.66
d. (2864 – (143 x 36)/32 ) ÷ √2568 − 121/4 x √630 − 5(89)
As per the BODMAS rule, bracket is solved first, then division, then solve the value of
multiplication in order to get the answer.
= (2864 – (5148) / 32) ÷ 2*10.34*6 - 121/4 x √630 – 445
= (2864 – 160.875) ÷ 2*10.34*6 – 30.25 * √185
= 2847.91 ÷ 124.08 – 30.25 * 13.60
= 22.95 - 411.4
= - 388.45
e. (34.78 – (5.63)^2 ) ÷ ( √365) / 5^2
As per the BODMAS rule, there is a need to solve the value of Bracket and then division,
so as per these
= (34.78 – 31.69) ÷ (19.10 / 25)
1

= 3.09 ÷ 0.764
= 4.044
TASK 2
a. Express 46.7% as a fraction in its simplest form
Step 1: Change the percentage by dividing the value to 100
= 46.7 / 100
Step 2: Remove the decimal
= 467/1000
Step 3: Solve the number in its simplest form
As 647 is a prime number which in not divided in any number so answer is 467/1000.
b. Express 0.01896 as a percentage
Percent means per 100 so steps are as mention below:
Step 1: Multiply by 0.01896 by 100/100, but the answer is 1, so only multiplying by \
= 0.01896/1 * 100/100 = 1.896/100
Step 2: value in percentages
= 1.896/100
= 1.896%
c. 0.7777 as a fraction in its simplest form
Step 1: First, write down the value as a fraction form
= 0.7777/1
Step 2: Multiply the top and bottom by 10 for every number after the decimal point,
= 0.7777 *10000/1 * 10000
= 7777/10000
As 7777 is a prime number which is already reduced form.
d. Adam score
The number achieved during examination is 51 and the sum of all subjects during
examination is 63. so to get the percentage, divide the number attain by total number and then
multiply the answer with 100.
= 51 / 63 * 100
= 80.9%
2

e. Percentage change of sales
Step 1: determine the difference between the sales i.e.
= 189 – 166
= 23
Step 2: Average of the two year's sales
= 189+166
= 355
Step 3: Divide the difference with average number
= 23/355
= 0.06
Step 4: To get the Value in percentage, multiple the value by 100
= 0.06*100
=6
As the percentage change value is in positive value, then it shows that the sales is
increase, which is beneficial for the company.
TASK 3
a. Calculating the total amount earned
Principal 20000
Interest rate 5%
Maturity 5 years
Formula P(1+i)^n
20000*((1+0.05)^5)
Amount earned 25525.63
b. Calculating the annual premiums paid at end of each year
Loan amount 300000
Annuity Payment P=[( r * PV/1-(1+r)^n]
3

[(300000 * 0.035) / 1 - (1.035)^5]
66445
OR
Annuity Factor (1+i)^n
4.515
Annual premiums A / (1+I)^n
300000/4.5150
Annual premiums 66445.18272
c. Calculating the total amount earned after 5 years for regular monthly cash investment
P 800
r 2.25 / 12 = 0.280333
n 5*12 = 60
Future value of annuity P[(1+r)^n-1/r]
FV 800*[(1+0.0020833)^60 - 1/0.0028333]
Monthly Installment 51067.31
d. Presenting the monthly repayments
Loan (P) 45000
Interest 6 /12 =0.5%
PV of annuity P=[( r * PV/1-(1+r)^n]
[(45000 * 0.005) / 1 - (1.005)^60]
e. Single amount that need to be save for 2026
4

Future Value 50000
Interest rate 4%
FV FV/(1+r)^n
50000/(1.04)^6
42740.21
TASK 4
Estimating the break even point
Particulars Formula Figures
(in £)
Fixed cost 5200
Selling price per coffee 4.5
Average cost per coffee 1.25
Contribution per coffee SPU - Variable cost per
unit
4.5 – 1.25
=3.25
BEP (in units) Fixed cost / contribution
per unit
5200 /
3.25=
1600
BEP (in £) BEP in units * SPU 7200
Computation of BEP
Sales units Sales revenues @ 4.5 Cost
Contributio
n margin
Break Even
(amount)
0 0 0
200 900 250 27.78% 18720
400 1800 500 27.78% 18720
600 2700 750 27.78% 18720
800 3600 1000 27.78% 18720
1000 4500 1250 27.78% 18720
5

1200 5400 1500 27.78% 18720
1400 6300 1750 27.78% 18720
1600 7200 2000 27.78% 18720
1800 8100 2250 27.78% 18720
2000 9000 2500 27.78% 18720
TASK 5
a. Function use to return the number of employees who have taken 10 or more days
To present the answer, Count if function is used, in which there are 12 employees who
taken 10 or more holidays
Employee Holidays taken (days)
1 6
2 10
3 12
4 7
5 8
6 11
7 9
8 6
9 8
10 11
11 13
12 8
13 12
14 6
15 13
16 8
17 10
6

18 11
19 8
20 9
21 7
22 12
23 11
24 6
25 14
Employees with leave > 10
10
Employees with leave = 10
2
b. Function use to return the number of employees who have completed less than 2 training days
To get the answer, Count If statement is used in which there are 7 employees who has
completed less than 2 training days.
Employee Training completed (days)
1 4
2 2
3 1
4 1
5 3
6 2
7 4
8 3
9 1
10 2
7

11 1
12 4
13 3
14 1
15 2
16 2
17 3
18 4
19 2
20 2
21 4
22 1
23 2
24 1
25 3
Employees completed < 2 training
days
c. If function statement
Employee
Holidays
taken
(days)
Training
completed
(days)
Leave >10
days &
Training
<3 days
1 6 4 No
2 10 2 No
3 12 1 yes
4 7 1 No
5 8 3 No
6 11 2 yes
7 9 4 No
8

8 6 3 No
9 8 1 No
10 11 2 yes
11 13 1 yes
12 8 4 No
13 12 3 No
14 6 1 No
15 13 2 yes
16 8 2 No
17 10 3 No
18 11 4 No
19 8 2 No
20 9 2 No
21 7 4 No
22 12 1 yes
23 11 2 yes
24 6 1 No
25 14 3 No
9