Excel is a powerful tool you can leverage easily and effectively to track parts for your manufacturing company.
You need to track inventory from start to finish because you need to know when you have ordered new parts or raw materials, track expenses, determine pricing for products, and manage suppliers.
We’re going to take a look at how to use Excel to track parts for manufacturing.
Set Up the Right Template First
It all starts with the right template. If you don’t set up the right Excel template from the start, you’ll be lost.
A basic template to track inventory should include product numbers, names, brief item descriptions, prices, cost, number in stock, quantity to reorder, and quantity of products sold.
Perhaps the most important column to add is time to reorder. You can set up a function that will change the days left to order on the final column as you decrease the number in the stock column.
For example, you have 50 crates of bolts that contain 20,000 bolts each. These bolts go in your self-sealing stem bolts manufactured by your company, Acme.
You know that each crate of bolts lasts three days. Every time you decrease the crates by one, Excel can decrease the days left to order by three. This comes in handy if you need an extra crate for a large order, a line goes down and production is delayed by a day, or you need to send a crate back due to defects.
Of course, your Excel template needs the right formulas for you and your teams to utilize it properly.
Excel Functions to Use for Parts Manufacturing
Now, we’ll go over the Excel functions to use for your parts manufacturing company.
SUM
The SUM function can add any figures that you put into two or more cells.
You can create a sales quantity that automatically updates how much revenue you’ve earned per line. Once you come up with a total for each line, then you sum up every line in the entire factory.
For example, if your finished self-sealing stem bolts are worth $52 each and seven different production lines created 4,235 in one day, the SUM function updates the ‘inventory value’ section to $220,220 for that particular day.
You can also multiply, divide, and subtract columns that have a SUM function to calculate revenue, profits, and other KPIs.
RANK
RANK lets you see what items you need in a particular order. RANK lets you select cells to see what items are most profitable, have the highest amount of inventory, or have the closest time you need to reorder.
It makes sense to rank high-sale items or high-production items that you have to remake or restock at the top.
Back to the self-sealing stem bolts, you can rank the parts you have on hand by the reorder date to see how soon you need to order parts for them. The one ranked 1 would be the first part you have to order soonest.
Write your RANK formula like this: =RANK (Cell, Cell:Cell), where the cells mentioned include all of the cells you want to rank.
RANK is different from SORT. SORT organizes your data in order when you tell it to. RANK does this automatically and keeps a running total.
SORT
SORT is a function that orders columns by certain criteria, including:
- Largest to smallest
- Smallest to largest
- Cell color on top
- Font color on top
- Icon on top
- Custom sort
Let’s say you want to see a specific data set on all products separately. Your products are color-coded a certain way. All stem bolts are color-coded green, and you have three varieties of them.
You can sort columns by green ones to see all data for those columns colored green to see inventory, sales, profitability, or remaining supplies for all stem bolts.
Tips for Using Excel for Your Parts Manufacturing Company
Here are some quick tips for using Excel for your parts manufacturing.
1 )Test your template to make sure it works.
Once you choose your template (and Excel has one or you can find one), test it using all functions, columns, and rows to make sure it works as you expect.
Make sure you get your first template and then make a copy of it, saved under a different file name, to play around with it. Every time you alter a copy of a template, consider saving it to know which one works the way you need it to.
Look for errors. Come up with a list of best practices for altering numbers in the Excel spreadsheet.
Once you get your chosen template working properly, take it to new heights for inventory on both ends of your production lines.
2 ) Update Excel sheets as soon as you have new data.
Your data is only as good as the updates you give it. Do you update the data hourly? Daily? Weekly?
Maintain the right update schedule based on your procurement and logistics schedule so you have the right numbers and when you need to reorder items so your lines stay productive. Regular updates prevent cost overruns, wrong inventory numbers, and inaccuracies that can cost your manufacturing company money.
3 ) Create charts when needed.
Excel is fantastic at storing and calculating numbers. It’s also very good at presenting data in visual formats. Create charts and graphs when needed, particularly when showing numbers to people higher up the production chain. Charts are great when you want to compare numbers from previous months or previous years to see how your company is doing.
4 ) Review data regularly.
Make sure the right people review the data in the spreadsheets properly to check for any anomalies. Have a process in place for who checks on the data and when, as well as who has access to these numbers. You don’t want people without any authority to do so updating numbers because they aren’t trained properly or they might misplace data or put numbers in the wrong columns.
5 ) Get help from the experts.
Need extra help with your inventory-tracking Excel spreadsheets?
ExcelHelp has skilled consultants ready to build a custom manufacturing solution for your company, using all of the above and more. We can expedite building a custom application to meet your inventory tracking needs. We’re only an email away! Contact us today for a consultation.