Need a spreadsheet to manage incoming inventory? We've created an Inventory Management spreadsheet template in Excel and Google Sheets to manage material inflows and the products they create. Our Inventory Management spreadsheet template includes a ledger to track orders and inventory levels for both materials and products produced. Dashboards then visualize the most important metrics related to total inventory value, expiration dates, materials most in demand, and maximum versus minimum order intakes. Here's how to use the inventory management spreadsheet template we created to easily manage and visualize material inventory levels.
Ask follow up
Say a restaurateur wants to use this inventory spreadsheet template to manage the inventory of a new burger joint. On the Fields tab, customize the name of the report, common status types of material orders, and inputs to assess the quality of inventory in stock since food spoilage is a big part of a restaurant's inventory check-ups.
Ask follow up
Storage locations, such as commercial refrigerators, freezers, and backstock shelves can be specified all the way down the aisle, sector, or shelf name. Use custom IDs to identify these locations later.
Material SKUs, such as beef, cheese, buns, lettuce, and pickles, are defined below. Next to it, enter the product SKUs for your menu items, along with the item's name, unit, and description.
Finally, the Vendor fields tab accounts for frequently used vendors and their most relevant information. If you need to add more rows for additional vendors, locations or SKUs, just add them above the gray borderline of each section.
Ask follow up
When you order from your food supplier, an order can contain many items. So when a new order of materials is received, go to the Material Orders tab and enter the order number in the first column. The order row now appears in yellow. On the order row, fill out the rest of the order information, such as order date and status, shipped date, any shipper fees, tracking number, order arrival date, condition upon receipt, and the vendor.
Ask follow up
Underneath it, all the material SKUs of the ingredients like tomatoes, beef, buns, and pickles - can't forget those pickles! - can then be listed out along with the quantity of each ingredient ordered and their unit price. To place a new ingredient order, go to the next empty row and enter a new order number, and bam - new order row.
Ask follow up
All of these ingredients are now summed on the Materials inventory tab under the "Quantity ordered" column. In this case, we have a thousand pounds of beef that we've ordered to date. But we also lose beef as we sell our burgers, which the "Stock calculated" column accounts for. It subtracts the "Quantity used" from the "Quantity ordered." This calculation is on the "Production" tab which we'll get to in a minute.
Ask follow up
Use the checkbox at the top to indicate the day a manual inventory check was made and the number of days until the next inventory check is due. As manual inventory stock checks are made, the "QA'd stock" column accounts for food spoilage, theft, or otherwise miscounted items. Use the "Restock requirements" section to set the minimum and maximum stock of each ingredient needed on hand every day, plus the delay time in days it takes to restock those ingredients. If the "Quantity ordered" column is less than the minimum, the stock calculated column will be flagged red to indicate a new order is necessary.
Ask follow up
The "Storage" section indicates where the items are located. If multiple items with the same SKU are in different locations, make a new location ID that's broad enough to encompass all the locations.
Last, the "Expiration" section defines the expiration time and which location to pick from first to ensure the right ingredients are being moved at the right time. For example, tomatoes go bad after a week, so mark the expiration date as seven days. Frozen beef can last three to four months. The Materials Dashboard visualizes this data to track top inventory metrics. The filters at the top comb through this data by order date or by specific material SKU. In this example, we can see how much beef we have in stock, the last quantity received, the current value ordered, and its average unit price. The inventory value is then visualized as a pie chart and a bar chart.
Ask follow up
Underneath that is the "Expiration" section. Here, the "Top 10 materials based on expiration time" (either shortest or longest expiration) and "Top 10 materials about to expire" based on the date range selected in the filter above are visualized.
Below, the historical unit price, movement over time, and total stock quantity over the given filter period of the specific SKU selected lets us assess how quickly we move through our supply of beef and if we need to change our order habits.
Ask follow up
Remember, if you want to utilize this Inventory Management spreadsheet to replace your expensive inventory management software and better manage your materials inventory storage, you can download and customize it right now.
So how do you account for the menu items you make with all these ingredients? That's where the Production tab comes in. Like the Material orders tab, first, enter the date of production and the product SKU that's being produced. Say we produced and served 50 classic hamburgers on our opening day. Under the Production row, the material quantities used to make all those hamburgers, like beef, romaine, special sauce, pickled red onions, and brioche bun can all be accounted for. All these ingredient quantities are then summed in the "Quantity used" column of the Materials ordered tab.
Ask follow up
The production tab also sums up the unit price of all these materials along with a basic calculation for the average unit cost per quantity made. This can help calculate your food cost percentage at a glance to price your products properly. The Product inventory tab counts how many products you've produced, with an option to manually count how many have been sold.
Ask follow up
In the case of our burger joint, these should be the same number, but other industries may produce more items than they sell. The minimum and maximum stock filters, storage location, product unit value, and expiration date can also be defined for each product in inventory. On the Product dashboard tab, we can select a specific product SKU to assess how many of each product we've produced and the average inventory value.
Ask follow up
Charts above assess the total inventory value in production and products based on their expiration time as well as a table to define the minimum materials needed to make the selected product and those ingredients' current stock levels to make sure you're always properly stocked. With easy-to-read charts that organize your inventory management, this dashboard solves your inventory management headaches. And remember, you can download and customize this Inventory Management template in Microsoft Excel or Google sheets right now. Now, go check out our Bookkeeping Dashboard to manage your books as easily as you manage your inventory.
Ask follow up
