Question 1: The table below shows students’ scores in different subjects (9 marks)
Name English Acc Maths
Queen 14 18 17
prince 09 18 19
Jo 18 15 17
Frank 07 10 05
Coefficient 2 1 4
a) Present the above data and all the requested formulae in the spreadsheet. (5mrks)
b) Use only formula to calculate the average score for each student and the Decision which is (Fail or pass
based on the average score of each student) (5mrks)
c) Write a formula to find the number of students who have failed and the percentage of students who have
failed. (5mrks)
d) Considering that, the following grades are given to the students: (5mrks)
Less than 10 - poor
Less than 12- average
Lessthan14 - good
Greater or equal to 14 - very good
Write formulas to provide each student’s grade.
Question 2: You are given the worksheet below
© MINESUP 2020 Page 1 of 1
Required
i) Open an empty excel worksheet and represent the information above exactly as it is (
including boarders and text placed in the same cells) / (3 marks)
ii) Using a formula, calculate the total marks of Ade Olu / (2 marks)
iii) Calculate the total marks of the other 10 student / (2 marks)
iv) Using a pre-defined formula, calculate each students average mark / (3 marks)
v) Using a predefined formula, rank the class in a descending order that is the student with the
highest total marks should be first / (5 marks)
vi) Using the IF function, give the student such that any student with 50 or above has the word
“Passed” written under the Grade section and any student with less than 50 has the word
“Failed” written under the grade section of his/her name / (6 marks)
vii) Create another column after Grades (L3) and name it “Failed marks”.Knowing that the total
marks of all 6 subjects is 120 (put 120 in B12), in the column “Failed marks”, using an
absolute
value for B12, calculate the number of marks each student needed to have all the marks (120)
/ (6 marks)
viii) Calculate the total marks per subject / (2 marks)
ix) Change the name of the worksheet from sheet 1 to Students marks / (3 marks)
x) Create another column after “Failed marks” (M3). Assuming all students with total marks
above 70 are been given a 5,000 frs, under the “Price” column, put “5000” against the name
of all those to receive 5,000 frs and nothing on the rest / (5 marks)
xi) Save the file to My Document as / (3 marks)
Question 3: A company is recruiting, as a candidate for the job offer, you are provided as an interview
test, the following information concerning the sales of the last quarter of the year 2019.
Sales in quantity for three months Unit selling Unit
prices purchase
Products October November December cost
Woofers 100 20 140 35000 21000
Television 150 40 110 80000 45000
Radios 200 60 30 12000 8000
Electric Fans 80 100 60 18000 10000
Air conditioning 40 80 35 210000 110000
© MINESUP 2020 Page 2 of 1
Formatting requirement:
- Title font: size “18”, type “Arial”, Bold, small letters
- Table font: size “12”, type “Arial”, Small letters
- Alignment: Title (Merge and centre), sales quantities (centre), Amount
columns (default alignment)
- Number: separate thousands, zero decimals. Additional information:
- Print in landscape
- Paper size A4
Work required:
1.1. Establish an excel spreadsheet of the above data titled “Sales statistics”, save as “Sales
Report” in a folder name “Interview”. (3 marks)
1.2. Calculate the total quantity sold for the three months period. (4 marks)
1.3. Determine the total sales and total purchase cost for the three months period. (5 marks)
1.4. Determine the average sales quantity for the period. (4 marks)
1.5. As per Sock turnover, name the product with an average sales above 60 as “Fast”, between
50 –60 as “normal” and below 50 as “slow”.
1.6. Show how the profit/loss realized will be calculated. (5 marks)
1.7. Determine the total sales units, value and purchase cost for the period. (4 marks)
1.8. Calculate the percentage (%) gain/loss on sales for the period. (5 marks )
(Total=40 marks)
Question 4: You work as the Assistant AR Accountant, and your boss furnished you with the
following information concerning some AR for the period.
Page 3 of 2
Credit Sales Payments
Amount tax Invoice date Due date Amount Date
Clients Incl.
Fany 546,000 21/01/2020 31/01/2020 231,000 23/01/2020
Anne 119,250 31/01/2020 15/01/2020 89,200 07/02/2020
Victoire 543,000 02/02/2020 09/02/2020 400,000 02/02/2020
Brenda 421,300 05/02/2020 20/02/2020 320,000 08/02/2020
Ebote 231,400 05/02/2020 10/02/2020 189,700 13/02/2020
Formatting requirement:
- Title font: size “16”, type “Arial black”, small letters
- Table font: size “12”, type “Arial Narrow”, Small letters
- Alignment: Title (Merge and centre), the rest (default alignment)
- Number: separate thousands, zero decimals. Additional information:
- Print in landscape
- Paper size A4
Required:
A. Represent the information on an excel spreadsheet, title « AR Credit Analysis for the Period
», save as “AR Report”, in a folder name “Customers’ Details”. (5marks)
B. Calculate the account balance for each customer debt (4marks)
C. In a column, determine the VAT (19.25%) on customers account balance
D. Determine the number of credit days given to customers. (5 marks)
E. Calculate the cash discount accrued to customers at 2% when payment is within 8 days and
3% within 3 days from the invoice date. (10 marks)
F. Calculate the profit on sales, if margin on sales is 20%. (5 marks)
G. Determine the total of customers’ account balance greater than and equal to 102,000frs. (6
marks)
Page 4 of 2
