Lilly is the owner of Buttercup Bakery. She considers every day an opportunity to make people happy by baking mouth-watering treats. Her specialties are red velvet cupcakes, fudge brownies, pecan nut
pie, apple crumble, chocolate mousse and baked cheesecake.
General ledger accounts, spreadsheets, and reconciliations are in her opinion dull and boring. However, she does realise the importance of proper accounting records. Fortunately, you are her best friend who is very good at compiling spreadsheets.
You offered to compile a spreadsheet for her to record her orders and calculate her gross profit for July 2011 and in return, she offered to bake you fudge brownies.
She gave you the following information:
The gross profit percentage is based on the cost of sales.
The gross profit percentage is not the same for all products. Characters 4-6 of the product code indicates the gross profit percentage to use.
The structure of the product code is as follows:
Characters 1-3: The first three letters come from the product description in column B.
Characters 4-6: Indicates the gross profit percentage i.e. ‘gross profit indicator’
Characters 7-9: The last three letters come from the product description in column B.
The gross profit percentage is 25% (refer to cell B4) in the instance where the ‘gross profit indicator’ is smaller than 500.
The gross profit percentage is 30% (refer to cell B5) in the instance where the ‘gross profit indicator’ is 500 or higher.
The details of all orders placed and delivered for July 2011.
The sales price per unit excluding VAT for every baked treat.
The order number is a combination of the customer code and the date of the order.
Buttercup Bakery is a VAT vendor.
You created the spreadsheet :