Inventory Assistance

Anonymous · Jul 1st 2004

The "unit cost" field is there for the order pivot table to be able to show as a row field. When you include it in a data field, as you did in the AOM picture, it will be summed as many times as it appears in the database for the page and row specifcations. Move it to the Row area after units and you will see the correct prices.

EDR · Jul 1st 2004

Derk:

Thank you again! You are brilliant. This spreadsheet is going to work wonderfully, and is ultimately going to make my life much easier. :rock:

Quote from Derk

The "unit cost" field is there for the order pivot table to be able to show as a row field. When you include it in a data field, as you did in the AOM picture, it will be summed as many times as it appears in the database for the page and row specifcations. Move it to the Row area after units and you will see the correct prices.

Yes, I played with the "layout" of the pivot table for some time to get the "flow" of info from left to right, as the AOM and others are accustomed to looking at it. If we are limited by the pivot table options then so be it.

Nevertheless, I would much more prefer that on the AOM page the unit cost shows up in the very last column. If there's a way to do this that is beyond my scope of knowledge, one more piece of guidance would be appreciated. The AOM person is used to looking at the data from left to right as Item#, Description, Unit, On Hand, and then any prices. When I add Unit Cost to the row area it adds an extra space in between each product and forces prices to show before On Hand values, which could be confusing and cause human error for hand entering all that stuff on a weekly basis.

Also, on the order page, I tried copying the pivot table 4 or five times and choosing the different vendors, so it can be printed with the order for each vendor. No big deal, but when you refresh the tables, certain column widths get reset and then certain things can't be fully read in certain areas of some of the pivot tables. Is there a way to hard code a column width in Excel, so that even a pivot table refresh can't change it?

Thank you again for your time, and best wishes!

:thanx: EDR

Anonymous · Jul 1st 2004

Perhaps the AOM sheet should not be a pivot table at all, but something created at the same time the database is created. That way you could have things in the order you want and sorted by description. Of course then you lose the flexability of the pivot table unless a similar capability is programmed in. What "filters' would you want on the AOB sheet or is it always to have all of the products on it?

The ordr sheet can have the columns reset to some specified width after each Pivot operation or can autofit to get the needed width. Which would you prefer?

I'm pressed for time today, but should have time tomorrow to do what's needed after I receive your prefernces for the above.

EDR · Jul 2nd 2004

Hi Derk:

You are too kind! I've always enjoyed this forum since I joined over a year ago. The friendliness, prompt responses and knowledge is amazing. Certainly, it's people like you that add to that reputation. This is, by far, the longest thread I've ever engaged in, so once again, I hope I'm not taking advantage. If you feel I am, by all means, let me know - no hard feelings. And, I'm still open to compensating you if you would like.

In any case, ideally, the AOM page should be in the same format they are accustomed to reading it, which is (from left to right):

The catch is, even though item # is on the far left, the display is alphabetical by Description (or name of product). Also, it's a summary of all the areas. So even if ABSOLUTE is entered in different areas (4 different times) as .3, .5, .5, .5, only one ABSOLUTE shows on the AOM page (in alphabetical order) with an On Hand value of 1.8.

Also having on the TOP of the first page - a cell that shows the total On Hand value (so we can know the total amount of in house product, like $35,468) - would be a great number to look at. This is something I can easily do by summing the entire appropriate column. This way I know the value of my total inventory (in house).

Also, as usual, I now have an additional request (very simple I think). I need to establish a Product Code ("PC#") for each product which would only be shown on the ORDER SHEET - maybe we can define it on the vendor page? Many times when you call in the order, and give a name like "Castiglioni Chianti", there may be different variations or vintages. Thus, each vendor has their own PC# for each SPECIFIC product, which is different than the item# we use in house. Strange, I know, but it's the way it is.

So, when I reference Item# in this post, it's referring to item# as we know it now. When I reference PC# it's referring to the number the vendor uses to describe the product on their end. It's actually better to give the sales person a specific PC#, rather than a vague product name, which may have similar variations. It helps prevent them from sending the wrong product accidentally.

Now that I've had time to think more about this and watch you develop something incredible, I'm wondering if doing your original idea of entering in the vendor codes in one area (instead of on the INVENTORY sheet) would be superior (and because there's now the PC# variable).

Since the "products" may change quarterly, and the format is intact, perhaps having a page that copies that data onto another sheet automatically, adding two extra fields, one for Vendor Code "S" for Southern, etc., and one for PC# would be best? Up to you.

This way we can get rid of the "pars" column on the INVENTORY sheet and having to enter the vendor code everytime we enter ABSOLUTE (many times) on the INVENTORY page. I also noticed some anomalies occur when I accidentally placed a "Y" for ABSOLUTE in Area 1, and an "S" for ABSOLUTE in area 2. If I only have to enter it once, it will prevent human error in this regard.

As far as the ORDER SHEET, having it output a list of items that only need to be ordered if the difference from on hand is greater than ZERO would be ideal. For example, if for ABSOLUTE, the minimum par is 8, and we have 10 On Hand, I do not need to order it that week, and therefore, there is no reason to print ABSOLUTE with a ZERO on the order sheet. If it doesn't need to be ordered, it doesn't need to appear. I'm not sure if a Macro or something else is needed here, but again human error may occur when calling in an order, if a bunch of products are listed on the ORDER SHEET with ZEROS.

Finally, the ORDER SHEET format would be best as:

Once again listing the order in alphabetical order by product description is crucial, even though the PC# is to the left of it.

When you call in the order, you usually use language like:

"2 cases of product number xxxxx, ABSOLUTE VODKA"

Then they repeat it back to you, so it's easiet to read from left to right.

In a nut shell, the item# on the AOM page is the number the Accountant needs to use to enter his/her data. The PC# on the order page is the number I would use to place my order with the vendor. PC#'s are generally 5 digit like 43125. I would hand enter this for each product and an "S" or "Y" for the vendor it's associated with in some database, and the rest would happen on its own - I'm hoping.

Thank you again for everything you are doing! :thanx:

EDR · Jul 2nd 2004

Cases

RE: CASES

ORDER SHEET ISSUES ONLY:

I was going to add this to the previous post, but as usual, it was getting so long, I thought I'd save it for this one. I can live without this, doing the math in my head when calling in the order, but if this were possible, man would I love it!!!

At any time, feel free to pull out of this one, or ask for compensation (I can live with what you've provided to date). My posts previous to this thread aren't near as long-winded, but with so many issues, I feel it best to explain as best as possible.

On whatever new page you create to add the PC# and Vendor Code as per last post, perhaps one last column can be entered so the end user, myself, can enter either a 24, 12, 6, or a 0 (ZERO) to define whether a case is 12 bottles or 6 bottles or none.

Maybe the new Vendor page can be a database (feed) page with a format similar to:

[Blocked Image: http://www.natt.net/temp/case.jpg]

Some cases come in packs of 24, 12, 6 and some things we do not order by the case (0). There are no other variations in terms of the word "cases".

That being said, on the ORDER SHEET again (only the order sheet), it would be absolutely wonderful if the UNIT field could automatically change to "CS" (case) instead of "BT" (bottle), when it sees the amount to order is more than the minimum case size, whichever was defined. And, if it could round to the nearest whole number (in terms of a case), that would be asbolutely amazing.

Here's some examples:

If our total on hand for CRISTAL VODKA is 19 (case defined as "12"), and the minimum par is 48 (pars still need to be defined only on the inventory page, per each Area, and then a total sum par for each product, as the pivot tables are doing now - but we can get rid of the vendor code column on the INVENTORY page), it means I need to order 29 bottles. However, if I read to the sales person "29 bottles of ..." they may charge me for 29 bottles as individual bottles (more expensive), whereas when possible, ordering a full case of something provides discounts to the client. So in this case, ROUNDING to the closest case value (29/12 = 2.41 = 2 cases). So, on my ORDER SHEET, it would show:

The key to this math is knowing that if we order by a case (value > 0) we calculate and round accordingly, and if there's a ZERO defined for that product, we only order by the bottle.

When I would call in the above order, I would read left to right, " 2 cases of 14325 Crystal Vodka..."

Another example: the Ornellaia Cabernet only comes in a case of 6 bottles. Therefore, if that wine case value is defined as "6", if my par was 12, and I had 4 bottles On Hand, I would need to order 8 bottles. Since 8 is greater than 6, I need to order a case (6 bottles). Therefore, instead of ordering 8 individual bottles, I would calculate (8/6 = 1.33333), so I would round and order 1 case only (it's ok of the actual order amount for cases doesn't quite meet the exact par but is close).

Now there are alot of liquors we order that are not by the case. For example, COINTREAU is very expensive, and our par may be 4 total bottles in house. Even though there may be a case discount at 12 with the vendor, we would never order that many. So, if defined as zero, and we had a par of 4 w/only 1 On Hand, we'd need to order 3 bottles. If COINTREAU has a "0" next to it for "case" on the Vendor page (as shown above), we would print the "BT" for "Bottle" in he Unit field and order 3.

Another example, if Glenlivet (which we never would order as a case) had a definition of 0, and our minimum par was 6, and we had only 2 bottles (total sum) on hand, the ORDER SHEET would show:

For a final example:

If Caymus Cabernet was defined with a case value of 12, and we had a minimum par of 4, then when On Hand gets to 3 bottles (1 under par) we would order 1 case (12), and the order sheet would show (again it's OK if par is exceeded a little bit, or is slightly less based on the case issue):

I hope this makes sense?

:thanx: EDR

Anonymous · Jul 2nd 2004

Well, let's do this a bit at a time. They are all good ideas, but some will take longer than others given my time constraints. The attached has a new AOM sheet for you to review. It is rewritten by a macro whenever the Inventory button is pushed. Will you still have need for the pivot table on the old AOB sheet? If not, don't delete it as the macro assumes it is there, but it's name (and the name of the New AOB) can be changed with no harm.

I like the idea of a master product sheet that maps your product numbers to the PC#. I'll work on that next week and start revising the order sheet to remove the Pivot table and come up with an equivalent capability to select and display by vendor.

EDR · Jul 2nd 2004

Derk:

All sounds good, thank you! The new AOM page is working perfectly! Have a great weekend! See you next week sometime. EDR

EDR · Jul 3rd 2004

Derk:

I realize I've stated this before, but I can state now with absolute certainty that if we can finish the items in the previous posts, and this one, I will be FINISHED! No more requests or modifications. This post is related to the previous regarding ORDER SHEET.

By the way, the New AOM page works flawlessly! Thank you.

Now, as we know, the ORDER SHEET is what I'd like to finalize. Previous posts address this issue, but I thought I'd add the following:

Perhaps on the Vendors tab we can define some additional information:

[Blocked Image: http://www.natt.net/temp/vendor.jpg]

Then ultimately, the ORDER SHEET would be best if displayed like:

[Blocked Image: http://www.natt.net/temp/order.jpg]

Also, notice APPLE PUCKER needs to have 28 bottles ordered to make par of 36. However, since the amount to order is equal to or greater than one case (12), we round to the nearest whole number to make a case. Thus, 28/12 = 2.33 or 2 cases.

If any product case value is set to 12 and 33 bottles are needed to make par, then 2.75 cases rounded to nearest whole number is 3 cases.

The rule should be: Always round UP if total bottles to order is less than one case (as case may be defined), and always round to nearest whole number (can be up or down) if the amount of bottles to be ordered is greater than 1 case (as case may be defined).

Also, we don't *need* a pivot table to "choose" the vendor (although this is OK if easier for you), as I will normally print out any order pages with all vendors showing (that have product that need to be ordered) and call them in that way. So, as per previous post:

1. Having only those products that need to be ordered (that have an order value greater than zero) should be displayed.

2. Having only those vendors that I need to call to place any product order with should display on the sheet.

3. As per previous, orders should round to the nearest case, when a case is defined. However, if the case value is > 0, the minimum case ordered must be 1. So, for example, if ABSOLUTE has a minimum par of 12, and has only 7 bottles on hand (one only needs to order 5 bottles), which is less than a case of 12. If rounded to the nearest (0), we wouldn't order any cases, which is NOT good. We need to order at least 1 case, and even though the on Hand would exceed the par by 5 bottles, that is OK.

Via the above logic, if I do not need to order any product from Wine Warehouse this week, then there is no need to print their information or the products they carry on the ORDER SHEET (again this is the ideal functionality). If I do need to order from the vendor, having the vendor's contact information, our account #, etc., posted above (as shown above), with only the products listed that we need to order would be incredible. Also, as you did with the AOM page, the top of the order sheet should have the total dollar value of the total order, so we can know how much we are spending that week.

That's it! I promise! If the ORDER SHEET works as per last two posts, I'm done! :thanx: :thanx: :thanx: EDR

Anonymous · Jul 3rd 2004

Ok, I understand. I'm taking most of the long weekend on other things, so don't expect anything back until Tuesday. One remaining question on Pars. It now appears that Par applies only to the totality of areas, rather than a par for each room. Is this correct? If 5 rooms each have .1 bottles left could Par be 1 for the total so that only 1 bottle need be ordered? Or would you need 1 bottle per area? If the former, then we can go back to the "par" sheet in an earlier iteration and have it carry the needed info to build the order sheet from. And the inventory sheet can mostly look up data from that master sheet.

EDR · Jul 3rd 2004

Quote from Derk

Ok, I understand. I'm taking most of the long weekend on other things, so don't expect anything back until Tuesday.

No worries! Please take your time! I am simply ecstatic that you are being so kind and helpful.

Quote from Derk

One remaining question on Pars. It now appears that Par applies only to the totality of areas, rather than a par for each room. Is this correct? If 5 rooms each have .1 bottles left could Par be 1 for the total so that only 1 bottle need be ordered?.

YES!

However, there are variances, and pros and cons. Let me think them out here, so we can be aware of all issues and come up with the ideal solution.

In theory, it is crucial to cover yourself with a bottle for all rooms, assuming that each one may run out soon. Although, some bars are busier than other bars, so the chances of all 5 bottles running out at the same time is low. It could be done either way. It is important though, to establish a par for each room.

For example, in bar 1, I would hope to have .3 or more of Absolute (I would set my minimum par to .3 for that area). If it's less than that, I can assume it will run out very soon, and thus I should order 1 bottle to cover that area when it runs out. In bar 2 (or Area 2), if Absolute is .5, and my par is .3, then I'm ok, I would still only order the 1 bottle from the previous area. In theory this is the correct way to do it, but not sure how rounding and case value will be implemented if this is the logic?

Also, there is one flaw in this logic, in that if I order 5 bottles (if .1 in 5 areas), and those 5 bottles sit in the liquor room, ready to be pulled out to a specific area when it runs out --- if next week I haven't emptied any of those bottles, and I do my inventory again, I would be ordering 5 more (now 10 in the liquor room) which is NOT good!

In addition, since we're considering the case issue, it might be easier to add up the total par from all areas (rounding up to nearest whole number) for ABSOLUTE, and the total ON HAND from all areas. So, if total On Hand for ABSOLUTE adds up to 7, and I have a total in house par of 12, (and case is defined as 12), then I need to order 1 case (5 bottles rounded up to the nearest case to get the discount).

Example 2: If GRAND MARN case value=0, and Bar 1 has a par of .5, with on hand of .3., and Bar 2 has a par of .5, with on hand of .4, and Bar 3 has a par of .5, with On Hand of .1:

Total Par = 2 (1.5 rounded up to nearest whole number or bottle value, ceiling)

Total On Hand = .8

Amount to Order = 2 BT (1.2 bottles rounded up to nearest bottle amount since case=0).

In the above GRAND MARN case, I realize all three bottles are below par, but we're only ordering two. This contradicts, ordering 3 because 3 areas are below par. However, summing and comparing all areas in one master area protects us from re-ordering too many bottles, as the total par from all areas is considered, and compared to the total on hand from all areas, right?

In addition, your example of .1 x5 bottles, and only ordering 1 bottle would be an extreme scenario, although possible. It's a tough call. In that extreme scenario, I'd hope to be ordering more than one bottle. I see pros and cons for either situation, but I think the better of two evils is adding up all pars and all on hands from each area and comparing the sums, then considering the case value for each product, then rounding appropriately, then sending the value to the order sheet as a CS or a BT.

This way if there are 5 bottles extra in the Area 2 (Liquor Room), even though 5 other areas are at .1, since the total par and total on hand will be considered, I won't be ordering 5 bottles over and over every week in the event the .1 bottles don't empty that fast. I can also establish a minimum par in the Liquor Room (Area 2), so if that is the backup room with a par of 3 bottles of Grand Marn, the chances of needing 5 bottles all at once and having only 1 is very low.

Hope this makes sense. Let me know if you have any other questions.

Also, I definitely do NOT want a master par sheet, because I may not know what my total par should be for each item until I've created the different Areas (on the Inventory sheet) and get a feel for what is right for each area - also an area may change, or be added or deleted. Therefore, I'd rather have a PAR column on the INVENTORY sheet for each item I call, which feeds data to somewhere else when I click to update database button (and sums the total pars and on hands from all areas for each product).

Quote from Derk

And the inventory sheet can mostly look up data from that master sheet.

Correct. I still need to enter pars though, but Vendor Code can be deleted from Inventory page and placed somewhere else.

I'm sorry this has become a larger project, and that my posts are so long, but I figure it best to explain everything, as I know programmers hate bugs, hate vague info, and hate having to recode if not explained right the first time. As previously stated though, once we've finished this order sheet and vendor page (additional info), I'll be finished for sure (minus any bug reports, which I doubt there will be).

:thanx: EDR

KiwiSteve · Jul 5th 2004

Hi EDR

I think I have a straight-forward solution. Give me another day and it'll be done.

Cheers

Stephen

EDR · Jul 5th 2004

Clarification

Hi Derk:

I realize you will be making edits to a different version (the one on your end) and I would like to start getting some serious data entered, so the new INVENTORY page format will be as follows (as per previous posts, getting rid of vendor code, but leaving par - I hope this is OK with you?):

This way, I can paste in my data to your final version and it should work.

[Blocked Image: http://www.natt.net/temp/format.jpg]

Also, for clarification, the AOM page should always display whatever was fed to the data base from the INVENTORY page, regardless of whether the On Hand is zero, while I would prefer the ORDER SHEET to only show those things that need to be ordered. So, the AOM does not need a filter, while Order Sheet does (if OK).

I think you have the New AOM page that way now (which will be renamed AOM), but just wanted to clarify. In fact, I justed tested it now by entering ZEROS for on hand par on some of the product "On Hands" on the inventory page, the New AOM page showed the product with a zero for On Hand, and a zero Cost (PERFECT!). Didn't want any confusion there. Because of my request to only shows those products that need to be ordered on the ORDER SHEET, didn't want to confuse you with the same on the AOM sheet. So far so good. I'm going to be entering some serious data now for all the areas (hundreds of entries) for the next few days. :thanx: EDR

EDR · Jul 5th 2004

KiwiSteve:

Stephen:

I didn't see your post until after my last. Nonetheless, you are also too kind, thank you. I'm open to any solutions, but feel close to Derk in this project, and don't want to conflict or offend. However, I also know Derk is busy, so don't want to infringe on him any more either -- whoever, whatever. Thank you all! As always, an incredible forum! I'm just entering data away like a mad man over here. I'm here if you need me. Thank you all so much!

:thanx: EDR

EDR · Jul 5th 2004

Just thought of something in the process of entering a ton of data:

Since we need "some page" that I can hand enter the following:

1. Vendor Code "S" or "Y"
2. PC# , i.e., "25467"

... I think that the Inventory page, feeding the AOM page (as it does now, or New AOM), then the New AOM page feeding another individual page would be ideal.

I say this because the Products page has alot of products that we may not carry or order for our individual unit (store), so having to define all of their vendors (and vendor product codes) or search for each one may be much more cumbersome. Having a page of products that we know we order, which can ultimately be determined from the INVENTORY page ( and then the AOM print out), then hand entering the Vendor Code and PC#, next to the products we know we order, would save an immense amount of time. EDR

KiwiSteve · Jul 5th 2004

Hi EDR

I think you and Derk have made good progress, so I'll leave it up to you guys to finish it. Yes, I was working on your first model, and I tried to stay true to your original requests, ie only 3 sheets. It made sense to have more, and I see you have now done this. It always happens, doesn't it? You start with an idea, and before long, once you see the possibilities, the idea takes on a life of its own, and suddenly the 'what-ifs' become a standard feature of the final idea. I regularly write Excel/VB things for people, only to have them modified untold before issue, so just come to expect it now...

Trust it all works out fine for you.

Cheers

Stephen

Anonymous · Jul 6th 2004

Here's the next iteration. (As Stephen noted, this project is a good illustration of how projects developed, debugged, changed, debugged,...)

There is a new Master sheet for recording by product code, the needed information on case size PC # and Vendor code. Note two Vlookups are used to get the product and vendor names.

The Vendors table is expanded to include the phone numbers etc.

There are no longer any pivot tables (although a hidden database is created in case any are needed).

I made up a rule that says if the case size is greater than the total PAR then don't order by case. With actual data, this may not ever come into play.

The code is rather slow, but can be made somewhat more efficient. However, I'll wait until we are closer to a final product before cleaning it up (and it may be fast enough as is).

There is no error checking, so it assumes that all products in the inventory have appropriate entries on the Vendors, Products, and Master sheets.

Case prices are now just multiples of the unit prices on the Products sheet. Where will the discounted case prices be found?

Test out the attached and let me know what more is needed. Don't hesitate to ask for refinements.

EDR · Jul 6th 2004

Hi Derk:

You rock! Everything seems to be working, and I don't notice that the code is "too slow."

I do have one request though in terms of logic (previous request). I think the way you've set it up now, is the "Master" page calls from the Products page, and then "Inventory" page calls from the Master page?

However, in one of my very last posts, I wrote:

[INDENT]"I think that the Inventory page, feeding the AOM page (as it does now), then the AOM page feeding another individual page would be ideal."[/INDENT]

Building from shelf to sheet is the easiest way to know which xxx products out of xxxx that your particular restaurant will carry. Therefore, building the Inventory Sheet, which calls from the Products would be wisest. Since the Products page feeds the AOM page, and that logic and alphabetizing are already there, why can't the AOM page then feed the Master page with summarized, alphabetized product data?

I realize that after I hit the button to send to data base, I'd then have to goto the Master page, and enter a PC# and Vendor Code for each listed product, but I'd much rather do that, then try to build a Master sheet first, not necessarily knowing which 987 products out of 2000+ that our restaurant may be carrying.

Would this be possible? Thank you so much! :thanx:

Anonymous · Jul 6th 2004

Here's another try. Rather than start from scratch, the Master sheet checks the AOM sheet and adds any new products at the end and tells you, so that the missing info can be provided and the order sheet created. It doesn't throw away any old data, so you only have to enter info on the master sheet as new products come into inventory. Of course you can always delete rows on the master sheet that you know will never be needed again if you want to, but no harm if they remain as long as the product numbers remain unique (there is no check at the moment on uniqueness). See if this approach works for you.

EDR · Jul 7th 2004

Run-Time Error '13' Type Mismatch

Hi Derk:

I think your new logic will work fine, thank you! I'm also glad you did that because I already entered so much data that cutting and pasting my data into the inventory page is best, if it's calling the "Products" page, and not the "Master" page.

In any case, things were working fine with your most recent version until I began updating the Vendors page:

Run-Time Error '13' Type Mismatch

I opened the debugger, and the error is showing in this line of code:

ii = Application.Match(lv, vendors.Columns(1), 0)

Since I cannot post actual (sensitive) information publicly to show you how I modified the information in each cell, I can't show you what I posted, but can say that on a couple of the columns, I left the account # blank (and tried zero and 1, and 123). Also, in the extension area, I entered "ext 4663", etc. But I also tried it without it.

I changed everything back and it still gave me the error. If I leave a column blank (other than "Y", "S", "W", etc.) it should still work OK. Nothing should prevent it. On some contacts, it may be an extension number, but an account number is not needed, or vice versa. Hope this makes sense? All I know is, as soon as I changed the info in each cell, and then pushed the Update info on the Inventory page, I got the error.

Also, I like your new pop up window that says "125 new items added, please update the Master sheet and then the order sheet will function", etc. Cool!

Thanks! EDR

Anonymous · Jul 7th 2004

The error on line
ii = Application.Match(lv, vendors.Columns(1), 0)
means there is a vendor code on the Master sheet that is not on the Vendors sheet. You should be able to locate it because there will be an #N/A error for the vendor name in the bad row of the Master sheet. (The sheet names can be changed as desired, but none of the sheets can be deleted without fouling up everything.) This error shows when the Order sheet is being prepared, so the program stops because it can't locate the vendor information it needs.

OOnce we get the design nailed down, I can clean up the code with some eror checking to help spot glitches like this. If I'm wrong about the source of the error and all of the master sheet entrie have vendors who are on the vendor page, please let me know.

Inventory Assistance

Participate now!

Share