Perhaps I've not explained this well. Ok, take product A. It can run on line 1 using batch X and line 2 using batch Y.
Film batches are recorded as I use the film.
Product A is created on our system as it's made but not telling me if it's been run on line 1 or line 2.
My production records (a few sheets per run) will tell me if it's run on line 1 or line 2.
My suggestion to the team was to put on a calendar when we changed over batches of film, however, to find out which line the product was run on and hence which film was used, I have to wade through pages and pages of production sheets which go on for months for the same batch. If I went from the daily planning sheets it would take just as long and wouldn't account for if a line had been changed due to a breakdown.
Does that make sense? In a way I can get around the issue of when we started and stopped using a batch, to be honest as long as you've finished a batch that's easy but due to production flexibility, I can't easily find out how much product was made on which line which is what I need to be able to find out to have the number to compare the mass balance with.
I think I get it, maybe?? So the problem isn't so much the mass balance of film, as it is being able to figure out what products ran with the batches quickly enough.
I'm trying to walk through this one as I go. The mass balance would be one film lot trace (hopefully for your sake), and comparing that to how much film you used, and what you still have in stock. Alright, so if you receive a purchase order that shows you received 3 skids of film rolls with lot ABC; you should know (or be able to find out) how many film rolls are on each skid. Let's say each skid has 20 film rolls. You've got 60 film rolls of that lot number. If you make a changeover log when the film spool empties on each line, that it would be 60 - 1 - 1 - 1 etc. So if you make a database for film, and do a query for lot ABC, and you find that there are 54 records of changeovers for lot ABC between January 1 - May 1, you should have used 54 film rolls, with 6 in stock or presently being used. If you find that there are exactly 6 in your inventory that can be accounted for, you're good with that portion. Note: the changeover log for film does not need product information (I was overthinking the document); it just needs the date, pertinent film roll identification information, and the line running the film.
The much harder part of this mass balance as you said, is to figure out what products ran with lot ABC, and how much was run during that 4 month span. Do you have a production record that just shows the following information?
Line, date, time of startup, product, lot, quantities:
- Line 1 ----- 1/1/16 -----7:00 AM ----- Product 1----- Lot 101A ------ 100 cases
- Line 1 ----- 1/1/16 -----9:30 AM ----- Product 2----- Lot 102A ------ 50 cases
- Line 1 ----- 1/1/16 -----1:00 PM ----- Product 3----- Lot 103A ------ 200 cases
- Line 1 ----- 1/1/16 -----3:30 PM ----- Product 4----- Lot 104A ------ 75 cases
- Line 2 ----- 1/1/16 -----7:00 AM ----- Product 1----- Lot 101B ------ 95 cases
- Line 2 ----- 1/1/16 -----8:15 AM ----- Product 2----- Lot 102B ------ 100 cases
- Line 2 ----- 1/1/16 -----11:00 AM ----Product 3----- Lot 103B ------ 400 cases
- Line 2 ----- 1/1/16 -----3:30 PM ----- Product 4----- Lot 104B ------ 50 cases
From this information, you could have a separate database just for production dates, lot numbers, products running and quantity made, and the line where the product was produced. Therefore, you can do a search for all products produced between January 1 - May 1, and view quantities made.
This also brings up another question: if you are running the same product on both lines, do you give different lot numbers or some sort of differential coding to your products on each line? Otherwise, you won't necessarily know which products would need to be traced, and you would be accounting for all products with the same lot number even if the film roll lot number was different between line 1 and line 2.
If you stick to FIFO, and try to use one lot of film at a time (since you said you can use one batch of film for months), you should be able to trace those with two separate databases. If you're pretty good with database, you can link both of those databases together by date and do a query for date of production and film roll lot; showing results for products produced, lot numbers, and quantities. Then you should be able to get results for the products, and quantities made with lot ABC for film roll made during January 1 - May 1. You also should be able to account for what you are currently running if you're still using the same lot of film. If the date of the mass balance is May 10th, there are 9 days since the last record of changeover. You should be able to find the products made since May 1st, and add that information to your mass balance.
In the case of a breakdown, if both lines are still using lot ABC, you should be ok because you should have production quantities of finished product regardless of the line running it. If the lines use different lot numbers (as you said may happen), the process becomes more interesting but still can be done. However, if you're only using 3-4 different batches of film per year, this all should be traceable. If there is still some uncertainty, you can trace a couple of days or so before and after what you think would need to be traced, to make sure you cover it all.
This will involve some data entry for sure, but would make tracing easier in the long run. This all works out in my head, but I'm not sure if it would work out for you.