Kitchen inventory system

[mcchieftan]

Dear Chandoo Forum Users,

I would like some help with my current project: a stock/inventory system for a course centre.

This is the situation:

The course centre feeds several thousand visitors every year. There is a kitchen and several stock areas. Deliveries are made several times a week. The centre requires a system which will enable them to track stock levels and expenditure (at present there is no system at all!)

Main issues so far:

Speed! I'm wondering if its just my computer but I am using a lot of formulae and once I have a few thousand rows any recalculation takes a minute or two (I've cut some of the data for the version that I've uploaded) There will be much more data to come as the year progresses and I'd like to know that my work will not fall apart under the strain. Is there a more intelligent way to approach this problem?

Data available:

The various suppliers provide a monthly summary of all items that have been purchased. The centre itself conducts a monthly stock check. There is no data available to show how many people have eaten a meal or how much ingredients are used per meal.

I deduced that if I know what the incoming deliveries are and I know the monthly difference in stock levels, then I can use these figures to work out what monthly consumption is.

My work so far:

SCIn - Stock Check input
DIn - Delivery input
Calc - Calculated values (designed with making a pivot table and charts in mind)

Please see the attached spreadsheet. There are a few different constraints/desired outcomes:

1) To make the stockcheck as quick as possible. Each item in stock has a product code. Ideally the stock checker will be able to search for this code in SCIn, find the relevant item, find out what constitutes a full package (this data is currently only available in DIn) and use this to fill in the correct quantity for each item.

The data regarding package contents and size is included in the delivery information in DIn. I thought about using INDEX/MATCH to include this information in SCIn but as I've already been having speed issues I didn't want to slow matters down further.

It would also be much more helpful if this sheet could be a matrix with future stock check dates as extra columns to the right of the data present. I structured the data in its current form as I understood that this would facilitate the lookups that I have included in the Calc sheet. Was this necessary? Could I still perform these lookups if the data was in a matrix? (or is there another way of collecting the data that I require without the lookups I have created in Calc?)

2) I would like to be able to use pivot tables and charts to provide analysis of consumption/expenditure. The Calc sheet represents my best attempt to calculate monthly expenditure/consumption from the stock check and delivery information and present the data in such a way that I can perform pivot analysis. I am much less experienced using pivots than some other aspects of excel so I may have gone about this entirely the wrong way!

A little explanation:

Columns -

UniqueDate: This column lists all unique dates from the SCin sheet (stock check input)
UniqueCode:This column lists all unique codes from both SCIn and Din (stock check and deliveries)

Date: This column generates date entries (from unique dates) for each unique code (from unique codes)

Item,Category,Supplier,PricePerUnit : INDEX/MATCH gathers relevant data from the DIn sheet.

CurrentStock: Returns the current month's stock figure for the relevant item code from SCIn

PreviousStock: Returns the previous month's stock figure for the relevant item code from SCIn

InterimDeliveries: Returns quantity delivered for this item between this month's stock check and last month's stock check

QtyUsed: Previous stock check minus current stock check plus interim deliveries.

TotalCost: Qty used mutiplied by PricePerUnit.

--------------------------------------------

Any input at all is most welcome! My apologies in advance for anything messy and chaotic in my work. I hope my explanations are clear, please ask me if they're not.

Thanks!

 

[vletm]

@mcchieftan & Kitchen Inventory System
1) I would 'clean' that 'Calc'-sheet - 'sorry' - that makes this s - l - o - w!
... I cleaned it and made two samples.
2) You need 'data' which shows IN (much more data if some analysis)
3) You need 'data' which shows OUT ... or just 'manual inventory '
after those You could get idea how much would be in 'Kitchen'.
If You need to have 'value of Kitchen' then Your 'Din' would use too.
>> Gotta think more ...
> Ideas ... questions ... comments?

 

[mcchieftan]

Thanks for taking the time to respond.

1) so I see that you have created two pivot tables but I don't see how they are generating the information that I require: i.e the real stock depletion (which I am calculating using the two monthly stock check values and the incoming deliveries) I am missing something?
2) Data in is "DIn", each item that is delivered has a value for "date" and "quantity"
3) I understand that I need the "out". There is no manual way to obtain this (it is not possible for the chefs to record every item that they take out of stock). This is why I created the calc sheet, to use the two monthly stock check values and the incoming deliveries to obtain the "out".

Thanks

 

[vletm]

@mcchieftan
If You have ONLY SCIn -values then it would be challenge to get exact values inventory values.
If You want to use 'Your style' 'Calc' then it will be as I wrote S L O W !
If You need to use 'Din' value then not challenge ...
but still You have only SCIn-values and
of course it would be possible to compare month-by-month and from that change could get an idea of 'flow'.... if menus will keep same.

'Chef' knows how much they use per day which item!
It's only a question, do 'Chef' want to tell it?
There should/have to be one kind of list of items which need for every day's foods. Like: Meal1 = 5* Coconut Milk (115107) and so on.
Or 'Stock' should be limited and then nobody knows how much is something!
As I wrote 'Gotta think'.

 

[Chihiro]

I always say this to someone looking for inventory management/tracking system in Excel.

Excel isn't well suited for this type of operation and it's best left to true databases.

While it is possible to develop inventory system in Excel. It is going to be very cumbersome to maintain and manage.

I'd recommend investing in MS Access at the least.

Or if you can, invest in a server that can host one of open source inventory management system that uses true database.