2019 S01 P03 EXCEL

Following an excellent spreadsheet, you prepared in Question 5, Dineo was encouraged by the gross profit results and is considering purchasing six new vehicles to expand his shuttle service business. He was so impressed with your Microsoft excel skills that he has contacted you again to assist him with the calculation of the monthly payments for these vehicles. Dineo also needed to determine whether he may or may not be required to purchase a specific tracking device at the initial purchase of these new vehicles.

He also provided you with the following information:

1) Interest rate per year is calculated at 13% (refer to cell B4).
2) The financing period is calculated at 5 years (refer to cell B5), with no residual amount remaining at the end of the period.
3) A list of vehicles on the showroom floor (refer to cell range A11:F17).
4) The purchase price per vehicle (refer to cell range G11:G17).
5) The VIN number of the vehicle contains important information.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Vehicle Manufacturer / Vehicle Year Model / Average vehicle consumption per km per 1000 liter Vehicle type code

Characters 1 and 4         :indicates the manufacture of the vehicle
Characters 5                    :indicates the forward-slash symbol (/)
Characters 6 and 9         :indicated the year model of the vehicle
Characters 11 and 15     :indicate the average fuel consumption per 1 000 litre
Characters 16 and 17     :indicated the year model of the vehicle

6) Monthly payments to the vehicle financing company will be made at the end of each month
7) The “Vehicle Types" table starting in cell H3, indicates the vehicle type code and the corresponding vehicle type.
8) The inventory stock code is made up of the following information.

1 2 3 4 5 6 7 8 9 10 11 12 13 14
Abbreviated vehicle make Vehicle Year Model / Vehicle Type

Characters 1-4                   :indicated the abbreviated make
Characters 5-8                   :indicated the vehicle year make
Characters 9                       :indicated a hyphen/dash (-)
Characters 10 and 14       :indicates the vehicle type


Please login to get access to this quiz