Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Public Sector

Government Use Cases related to Supply Chain / Logistics & Finance, Tax and Audit

amacisaac
Alteryx
Alteryx

Hello Public Sector Group,  I am looking for information around use cases/workflows that someone might have for government organizations using Alteryx for Supply Chain / Logistics and/or Finance Tax and Audit. Thanks for the help.

Andy MacIsaac
8 REPLIES 8
ChrisS
Alteryx Alumni (Retired)

Thanks for reaching out @amacisaac!

 

I'd love it if some of our current User Group leaders would be willing to share a bit about their experience in these areas! I'm tagging the User Group leaders to this thread for comment: @Deanna@ColleenH218 @HeatherMHarris 

 

Thanks!

 

Chris Shernaman

User Group Program Lead | Industry, Internal, APAC

Alteryx

Chris Shernaman
Community Manager
ColleenH218
8 - Asteroid

Hi there,

 

Unfortunately I haven't created workflows in any of those areas in my professional capacity. But I do know there is a Tax & Audit User Group - might they have some solutions shared in their group's page that might be useful? https://community.alteryx.com/t5/Tax-Audit-Office-of-Finance/gp-p/Finance-User-Group

 

Best of luck & sorry for the delay in response!

Colleen

dhovey
7 - Meteor

The first workflow I built and put into production looked for duplicate payments made to suppliers. In the first run, going back one year, I found over $80K of true duplicate payments that we were able to recover directly or through account credits. Year-to-date we have exceeded $250K. Now we check weekly and stop the duplicates before the payments go "out the door." 

ColleenH218
8 - Asteroid

That's amazing @dhovey ! Isn't it a great feeling when you can attribute actual dollars to work you've done?

SparkleMotion
5 - Atom

Hello. What sort of information are you interested in? For our Fleet division, I have created quite a bit of workflows supporting billing, voucher processing, and auditing. For example:

 

* Billing application: a one-click chained workflow that looks up available batches from application A's database (a user has to go into application A and prepare the charges for billing). The user selects a batch (second step of the chain) and then the workflow retrieves all of the records for that batch (say about 57k records per month). The records run through a macro that looks at specific columns to determine whether the charges are labor, fuel, parts, motor pool, sublets, and then for each category, calculates the charges, the overhead (if any), identifies whether they are capitalized expenses, and so forth, based upon our billing charts, and mapped to the database fields. The charge processing is done as a macro because it is reused in various reports as well as a process that I created for "Pre-Billing", which allows for reviewing the charges before the batch is created, as well as spot-checking the billing through the month. Once the charges are processed, they are saved into a database table. That allows us to run historical reports against the data without going through the overhead of reprocessing all the charges.

 

The next parts of the workflow run concurrently, if the user opts to have this done. They may not for older batches, since those were already processed, but they need to go into the database table for further reporting.

- Create canned reports, like a summary report for accounting, an accident report for one department, a summary of capitalized and non-capitalized charges

- (currently) generates a flat file that is saved into a directory in the FMS system and a process imports the flat file into the FMS system (hoping to have this converted to query against the above mentioned database).

Then after it is finished, the summary report is emailed out to notify specific people that the billing process has finished.

 

In support of the billing application, I have created workflows that:

- Manages email recipients for applications (not specific to billing - but used by billing)

- Deletes a batch if needed from the database the billing app uses

- Generate individual reports on the fly (without going through the entire billing process)

 

The billing application was converted from a Microsoft Access application that took about 45 minutes to run, if it ran successfully - you wouldn't even know it failed until the end. The Access app also did not maintain the data, so any time a report had to be generated from historical data, you had to re-run charge processing. It also required numerous steps, and in some cases creating pivot tables to generate specific reports. It was a horribly painful process! 

 

While Alteryx could obviously do the step of saving the data into the FMS system, the accounting group was explicitly against that.

 

* Voucher processing application - from the same application A, it extracts data from a group of tables and then generates the information for the vouchers/invoices, which may then be saved to the FMS file server and imported into the FMS system. There are a set of reports that are generated before the vouchers are saved off, which helps the user spot when there are problems, before it ends up in the FMS system. This is another application that was converted from a MS Access project. The Alteryx one allows for more flexibility in reporting and is better able to stop errors before they move on.

 

* I have had to recreate stored procedures in Alteryx in order to prevent application A from being modified. For example, for the pre-billing process, I needed to create a macro (used in multiple applications now) that extracts metered data. The stored procedure in the application wipes out a temp table every time something calls it, so it was simpler to have that data generated on the fly. Another one for pre-billing does the same work that would otherwise be used to create the batch for billing, without doing the last step. It is the same data that will go into the billing table, but does not interfere with application A's data.

 

* I have created multiple applications that allow for reporting data to the state and FEMA. We had to report on hurricane related charges, so I have created both specific hurricane (Michael) related reports, as well as ones that may be ran for any past or future storm-related events. Since they may be used for FEMA reimbursement, they had to be extremely accurate.

 

* The state recently audited the fuel usage reports that are submitted quarterly. I created a workflow that may be run at any time to generate reports that will support an audit.

 

* I had to supply accident data to the Risk Management group. I turned the request into an application that may be used to lookup accident data on the fly. We went over it yesterday, so I have a few more changes to make now that they know how much information they can get from it.

 

NOTES:

* Any workflow that modifies a database, generates import files, etc, goes into a collection we created that requires a user to be logged in and authorized.

* Any time I am asked for a report, I assume that I will need to create that report again in the future, so I will make that report customizable, adding date selection, for example.

* When I produce reports, I send a link to the app in the gallery and encourage people to run the reports on their own whenever they need them. That has actually resulted in some of the people becoming more confident in the results that they receive. It has also created an awareness of how Alteryx may be used in other cases. The more workflows I produce, the more I get asked to create!

* The workflows have been used to successfully prevent some rather large mistakes from going in, as well as to detect some mistakes made on the FMS side. They have also caught issues where there are charges being allocated to the wrong funds.

 

I hope this helps. If it's TMI, oops. If you have any questions, let me know.

amacisaac
Alteryx
Alteryx

@dhovey thank you for sharing. Was there an ah-ha moment that Alteryx helped with? 

Andy MacIsaac
amacisaac
Alteryx
Alteryx

thank you @SparkleMotion lots of good information here.

Andy MacIsaac
dhovey
7 - Meteor
I don't think there was an ah-ha moment with this work flow. We knew there was potential for duplicate payments and consultants had found some in the past. We are still refining our work flow to try and catch more and reduce our false positives. The fuzzy match tool has been very helpful.