tracking inventory

[Michelle]

Is there an easier way to update inventory on a daily basis using Excel from
an invoice? (I am using office/excel 2002)

I have read up on VLOOKUP but can't get my head around it. And it seems I
would have to create a formula for every field/item etc.

Up to now I have a price page that I copy and paste items into an invoice,
which has a blank column I fill in the qty and gets totalled.
I would like the inventory to be adjusted down each time (simply). I
currently have all information on seperate worksheets (ie price pages,
inventory, invoice).
Your help is greatly appreciated.
Michelle

 

[smartin]

Is there an easier way to update inventory on a daily basis using Excel from
an invoice? (I am using office/excel 2002)

I have read up on VLOOKUP but can't get my head around it. And it seems I
would have to create a formula for every field/item etc.

Up to now I have a price page that I copy and paste items into an invoice,
which has a blank column I fill in the qty and gets totalled.
I would like the inventory to be adjusted down each time (simply). I
currently have all information on seperate worksheets (ie price pages,
inventory, invoice).
Your help is greatly appreciated.

Hi Michelle,

You are off to a good start keeping things on separate worksheets. If I
were keeping track of inventory, I would have a master "catalog" page
that lists all the items, one per row, starting inventory counts, and a
/calculated/ current inventory number.

Calculated from what, you might ask? Well, the transaction history. I'm
not sure you are capturing this. This would be another worksheet where
you keep track of invoice#/po# and date, item id, quantity ordered,
price, etc., in an ever-growing list. Maybe (but just maybe) your
invoice page(s) capture this, but I can't tell from your description.
Even if you have this kind of information stored per-invoice, it will be
difficult to accumulate if it's not all in one nice table.

You mentioned copy/pasting from the price page and pains with VLOOKUP.
To be successful with this project I you will need to be comfortable
with VLOOKUP, SUMIF, COUNTIF, and likely a few other handy ways to grab
and summarize data in various ways. The effort you put into these basic
functions now will pay dividends later when you see how much automation
can be achieved.

Hope this gives you some ideas. Write back if you have any questions.

Good luck!

 

[Michelle]

Thanks for your input.
I have seperate worksheets for each supplier with their products.
Are you saying you would prefer to see all items/suppliers in one master
inventory worksheet? We have approx. 125 items

We don't keep track of "detailed" transctions, i.e. we just keep a sales
summary in another workbook that lists, date/cust/invoice#/amt of
invoice/blank col to check off when paid.
I do monthly and qrtly summary and a final at end of year.
How would you structure the master inventory list to take advantage of all
the benefits of excel?
I have read about pivot tables and combo boxes all sound great, with tons of
options availa for sales analysis.
thnx again

 

[smartin]

Well there is something of a dichotomy here, at least for me. At one
extreme I think of this project and how I would approach it from the
ground up, applying what I think I know of data modeling and so on to
build an application that serves the requirements both in the present
and the future.

On the other hand, you have something that works to a respectable
degree, and scrapping everything to start over for the sake of a perhaps
smallish future gain may well not be cost-effective.

So let's see if a balance can be had? Let me put this question to you:
Your original question was how to maintain inventory numbers. In your
sales records, are you keeping tallies on quantities of items sold/shipped?

To your other questions, here are some things to think about. I'm not
challenging your approach, just offering views on the situation.

Product/inventory list: why separate products on worksheets? A product
is a product is a product. /One/ distinguishing factor is the supplier,
but so are description, cost, margin, size, weight, etc. Supplier is
just one more column (or "dimension", as we might say) to add. The
advantage of having all the products in one place is having /one/ list
to update and /one/ list to consult when you are querying for a price.
And, lest I fail to mention, /one/ place to keep track of inventories.

Transactional data: This gets down the the fundamental level of doing
business and so is a mainstay. The transaction record says we sold x
quantity of product y at price p on date d to customer c on invoice i.
From this detail a lot of information can be deduced: How many product
y were sold this year? What was the revenue? What was the value of
invoice i? How much did customer c purchase this year versus last year?
What products were profit leaders this year? Last year? And, knowing how
many y were reduced from our inventory is pivotal to knowing what
remains... offset of course by a similar transactional log of receipts.

I am officially rambling now so will sign off... hope this helps!

 

[Michelle]

Smartin;
I am trying to re-structure (play) with a new system before our new fiscal
year May 1st., so I have some time to set this up.

Our sales records do not show a tally of items qty's etc., just a lump sum
total for the invoice.
I understand how a detailed summary would give us more information to play
with.
That is my next of many steps. Is this what is called data tracking add-in?
This is why I am enquiring, because of the need to know more.

You are right the seperate sheets for inventory are for different suppliers.
It would be no big deal to put all on one worksheet with an extra column for
suppliers.

How would I be able to write up an invoice and have that "Transactional
data" automatically transferred to a detailed report/worksheet? without
re-entering all the information, ie. cust/prod sold/qty/value etc? AS WELL AS
reducing the inventory level?


any thoughts?
Thanks again,
Michelle

 

[smartin]

Hi Michelle,

I am not familiar with "data tracking add-in", but it might be a synonym
for something else.

Invoices could (and in the purest sense should) be built from the
transactional data. Not the other way around. Plus, the transactional
data can ideally take care of adjusting inventory stats and other
reporting needs.

If you like, send me a mock-up of your data.

 

[Michelle]

I think that trying to fix something is sometimes more involved than just
starting from scratch. I have all the data, it would be simple to
re-configure properly.

So if I understand you correctly, you would have 1 worksheet with all
product info price/size/supplier etc. including a starting inventory balance.
Another sheet would be an invoice template, that gets printed.
Another sheet would be all invoice details recorded on a masterlist which
would give us our transactional details from which we could do all kinds of
analysis.

If I setup a product list (approx 125) with all the required columns,
including starting inventory, on 1 worksheet, and an invoice template on
another sheet how would I call up a product from the master list to the
invoice and have the inventory adjust itself?
Then how would I record the details of that invoice onto the master
"transactional data" list?

Thanks,
Michelle

 

[smartin]

Sorry, more questions before answers... I guess this is what we call
"requirements gathering".

Something I should have asked earlier: How important is it that you
capture customer information? Would you like to be able to look back on
your annual sales and see who is buying what and how much?

How do you envision the work flow to complete a sale? Would you rather
(a) enter all the invoice information and expect the data is pushed to
the transactional record, or (b) would it be OK to enter the transaction
details first (possibly including setting up a customer /before/ the
sale can be recorded) and then generate an invoice? There are pros,
cons, and varying levels of complexity on both sides, but nothing
impossible. You may have something else in mind too.

I do think you are on the right track! To your questions about calling
up details and adjusting inventory, these should be relatively easy
tasks once the broader structure of the application is defined. We will
most likely use combination of vlookup, sumif, countif, index, match,
etc. No worries -- you will get up to speed with these quickly. The hard
part is what we are going through now.

 

[Michelle]

This project is like renovating my old house, since we are there "you might
as well", by that I mean, initally I just wanted to keep track of inventory
because our office is not in the same location as the warehouse, and
sometimes the manual entries are not done after picking an order.
But with a good database properly setup, yes we would like to be able to see
who is buying what and how much.
We did a physical count end of January and are reviewing slow movers and
adjusting inventory levels to improve cash flow.
We are finding that we have too much of some items and very low stock on
other items.

As for work flow;
being sales oriented we consider the invoice the main character, with the
the other data being the supporting actors, or your suggestion a), whereby
after filling in the invoice other things happen in the background.
I have been looking around the net at some accounting packages and I have
been looking at their modules to see how they are set up, and I am seeing the
light. One system was based on excel, we don't really want an accounting
package per se.

I have already started a test work book with 4 sheets so far, based on what
I have seen.
I have all the products on one sheet with all information and a blank column
for qty, the other sheet is our invoice, and I have re-structered our
customer list so that the information is set up in rows, i.e. company
name/adress/city/ etc. (A1,B1,C1,D1...) Previously they were blocked
vertically as we just did a copy and paste entry.
Having done this, I see how (conceptually) vlookup works.

The last sheet is sales, with columns titled date/sold to/invoice #/ invoice
amt/paid,
the paid column would be checked off when payment comes in. Our type of
sales are usually paid in full no partial payments. At a glance or with some
magic formula we could use this as our accts rcvble.
The one thing I can't see on this page are the details of the invoice, that
is, all the products sold. Some small orders have between 3-5 items and
others can be upto 20 items.
Should I stop and wait for your input? Business is slow so I have time to
play with this.

 

[Michelle]

Just to expand on the last note about the sales sheet; would I have to have
125 columns across the top??to enter all the details of the invoices?

 

[smartin]

Sounds like you are making great strides! As you learned, to set up the
customer table effectively you should have one row per customer, with
the various details in columns.

The sales table will be similar. Since you want the invoice to drive the
sales table we will break tradition somewhat, and end up putting more
information in the sales table than we might if we were building this in
a proper database*. It might look like this:

I# IDate Cust# Item Qty Price ExtPrice
1000 1/1/09 100 Q102 10 5.50 55.50
1000 1/1/09 100 R600 4 12.00 48.00
1000 1/1/09 100 SHIP 1 10.00 10.00
1000 1/1/09 100 TAX 1 9.00 9.00
2001 1/9/09 115 Q102 100 5.50 550.00
2001 1/9/09 115 SHIP 0 25.00 0.00
2001 1/9/09 115 TAX 1 15.00 15.00

This is what two invoice's worth of data might look like. Invoice #1000
is for customer #100. Two items were sold, plus I mocked up a way to
include non-inventory data like shipping charges and tax. (This is
important if you want to recreate an invoice with correct totals, but
will not affect your inventory counts.)

Notice how you can sum column "Qty" to determine the count of a given
product sold (using SUMIF or SUMPRODUCT to limit the count to something
in "Item", perhaps within a date range). This is only the beginning of
what this table can tell you. Doubtless there will be other fields that
should be put in here to make summarizing certain things easier. Offhand
I'm thinking an "inventory/noninventory" flag and a single field to
represent invoice+date+customer. Don't get hung up on that though.

A hurdle will be how to get this data into the table. Since you want the
invoice as the driver, we will need to come up with a way to "push"
invoice details into this table, which is not something Excel usually
does. This will likely require some VBA code, but this is certainly not
a show-stopper.

Now you mentioned being able to check off invoices as payments come in.
This presents a new way of looking at the data, where you probably want
one row per invoice (a path I think you were already traveling). The
difference here is this table will not have detail sales, just a
summary. Some or all of this data can be pushed from the invoice entry
as well, or derived from the sales table.

*In the interest of full disclosure, this project is taking the shape of
a full-blown database application. Like you say, it's like renovating an
old house... as long as you're tearing out the plumbing, why not replace
the wiring too? Just food for thought. Access is well-equipped to tackle
this sort of thing, but there is a certain up-front price you pay in the
development effort (more so if you need to learn about Access along the
way.)

Keep us apprised!