Inventory Management?

[yarnaholic1963]

Hello! I know very little about Excel, but I'm wondering if it's
possible to set up a very simple inventory managment "program" so that
I'm able to adjust the amount of a particular item on hand. I know I
can set it up so that column a = our new inventory, column b = our
existing inventory and then column c would = the new total inventory...
the only problem is that then I would need for the numbers in column c
to jump over to column b to be our new existing inventory... I have no
idea if this is making sense, but it seems like a pretty simple
calculation that I can't seem to figure out! Any help is greatly
appreciated!

BTW, I'm using Excel 97.

 

[Arvi Laanemets]

Hi

Let me explain it by a simple example.

Create a workbook with 4 sheets: Transactions, Store, Articles, Accounts.

On sheet Accounts, create a table
AccountName, Account

Account determines the type of transaction. And when determined wisely, they
are great help when you want to greate various reports later. For my
example, I created 3 accounts:
StartInventory 1000
Purchase 1001
Selling 2001


Both AccountName and Account columns (A:B) are formatted as text. In my
example, 1000 ... 1999 are income accounts, and 2000 ... 2999 are outgive
accounts.

Create a dynamic named range Accounts
=OFFSET(Accounts!$A$1,1,,COUNTA(Accounts!$A:$A)-1,2)

On sheet Articles, create a table
ArticleName, Article, Price

Article determine items in your store. Here in my example all articles have
fixed price. In real life probably you have to add a price timetable as 5th
sheet, where prices for every article are entered together with date, the
price was set (then the price in Article table will be abundant).
Both ArticleName and Article columns are formatted as text too.

Enter some articles and their prices into table.

Create named range Articles
=OFFSET(Articles!$A$1,1,,COUNTA(Articles!$A:$A)-1,3)

On sheet Store, create a table
ArticleName, Article, amount, Value

Into A2, enter the formula
=IF(Articles!A2="";"";Articles!A2)
Into B2, enter the formula
=IF(A2="";"";"" & Articles!B2)
To formulas in C22 we will return later.

Really you can do without Store sheet - you can add columns Amount and Value
to Articles table, but it may give you some troubles later, when you decide
you need more complex application.

On sheet Transactions, create a table
Date, ArticleName, AccountName, Amount, Value, Article, Account

Define named ranges
TADate=OFFSET(Transactions!$A$1,1,,COUNT(Transactions!$A:$A),1)
TAAmount=OFFSET(Transactions!$D$1,1,,COUNT(Transactions!$A:$A),1)
TAValue=OFFSET(Transactions!$E$1,1,,COUNT(Transactions!$A:$A),1)
TAArticle=OFFSET(Transactions!$F$1,1,,COUNT(Transactions!$A:$A),1)
TAAccount=OFFSET(Transactions!$G$1,1,,COUNT(Transactions!$A:$A),1)

Select the range B2:B# (where # is row number big enough for your needs at
start), and apply data validation list to it with source
=INDEX(Articles,,1)
Select the range C2:C#, and apply data validation list to it with source
=INDEX(Accounts,,1)

Into cell E2 enter the formula
=IF(OR(A2="",B2="",D2=""),"",VLOOKUP(B2,Articles,3,0)*D2)
Into F2
=IF(OR(A2="",B2=""),"","" & VLOOKUP(B2,Articles,2,0))
Into G2
=IF(OR(A2="",C2=""),"","" & VLOOKUP(C2,Accounts,2,0))

Copy range E2:G2 down to row #

Back to sheet Store. Into cell C2 enter the formula
=IF(A2="","",SUMPRODUCT(--(TAArticle=B2),--(TAAccount>="1000"),--(TAAccount<
"2000"),TAAmount)-SUMPRODUCT(--(TAArticle=B2),--(TAAccount>="2000"),--(TAAcc
ount<"3000"),TAAmount))
Into cell D2
=IF(A2="","",SUMPRODUCT(--(TAArticle=B2),--(TAAccount>="1000"),--(TAAccount<
"2000"),TAValue)-SUMPRODUCT(--(TAArticle=B2),--(TAAccount>="2000"),--(TAAcco
unt<"3000"),TAValue))

Copy rows A22 down for some reasonable amount of rows (at least as much as
you have articles in Articles table.

My example is done. From here on you'll be probably able to design various
report sheets yourself, p.e. where you get listed all income and outcome
transactions for given month, etc.


Arvi Laanemets


"yarnaholic1963"