A list of cars available at the Mzansi Car Sales showroom has been captured in a spreadsheet - NSC Computer Application Technology - Question 3 - 2017 - Paper 1
Question 3
A list of cars available at the Mzansi Car Sales showroom has been captured in a spreadsheet.
NOTE:
- Use formulae and/or functions for ALL calculations in the spr... show full transcript
Worked Solution & Example Answer:A list of cars available at the Mzansi Car Sales showroom has been captured in a spreadsheet - NSC Computer Application Technology - Question 3 - 2017 - Paper 1
Step 1
3.1 Insert the text 'MZANSI CAR SALES' in cell A1.
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell A1, type the text: 'MZANSI CAR SALES'.
Step 2
3.2 The code for each car is created as follows:
- The first two letters of the name of the car manufacturer (column B)
- Followed by the last three digits of the year in which the car was manufactured (column F)
- Followed by the owner's name (column K).
Insert a function in cell A3 to create the code for that car.
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell A3, use the formula: =LEFT(B3,2) & RIGHT(F3,3) & " " & K3. This combines the first two letters of the manufacturer, the last three digits of the year, and the owner's name.
Step 3
3.3 Insert a function in cell G4 to calculate the age of the car by using the current year and the date the car was released from the factory in column F.
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell G4, enter: =YEAR(TODAY())-F4. This will calculate the age of the car based on the current year.
Step 4
3.4 Use a function in column J to display the car's registration number in column I in upper case.
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell J3, use: =UPPER(I3). This converts the registration number to uppercase.
Step 5
3.5 Insert a function in cell L4 to determine the position of the space located between the owner's initials and surname in column K.
97%
117 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell L4, type: =FIND(" ", K4). This finds the position of the space in the owner's name.
Step 6
3.6 Insert a function in cell M5 using the position of the space in column L to display only the surname from column K.
97%
121 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell M5, use: =MID(K5, L5+1, LEN(K5)-L5). This extracts the surname using the position found earlier.
Step 7
3.7 The deposit for a car is calculated using the year (column F) and the selling price (column N) as follows:
YEAR DEPOSIT
2014 or newer 10% of selling price
2010 to 2013 20% of selling price
2009 or older 30% of selling price.
Insert a nested IF function in cell O6 to determine the actual deposit to be paid.
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell O6, enter: =IF(F6>=2014,N6*0.1,IF(F6>=2010,N6*0.2,IF(F6>=2009,N6*0.3,N6*0.1))). This calculates the deposit based on the year.
Step 8
3.8 The total amount owed for a car is calculated by adding the interest (column P) to the selling price (column N) and deducting the deposit (column O). Insert a formula in cell Q7 to calculate the monthly repayment of the car over 60 months.
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell Q7, use: =(N7+P7-O7)/60. This calculates the monthly repayment based on the total amount owed.
Step 9
3.9 Insert a VLOOKUP function in cell R8 to determine the CO2 emissions for each engine size (column E).
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
In cell R8, enter: =VLOOKUP(E8, 'C02'!$A$2:$B$10, 2, FALSE). This looks up the CO2 emissions based on the engine size.
