Manufacturing Inventory reduction formula

Hello,

I work in an electronics board manufacturing lab. For many years inventory was not kept current, and parts shortages were common.

Using a previous excel inventory list (10 years old), I've updated the many thousands of individual parts counts.

Now with inventory correct, I need to write a formula removing the parts from inventory as I build cards. Meaning if I build a single board (or numerous cards), I need it to remove five parts from box 21E (or E21?), plus 20 parts from box 59E, 2 parts from box 308E and so on. For each card entered into the formula box.

There are up to a total of 90 parts that go into a card in some cases. So entering reduction numbers for each part is tedious and time consuming. In some cases taking as long as the build itself.

I've done serious hunting on the net. Finding mostly over complicated, often unneeded featured like profit margin, forecasts etc...

I only need it to remove what I use based on the number I build (enter).

Thanks

P.S. I have not written Excel function since Windows 3 So, I've lost most of that schooling. So, I'll need a template/example I can work with/build up.

Hi Vancefish and welcome to the forum,

See the attached for a first idea. The inventroy for parts is in columns A and B.
Each board parts are to the right in a column of its own. You would put the number of parts needed for that board in the appropriate rows.

Now - I've got event code where you double click on the board name, which will subtract the board numbers from the In Stock values.

If you do lots of multiple boards, I'd invent a loop to count times it subtracted. I think an add back on parts because you double clicked too many times would also be a good idea (enhancement).

See if the attached works for a starter.

Hi Vancefish,

Here is what I did in my attached file.
1. In Column A and B are part names and number of them in stock
2. In columns E to K are Boards you are building with the number of parts that go on that board.
3. You will need to update the numbers below the boards so they reflect the chips/resistors/capasitors on each board.
4. You DOUBLE-CLICK on the Board1 (Row 1) and it will subtract all part numbers under that board from the total.

This is how I saw you could accomplish your goal of keeping track of parts in stock.

What if you double click on a Board Number and a prompt will ask how many cards you are taking parts out for. See the attached with this new feature.

Double Click on row 1 and see that card's parts subtracted from the In Stock numbers. I think this is a GREAT solution for your problem. You will need to fill in the number of stock parts are in each card.

Hi Vancefish,

I understand the accountant's concern. S/he needs to document each transaction so it can be recreated or undone if needed. What happens if you claim 20 boards are made and you put in the wrong board number or it was only 17 boards? Reconcileing is a big part of acconting and my macro doesn't do this well.

A few questions for you.
1. Does a date field need to be included in the process?
2. How do you add supplier parts to the total inventory?
3. What if a board changes and you need to add a resistor or chip and document old boards after the update?
4. How do you undo a wrong number of boards you've already subtracted?

The questions above depend on how your work processes work. Do you get resistor or chip parts and simply add them to the box of chips? How would you do this process by hand? Should the Excel process be a similar process?

Do you use this data to determine when to order new parts? Do you need a trend line of individual parts inventory?

How many times a day do you subtract board parts from the big inventory. Is it 2 ot 200? What is the process?

How many different boards do you need to have a list of parts for?

As for the Macro -
It is behind the worksheet because it is an EVENT macro. You should set a breakpoint and step through it to see how it works.

What would happen if evertime you subracted parts by double clicking on a board number it did this...
After subtracting the parts it would copy the total inventory to a new sheet (in a new column) with a Time and Date Stamp at the top along with a Board Name and number you of boards you specified? This might make the accountant happy.

As I think about this problem - I think you should transpose your data.
Column 1 = Board Name
Column 2 = Number of boards
Column 3 = Date
Column 4 = Part1
Column 5 = Part2

In the top two rows you would have your current total inventory.

Starting in Row 4 you would have individual board names along with number of parts needed for each.

On a separate sheet or perhaps below the last board you would start documenting the date and time and number of boards subtracted along with a copy of row 2. This would then show the declining of parts in total inventory.

I hope this makes sense. Please answer some of the above questions if you can so we can get you a GREAT answer instead of just a first concept test.

Hi,

See the attached with a better way to look at the data. This might keep the accountant happy.
Once Again - double click on the Board Number to activate the macro behind sheet 1.
I've added an Order / Invoice number. The total of all parts will show below the boards. It will add to the bottom an accounting of how many parts were used for that board's order.

If I did this for myself, I'd put a date after each order and do a pivot table on it.

Hi Vancefish,

If you create a new order called "Stock In" or some name like that and put positive numbers in that row, they will be reflected in the "In Stock" number. This is how to add parts to the stock.

If you get a completely new part, you should create a new column with that part / box number and start using it from the beginning. You will need to copy the row formula from the last In Stock Column to this new column.

You should be able to copy and Paste Special using Transpose to convert your parts data.

See
http://www.techonthenet.com/excel/fo.../transpose.php

I am working on that aspect now.

Issue I have is adding a couple of extra inactive rows across the top, while changing which rows are active "Board" rows.

My boss wants (and currently has in the old inventory) a category row (Resistors, Diodes, etc), as well as a physical location row (Shelf A1-J9) of each part. So, I've been messing with the Macro trying to add two inactive lines on the top, without messing up the current functions. I actually messed it up once, having to start over to recover function.

If we can get those two inactive lines on top, one for part category such as "Resistor". Plus a second inactive row where I can write the physical location address like A1 or F12 for each part. I think I'd be set!

Thanks

It seams the reason we were running parts down the side was, how many parts we have. I've been eliminating parts we no longer carry, reorganizing and confirming counts. I've made it through most of the input, (and cleanup). (Alarms, Batteries, Caps, Diodes, fuses, IC chips,). Now I'm part way through Resistors.

Issue is, I've come to column "I.V.". (The last available column?) I still need about 60 more parts columns. I thought this would continue through ZZ.

I know sheets can be linked. However I do not know how to do that, OR if the macro would need to be (or even could be?) linked to the second sheet?

I thought by separating my co-workers "hardware" into it's own inventory, I'd be able to fit this all in. (I didn't know it would cut off at IV).

At this point I'm stuck. I'm thinking I can continue on a new copy of the sheet (unlinked) then after we figure out this new issue I can easily copy it over. (no missing, or no longer used parts in the mix).

I tried re-starting excel, and adding columns before I.V. , but all that did was moved everything to the right, eliminating I.V. and making I.U. into the new I.V.

Hi Vancefish,

Excel 2007 and 2010 have a larger max for rows and columns. If you could start using 2010 on your 2003 data that might let you use more columns. When you are done trimming things down you could then again save as .xls.

So, what your saying is,.. I need to find a computer with a registered, working copy of Excel 2007/2010, transfer the data to said PC, use that PC to add more columns (and fill them in), transfer the data back to my PC after filling those columns, THEN I might have more columns in my PC? Maybe?

Also, I've already BEEN trimming the fat. I'm up to 256 parts, and need about 60 more. Nothing already added can be removed, because it exists. What I'm removing is the stuff that we used up, but was still showing as stock.

I'm starting to think it'd be easier to spend my own money to buy inventory software! I can barely afford to eat, but at least I wouldn't be spending 7 hours a day trying to build this stupid system with this faulty, overcomplicated software! I NEED to build an order of cards, It's due in two weeks. YET I've spent the last two weeks doing nothing but THIS! It's starting to look like I'll have to start over,.. AGAIN.

Basically, I'm floating down the river, headed for the falls. If I don't get this done, My boss is going to freak out. Yesterday morning ALL I needed was 68 more columns and a half hour. Now I need another week, another PC, another version of Excel, or just blow it all off and loose my job! WTF???

So, I can't link a second sheet to this one and just continue on another page?

The Software DOES end at 256 columns? If so WHY are we using columns instead of rows like before? We KNOW there are enough rows, because it all fit (including hardware) in rows. (256 columns and 65,536 rows)

I'm sorry to be flustered, but like I said I have MONTHS of counting, WEEKS AND WEEKS of input, and study, and as of today, I feel I'll need to blow it off and start over after I build this order. (thus screwing up the inventory again).

Has electronics inventory always been such a HUGE pain? Has nobody ever done this??? It seems simple enough, you buy parts, build a product FROM said parts, thus needing THIS type of inventory software to remove the parts, converting them to end product (which the inventory does not need to do in my situation).

I have a 26 mile commute. Since I CAN'T actually DO anything until I figure this out. I'm loosing hours and NOT going in. I can stair at this and (and yell and scream at it) right here in the comfort of my own un-paying home, without wasting gas. Instead of just yelling and staring at it there.

ONLY a registered, working copy of Excel 2010 (not Student) has more then 256 columns. (according to a Google search)

Basically, if I can't link pages, All this was time wasted. I will not be getting more columns, and can't afford Excel 2010 ($239.99 for business version). So, I'll either need to build a second sheet where I RE-enter the invoice and number built in a second inventory. (thus having two inventories, with two inputs of orders). OR I can try to build it vertically with a macro that runs the other way. (still voiding all this last two weeks work). AFTER I build this order.

Problem is, trying to pawn this TWO inventory system off on my boss. (He WILL NOT be happy!)

This sure is fun!

Hey Vancefish,

I needed to check my thinking about max columns in 2003 vs 2007. Here is what I've found.
http://www.rivier.edu/staff/acallaha...7excel/max.htm

I had no idea you had so many boxes of stock parts until yesterday's post.

Microsoft Access may be more suited to a large company inventory control. The problem is the learning curve is very steep and is more suited to a team of people, all who build or use the data. Excel is the standard, easy to learn and use and can be used for many different problems, once learned.

I believe I suggested a Copy and Paste using TRANSPOSE a few posts ago. Did you ever examine this feature. It may allow you to convert back and forth between Rows and Columns arrangements of data.

It looks like I've given you 3 different possible sample files to work with and you haven't shown me any samples of what you are really working with. Perhaps when you ask another question you could supply a sample attached spreadsheet so we could get on the same page earlier.

I'm sorry if my suggestions aren't working out for your problem.

Maybe one of the smart guru's can jump in on this one and help.

Again,

Microsoft has a site for free exchange of templates. See Excel templates with Inventory in their descripton at:
http://office.microsoft.com/en-us/te...ventory&av=zxl

You asked if two sheets can be "linked" so you can expand your number of columns. Anything is possible (almost) in excel. It just gets harder to do things when this happens. Why don't you have a sheet for resistors, another for capasitors, another for transistors, etc. BUT BEFORE YOU DO THIS, test it with a small sample workbook. See if it works for you and then start updating your data.

It sounds like you have spent a majority of your time focused on inputting stocking numbers and less seeing if your input will result in a bigger and better method or answer.

I am a manufacturing, electronics technician. Not an inventory/admin person. I have no experience with Excel other then a month in Business school 19 years ago.

I'm sorry you didn't get the number of parts in my inventory earlier. However 90 different parts per card TIMES 8 cards. Equals 720 differing parts. With about 30% being duplicates (in more then one card), we have about 500 different parts in stock. I'd planned to separate SMT parts, from Through hole parts with separate inventories.

However I think I'll need to either spend money, or start over.

I don't know how to show you what I've done. (Excel illiterate) Our old inventory was repetitive, had unneeded, excessive data that the new inventory did not need copied.

For example in the old inventory we had TWO separate boxes with a part NAME and a second with a part DESCRIPTION. Issue was almost every part had the same thing in both boxes, with excess included in each box. Such as ['R 100k OHM 1/4W 5% CERAMIC]. Instead of ['100K 5% 1/4W C]. By eliminating the excessive crap, I could fit more info in a tiny, unexpanded box.

PLUS I was entering which parts are used in each board as I went. (which IS why I'm doing this).

I don't think Excel has the ability to correct those things OR clean it all up like I did. Which is why I did it manually. I was able to transfer the data at 1 part per 1 min. with corrections, clean up, and parts used on each card.

Currently
Row 1 is Part Category such as 'Resistor.
Row 2 is Physical location such as, 'A1
Row 3 is Part Name/Desc. Such as '100k 5% 1/4w c

The rest is YOUR spread sheet filled in with MY data, and shifted down two lines (YES the macro is shifted too). I DID test it MANY times. my only issue is running out of columns.

Suggestion -

Continue adding parts on a second sheet. We'll rewrite the macro to jump to the second sheet and subtract card parts from the entire stock number.

There is no "official way" to "link worksheets" but adding on the macro might work.

To attach a file, so we can see what you have, you need to click on the "Go Advanced" below the message area and then on the Paper Clip Icon above the message area. This will open a window to allow you to select a file to attach.

I was able to copy and paste the entire thing to a new 2007 Excel spread sheet. :-)

Issue is, I still do not know how to install the Macro into a spread sheet. For now, I can continue to install numbers into the new 2007 spread sheet. After I learn how to either install the macro into it, or get a working 2007 expanded version I can complete this.

Again, sorry about yesterday, and Thank you for all your help.

Hi Vancefish,

I opened your attached file and did a copy all the data on sheet 1 and Pasted it TRANSPOSES onto sheet 2. It took about 5 keystrokes. see attached.

Then I created an Autofilter on the data in Sheet 2. The idea is that you can click in Row 1 and filter to see only parts that are used by a single board. You simply uncheck the zero for number of parts for that board.
This might be useful in seeing which parts go with each board and not having to view all those zeros.

Now that I have a sample of your data, putting it in Rows (like sheet2) has some advantages. What is lost is the ability to track the dates and orders that decrease the parts.

I think Excel can handle it but there are many ways to deal with this problem. What question does the boss ask. How many parts are left? When did we go negative? Which board used those parts by date?

It is very useful to see a sample of what you are working with.

I wrote code for my sample data but what exactly do you want with what you have now. Do you want it to work on sheet 1 or sheet 2?

Hi Vancefish,

You need to attach what you have so I can see what you are talking about.

Here you go - Put the code in "Even Newer" and also did Sheet2

Fabulous! Thank you. I just wrote this below. (I'll leave it).

As you can see. I was able to continue past I.V. using Excel 2007. Issue is I still don't understand how the macro is installed into an Excel spread sheet. So, as I said a few posts ago I've just continued in this new spread sheet which does not have your Inventory reduction Macro installed.

As I said earlier today, I like the second sheet you created allowing you to look at only board specific parts. (Fabulous idea!) I did not check to see if it was linked to reduce both Page 1 and Page 2, From page 1. (Which I should have done before leaving work).

Issue is getting the reducing Macro to work on the extended column 2007 Excel spread sheet. I've continued well past the point 2003 stopped (I.V. / 256 columns). Which all the entered new stuff, is very easy to copy and paste (being cleaned up and verified).

When I left work tonight I only had about 20-30 more resistors to enter (I think). Then maybe a few other things to finish it off.

I again am sorry I do not understand. How exactly are you attaching these macro's to a cell? My accountant showed me how to access your macro via entering 0 as the number of cards. Then analyzing? I think? However how you attached that to the box in the first place is beyond my knowledge. Maybe if I understood HOW that was done. I could better understand this. (The only Macro's I ever wrote were to spam Local chat in Mos Eisley courtyard for Merchant/guild promotion in SWG.)

OK Vancefish,

Find attached with a variable for the number of columns. Look at the vba code behind Sheet1 to see the new code. Hope this does it for you.

It may be time to start learning more about Excel and VBA so you can understand how Transpose, AutoFilters and VBA code fit into Excel.