Excel Wizard for Sales Data Analysis

For our cosmetics company we need to record all ingredients that we buy with their associated data. Then when we make a product using all of the ingredients we need to record per batch which ingredients we use. Example attached and detailed information below: Please see attached spreadsheet and below explanation of each point as well as what we are trying to achieve. Sorry it is a bit long winded but hopefully this explains why we need help!! Ingredients Master Tab Currently: • KAN Unique Code o When a product comes through the door it goes into a box. I then go through this box and write on it with a marker pen its own unique number. This unique number is then placed into Column A • Product Name / Manufacturer / Product Expiry Date / Manufacturer Record Number o I input the information from the ingredient that comes though the door which matches its unique code above  Is there a way we can have the product expiry date automatically be green if it is in date, red if it has expired? Is there a way to make this look cleaner? I am happy if you want to completely redesign this as it is quite messy at the moment. Night Cream Tab Currently: As of now, we only have a night cream but when we make new creams we will add new tabs (or else if you advise differently). Night Cream code NC. • Batch number o Code followed by number in chronological order i.e. NC001, NC002…  Could this number auto populate? • Pot size (ml) o When we make a batch, its volume is its weight X 1.056 which means it has a density/specific gravity of 1.056 thus is heavier than water. It also serves a cross check for us. So… when we have finished making the product we weigh it. We then times it by 1.056 to get a theoretical volume. As long as that volume matches (or as good as) the actual volume of the product then it passes the gross error check. At the moment, we manually write in the pot size in Column B as measured from the jug after it has cooled down (as it will shrink a small amount as it loses temperature). • Date created o Entered manually • Ingredient o This is a constant and will not change  However, what may change is if we are making the product and have to use more than one ingredient from the ingredients master tab. For example, if we have 50g left of Ingredient A (A1), then we will need to add x grams from a second pot (A2) so this needs to be broken down (as you can see was the case in NC002) • KAN Unique Number o Taken directly from the Ingredients Master.  We currently type this in manually but would be good if we could select it from a drop down. If the ingredient is in date, it should work. If the ingredient is not in date then it should be rejected (this acts as a safe guard to us when making it if we miss the expiry date on the pot) • In date? o At the moment this is us checking visually. But if the above point can be made automatically then we can remove this column or even have the expiry automatically appear…. • % o The total % must always be the same. So looking at NC001, you will see the correct breakdown. Looking at NC002, because we used two pots of Ingredient A the individual ingredients are different but the add up to be the same total overall • Weight (g) o NC001 acts as our datum. When we made NC001 this was what was sent to the lab for testing. Thus everything we now do is based of this reference pot. So the reference weight is 520.75g and the reference volume is 550ml. If you look ahead to calibrated weight for NC001, they are 1:1 but if you look at the calibrated weight for NC002 they are different (more below) • Calibrated Weight (g) o This weight depends on the size of the pot we are making. So for NC001, the calibrated weight (column I) matches the weight (column H). However, for NC002, we wanted to make a 209ml pot. 209ml is 38% of 550. So all calibrated weights were based off the datum/reference figures from NC001 and X by 0.38. So this is a very complicated way of doing it but it’s information that we need to record. To make things simpler, I would like to be able to do the following: 1. Commercial team tells lab team what size pot they need to make a. For example: commercial says make a 700ml pot 2. Lab team go into the spreadsheet, select the product they are making (in this case Night Cream (NC)), it auto populates the next batch number, and they enter manually the pot size as requested by commercial (in this case 700ml) 3. Then the spreadsheet to be able to show the ingredients required (which is a constant) 4. The lab technician then selects which ingredients they are using (which would have already been pre-recorded in the ingredients master). The spreadsheet automatically checks if it is in date and if so it allows the selection to be placed into the column along with its associated expiry date. 5. The weight is not applicable as it is only really applicable for the datum/reference which could also be stored somewhere else? 6. The calibrated weight is automatically populated based on the difference between the pot size requested by commercial and the reference of NC001 7. Should the technician wish to then use two of an ingredient (due to not enough left from the previous round) then there needs to be a way for them to do that. I suggest by clicking to add the same ingredient twice which maybe auto populates a warning? Then the lab would need to weigh the old ingredient, enter it in to a box and the remaining amount required should auto populate – something like that? Anyway, I hope this makes sense and I hope this helps a bit? If you have any questions please let me know. Ultimately, we just want a user friendly and easy to use system with as much automatics as possible but also records everything we make so we have a legal record and can trace everything back to where we brought the initial ingredients from if we need to,