“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.

Utilize Power BI reports

It’s very important in today’s competitive environment to know as much about your customer as you can. Using Power BI can enable you to understand vital statistics such as what products, area and day customers enjoying shopping at your business. One of the basic and most important ways to learn about your customers and your business is by statistical comparison. And Power BI helps to do it easily.

Below is a sample report with Power BI to extract your customers data based on sales and customer information. These data sources are pretty simple to obtain and every company should record; products, branch, date, and customer ID if possible. There are 3 areas in this report, basic information area, graph area and filter area.

Power BI report sumple

Customers are classified into 4 groups depending upon their sales. The highest sales group is named STAR, followed in descending order by PROMISING, NOVICE and NEW.

When you begin analysing your company sales, it is important that you include basic information such as;
– branch sales per month
– sales per customer, expressed as a percentage
– what your top selling products are, and how many customers buy those products

Even though you may already know this instinctively, using this chart makes it easier to share those information with your colleagues. Furthermore, you can immediately start comparing various views with a simple click. This chart enable you to drill down into your sales data, correlating the main factors that affect sales.

For example, when you want to find out which product has the potential for increased sales, one of your research options is customer segmentation.
For example, you might find that more ‘Star’ customers buy Group C products, (especially C3 and C5), than other customers, and this tendency is particularly prominent in your Hamilton branch.

Now you focus on C3 in detail so that you can find some ideas to increase sales in other branches.

There are many options to gain new insights from existing company.
I am confident that it will give you a good starting point for your sales and customer analysis.

Here is the sample of power BI report I have made from website data.

waste management

real estate

5 Tips to succeed optimization in your business

1. Begin with bottom up and top down

Bottom up: real opinion from employees (your colleagues)
Top down: decision making or common goal
These stage is quite important, because
-everyone doesn’t prefer changes even if they know it is useful.
-On the other hands, we like to hear that our opinion works to company decisions.
-To go next action, everyone need to get images how will this changes result in.

2. Think out how to involve your colleagues

-utilize number : track and find out small changes
If possible , it should be quickly shown as results. Even if it was really tiny improvements. Visualization moves people with convincing.

3. Try “Cushion step” before introducing new system
4. Enjoy try and error(run PDCA cycle)
5. Utilities structure, not rely on individual efforts

Analyze key factors for actionable insight

According to business process breakdown and organizing small goals and tasks, I researched and analyzed sales data ,customer data and website data.

Sales data analysis

Purpose : checking company achievements and issues roughly
After summarize data, I proceed detail research of each issues.

Sales data summary
Sales data summary

Also I measured each process time from recieving new application to payout and  to grasp correlation between process time and cancel rate.

process time summary
process time summary

Repeat customer analysis

Picked up the data of current customer who got finance twice or more with us and analyzed customer’s attribute and profile. After grabbed tendency, we utilized it for repeat customer marketing.

Search Engine market and keyword research for overseas customer

According to demographics of New Zealand and current customer’s tendency, I estimated our target potential customer and summarized main keyword in each countries native language. After that we made Landing Page with search engine optimization and also I registered our website to prime search engine such as Google, Baido, Sogou, Yandex du.


Business Process Breakdown

Goal : Increase Business

It can be roughly divided into two ways o achieve the goal, “improve success rate after customer applied finance”, “increase new application”. The former is divided into detailed process rate and the latter is divided into channels. In this way, the goal get rolled into concrete process and it help considering next step.

business flow breakdown
business flow breakdown

And I also made staff workflow breakdown to visualize where is there a problem to hinder the finance process.

Staff workflow
Staff workflow

According to the goal and each business process, we set a couple of CSF and KPI.


Next Step : Organizfe small goals and tasks