Managing Inventory with Excel

Brass Contributor

Hello.  I have a small inventory that I'd like to manage with Excel, but am looking for some help with running totals.  I have a starting inventory, and would like to subtract the number of inventory to come up with a balance.  This is easy to do; however, I would like to have a running balance as I use the product.  For example:  I have 1000 parts in A1, I have 100 parts used in A2, with a balance of 900

(A1-A2) parts in A3.  I then use an additional 200 parts.  I want to be able to enter 200 in A2, to come up with a new balance of 700 parts.  How do I tell my balance column to stay at 900 when I add a new number in my parts used column?

7 Replies

@Sue_G 

I'd restructured data as this

image.png

with formula in C2

=N(C1)+A2-B2

and drag it down

@Sergei Baklan 

Hi Sergei,

What I'm looking to do is not use more than one line.  I would have my starting inventory (A1), amount used (A2), balance (A3).  I want to be able to have Excel take the current balance in A3 and keep it, then subtract the new amount used and give me the new balance in A3.  As an example, using 1,000 as starting balanced in A1, used parts in A2 is 200, balance in A3 is 800.  Then I use an additional 100 parts, so I want to enter 100 parts in A2, and A3 would have a new balance of 700.  Is there a way to do this?

 

 
 

@Sue_G 

Not sure, even with Visual basic programming. Assume you have 200 in A2 and balance 800. Next, you type in A2 100 on place of 200. Macro takes current balance 800 in A3, deduct 100 from it and put into A3 700. Fine.

 

Now assume you make a misprint, it shall be 10 in A2, not 100. You type 10 in A2, macro takes current 700 and deduct 10, balance is 690. Instead of desired 790.

@Sue_G 

I recommend that you adhere to the suggestion of @Sergei Baklan. Parts are classified under the line item "Raw Materials Inventory". The policy you wish to adopt is a flagrant violation of International Financial Reporting Standards (IFRS) and US Generally Accepted Accounting Principles (GAAP) because you deliberately conceal the data regarding the value of parts acquired and used during a given period. Such parts, whether acquired (or used) during a given period or unused as at a given date, must be presented in the financial statements (or disclosed in the notes thereto). Otherwise, such a concealment is tantamount to a material misstatement that warrants an adverse opinion on the financial statements. 

@Twifoo 

 

Thank you Twifoo, but this has nothing to do with our Financial Reporting or our Accounting Principles.  This is only for tracking of labels that are used in my department and to have a quick reference for reordering.  I do not work in Finance or Accounting, or Inventory.

@Sue_G 

Regardless of your intention, the golden rule is that data must be preserved to satisfy present and future needs. Erasing or concealing data is nonsense. You may find no use for it now but you may find it useful later. Whether in corporate or personal work, data must be preserved always. No reason is acceptable!

@Twifoo 

 

Again, Twifoo, I am only using this as a counting mechanism.  It is not intended to be an actual Inventory tracking function.  There is no need for you to explain to me why I shouldn't be using Excel in this manner; nor is there a need for you to berate me.  I would appreciate it if you would kindly keep your comments to yourself.  Thank you.