2012 S01 P02 EXCEL

Mr. Plant is the owner of Glorious Gardens Nursery. His merchandise includes plants and gardening tools.

Jack Snipper is one of his staff members watering all the plants on a regular basis.
Early Monday afternoon the 23rd of January 2012, Jack Snipper was busy watering the plants as usual and was supposed to spray bug-killer on all of the plants in the nursery. Jack Snipper accidentally used weed-killer instead of bug-killer, only realising his mistake after spraying all of the plants in the nursery.

Mr. Plant is of the opinion that the specific weed-killer used will probably not kill all of the plants sprayed but he would rather take the conservative route and create a provision for an obsolete stock for all herbicide sprayed plants in the nursery. Glorious Gardens Nursery will not be able to sell plants that have died due to the incorrect spraying of the weed-killer, thus this stock (the plants) is therefore classified as obsolete.

You are a good friend of Mr. Plant. He heard that you have had some experience in using Excel and asked if you could assist him in performing certain calculations using Microsoft Excel.
Mr. Plant needs help to perform calculations regarding profit and an obsolete stock provision on 31 January 2012.

He gave you the following information

• A report showing quantities on hand of all inventory items as at 31 January 2012

• The same report also shows the value per inventory item

• The gross profit percentage is not the same for all products He was unfortunately unable to provide you with the various gross profit percentages He noted that the gross profit percentage is calculated based on the cost of sales

• The Value Added Tax (VAT) percentage is 14% as indicated in cell B13

• Glorious Gardens Nursery is a VAT vendor

• For all inventory items, the selling price includes VAT

• The obsolete stock provision is calculated based on inventory category The full value of each plant inventory item is raised as a provision but no provision is raised for tools

• Inventory categories :
Tools Category 1
Plants Category 2

• Mr. Plant has decided to create new inventory codes for the various inventory items. Mr. Plant indicated that the new inventory code should be created as follows:

o Use the first four characters of the current inventory item code. (Tip Think of a text function that you can use)
o Insert the character after the above,
o Add the specific group number for the applicable inventory item after the above

You created the spreadsheet on the following page


Please login to get access to this quiz