Microsoft Excel is an immensely powerful spreadsheet package that is capable of providing 100% business solutions to small and medium sized enterprises. Very large corporations also manage many of their operational systems using Microsoft Excel. A company can easily manage its administrative activities, purchase and sales process, customer and supplier management, employee, payroll and financial information management and reporting activities using Microsoft Excel. However, running business using Excel requires high level of commitment from top level management, expert skills of operational management and fluent workings skills of employees.
How participants will benefit after the course:
(1) Understand the general and specific business process management at your organization;
(2) Understand Purchase and Sales Management;
(3) Gain advanced skills on Microsoft Excel required for Purchase and Sales Managers;
(4) Develop and implement company-wide Excel based business models for purchase and sales management.
Workshop topics to be covered:
Day 1 Session 01
Understand business process management in manufacturing and service based organization.
9.30 AM 10:15 AM
Identify and understand all the key processes of a regular business.
Know how all the processes are interrelated
Know how a system can be developed that integrates all the process in a single platform
Know how to plan before you start implementing Microsoft Excel as a company wide operational assistance tool
Day 1 Session 02
Understand Purchase and Sales Management.
10:30 AM 11:30 AM
Gain understanding on purchase and sales process in manufacturing and service based organizations;
Understand and develop documents, forms and systematic procedure for managing purchase and sales;
Know how to build sales and purchase information database;
Know how to build customer and supplier database and use the database in purchase and sales activities.
Day 1 Session 03
Understand Functions, Formulas and Referencing in Microsoft Excel
11:30 AM 3:00 PM
Understand absolute and relative referencing in writing functions and formulas;
Understand and use commonly used statistical formulas: sum, average, count, max, min, sumproduct etc.
Understand basic and advanced logical functions: IF, Nested If, Countif(s), Sumif(s) etc.
Understand lookup functions: Lookup, Vlookup, Hlookup, Match, Offset functions.
Day 1 Session 04
Understand and Apply Basic and Advanced Tools in Excel
3:00 PM 5:00 PM
Understand and Apply Basic and Advanced Conditional Formatting in Excel
Understand and Apply Basic and Advanced Filtering
Understand and Apply Subtotal, Pivot Table and Pivot Charts
Understand and apply What-if-Analysis
Day 2 Session 01
Know how to Create Application Interface in Microsoft Excel
9.30 AM 11:00 AM
Understand and apply Drop-Down List;
Understand and apply Check-Boxes;
Understand and apply Option-Button;
Understand and apply Macros with Buttons.
Day 2 Session 02
Case 1: Sales/Purchase Quotation, Purchase Order Form, Sales Order form and Invoicing using Microsoft Excel
11.00 AM 12:00 PM
In this session participants will solve a case which will enable them to develop Automatic Quotation/Invoices by collecting data from customer, supplier and inventory database. Using such form will save time and money for the organization. Knowledge of this session will achieved by applying;
Integrating Vlookup/Hlookup Functions with drop down list;
Using Logical functions to set credit terms, discount, interest rates
Using Check-boxes and Option buttons to determine type of transactions;
Using Advanced Conditional Formatting/Row and Columns Functions
Day 2 Session 03
Case 2: Store sales and purchase invoice data to Sales/Purchase Database; Create and Share Purchase/Sales Reports Using Excel
12.00 PM 3:30 PM
Using macros and control toolbox, in this session, participants will learn how to store purchase and sales invoice information directly to database. Also automatic reporting and summary system will be built so that management reports can be made easily from purchase/sales database. . Knowledge of this session will achieved by applying;
Using Paste Special Utilities
Integrating Buttons with Macros
Editing Macro Codes
Using Graphical Tools and Logical Functions
Day 2 Session 04
Sharing Purchase/Sales Information with Connected Parties
3.30 PM 5:00 PM
Understand collaboration and security issues;
Develop secured collaboration platform using free and paid resources;
Share works with all connected parties (suppliers/ customers/ investors/ creditors/ auditors) in business process management;
Develop organization-wide infrastructure for implementing Microsoft Excel.