Module Code: BA3020QA

Module Subject: Using and Managing Data and Information

Answer each task on a separate worksheet of the same Excel file.

Task 1 (5 marks)

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) 61.48 + 8.8 x 1.9 /√87

c) (339 – 173 / 4) ÷ 48 / √63

d) (864 – (143 ÷ 36)/3³) ÷ √3768 − 121/3 x √630 − 9(61)

e) (478 – (13)²) /105 x ( √1081)

 

Task 2 (5 marks)

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)?

 

Task 3 (10 marks)

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%?

Task 4 (10 marks)

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.

 

Task 5 (5 marks)

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 Excel spreadsheet.

	A	B	C
1	Employee	Holidays taken	Training completed
		(days)	(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.

 

“You can order BA3020QA : Using and Managing Data and Information Assignment from our service and can get a completely high-quality custom paper. Our service offers any of the BA3020QA Academic paper written from scratch, according to all customers’ requirements, expectations and highest standards.”