東南亞工廠活用EXCEL應對「插單+缺料」內訓及諮詢 (Asia Factory In-House Training & Consulting)

Leveraging Excel Functions to solve ERP Limitations: Rush Orders Handling & Materials Shortage

運用Excel解決ERP不足: 插單及缺料要求

• Reduced Obsolete Inventory by 30%    

  < 降低呆料庫存達30% >

• Achieved 100% On-Time-Delivery Rate  

  < 準時交貨率達100% >

• 100% Accurate Forecast of Material Supply & Demand for Future Weeks 

  < 準確計算未來數週的物料供求>

Why Asia In-house Training?

Most HK manufacturers establish their factories or contract manufacturing partners in Asia but encounter lots of operation problems due to insufficient training & inefficient business processes.  

This 2-day training program plus 1-day consulting service will be conducted in your Asia factory, teaching medium will be in English.  The program content will also be tailor-made to suit your own business needs.  

Course Objectives:

The Planning System in ERP often fails to adequately address many real-world challenges, including:

• Determining safety stock levels in MRP – identifying which items have excess inventory, which have shortages, and which materials require expediting or deferral.

• Assessing based on current inventory and incoming receipts, which finished goods can be produced, in what quantities, and under what conditions – whether production must follow strict priorities or quantities can be flexibly adjusted to meet the minimum requirements of multiple orders.

• For urgent, last-minute orders, calculating the feasible completion date given current material availability and lead times (as customers waiting your responses).

What is ultimately needed is a dynamic Excel-based model that can be adapted to changing circumstances – such as order insertions or material shortages—while providing clear, at-a-glance visibility into the impacts and outcomes.  This approach not only meets current requirements but also enables future in-house development of additional customized material planning templates.

This course is designed to address the practical challenges outlined above.  Regardless of the ERP system in use – whether Kingdee, UFIDA, SAP, Multiable, BPCS, or any other – as long as data such as BOM, Purchase Orders, Demand, Inventory, and Forecasts can be exported, participants will learn to leverage Excel as a powerful modeling platform.

All Excel template models developed during the course will be provided to participants free of charge, with full permission to customize them to suit their company’s unique operational requirements.

Following the 2-day course plus 1 day consulting , the instructor will establish a dedicated WeChat group to offer ongoing support, guidance, and consultation, ensuring sustained practical benefits and successful implementation.

What you will learn & get:

• Apply target inventory techniques to effectively plan and control inventory levels  

• Establish internal mechanisms to manage fluctuating sales forecasts (e.g., rush orders and urgent insertions)  

• Master advanced inventory analysis techniques  

• Master advanced Excel function applications to solve complex day-to-day challenges  

• Master efficient production material allocation mechanisms and develop corresponding models in Excel

Target Audiences: 

Company Owner, Managers/Supervisors from the Finance Department, PMC Department, Production Department, IT Department, Logistics Department, and Inventory Department.

About the trainer:

Mr. Dick Lam – Senior Consultant at Universal Training, Guest Lecturer for Hong Kong Productivity Council (HKPC), Federation of Hong Kong Industries (FHKI), The Chinese Manufacturers’ of Association of Hong Kong (CMA) and the Hong Kong Institute of Supply Management.  Formerly Financial Controller of a major U.S.-based multinational manufacturing company (with annual revenue exceeding US$4 billion), Mr. Lam also currently serves as an Independent Non-Executive Director of a Hong Kong-listed company.

He specializes in delivering financial management, business planning, operational analysis, and performance review services to enterprises. Mr. Lam is a Fellow of the Association of Chartered Certified Accountants (ACCA), and holds a Bachelor’s degree in Business Administration and a Master’s degree in Electronic Commerce.

From 2000 to 2023, Mr. Lam held senior executive positions (at Director or Vice President level) in the China operations of several U.S.-based multinational companies.  His experience spans multiple manufacturing sectors, including electronics, textiles, and food processing.  He has led various factory functions, including supply chain, customer service, IT, human resources, and business development.

Mr. Lam has extensive practical experience in leading project planning, establishing new business entities, and implementing financial internal controls. He is particularly skilled in financial regulatory compliance, Enterprise Resource Planning (ERP) system implementation, and the application of financial controls across production processes. 

Notably, he spearheaded a supply chain improvement project for a publicly listed company operating 80 factories across China that supplied plastic bottles to Coca-Cola and PepsiCo. The project successfully restructured production planning and achieved a substantial reduction in nationwide inventory levels.

2-Day Course Outlines:

(1) Business Logic behind : Bill-Of-Materials (BOM)， Materials Requirement Planning (MRP)， Sales Forecast

• Formula for the MRP

• BOM

• Re-define some concepts and eliminate any misunderstanding

 Week Coverage vs. Safety Stock Level

 Suppliers’ Lead time on safety stock level

 How to define the best safety stock?

 What materials to be included?

• Materials Planning Model

 The Parameters

 Demand Calculation

 Pull-in and Push-out 

• Special Excel Functions Introduction and Application (What a surprise!)

• Min/Max

• Index/Vlookup

• Sumproduct

• Vlookup

• Offset/Choose

• Dynamics: Range/Array

• User Defined Functions using Lambda()

(2) Template Construction

• Production Materials Plan

 How many set of full BOM Materials?

 What are short?

 Priority of Materials Usage for customers’ order?

• Additional Order Checking

 Sudden Orders materials requirement, what can we produce?

 What materials to add? And when?

 Pull-in

(3) Conclusion

• Common Materials Matters

• We cannot fulfilled every Customers’ Order?  Need to set Order Priorities

1 day On-Site Consulting Service after the training, regarding the following:

• All problems related to the skillset covered by the course content;

• Advise on workflow issues related to supply chain report & planning (models), financial report & model and other similar discipline;

• Upon readiness of current operation data, a basic viable solution (formula of core solution) will be provided; if data not ready, solution guidelines will be provided so that the attendants can develop the solution by themselves when data is ready (data entry template will be provided)

• The consultant will perform the best to meet the needs of attendants subject to the time limit, but there is no guarantee of robust solution!

———–—————————————————————

Dates & Venue:Designed Schedule(s) & Asia Country by Client

Medium of Instruction:  English / Putonghua / Cantonese (Any) 

3-day Project Fee:  HK$39,800 

• All EXCEL templates will be provided to client use for FREE

• Class size limited to 30 persons ONLY

Note: Client needs to cover the trainer round-trip air ticket (economy) from Guangzhou to client city, local transportation, accommodations & meals.   

——————————————————–

課程主辦: 環宇培訓顧問 (Universal Training & Consultancy) 成立超過20年, 顧問資深, 信譽可靠

查詢電話: (852) 23676668

WhatsApp在線: (852) 65572939

電郵 :  Training@Universal-hk.net