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

 

[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

 

[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

 

[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

 

[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?