CODEWITHSUNDEEP
excelinventoryinventory-management
Asim Siddiqui
Asim Siddiqui

Reputation: 309

Stock Count Inventory Management in Excel

I am developing a dummy feasibility model for the restaurant business. The model I have made so far is attached. I want the model to automatically calculate the inventory stock left so that the flags can be raised for the items to be reordered. This requires the total stock utilized by the recipes cooked so far.

Please check out the excel file.

Thanks.

Upvotes: 1

Views: 1094

Answers (1)

Ulli Schmid
Ulli Schmid

Reputation: 1167

There probably is no one formula solution to this problem, since you want to cross reference over 2 data relations (InventoryId-RecipeId, RecipeId-QtySold) and then sum up the results.
It is particularly difficult because you enter all data into a single sheet, in a non-relational form (albeit human-readable).

A simple solution may be introducing a "Qty Sold" column P:

https://i.sstatic.net/5Ods6.png

You can get the qty sold with a simple lookup from your monthly revenue section:

=VLOOKUP($N$3,$U$1:$V$100,2,FALSE)

Building on this intermediate column, enter

=SUM(IF($M$1:$M$100=B4,$N$1:$N$100,0)*$P$1:$P$100)/1000

into cell J4 and press CTRL+SHIFT+ENTER (array formula). You can then copy cell J4 to J5:J19.

From the bottom of my heart, I want to strongly urge you not to use Excel for your production setup. You already experienced its limitations building only this basic "model". A real world business should be using a real database connected to a well thought out user interface.

Upvotes: 1

Related Questions