Fight Back Pharmaceuticals (Pty) Ltd (FBP) is a company that was established in 2015 as a unit to assist in providing aid in the crisis of viruses’ pandemics. The company (FBP) manufactures and provides a delivery service to its clients, who are in the different provinces, demographically located identified as coastal and inland. Each client has a demand for a specific product, which FBP supplies. Each product has a unique code. The range of products offered by FBP to its various clients is focused on promoting good hygiene and protection in the event of viral exposure.
Each client has a delivery-receiving warehouse with a unique dual code for identification purposes.
There have been several reports by FBP’s clients that inventory quantities received were not correct and were mixed with other inventory products. Upon investigations by FBP’s management, it was discovered that the current product codes needed to be adjusted. A new code (New pharma code) was created to help in solving the problem. Your value-adding Microsoft Excel skills were requested.
You were issued with the following product spreadsheet:
OPEN PDF HERE
FBP’s management team also provided you with the following additional information:
a) There are 5 different products, each with a unique product code structure.
b) The product code consists of ten (10) characters. The product code relates to each product offered by FBP (refer to range B10:F10). Each product code includes the product description initials, the batch date as well as the province code (refer to range B11:F11).
The product code structure consists of the following:
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|---|---|---|---|---|---|---|---|---|---|
| P | Q | 2 | 0 | 2 | 0 | 1 | 0 | M | P |
Characters 1 to 2: The product description initials
Characters 3 to 8: The batch date
Characters 9 to 10: The province code
c) The province code is linked to a warehouse store code and is unique for each province (refer to cell A2:B7).
d) The warehouse store code is made up of a combination of the distance traveled in kilometres (km) to the warehouse location and whether it is coastal or inland. A code of “100” reflects an inland demographical region and “200” reflects a coastal demographical region.
The warehouse store code structure consists of the following:
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
|---|---|---|---|---|---|---|
| 4 | 3 | 1 | – | 1 | 0 | 0 |
Characters 1 to 3: The distance to travel to the location (kms)
Character 4: (-) Hyphen or dash
Characters 5 to 7: The demographic region
e) The structure for the New pharma code consists of eleven (11) characters. The New pharma code is a combination of the province code, energy supplement, a hyphen/dash and the energy code (refer to range G14:G19).
The New pharma code structure must be as follows:
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
|---|---|---|---|---|---|---|---|---|---|---|
| M | P | 4 | 3 | 1 | – | 1 | 0 | 0 | P | Q |