I want to use this post to run through how I encapsulate all key information for a product production run into a Google Sheet. You can use Excel, but I prefer Sheets as it is easier to use when cross-linking, as I explain in this post.
You can access the template used in this post from the email input at the bottom of the post.
The log example I am using is for a pet food product I produce. The format, layout and equations are not important. What is important is to understand all the relevant and key pieces of information to include.
Each production run is its own sheet, so I copy the sheet for the last production production run into a new sheet for a new production run. That way, I maintain a history of all the production runs in one file.
Any tweaks to the format, layout or equations I make for the most recent production run can then be copied to a new sheet for the upcoming production run. That way, I make sure I am using the most recent template.
One of the operational principles for my business is that what gets tracked, measured and reported, gets improved. This principle has really worked for me so I go to great lengths to design my systems and processes to track every available input that goes into producing my products.
But before we dive into the details of the production log, I want to back up and explain how the production log fits within my operations database.
Operations Database
My operations database is an online relational database, which I architected and had a developer build for me. It is one application with many objects (tables).
Cloud Drive
The Cloud drive is a simple online file storage system so that I can store all copies of receipts and images. You can use any online file system as long as it generates a link to each file. Google Drive, Onedrive, Dropbox or Amazon S3 works just fine, or if you use an online database as I do, then it is more efficient to do it there.
Accounting System
I don’t have a full fledged accounting system, but it is more of a general ledger to record all debits and credits. It is very detailed and I categorize expenses very specifically. I also include a link to the PDF or image of the original expense receipt, which is uploaded to the cloud drive.
So, for an inventory-related purchase, I include original receipts so I can understand costs by each individual unit for an inventory purchase.
Inventory System
My inventory system is broken down between raw and finished goods, with the production log sitting in between (the subject of this post).
For raw goods, any inventory items purchased are categorized this way. I link back to the original accounting entry and I also include product images, with particular emphasis on images of any log identification and expiration dates on products.
A child table is included for each inventory item that allows me to record the transfer of that inventory item.
So, I might purchase in bulk, then use up small quantities at a time in different production runs. which I log in the child table for that inventory item and note where they went and how much was used. I setup my database to automatically reduce the total in inventory for that item from the child table entries so I can see where I stand when I review all my inventory items.
Raw goods and finished goods are just one database table (or object). I just categorize them as either raw or finished in a field.
Finished goods are linked back to any of the raw goods used, which are linked back to the original accounting entry. Also, raw goods and where they end up, via recording in the child table, are linked to the finished good.
This linking backwards and forwards lets me dive deep into the exact disposition of all of my production inputs.
Additional Objects and Functions
Additional tables I use are a facilities log and a mileage log.
My facilities log allows me to keep track of any repair, maintenance or cleaning of my production facilities and my equipment.
The mileage log serves to record all vehicle mileage for the business.
I also have a module that holds all the various reports I have created that pull data from the various objects in the operating database.
Outside of the database, I am using information to do different things, which is identified in the lower red and green boxes.
I go into far more detail about my operating database in Section 2: Systems of the Framework
Product Production Log
The production log, from a process view, sits between my raw goods and finished goods in the yellow box. It records what happens between raw and finished.
Let’s start with a screen shot of the entire sheet. You can download this template from the email input at the bottom of the post.
Production Quantities and Notes
This block tells me how much of each ingredient to use and allows me to record that data.
The yellow cells are variable requiring input from the user to compute equations.
Since this is a food item with multiple ingredients, I already set up the template to guide me into how much of each ingredient to use.
I also include some information capture for post production and take any relevant notes for this production run.
I keep the SOPs (standard operating procedures) for how to execute production for this particular product in a separate Google Docs file, so as not to clutter it up in this sheet. But, you could put the SOP’s here or even in a separate sheet in the same file.
Ingredient Costs and Source
This block includes the link back to the inventory raw good item in my operations database (yellow cells are variable, so the link goes there), plus any costing information.
In this block, I am just looking at ingredient costs for this production run.
Production and Facilities Costs and Source
This block contains my production and facility costs.
I also include any links associated with those costs, which usually goes back to the accounting module in my operations database, the facilities module or the mileage module.
These are all tables (objects) in my operations database where I keep track of all this information.
Packaging Costs and Source
This block includes my packaging costs and all specific pieces that make up my packaging for this product.
In yellow cells, I link back to the packaging inventory item in the inventory module of my operations database, where I can see current available quantities of this item, product images and any other information related to this packaging item.
Miscellaneous Calculations
This final block includes miscellaneous calculations.
I like to specifically understand my COGS for the product in this specific run against SRP (suggested retail price).
I also have separate calculations on the right that I use for COGS when filing my annual tax return for the business.
You will also see the last lines (yellow cells) would include links to the finished goods items in the inventory system.
All other links above go back to raw goods or back to the accounting module, while these these last links point forward to the finished goods item in the inventory module of the operations database.
Summary
The different blocks that go into the production log template include:
- Production inputs
- Production notes
- Ingredient costs
- Production and facility costs
- Packaging costs
- Miscellaneous calculations
Those are the major areas of information that I track for production runs.
I also want to show how this production log file sits in relation to my operations database, and to emphasize that you want to include links to any raw goods, finished goods, or any related expense items.
That way, you have an easy setup to completely track everything about your production…backwards into your supply chain, and forwards to your finished goods, and links to it all.
Linking makes everything in your supply chain and manufacturing easily accessible, so you want to make sure you scan/image receipts, supply packaging, lot codes, expiration, etc.
This reduces the need for physical file or paper-drive processes in favor of digital filing and process management.
