At the company there's a safelaunch location where there are 4 inspectors who inspect parts before they are shipped to the customer. I have included the part numbers on the left side of the spreadsheet.
I have a column for number of inspectors, and the quantities that they have to inspect. For example, the third part number 2151173, there are 60 parts in a tray, and there are 8 trays per bundle, and each box contains 2 bundles. Therefore there are 960 trays pieces per box.
I made a time study for the inspection for each of the part numbers, for example, how long will it take a certain number of inspectors to inspect the 960 pieces that we talked about. It will take them 110 minutes to fully inspect a box.
In column N, that is the quantity of pieces that we have to ship. I have converted the quantity from pieces to boxes in column O.
Column P is based on the total available inspection time that we have, for example, how many inspectors do we have? What's the volume (quantity) that we have to ship? Will we be able to make the shipment in time if we have 3 inspectors? Will we be able to make the shipment in time if we have 2 inspectors?
If we have too few inspectors, meaning we don't have enough inspectors, then a cell would turn RED because we will not be able to make the shipment. If we have too few inspectors, meaning we don't have enough inspectors, then a cell would turn RED because we will not be able to make the shipment.
If we have enough inspectors, the cell would turn GREEN because we have enough inspectors.
However, the volumes change from day to day. I want to be able to view it from a different angle, I want to have weekdays in the spreadsheet. Say for example, Monday we will ship 15 boxes, tuesday we will ship 8 boxes, etc. I want to be able to see and make sure that we have enough inspectors for each day.
If you have other ideas to improve this spreadsheet let me know please.
So one of the cell's color would depend on the quantity that needs to be inspected and how many inspectors we have based on a timeline that we have. For example, we need to ship 15 boxes in 24 hours, how many inspectors will it take to get this accomplished?
7 freelancers are bidding on average $170 for this job
Hello, I’m an Excel expert and I would like to help you with your project. Please check my profile and contact me to discuss further details. Regards. Stay tuned, I'm still working on this proposal.