“Stock Checker” for Food Ingredient Management with Power BI

As a restaurant owner, you may often encounter challenges in food ingredient management. We have heard the following issues frequently:

  • Excessive food wastage or over-preparation
  • Only veteran staff or managers know the appropriate quantities and timings for preparation
  • Relying on personal intuition and guesswork, lacking informed decision-making based on factors like sales figures

To address these challenges, we have engaged in discussions with restaurant owners and developed the “Stock Checker” report, designed to support food ingredient management. By utilizing this report, you can enjoy the following benefits:

  • No specialized knowledge required – You can easily check and predict inventory levels.
  • Anyone can consider the timing for the next preparation based on sales data.
  • It can be combined with sales analysis and profit margin calculations for further applications.

Now, let us provide you with more details about the specific functionalities of Stock Checker.

  1. Stock Status ScreenWe have designed a simple interface where anyone can easily determine which ingredients need replenishment by simply viewing the screen. Each ingredient has customizable threshold values, allowing you to adjust alerts (red) for fast-moving items, ensuring timely restocking.
  2. Sales & Stock ScreenThis page allows you to compare past sales data with calculated ingredient usage based on the menu items sold. It features a dashboard that provides an overview of high-usage ingredients, the temporal trends of sales and ingredient usage, and sales breakdown by menu item. You can easily drill down into specific periods or ingredients to examine their individual trends. Clicking on elements of interest on the graph will display related information on other graphs, facilitating simple analysis. This sheet primarily caters to owners, keeping the displayed elements to a minimum, but we can accommodate additions or modifications as needed.

This report is created using Power BI. By downloading the free Power BI Desktop, you can use it on your computer. With a Power BI Pro license, you can publish the report online and access it from your mobile device as well.

The underlying data sources are sales data and inventory data. Daily sales data (which can be downloaded as a CSV file from your POS system) can be automatically imported and reflected in the report by uploading it to a specific location. Inventory information can be updated by counting the ingredients in your store at a given point in time to determine the latest stock, or by uploading a list of added or replenished ingredients to the same designated location, similar to the sales data.

By initially setting the ingredients and preparations used for each menu item and their approximate unit usage, the report can automatically calculate ingredient usage from sales data. Once set, this can be continuously utilized. Anyone familiar with Excel can update the data, so it’s accessible to a wide range of users.

The following report(same as above) provides an example report based on a fictitious Japanese restaurant. With prior configuration, you can create reports for cafes or restaurants in other genres as well. We are currently inviting users for trial usage, so if you’re interested, please contact us.

Leave a Reply

Your email address will not be published. Required fields are marked *