ERP System Analysis
(Procurement, Sales, Inventory)
Raymond Industrial Ltd is a Hong Kong-based company principally engaged in the electrical household appliance businesses. The company operates under OEM agreements with major overseas customers in the U.S., Canada, Australia, Europe, China, Korea, and Japan.
Excel Reports
Utilized VBA, Excel, and T-SQL to generate visual reports.
The left video demonstrates the 'Purchase Goods Receipt' report. When the user selects the range of supplier's name, purchase order number, and receipt date, it runs the query to get the relevant data from the company's ERP database. The right video shows the 'Quantity Need to Purchase' report. Once the user inputs a specific item, it extracts the multi-level BOM to get all the parts and sub-assemblies that are required to be purchased with their current sales order status, past purchase order status, and current inventory on hand. The query for this report can be found above the button.
Power BI Reports
Raymond Industrial Ltd manufactures and sells home appliances for international customer companies. In this process, the company sources raw materials and parts from suppliers in other countries. The management desired to figure out how much the parts are supplied from China and non-China for a specific item. Referring to engineering BOM, this report gives the proportion of parts and raw materials purchased from China for specific final products. While the left dashboard relates to the purchase orders, the right dashboard indicates the destinations for the company's sales orders.
The left dashboard is triggered by searching the customer while the right dashboard is to be searched by the final product. If the user inputs customer id, it runs queries to find all the final products, which are sold to the corresponding customer. Then it breaks down BOM to get the list of purchased parts or sub-assemblies, which are needed to manufacture those final products. Similarly, if the user wants to search by product id, it extracts BOM by a top-down approach. To report to the executives, it shows the total counts of parts that are used for final products and the proportion of parts that are purchased from China by dates.
This report provides the total material cost in USD and RMB for the final product that the user inputs.
It extracts multi-level BOM first to get the parts and sub-assemblies then classify them into purchased items and manufactured items. Only for the purchased items, the report shows their required quantity, purchase price, level, and position number in order to compute the total material cost.
The stock movement report on the left shows how much stock is transferred in and out. Based on the inventory on hand and sales orders, this report keeps track of the movement of stock for the specific period of date.
※ I have the permission of the company to disclosure my works.
© 2022 by Jungeun Melissa Eom