AIN2601
ASS2/SEM1 2023– memorandum
NB : When opening the spreadsheet, make sure that you enable Macros if asked to
do so. This takes place with a macro-enabled workbook xlsm.
Before you begin marking : You also need to ensure that the « Autosave » on the top
left of the file is indicated as « Off »
QUESTION 1 (38 marks)
Mark the formulas and functions from left to right.
• Mark the first option where students give options between different formulas i.e. XXXX
or YYYY.
• Where students ‘build-up’ a formula mark the final answer i.e. amounts →cell
reference→cell reference with $
• For a student to earn the applicable mark the cell references (taking the $ into account
where applicable) must be exactly the same as indicated on the memorandum.
• Students can use either the , or ; as a function separator but it must be used consistently
throughout the question.
• If information is given in a cell the student must refer to that specific cell. E.g. Vat given
in a cell – student cannot use 15% in a formula but must refer to that specific cell
reference.
Marks are deducted as follows (per individual question):
(Note: A student can’t get less than 0 for a formula.)
Not starting a formula with = -1
When referring to a range of cells using a ; to indicate the range i.e. A10:D10 (correct) -1
A10;D10 (-1 mark)
Logical issue in a formula e.g. multiply where we should divide, subtract where we -1
should add, etc.
Incorrect use of ( ) or not using it where necessary or not closing brackets -1
Using [ ] instead of ( ) -1
Using SUM in a formula where it is not applicable i.e. =sum(H18 - H23) -1
Using a formula when a function is available i.e. = B5+B6+B7 instead of =sum(B5:B7) -1
Add an argument which is not applicable to the end of the function i.e. =Right(A11,1,1) -1
For marks deducted on a function: The part in brackets can however not be less than
0
Marks are deducted for a whole question and NOT per individual question/line
Students can use either the, or; as an argument separator but it must be used -1
consistently throughout the question.
When using ; or , inconsistently: - deduct 1 mark for whole question and not per
individual question e.g. deduct 1 mark for question 5 and 1 mark for question 6 (i.e. two
in total)
Using x instead of * or ÷ instead of / -1
When using x: - deduct 1 mark for whole question and not per individual question e.g.
deduct 1 mark for question 5 and 1 mark for question 6 (i.e. 2 in total)
1
CONFIDENTIAL TURN OVER
, AIN2601
ASS2/SEM1 2023– memorandum
QUESTION 1 (38 marks)
1.1 =LEFT (A18,3) 2
=VLOOKUP (A18,G$4:H$10,2,FALSE)
1.2 $G$4; G$5; $G$5; G$6; $G$6 and also $H$10 correct 3.5
False can also be reflected as “0”
=CONCATENATE (C18,"-",D18) OR,
=C18&"-"&D18
1.3
=(LEFT(A18,3)& "-" & D18) Allowable option to allow student to
proceed if they got 1.1 incorrect 2.5
=VLOOKUP (D18,F$12:H$15,3,TRUE) OR,
$F$12; F$13; $F$13; F$14; $F$14 and also $H$15 correct
True can also be reflected as “1”
1.4
If (D18<=200,H$14; H$15) OR,
If (D18>200,H$15; H$14) 3.5
$H$14 and $H$15 is also acceptable
=IF (G18="New", 5%,8.5%) OR,
1.5
=IF (G18="Used", 8.5%,5%) 3.5
=C25*(1+B$6) OR =C25+C25*B$6
1.6
$B$6 is also correct. B6 receives no marks 1.5
=1/(1+B$5)*D25 OR =D25/(1+B$5)
1.7
$B$5 is also correct. B5 receives no marks 1.5
1.8 =D25*B25 OR, =D25*H18 1
1.9 =(D25-C25-F25)*G25 2
1.10 =ROUND (SUM (H25:H29),0) 3.5
1.11 =MIN (C25:C29) 2
1.12 =MAX (D25:D29) 2
1.13 =AVERAGE (E25:E29) 2
2
CONFIDENTIAL TURN OVER
ASS2/SEM1 2023– memorandum
NB : When opening the spreadsheet, make sure that you enable Macros if asked to
do so. This takes place with a macro-enabled workbook xlsm.
Before you begin marking : You also need to ensure that the « Autosave » on the top
left of the file is indicated as « Off »
QUESTION 1 (38 marks)
Mark the formulas and functions from left to right.
• Mark the first option where students give options between different formulas i.e. XXXX
or YYYY.
• Where students ‘build-up’ a formula mark the final answer i.e. amounts →cell
reference→cell reference with $
• For a student to earn the applicable mark the cell references (taking the $ into account
where applicable) must be exactly the same as indicated on the memorandum.
• Students can use either the , or ; as a function separator but it must be used consistently
throughout the question.
• If information is given in a cell the student must refer to that specific cell. E.g. Vat given
in a cell – student cannot use 15% in a formula but must refer to that specific cell
reference.
Marks are deducted as follows (per individual question):
(Note: A student can’t get less than 0 for a formula.)
Not starting a formula with = -1
When referring to a range of cells using a ; to indicate the range i.e. A10:D10 (correct) -1
A10;D10 (-1 mark)
Logical issue in a formula e.g. multiply where we should divide, subtract where we -1
should add, etc.
Incorrect use of ( ) or not using it where necessary or not closing brackets -1
Using [ ] instead of ( ) -1
Using SUM in a formula where it is not applicable i.e. =sum(H18 - H23) -1
Using a formula when a function is available i.e. = B5+B6+B7 instead of =sum(B5:B7) -1
Add an argument which is not applicable to the end of the function i.e. =Right(A11,1,1) -1
For marks deducted on a function: The part in brackets can however not be less than
0
Marks are deducted for a whole question and NOT per individual question/line
Students can use either the, or; as an argument separator but it must be used -1
consistently throughout the question.
When using ; or , inconsistently: - deduct 1 mark for whole question and not per
individual question e.g. deduct 1 mark for question 5 and 1 mark for question 6 (i.e. two
in total)
Using x instead of * or ÷ instead of / -1
When using x: - deduct 1 mark for whole question and not per individual question e.g.
deduct 1 mark for question 5 and 1 mark for question 6 (i.e. 2 in total)
1
CONFIDENTIAL TURN OVER
, AIN2601
ASS2/SEM1 2023– memorandum
QUESTION 1 (38 marks)
1.1 =LEFT (A18,3) 2
=VLOOKUP (A18,G$4:H$10,2,FALSE)
1.2 $G$4; G$5; $G$5; G$6; $G$6 and also $H$10 correct 3.5
False can also be reflected as “0”
=CONCATENATE (C18,"-",D18) OR,
=C18&"-"&D18
1.3
=(LEFT(A18,3)& "-" & D18) Allowable option to allow student to
proceed if they got 1.1 incorrect 2.5
=VLOOKUP (D18,F$12:H$15,3,TRUE) OR,
$F$12; F$13; $F$13; F$14; $F$14 and also $H$15 correct
True can also be reflected as “1”
1.4
If (D18<=200,H$14; H$15) OR,
If (D18>200,H$15; H$14) 3.5
$H$14 and $H$15 is also acceptable
=IF (G18="New", 5%,8.5%) OR,
1.5
=IF (G18="Used", 8.5%,5%) 3.5
=C25*(1+B$6) OR =C25+C25*B$6
1.6
$B$6 is also correct. B6 receives no marks 1.5
=1/(1+B$5)*D25 OR =D25/(1+B$5)
1.7
$B$5 is also correct. B5 receives no marks 1.5
1.8 =D25*B25 OR, =D25*H18 1
1.9 =(D25-C25-F25)*G25 2
1.10 =ROUND (SUM (H25:H29),0) 3.5
1.11 =MIN (C25:C29) 2
1.12 =MAX (D25:D29) 2
1.13 =AVERAGE (E25:E29) 2
2
CONFIDENTIAL TURN OVER
