This article was written by georgewillv and originally appeared on the Alteryx Engine Works blog here: https://community.alteryx.com/t5/Data-Science/Using-AIS-Computer-Vision-Tools-to-Streamline-Purchase-Order/ba-p/926362
Are you an accountant that works in the procurement or financial control department? Are you tired of spending countless hours loading data into spreadsheets? Do Pivot Tables pivot your sanity? What about the nitty gritty details of numbers and making sure everything matches up just right. Well the Alteryx Intelligence Suite has an exciting time saving feature for you.
Last time, we explained how AIS tools can extract tabular data from a Form 10Q, but today we’re going to go over how the Alteryx Intelligence Suite Computer Vision tools are able to analyze and automate away manual work surrounding purchase orders and invoices.
You more than likely know what a purchase order is but just so we’re on the same page, a purchase order is a document that is sent by a buyer of a product/service to the seller or supplier of that service with a request for an order. Think of it like you’re sending a supplier a very specific grocery list of products that you, or someone in your company, would like to purchase. Once the supplier has received your grocery list, they will verify they can fulfill the purchase order by checking the form against their inventory. If they can fulfill the order, they ship the order with an invoice. Once you receive the invoice, it likely needs to be approved and submitted so that the supplier can be paid.
Take a look at the document below, this is an example of what a purchase order looks like:
Example purchase Order form from Ryx Data Solutions to Alter Furniture Company
As you can see, there is a lot of key information companies need to extract on their purchase orders. We’re going to extract all of this necessary information from both the Purchase Order and its corresponding invoice. This will streamline any financial controls set forth by the company, enabling audit compliance.
For reference, below is what our example invoice looks like. This invoice corresponds to the purchase order sent by Ryx Data solutions to Alter Furniture:
After looking at both these documents, you can plainly see some key differences as well as things to match and verify. The purchase order number should match on both forms, along with the itemized lists, and the final pricing breakdown at the bottom of each document.
Lets jump into some prep work, first we have to load the forms into Designer and annotate them so the Computer Vision tools know what information we’re looking to extract.
Computer Vision Tool Setup
In the configuration of the Image Template tool, we’re going to create annotations over the textual information we need:
Annotated Purchase Order
We want to make these annotations very specific, as this will aid in our data prep. Simply select the field you want to extract and give it a name that makes sense. For example: the annotation of “PO00123” has been named “PO Number.”
Additionally, for the itemized list, you have the option to select the annotation as a table:
Table Annotation Option
Selecting this option tells Designer to add pipes as delimiters to what is being extracted. This makes the prep and cleansing of the table extremely easier.
Prep and Cleansing of the Itemized List, “Table” Annotation Option
If you didn’t select the table annotation, no worries! You are still able to parse and extract the information the Image to Text tool discovered, there are just some additional steps needed to take.
Prep and Cleansing of the Itemized List, “String” Annotation Option
Data Prep can be seen from the screenshot below and can be summarized into a few steps:
Snapshot Prep and Cleansing of Purchase Order
- Use a Select tool to grab the annotation you want to format.
- Use Text to Columns to split the data into Rows on the newline character.
- Use Text to Columns to split the data into Columns on the pipe ('|') character, if applicable.
- Use Select Records to grab the records that have information OR use a Select tool to drop columns no longer needed downstream.
- Apply Regex expressions if necessary (there are none in this snapshot, but you may need to use them in your workflow).
- Use a Data Cleansing tool to clean up leading/trailing white spaces.
The same steps are implemented to parse the Invoice as well. Once these steps have been utilized on all the annotations, we’re now ready for some analysis!
Since everything has been cleaned and parsed nicely, we will be doing a lot of joins on the annotations from both forms.
The first check we’d want to do is ensure that the PO number match on the invoice and the purchase order. This will be the easiest check and the one that would save us a lot of headaches. Imagine if we were to analyze the whole form only to realize they don’t match at the end?
Validated PO Number
Next, we’re going to ensure that the “Vendor Address” matches the “Supplier Address”, along with the “Delivery Recipient” and “Recipient”. This is because some companies have different offices and distribution centers. We wouldn’t want the order to go to the wrong office or be billed to the wrong department of the company.
Validated Delivery Recipient & Recipient
Validated Supplier & Vendor Address
Then we match the “Requested by” with the “Purchaser”. This is necessary so that an internal auditor can reach out to the correct department or person who requested the order, should they need to gather additional information.
Validated Requested By & Purchaser
Now we get into the nitty gritty of the data checks by matching the itemized lists. We want to make sure that what the company requested via the purchase order matches what is billed on the invoice. We recommend joining the tables on the “Item Name” and “Description”, so that we can easily see if any products are missing from either form.
Thankfully, nothing fell out of our join and all our products are in the J output of the Join tool. From looking at the table however, we can clearly see things are already off. The item prices don’t match up and there is a quantity difference between the invoice and purchase order. This could be because the company recently updated prices or someone mistyped the quantity when generating the form (perhaps if they were using Alteryx, they could automatically generate the Invoice after scanning in the items from the Purchase order).
With this check, we can see that Alter Furniture sent and billed Ryx Data solutions an additional Einstein desk bike.
And here we can see that the prices listed on the purchase order do not match the prices on the invoice.
Since we found that the item prices are different, our total prices are also bound to be different. But this check is more of a “sanity check” to make sure everything would be in order should everything upstream line up.
Final Itemized list sanity check to make sure totals are in (dis)order.
The final series of checks we’re going to do is verifying that the Total Due on both forms match. First, we run some simple math to make sure that the charges at the end of each form match up.
Calculating individual line item sums on each form.
We then calculate the difference in total prices on the charges on the form and create a Discrepancy flag to highlight which items are different.
Discrepancies among “Sales Tax” and “Total Due” line items.
Alteryx Intelligence Suite Computer Vision tools arm accountants, auditors, and other finance professionals with the ability to automate a wide array of financial controls. Once you have a workflow like this one built, you can process hundreds of purchase orders and invoices as long as they follow the same format. In this article we’ve showed how these tools can save valuable time and effort when auditing purchase orders and invoices!