Data Science

Machine learning & data science for beginners and experts alike.
georgewillv
Alteryx Alumni (Retired)

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.

 

via GIPHY

 

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:

 

image-20220308-195655.png

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:

 

image-20220308-203804.png

 

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.

 

Prep Work

 

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.

 

image-20220308-223442.png

 Computer Vision Tool Setup

 

In the configuration of the Image Template tool, we’re going to create annotations over the textual information we need:

 

image-20220318-193814.pngAnnotated Purchase Order

 

image-20220318-193753.png

 Annotated Invoice

 

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:

 

image-20220315-223403.pngTable 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.

 

image-20220315-223550.png

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.

 

image-20220315-230539.png

 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:

 

image-20220318-200949.png

 Snapshot Prep and Cleansing of Purchase Order

 

  1. Use a Select tool to grab the annotation you want to format.
  2. Use Text to Columns to split the data into Rows on the newline character.
  3. Use  Text to Columns to split the data into Columns on the pipe ('|') character, if applicable.
  4. Use Select Records to grab the records that have information OR use a Select tool to drop columns no longer needed downstream.
  5. Apply Regex expressions if necessary (there are none in this snapshot, but you may need to use them in your workflow).
  6. 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!

 

Data Checks

 

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?

 

image-20220310-184654.png

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.

 

image-20220318-195620.png

 Validated Delivery Recipient & Recipient 

 

image-20220318-195656.pngValidated 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.

 

image-20220318-200022.png

 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😉).

 

image-20220309-224532.png

 With this check, we can see that Alter Furniture sent and billed Ryx Data solutions an additional Einstein desk bike. 

 

image-20220309-230424.png

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.

 

image-20220309-231442.png

 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.

 

image-20220309-233359.png

 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.

 

image-20220309-233552.png

 Discrepancies among “Sales Tax” and “Total Due” line items.

 

Conclusion

 

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!

 

Try it yourself

 

If you would like to try out this workflow tutorial:

  1. Download a trial version of Alteryx Intelligence Suite if you don’t already have it here.
  2. Download Purchase Order to Invoice Matching.
  3. Unzip the Folder.
  4. Open PO_Invoice_Matching.yxmd in Designer.
  5. Select the Forms folder for both input image tools.
  6. Import the annotations that correspond to their forms.
  7. Run the workflow!

 

Additional resources on the AIS tools:

  1. Alteryx Intelligence Suite Learning Path
  2. Alteryx Intelligence Suite Tools Help Main Page
Comments
royhamptonTD
5 - Atom

Hi George, thank you for this post, it has been a huge assist for me in getting started with pdf reader (image input) in the intelligence suite.

I have a question for you as a follow up to your post:

  • How dynamic is the annotation in the image template? For instance, if I set the template for an August pdf,  but the following month the table has more rows, will the image input be able to pick up that change automatically?
adarshxr
7 - Meteor

Thanks for this post. very helpful!