Worldcom: Database Project
Problem-Solving in Action
Problem:
Worldcom faced significant challenges managing telecom-related expenses and billing complexities. One example was the monthly costs for presubscribed interexchange carrier charges (PICC) from carriers such as AT&T. However, there was also a reciprocal billing structure in which AT&T owed PICC charges to Worldcom. This dual billing setup led to complexities in accounts payable and receivables, resulting in redundancy, potential overbilling, and inefficiencies in financial reconciliation.
Solution:
I developed an Access database to streamline the reconciliation process between incoming and outgoing PICC charges with AT&T. The database enabled precise tracking of PICC expenses Worldcom owed and matched it against charges billed to AT&T. By consolidating these records, the database reduced redundancies, improved the accuracy of monthly accruals, and enhanced the efficiency of accounts payable and receivables processing. This solution provided clearer insights into net financial obligations and receivables, significantly optimizing the billing workflow.
Access Database Project:
- Identified the specific fields needed for tracking PICC charges, such as Carrier Name, Charge Type (Incoming vs. Outgoing), Date, Amount, and Status.
- Determined the database structure, including tables for tracking charges, carriers, and reconciliation records.
- Carrier Table: Stored information about each carrier (e.g., AT&T, Worldcom), including unique Carrier IDs, names, and other identifiers.
- PICC Charges Table: Stored each PICC charge with fields for Charge ID, Carrier ID (linked to Carrier Table), Date, Amount, and Charge Type.
- Reconciliation Table: Tracked reconciled charges, storing matched records with fields for Charge IDs (from both sides), Date, Amount, and Reconciliation Status.
- Defined relationships between tables, linking the Carrier Table to the PICC Charges Table using Carrier ID.
- Established relationships between the PICC Charges Table and Reconciliation Table to link incoming and outgoing charges for easy cross-referencing.
- Built a PICC Charge Entry Form to input incoming and outgoing charges easily.
- Designed a Reconciliation Form to display potential matches, showing details of both incoming and outgoing charges for manual reconciliation.
- Created queries to:
- Identify outstanding charges (e.g., charges from AT&T not yet reconciled).
- Match incoming and outgoing charges by date, carrier, and amount for reconciliation.
- Set up automated queries to flag discrepancies, such as charges where the amounts didn’t match.
.
- Designed reports that provided summaries of reconciled and unreconciled charges, monthly PICC expenses, and any discrepancies.
- Created financial summaries to support monthly accrual calculations and ensure accurate financial reporting.