WAC Disclosure Automation Case Study

This case study focuses on a successfully deployed WAC disclosure automation solution for a pharmaceutical compliance process involving Connecticut, Colorado, and Vermont state-specific reporting requirements.

The process was designed to automate the creation of long-form and short-form Wholesale Acquisition Cost disclosure documents for each product using Power Automate Desktop, Excel templates, Outlook, VBA, Python, and PDF generation.

The automation helped transform a manual, document-heavy process into a more structured, repeatable, and reviewable workflow while keeping human approval in place before final publication.

Industry: Pharmaceutical / Life Sciences
Business Area: State Price Transparency Reporting / Compliance Operations
Solution Type: Power Automate Desktop RPA + Document Automation
Use Case: WAC Disclosure Form Generation and Review
States Supported: Connecticut, Colorado, Vermont
Technologies: Power Automate Desktop, Excel, Outlook, VBA, Python, PDF Generation, Web Upload
Output: Long-form and short-form WAC disclosure PDF documents for each product
Review Process: Human-in-the-loop review and approval before final upload 

Wholesale Acquisition Cost, or WAC, is a manufacturer list price used as a reference point in pharmaceutical pricing, reimbursement, contract discussions, and transparency-related reporting workflows.

For pharmaceutical manufacturers, WAC disclosure processes can be state-specific and time-sensitive. Each state may require different formats, data points, or disclosure outputs, which makes accuracy, consistency, and documentation especially important.

In this project, the process supported state-specific WAC disclosure form creation for Connecticut, Colorado, and Vermont.

The original process started when the process owner received a raw spreadsheet by email containing product and pricing information for the quarterly review and compliance request.

From there, the team had to prepare state-specific disclosure forms manually. Each product required two outputs: a long-form WAC disclosure and a short-form WAC disclosure. Depending on the state and product volume, this could mean creating 40, 60, or more PDF forms for a single cycle.

The process required the team to work across Excel, Outlook, PDF files, templates, websites, and manual review steps.

The manual process typically included:

• Receive product data by email 
• Open and review the raw spreadsheet 
• Use state-specific form templates 
• Prepare long-form and short-form WAC disclosure files 
• Repeat the process for each product or medicine 
• Generate PDF outputs 
• Save the forms to the correct repository 
• Notify the business team for review 
• Upload approved forms to the required web location

The challenge was not only the volume of forms. The bigger challenge was that each state had different requirements, layouts, and disclosure expectations.

Connecticut and Colorado could be supported by one Power Automate Desktop RPA bot because their form generation process followed a similar pattern. Vermont required a separate but related RPA automation because the form structure and process logic were different.

The previous process was difficult to sustain because it depended on manual effort, multiple handoffs, and other teams. The final results were not always aligned with the desired output, which created rework and made the process harder to control.

Pain points:

• State-specific disclosure requirements 
• High form volume per reporting cycle 
• Two PDF forms required for each product 
• Manual spreadsheet handling 
• No validation in the original spreadsheet 
• Dependency on multiple teams 
• Rework when outputs were not correct 
• Need for senior leadership and team review before final publication

RAM Solutions developed an RPA automation solution using Power Automate Desktop to automate the WAC disclosure form creation process.

Once the email and spreadsheet were received, the bot used an Excel template containing the required final form layouts and supporting tabs. VBA scripts were used to extract and format product information, while Python was used to calculate specific values related to products and amounts.

For each product, the bot generated two PDF forms: a long-form WAC disclosure and a short-form WAC disclosure. The final PDF files were saved to the required repository, and the user was notified when the process was completed.

The solution kept a human-in-the-loop approach. The State Price Transparency Reporting team reviewed and approved the generated forms before final upload to the web.

The automation followed a structured process designed to reduce manual work while maintaining review and approval controls.

The process follows this flow:

Email Request → Spreadsheet Intake → Excel Template Processing → VBA and Python Logic → PDF Form Generation → Human Review → Web Upload

The bot received the input data, processed the spreadsheet using a controlled Excel template, applied formatting and calculation logic, generated the required long-form and short-form PDFs, saved the outputs to the correct repository, and notified the business team for review.

The automation reduced a process that previously took up to two weeks due to team dependencies into an RPA-driven workflow that can generate the required WAC disclosure forms in approximately 6 minutes per state, while preserving human review and approval before publication. 

From up to 2 weeks to approximately 6 minutes per state