Using and Managing Data and Information

BA3020QA Assignment 1 

There are FIVE tasks and you are expected to answer all of them. Answer each task on a separate worksheet on the same Excel file.

Task 1 (10 marks)

Use Excel as a calculator to evaluate the following calculations. For each calculation you should provide the formula you have used (1 decimal place)

Task 2 (10 marks)

Use the Excel formatting facilities to answer the following questions:

• Express 72.5% as a 2 digit fraction.
• Express 0.58 as a percentage.
• 0.425 as a fraction in its simplest form.
• Adam achieved 22/30 in a test. Express his result as a percentage.
• Work out 5/9 ÷ 3/7, giving your answer as a fraction in simplest form.

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.

• Calculate the total amount accrued after 5 years for saving a single amount of £10,000 in a bank account that pays interest of 2% per year. Assume that interest is calculated at the beginning of every year.
• Calculate the monthly repayments for a 5 year loan of £25,000, with 5% interest per year. Repayments are made at the beginning of each month.
• Calculate the total amount accrued after 10 years for regular monthly cash investments of £300 paid into a savings account at the beginning of each month and pays 3.20% interest per year.
• Calculate the monthly repayments (payable at the end of each month) for a period of 25 years, for a mortgage of £300,000 at a fixed interest rate of 2.8% per year.
• What is the single amount you need to save today so that you will have £30,000 in 5 years, if the saving rate is 3%? Assume that interest is applied to the account at the end of every year.

Task 4 (10 marks)

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 2020 was £874 bn. The sum is split between each of the departments as follows:

Pensions 18.4%

Healthcare 18.8%

Education 10.5%

Others 52.3%

Total UK Government spending for the financial year 2021 was estimated to be £908 bn. The sum is split between each of the departments as follows:

Pensions 18.9%

Healthcare 19.2%

Education 10.8%

Others 51.1%

By comparing the UK Government spending (£bn) in healthcare, work out the percentage increase for 2021 (show formula)

Task 5 (10 marks)

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:

• Write down a function that will return the number of employees who have taken more than 9 days as holiday.
• Write down a function that will return the number of employees who have completed less than 3 training days.
• Write down a logical function with an “IF” statement to test whether an employee has taken more than 9 days as holiday and completed less than 3 training days. You should use “Yes” if the employee has taken more than 9 days as holiday and completed less than 3 training days.

Note: The functions you show in the table for questions a. to c. should be written using relative references to your spreadsheet rows and columns.

“You can order BA3020QA: Using and Managing Data and Information Assignment 1  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.”