Wasted Inventory Tracker

[sgtpsychosis]

Right now I am tracking all the produce inventory my store throws away, along with the cost of those items, in Excel. I then use this information to produce a report on what we have thrown away each day or each week. While this is very simple, I have long terms ideas for this data (identifying trends of when certain produce is not selling, seeing how high or low produce is priced when it is thrown away, and comparing how much we have sold to how much is thrown away, to name a few) and Excel will not meet my goals.


Additionally, data entry is a slow process. Why? PRICES. We have a 28 page list, and when a user wants to enter the information they have to look through the inventory list to find the price of that item THAT WEEK. Fortunately, most of our products have set prices. My first, biggest question is this:
Can a create a list (PRICE WEEK 1) and populate it with all our inventory, and give it a price THEN the next week duplicate that list as (PRICE WEEK 2) and change only those items that need to be updated.
In this, I am hoping that I can show that on week 1 I threw away 10 pounds of celery at $1 a pound, but in week 2 I threw away 6 pounds at $1.05 a pound. It would be very important that when I create the new (PRICE WEEK) list that new data refers to this new list, and not "last weeks."

And I have no problem adapting another persons pre-existing database to my needs if one is offered! I have been looking online, but so far I am only finding DBs that track inventory, NOT any that track trash!

Any help would be greatly appreciated,

Tim

[orange]

Tim,

Work through this older tutorial from RogersAccessLibrary. It will take 45 minutes to an hour. You have to work through it but you will learn the basics of database design. That's a critical first step.

You can adapt his description to suit your situation once you understand the basic design process.
Focus on what you are trying to do (in plain English), not how to do something not designed in Access.

Don't try to jump into physical Access from Excel without reviewing database concepts.

Tutorial 1 http://www.rogersaccesslibrary.com/T...lationship.zip

(a second tutorial)
http://www.rogersaccesslibrary.com/T...nformation.zip

Good luck.

Some thoughts to consider: (if I have understood)

Think of the weekly trash as an Order (or TruckLoad for shipment??)
You have weekly Orders
Every Order has an OrderDate
Orders consist of 1 or more line items (product/produce being thrown out)
Each LineItem has a Quantity and CurrentPrice (store these with the line item)
Each weekly Order has a Cost ( for each LineItem you have Quantity and CurrentPrice; Sum all line items for the Order to get OrderCost via query)

[Micron]

Agree 100% with the principles Orange has put forth. The only thing I might suggest is that in tblDisposedProduct, the DisposedPrice may not be the current price. It depends on your process. I'd consider the current price to be exactly that, while the price at disposal could be what you initially offered it for, which could be different. In fact, consider that there's an opportunity to capture both the cost to dispose and the lost profit. The lost profit would be the price at time of display times the units disposed. The disposal cost would be what you paid for the item (your cost) times the number of disposed units. From this, you could know the cost to dispose (for which you could include disposal cartage fees if you're paying by the ton) versus the loss of potential profit.

[orange]

Just a point of clarification after reading Micron's response.

My recommendation was to record the current price of the Produce as a field in the LineItem record. In effect, you want the Price of the Produce/Product at the time it was disposed/scrapped; and you want to record that in the LineItem record.

Your Product/Produce price can change from week to week as you noted, so you don't want to rely on the
Price of the Product/Produce from the Product/produce table in calculations.

see my comments on AgreedTo/AgreedUpon Price here.

Good luck.

[Micron]

I did understand the notion of having the price as a line item in a record and agree with the idea. As usual, you have great insight.

you want the Price of the Produce/Product at the time it was disposed/scrapped;

Maybe not. What if the price of the product at the time of disposal was more or less than the current price on the shelf? Would you calculate the loss as a function of quantity * sale price before you removed it for disposal, or use the current price? Not saying you are wrong, just that the OP has to decide what's right for the tool being created. It might depend on whether or not the desired information was to know its value versus its cost, which is why I think having both might be a good way to approach it. Easier to incorporate both now than try to add one later.

[orange]

Micron,
My concern was that I may have used Current Price in a manner that was confusing to the poster.

I agree with your comments. It seems a lot of things depend on exactly what he wants/needs.
I understood the issue to be related to accounting for the wastage and to quantify that wastage for management.
Since it related to Produce -which I would think has a limited shelf life - my first reaction was:
-how much of this Produce do we throw out/scrap
-how much did it cost (money spent but produce is wastage)
-how often do we throw this away
-what are the cumulative costs associated with this waste

I could see certain corrective action(s):
- on average we waste 20% of Produce X, let's buy 20% less and see it it changes our habits/sales/waste
- our average monthly costs associated with waste are $$$, let's review our processes to save money
- we will change our buying processes to buy less of articles of which we waste more than 15%...
- we direct wasted produce to animal feed/farms..

If my understanding is at all on target, then I think the costs are associated with money spent to purchase the Produce. Again it depends on what the OP is trying to do. He may take the higher of purchase price or current selling price if he wants to "enhance" the argument.

Again I agree, easier to design and collect facts now that may be useful to the analysis, than to wish you had done so after the fact.

You make good points - as always.