Workshop on Advanced Excel Application for Inventory Management

Program Overview:

Microsoft Excel is one of the most powerful software used for analyzing data to make a better decision. It helps in data organizing, data analyzing, and data interpretation. Utilizing Microsoft Excel for inventory tracking is an easy way to cut costs, save time, and organize inventory. This course will take participants through intermediary and advanced excel tutorials, they will understand how Excel work. They will learn complex functions, nesting, data manipulation and automation using basic macros in excel. They will further be taken through tutorials on how to use excel for inventory management. The course is for both intermediate and advanced excel and its dynamic application.

For whom:

This course is designed for Inventory and warehouse management personnel with little knowledge and experience in Excel usage but are expected to use excel for their day to day inventory management system.

Learning objectives:              At the end of the program, participants will be able to:

• use logical functions, financial and statistical functions;
• use nest functions;
• use multiple worksheet management and formulae;
• create charts, use what-if tools;
• protect worksheets & workbooks;
• use lookup & informational functions;
• create simple inventory system with excel template; and
• create and manage an inventory system with excel spreadsheet.

Course outline:

Day 1: Data analytics Excel Philosophy,

• Excel interface, Excel shortcut keys
• Naming & Date Function and Logical Test, Text to column
• Cell referencing,
• Conditional Function and Formatting,
• Count if, Average if and Sum if

Day 2:  Advanced Formula V & H Lookup and its application to data analysis 

• Concatenation and Rept function
• Data Consolidation,
• Linking worksheet, workbook together, hyperlink
• Match and index function,
• Pivot Table and its dynamic application
• Introduction to macro

Day 3: How to create and manage an inventory system

using Excel Template

Inventory System

• Creating and maintaining stock in Excel
• How to create a dynamic expandable

in and out inventory in Excel

• How to create inventory tracker:
  • Retail inventory tracker,
  • Manufacturing inventory tracker.

Day 4: Introduction to Excel Dashboard Reporting

• Expected to be covered are:
• Combo chart,
• water flow chart,
• option tab, list box,
• Gant chart,
• Side by side compare among others.

Day 5: Case Study/Class Assignment for hands on practical examples and exercises.

• Workshop review

Training Methodology

Lectures, discussions, exercises, and case studies will be used to reinforce these teaching/learning methods.