- Client: Umbrosa
- Data sources: Microsoft Business Central & Excel
- Total budget: 17 days
- The Data Warehouse for Business Central – 7 days (find out more on: DWH BC edition )
- Custom Power BI Reports:
- Sales Reporting – 5 days
- Stock Reporting – 5 days
- The content of this article is focused on Stock Reporting.
Intro
Umbrosa is a successful parasol manufacturing company based in Roeselare, Belgium, that provides high-quality products with trend-setting designs to some of the most renowned hotel chains worldwide. They collaborate with high-end designers to offer the market eye-catching parasols that are as aesthetic as they are functional.
The combination of design, flexibility (the movable arm), storm resistance, price/quality, ease of maintenance/storage and availability of replacement parts.
Some of the main reasons why clients choose Umbrosa
Umbrosa strongly supports the local economy by keeping its manufacturing process in Belgium. This allows for quick shipping of orders placed by customers within the EU! The same goes for clients in the US, thanks to a local warehouse.
To ensure that customer orders are delivered on time with minimal waiting periods—a key distinguishing factor for Umbrosa compared to other parasol companies—they transitioned from a manual Excel-based planning system to an automated Power BI planning report. Today, Umbrosa utilizes two Power BI reports to efficiently manage inventory, production, and sales.
Stock Overview
Umbrosa ships its products worldwide—from Australia and the UAE to Europe and the United States. Efficient stock planning is essential to servicing incoming orders on time, especially when delivering to premium international hotel chains such as Hilton Hotels & Resorts and The Ritz-Carlton.
As demand grew, planning for spare parts became overly complex and error-prone due to manual inputting of supply demands into Excel spreadsheets.
To address this, Umbrosa sought to automate its inventory overview. This is where Power BI proved to be the perfect solution, eliminating the need for manual demand inputs and saving significant time in the planning process. By smartly organizing and modeling the data, Umbrosa created a clear, visual representation of the stock. This provides real-time insights and faster decision-making.
This tool saves a lot of time in our planning.
Eveline Debruyne, Umbrosa
The tool is used twice a week to check which items need to be ordered or manufactured. Every three weeks, the report’s figures are validated against Business Central inputs. If discrepancies arise, they always stem from incorrect data entry in the ERP system, where the data originates. This demonstrates that Power BI accurately reflects input data, underscoring the importance of data validation. Rest assured that we have implemented data checking reports for several of our clients, so they can leverage data-checking reports to monitor and correct manual input errors.
Forecast stock levels
When Eveline conducts planning, she considers the next 10 weeks. The tool uses weekly values to analyze demand vs. production output. For each week, the following metrics are displayed per stock item:
- Starting Inventory
- Output from Production
- Purchase
- Sales
- External Output
- Stock Level
The listed elements are crucial for calculating the current stock level because they represent the key factors that influence inventory movement over time. Here's why each element matters in the formula:
1. Starting Inventory
This is the baseline stock at the beginning of a specific period. It serves as the foundation for calculating any changes that occur due to production, purchases, or sales. Without this starting point, it's impossible to determine the net effect of subsequent activities.
2. Output from Production
Represents the inventory generated by manufacturing processes. This adds to the stock level and reflects the business's ability to replenish inventory internally. For manufacturers, this is a critical input since it shows the capacity to meet demand without external sourcing.
Date of importance: in our model we look at the Output Date on the production order
3. Purchase
This accounts for inventory acquired from external suppliers. Purchases directly add to the stock level and are essential for meeting demand when internal production alone is insufficient. This factor ensures that stock levels reflect both internal and external contributions.
Date of importance: in our model we look at the Promised Delivery Date by our supplier
4. Sales
Umbrosa works with sales orders that are delivered at a later time, while new orders are constantly coming in. It is crucial for customer satisfaction that orders are delivered on time. That is why the entire production must be carefully tailored to this.
Date of importance: in our model, we consider the following:
- The Promised Shipment Date for goods shipped to customers.
- The Promised Shipment Date for internal transfers of items between locations.
- For Assembly Lines, we use the Due Date, which reflects the planned completion date of the assembly process. This ensures that any stock consumed during assembly is accounted for at the right time.
5. External Output
Includes stock that leaves inventory for reasons other than sales, such as transfers to other locations, returns, or shrinkage (e.g. damage). Accounting for this ensures accuracy by considering all non-sales-related reductions.
Date of importance: in our model, we look at the Promised Shipment Date for incoming item transfers
6. Stock Level
The resulting balance after all the above adjustments. It provides a real-time snapshot of available inventory, which is crucial for operational planning, meeting customer demands, and avoiding overstock or stockouts. In the case of our example Power BI report, it’s not only near-time but also offers valuable insights into the future.
This method is vital for decision-making, ensuring efficient inventory management, and optimizing costs while maintaining the ability to meet demand.
The Stock Level KPI is of particular interest, forming the focal point of Umbrosa's tailored Power BI report.
Key KPI Formula:
Starting Inventory + Output from Production + Purchase – Sales – External Output = Stock Level
The base formula for the Stock Level will be similar for most companies, however, you can tweak the formula to your specific company requirements with some Power BI magic!
The report uses a simple layout following IBCS color-coding guidelines, enabling Eveline to easily spot red dots in the Stock Level column, which indicate that a product needs to be reordered.
The focus is always on items in danger of running out of stock based on current demand. A top filter lets Eveline select problematic items:
- “NOT OK” Filter: ⛔ Shows products below their reorder point, requiring immediate attention.
- “OK” Filter: ✅ Shows products above their reorder point.
Additionally, tooltips provide reorder quantities for specific items when hovered over.
Icons in the “Stock Level” column highlight weeks with potential stock issues. The timeline view helps Eveline understand when to produce more, influenced by orders placed for future dates.
Open Orders are another key focus area, in Umbrosa’s Power BI Sales report. As seasonality affects business cycles, Open Orders provide insights into expected cash flow and timing.
This demonstrates the power of actionable data through Power BI. It provides forward-looking insights and identifies bottlenecks in inventory management, guiding users toward concrete actions—such as reordering specific products—to maintain optimal stock levels.
80% of our sales-shipments we have in the summer months April – July.
Eveline Debruyne, Umbrosa
That's why it's important to plan in advance for some inventory levels throughout the year. We determine per SKU how many we can make in stock. In the months that we can make more stock, the SKU % is set higher, so with the help of the Power BI stock overview we always have a perfect balance between stock levels and stock value at the right time
Poor stock management—whether excessive or insufficient—can result in high storage costs, product deterioration, or missed sales opportunities. Umbrosa understands this balance, which is why they prioritize stock planning and continue exploring ways to enhance their reporting and planning capabilities.
Developing a custom-tailored Power BI report for a client is rarely a straightforward process. Challenges and setbacks are a natural part of the journey. At Umbrosa, managing the numerous components involved in manufacturing their products can be particularly complex, especially when it comes to planning the reordering or production of these components.
The "Components" section of the Stock Overview report was designed to closely align with the view Eveline currently uses. However, this new overview is more detailed, which introduces additional challenges. It requires further redesign efforts to ensure a smooth and intuitive user experience for the primary user responsible for overseeing the reordering process within this operational segment.
Curious About Unlocking the Power of Power BI?
Does Umbrosa’s old Excel-based inventory planning process sound familiar? If you’re facing similar challenges and want to optimize your workflow, save time, and act on key KPIs, book a consulting session today. Begin a data exploration journey that can elevate your business to new heights.
