The unique approach taken for this practice specific webinar is designed to produce significant return on investment. The provider enables the investing organization to quickly adapt and put into place an Procurement (PO) process with flexible reporting capabilities. Participants would also be able to apply advanced Excel techniques to produce company-specific reporting requirements, on top of webinarbased reports created. Applying metrics of accomplishments vis-à-vis the training investment are expected to produce attractive yields of return for the training

Reports to be generated as follows:

The facilitator shall provide participants with data structures conforming to standard practice capturing data, such data capture covering the following:

  • Open Purchase Orders Report
  • Purchase Orders Receipts Report
  • Unbilled Purchase Order Report
  • Purchase Order Status Report
  • Pending Inspections Report
  • Completed Inspections Report
  • Vendor Performance Report
  • Supplier Masterfile Report
  • Inventory Masterfile Report
  • Recurring Purchase Order Report
  • Supplier Canvass Report
  • Converted Canvas to PO Report

Course Outline

The following are the subjects to be taken up during the workshop proper

Module 1: Understanding the general practice of Procurement management and reporting

    • Establishing Master Lists (pre-formatted and pre-populated with data)
    • Creating Supplier Bids or Canvass (Proforma Purchase Orders)
    • Creating Purchase Proforma Orders
    • Finalizing Purchase Orders
    • Receiving Confirmed Purchase Orders Deliveries
    • Rejecting Selected Purchase Order Items Delivered
    • Assigning Purchase Order Delivered Items Requiring Serial Numbers
    • Assigning Purchase Order Delivered Items Requiring Lot ID with Expiry Dates
    • Posting Purchase Order Deliveries Inspection
    • Closing Open Purchase Orders (With or Without Partial Deliveries)

Module 2: Understanding the normalized data model prepopulated

Module 3: Using the prepared and formatted data entry forms

Module 4:Applying the Excel tabular model for reportorial purposes

Module 5:Establishing summary data using named ranges

Module 6: Creating hyperlinked navigation for easier access of various worksheets and named ranges.

Module 7: Understanding the GL transactions related to PO Transactions.

Module 8: Understanding data slicers

Module 9: Creating Pivot tables within a worksheet with attached Pivot Chart to simulate small multiples.

Module 10: Establishing key-metrics for Procurement processes

Module 11: Creating additional date/time-based reports

Module 12: Generating comparative analysis for PO per various suppliers, PO fulfillment per supplier, PO outstanding per supplier and date

Module 13: Included exercises includes the metrics and KPI’s applied to the entire PO practice.

Module 15: Creating target to actual PO visualization.

Module 16: Creating data visualizations for comparative analysis of PO by supplier

Module 17: Creating data visualizations for analysis of PO scope by supplier using Area and Radar maps

Module 18: Creating PO Waterfall charts to monitor PO transactions according to various dimensions of data (i.e., by supplier, by Month, by Quarter, by Year, by Items purchased, by PO status).

Module 19: Creating data visualization using sparklines

Module 20: Creating pattern-based reports using:

    • Heat Maps
    • Bar Chart Maps
    • PO using GeoMaps by City.
    • Percentile Ranking for PO Transactions
    • Creating PO Scenarios
    • Creating metrics for insights
    • Creating reports and data visualization for better insight
    • Creating PO Scatter Charts with animation
    • Creating fully interactive dashboards
    • Creating single-element summaries for dashboarding purposes
    • Creating Year-on-Year PO variances and percentages
    • Creating workbook-based navigation.