FINAL MARK CALCULATION

Year mark: 25%

Exam mark: 75%
Final mark: 100%

You need a final mark of 50% to pass.

Supplementary students:
You need a final mark of 50% to pass (refer above)

Aegrotat students:
Last semester’s year mark transfers to this semester

Refer to TL102/2021 for more detail

1
 WHAT TO STUDY
PASTEL/TRANSACTION PROCESSING:

• Pastel training manual

• Topic 6 in your study guide
• TL202
• Transaction processing: Topic 3 SU 7,8,9,10

Answers to the Pastel manual questions:

• Pastel CD: “Data files accompanying Pastel course
ware”

2
 WHAT TO STUDY
EXCEL:

• Topic 2 in your study guide – the complete topic

❖PRACTICE EXCEL!!! Complete all activities in

Topic 2
❖Know structures of Functions
❖Given structures - PMT, PV, FV & VLOOKUP–
know when and how to use

3
 WHAT TO STUDY
THEORY:
All the remaining study units

Topic 1: SU1,2,3
Topic 3: SU11,12
Topic 4: SU13
Topic 5: SU14
Topic 7: SU19,20

STUDY THE COMPLETE STUDY UNITS

4
 HOW TO ANSWER AN EXCEL QUESTION

➢ Write down the structure of the Excel functions you must know out of your head
Example =IF(logical_test,value_if_true,value_if_false)

➢ READ all the provided information and underline/highlight the important information

➢ READ through the spreadsheet (left to right & top to bottom)

➢ If an amount or a percentage(%) is given in a cell you MUST use that cell reference
to use it in your formula/function

➢ Writing a formula: Write the formula using amounts and replace the amounts with
the applicable cell reference

➢ Negative marks in exam – Refer to MO001 document at the end of Study Unit 5

➢ Have a look at the bigger picture

Examples of exam questions
AVOID LOSING MARKS !!!!
 EXCEL
QUESTION 2

14% VAT
For Practice Purpose
 EXCEL QUESTION 1
Having just completed her Accounting degree, Jane Smith was appointed as Assistant Inventory Clerk
of Read-A-Lot Bookshop (RaL), a medium sized book shop co-owned by Mr and Mrs Pages. The
bookshop prides itself with stocking all the latest and best-selling books across various genres, eg
academic, fiction, non-fiction, sci-fi, and the Pages’ personal favourite romantic novels. RaL is a
registered VAT vendor.

During the interview with Jane, the Pages said: “We have been experiencing some serious challenges
with managing our inventory (stock), and we urgently need someone that will be extremely hands-on
from day one. The first task will be to fix/improve our inventory management system.”

In line with the Pages’ request, Jane’s first assignment was to develop a detailed inventory
management schedule “IMS” (see the Excel spreadsheet on the following page) for monthly inventory
management. The IMS will perform the following functions amongst others.

1) Illustrate the book’s physical location in the store,

2) Indicate book authors,
3) Indicate the book’s source; ie whether the book is “locally” bought/sourced or “Imported”, and
4) Project the total sales income (excluding VAT) generated per book title.

RaL’s current standalone inventory system uses a twelve (12) character stock reference number (SRN)
as inventory code. Jane rather wants to use the accounting information system’s (AIS) integrated
inventory function to manage the inventory. The AIS’s stock code format is however limited to seven
(7) characters and Jane therefore needs to create new SRNs to be used as inventory codes.
 EXCEL QUESTION 1
A B C D E F G H

1 Read-A-Lot Bookshop: Inventory Management Schedule (IMS)

2 Authors Table
3 VAT percentage 14% Author code Initials
4 HashimL2 HL
5 Book source and related mark-up margin GuguB012 GB
6 Source Mark-up % GroblerO GO
7 Imported Books (IM) 25% ButlerP1 BP
8 Local Books (LS) 15%
9
Author Location Author Source
10 Book Title Current SRN New SRN Mark-up %
code Code Initials Code
11 Book-Worm ButlerP1 2005R4S2BPIM R4S2 BP R4S2*BP IM 25%
12 Silent Killer GuguB012 1998R1S1GBIM R1S1 GB R1S1*GB IM 25%
13 New World GroblerO 2007R2S4GOLS R2S4 GO R2S4*GO LS 15%
14 Love Portion HashimL2 2010R1S4HLIM R1S4 HL R1S4*HL IM 25%
15
Unit Unit
Unit Cost Total Sales
Unit Cost Price Selling Selling
16 New SRN Mark-up% Price Excl. Units sold Income
Incl. VAT Price Incl. Price Excl.
VAT Excl. VAT
VAT VAT
17 R2S4*GO 15.00% R 98.76 R 86.63 R 113.57 R 99.63 14 R 1,394.77
18 R1S4*HL 25.00% R 290.56 R 254.88 R 363.20 R 318.60 38 R 12,106.67
19 R4S2*BP 25.00% R 198.25 R 173.90 R 247.81 R 217.38 22 R 4,782.35
20 R1S1*GB 25.00% R 458.98 R 402.61 R 573.73 R 503.27 9 R 4,529.41
21 Grand Total Sales Income Excl VAT R 22,813
22 Lowest Unit Cost Price Incl VAT R 98.76
23 Highest Unit Selling Price Excl VAT R 503.27
 EXCEL QUESTION 1
a. Value Added Tax (VAT) percentage is 14% (refer to cell B3).

b. Book source and related mark-up margin (refer to range A5:B8).

Each book’s mark-up margin (mark-up %) is determined based on whether the book is imported
(IM) or locally sourced (LS). Imported books (IM) attract a 25% mark-up margin (refer to cell B7)
and locally sourced books (LS) attract a 15% mark-up margin (refer to cell B8).

c. Authors Table (refer to range G2:H7) indicates the unique 8 character author code and the
corresponding authors’ initials.

d. Current stock reference number (SRN) - (refer to range C11:C14)

The current SRN structure is as follows:
1 2 3 4 5 6 7 8 9 10 11 12

Year published Location Code Author Source code

Initials

• Characters 1 to 4: Refer to the year the book was published.

• Characters 5 to 8: Refer to the book’s location in the store.
• Characters 9 to 10: Refer to the initials of the author of the book.
• Characters 11 to 12: Indicate the source of the book as either imported (IM) or locally sourced
(LS).
 EXCEL QUESTION 1
e. The New stock reference number (SRN) (refer to range A16:A20) is created by combining the
location code, an asterisk (the * sign) and the author initials.

The new SRN structure is as follows:

1 2 3 4 5 6 7

Location Code * Author Initials

• Characters 1 to 4: Refer to the book’s location in the store.

• Character 5: * (Asterisk sign)
• Characters 6 to 7: Refer to the initials of the author of the book.

f. The Source Code (refer to range G11:G14) is extracted from the current SRN (refer to point d)\

g. The Unit Cost Price Including VAT (refer to range C17:C20) was obtained from the inventory
system and is the cost price (including VAT) of one book.

h. The Unit Selling Price Including VAT (refer to range E17:E20) is based on the applicable Mark-up
% (refer to point b.) and the Unit Cost Price Including VAT.

i. The Total Sales Income Excluding VAT (refer to range H17:H20) is calculated by multiplying the
given units sold for every SRN (refer range G17:G20) and the Unit Selling Price Excluding VAT.
 EXCEL QUESTION 1
REQUIRED:

Use the case study information and the spreadsheet to answer the following questions.
Note: Where it is indicated that your formula will be copied to other cells, your formula must
take absolute and relative cell references into account.

5.1. Which spreadsheet formula was entered into cell D11 to extract Book-Worm’s Location Code from
the current Stock Reference Number (SRN) in cell C11?
Note: Your formula will be copied to range D11:D14. (2.5)

5.2. Which spreadsheet formula was entered into cell E11 to obtain Book-Worm’s Author Initials from
the Authors Table in starting in cell G2?
Note: Your formula will be copied to range E12:E14. (3.5)

5.3. Which spreadsheet formula was entered into cell F11 to determine Book-Worm’s New Stock
Reference Number (SRN)?
Note: Your formula will be copied to range F12:F14. (3)

5.4. Which spreadsheet formula was entered into cell G11 to extract Book-Worm’s Source Code from
the current Stock Reference Number (SRN) in cell C11?
Note: Your formula will be copied to range G12:G14. (2)
 EXCEL QUESTION 1
5.5. Which spreadsheet formula was entered into cell H11 to determine the Book-Worm’s Mark-up
margin?
Note: Your formula will be copied to range H12:H14. (3.5)

5.6. Which spreadsheet formula was entered into cell B17 to obtain R2S4*GO’s Mark-up margin from
the table in range F10:H14?
Note: Your formula will be copied to range B18:B20. (3.5)

5.7. Which spreadsheet formula was entered into cell D17 to calculate R2S4*GO’s Unit Cost Price
Excluding VAT?
Note: Your formula will be copied to range D18:D20. (2)

5.8. Which spreadsheet formula was entered into cell E17 to calculate R2S4*GO’s Unit Selling Price
Including VAT?
Note: Your formula will be copied to range E18:E20. (2)

5.9. Which spreadsheet formula was entered into cell H21 to calculate the Grand Total Sales Income
Excluding VAT? The formula should round your answer to zero decimal digits. (3)

5.10.Which spreadsheet formula was entered into cell H22 to determine the lowest unit cost price
including VAT? (1.5)
 EXCEL QUESTION 1
5.11. Which spreadsheet formula was entered into cell H23 to determine the highest unit selling price
excluding VAT? (1.5)
[28]
 EXCEL QUESTION 1
QUESTION 5 (28 marks)
5.1. =MID (C11,5,4) 2.5

=VLOOKUP (B11,G$2:H$7,2,FALSE)
5.2. 3.5
$G$2, G$3, $G$3, G$4, $G$4 and $H$7 is also correct.

5.3. =D11&"*"&E11 OR CONCATENATE (D11,”*”,E11) 3

5.4. =RIGHT (C11,2) 2

=IF (G11="IM",B$7,B$8) OR =IF (G11="LS", B$8, B$7)

5.5. 3.5
$B$7 AND $B$8 is also correct.

=VLOOKUP (A17,F$10:H$14,3,FALSE)
5.6. 3.5
$F$10, F$11 and $F$11 is also correct.

=C17/(1+$B$3) OR =C17 * 1/(1+$B$3)

5.7. 2
B$3 is also correct.

5.8. =C17*(1+B17) or =C17+C17*B17 2

5.9. =ROUND (SUM(H17:H20),0) 3

5.10. =MIN(C17:C20) 1.5

5.11 =MAX(F17:F20) 1.5

EXCEL QUESTION 2
EXCEL QUESTION 1
EXCEL QUESTION 2
EXCEL QUESTION 2
EXCEL QUESTION 2
EXCEL QUESTION 2
EXCEL QUESTION 2
 EXCEL QUESTION 2
REQUIRED:
Use the provided additional information and Sam’s property portfolio to answer following questions.

Note: Where it is indicated that your formula will be copied to other cells, your formula must take absolute and relative
addresses into account, but only where necessary.

6.1. Which spreadsheet formula was entered into cell B22 to extract Property 1’s province code
from the property code?
Note: Your formula will be copied to cells C22:E22. (2.0)

6.2. Which spreadsheet formula was entered into cell B23 to retrieve Property 1’s province name
from the location table?
Note: Your formula will be copied to cells C23:E23. (3.5)

6.3. Which spreadsheet formula was entered into cell B24 to extract Property 1’s square meter
(m2) size from the property code?
Note: Your formula will be copied to cells C24:E24. (2.5)

6.4. Which spreadsheet formula was entered into cell B25 to create Property 1’s “Prop-man”
code?
Note: Your formula will be copied to cells C25:E25. (2.5)

6.5. Which spreadsheet formula was entered into cell B29 to calculate Property 1’s selling price
excluding VAT?
Note: Your formula will be copied to cells C29:E29. (1.5)
 EXCEL QUESTION 2
6.6. Which spreadsheet formula was entered into cell B30 to calculate Property 1’s deposit
amount from the Deposit percentage table?
Note: Your formula will be copied to cells C30:E30. (4.0)

6.7. Which spreadsheet formula was entered into cell B31 to retrieve Property 1’s transfer duty
rate from the transfer duty table?
Note: Your formula will be copied to cells C31:E31. (3.5)

6.8. Which spreadsheet formula was entered into cell B33 to calculate Property 1’s financed
amount excluding VAT?
Note: Your formula will be copied to cells C33:E33. (1.5)

6.9 Which spreadsheet formula was entered into cell B35 to calculate Property 1’s agency
income?
Note: Your formula will be copied to cells C35:E35. (1.5)

6.10 Which spreadsheet formula was entered into cell B39 to calculate Property 1’s monthly
installment amount? The formula should return a positive answer.
Note: Your formula will be copied to cells C39:E39. (5.0)

6.11 Which spreadsheet formula was entered into cell B41 to calculate Property 1’s price per
square meter (m2) excluding VAT? The formula should round the answer to zero digits.
Note: Your formula will be copied to cells C41:E41. (2.5)

6.12. Which spreadsheet formula was entered into cell B42 to calculate the lowest price per
square meter (m2) excluding VAT for the whole portfolio? (1.5)

6.13. Which spreadsheet formula was entered into cell B43 to calculate average finance term in years for the whole
portfolio? (1.5)
EXCEL QUESTION 2
EXCEL QUESTION 2
 EXCEL
QUESTION 3
 EXCEL QUESTION 3
Catman is the financial consultant for the Merval Superhero Franchise, who provides an analysis on the monthly company
performance. On his way to the office, Catman was involved in a Hit and Run incident and had his computer damaged in the
process. He contacted you as the Excel expert in the department to assist him in providing management with the Gross
Profits for each Sales product, located in an excel file on the company server. Fortunately, you locate the file and forward it to
the finance manager. Within 5 minutes, the finance manager (not an excel expert) calls your office land line to ask you, how
the formula was determined, as the spreadsheet that you sent only had values and no formulas included.

You provided him with the following spreadsheet:

 EXCEL QUESTION 3
You provided him with the following spreadsheet:
a) There are 5 sales products that are hot sellers within the franchise. These products are identified by a specific sales
code found in row 11 of the spreadsheet

b) The sales volume for the current month, for each product is found in row 12 of the spreadsheet.

c) The gross profit for each sales product is dependant on the sales volumes generated in the month. The gross profit for
each sales product is located in row 13 of the provided spreadsheet

d) The “Gross profit” table can be located in the following range D3:F9 on the spreadsheet

Required:

Use the information and spreadsheet provided to answer the following question:
Note: Where it is indicated that your formula will be copied to other cells, your formula must take absolute and
relative addresses into account, but only where necessary!

1) Which spreadsheet formula was entered into cell B13 to obtain the gross profit for Hulk 48 from the Gross Profit Table
starting in cell D3? Note: Your formula will be copied to cells C13:F13. (4.5)
 EXCEL QUESTION 3

=VLOOKUP(B12,$D3:$F9,3,TRUE)
Best Wishes for
your exam!!

Duwayne Kock