2020 S01 P02 EXCEL

Use the information provided to answer the following questions based on the provided spreadsheet:

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!

Junior Jimmy Juicers Ltd (JJJ) is a company that specialises in nutritional juices that are made of 100% fruit with no preservatives or additives. The company (JJJ) has been doing extremely well and has a customer base of regular customers who purchase a juice at least once a week. Juices are sold in two (2) sizes namely regular (RG) and large (LG), with each size attracting a different mark-up on cost price. Historical trends show that customers enjoy buying the large size juices of specific flavours, resulting in those flavours being more popular than the other juices. Less popular flavours are usually sold in regular sizes.

Due to the steady growth in JJJ’s customer base, they were looking to expand their juice range to include an energy supplement. The inclusion of an energy supplement will give JJJ an opportunity to tap into a larger customer base. The additional product, which consists of a juice flavour and an energy supplement will be categorised under a new enerjuice code. As the Microsoft Excel guru, JJJ’s management team has asked you to assist them in understanding the formulae that were used by their former spreadsheet analyst.

You were issued with the following product spreadsheet:

JJJ’s management team also provided you with the following additional information:

a) There are 6 different fruit juice flavours, each with a unique juice code structure.

b) The regular (RG) juice size attracts a mark-up on cost price of 15% (cell B4) and the large (LG) juice size attracts a mark-up on cost price of 20% (cell B5).

c) The energy table containing the ranges of energy codes and the corresponding energy supplement (refer to cell E6:G11).

d) The juice code structure consists of eleven (11) characters. The juice code is specific to each juice flavour and indicates the energy supplement that is best suited with each juice flavour (refer to range B14:B19).

The juice code structure consists of the following:



1 2 3 4 5 6 7 8 9 10 11
2 0 0 2 0 1 1 0 1 L G

Characters 1 to 4:            The year the juice was produced

Characters 5 to 6:            The juice range number

Characters 7 to 9:            The energy code indicating the energy supplement

Characters 10 to 11:       The size most frequently sold per juice flavour

e) The new enerjuice code structure consists of ten (10) characters. The enerjuice code is a combination of the juice flavour, energy supplement, a hyphen/dash and the energy code (refer to range G14:G19).


The juice code structure must be as follow

1 2 3 4 5 6 7 8 9 10
O R A G i N - 1 1 1

Characters 1 to 3:            The first three (3) characters of the juice flavour

Characters 4 to 6:            The first three (3) characters of the energy supplement

Character 7:                        ( - ) Hyphen/Dash

Characters 8 to 10:          The energy code indicating the energy supplement

Please login to get access to this quiz