2016 S01 P02 EXCEL

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 on stocking all the latest and best-selling books across various genres, eg academic, fiction, nonfiction, sci-fi, and the Pages’ personal favorite romance 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” 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.


Additional information applicable to RaL’s operations and the IMS:

a) The 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 Initials Source code

Characters 1-4 Refer to the year the book was published
Characters 5-8 Refer to the book’s location in the store
Characters 9-10 Refer to the initials of the author of the book
Characters 11-12 Indicate the source of the book as either imported (IM) or Locally sourced (LS)

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-4 Refer to the book’s location in the store.
Character 5 *(Asterisk sign)
Characters 6-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 unit sold for every SRN (refer range G17:G20) and the Unit Selling Price Excluding VAT.

Please login to get access to this quiz