Reduced royalty accounting time by 90%, from weeks to hours

This project arose to solve some important business problems for American Cinema Inspires (ACI), an international film production and distribution company based in Los Angeles. Their website is https://americancinemainspires.com

It is very important for me to acknowledge Chevonne O'Shaugnessy, CEO, and Carlos Herrera, VP Operations. Both are students in the 2024 AI Mastery course. They had a belief that there must be a way to leverage AI to reduce the enormous amount of time the company spends each month performing their royalty accounting tasks. 

The following were the key business problems ACI was facing with their royalty accounting:

- The company licenses their movies to 6 different streaming companies to stream to their customers in both US and international markets.

- The streaming companies typically provide royalty reports with statements once per month.

- Some of the royalty reports exceed 600 lines in length.

- ACI uses project accounting to account for the profitability of movies

- ACI needs to account for the streaming revenues by the titles in their inventory because these titles are the names of the projects to which all streaming revenues must be booked.

- This process was taking so much of their accountant's time that they were constrained from going out and acquiring more movies since they had not figured out how to operationally manage more movies. ACI had grown their titles last year to about 1,000 total titles but needed to get comfortable that they could speed up their accounting processes before taking on more movies.

- There are multiple reasons why a simple matching algorithm or even a complex Excel macro could not be used to automate the matching process:

- Sometimes the streaming company would change the title of the movie to appeal to viewers in their markets. Other times, ACI may have an alternative title that is sometimes used instead of the main title or placed in parentheses 

- Many movies are dubbed in multiple languages, such as Spanish, French, Portuguese, and Italian. Usually, the royalty report will show the translated name in the language it was viewed in (e.g. Spanish) instead of the ACI inventory title.

- Even in situations when there might be an exact match in the movie title, the royalty report may put the year in parentheses or do something else that may prevent an exact match like leave out or insert a "the" in the movie title.

- Sometimes the royalty reports use separation characters such as | to separate the title from the year from the actors all in the same cell.

- Sometimes "&" is used in place of "and"

- In at least one title "4" is substituted for "For" as in "Always and 4ever".

- Sometimes accented characters such as 'ó and 'é' are used and need to be changed.

- Sometimes non-breaking spaces are used and need to get replaced with regular spaces

- Etc.

I successfully delivered an earlier project, so Chevonne and Carlos decided to entrust with me automating the royalty accounting report processing processes. 

Prior to 2024, I had never created a Python program or complex script. Years ago, I had taken an introductory Python course but had forgotten most everything I learned. Still, I had the following skills that I was able to draw upon to convince me that I could use Python to automate the matching process:

- I have worked in Product Management for years in such areas as data protection, risk management, and finance, so I am used to thinking in algorithms and step-by-step instructions

- Prior to working in Product Management, I was an accountant, and still have my license as a Certified Public Accountant in Texas

I used ChatGPT 4 to code the original version of the script and then revised later versions using ChatGPT 4o and then 01-Preview. The process was very painful at first because I would solve a new problem I found when I was testing the script only to have ChatGPT forget about a problem that we solved 5 iterations previous. But I got better at using ChatGPT to code and ChatGPT itself got better. In particular, 01-Preview was a huge leap forward that made the process of making changes much easier.

At a high level, here is how the script works:

- The script is placed into a folder on the OS. The script supports both Windows and Mac. 

- Within the current folder, the script finds an Excel file with the name "Royalty" in it. This is the presumed royalty report. The script then loads this file into a Python Data Frame.

- The script then finds the ACI inventory file that it loads into Data Frame. This file is a large file that extends from Columns A to CO. It has many different film attributes in the titles. When the script processes this Data Frame, it only processes the 10 columns with the title, alternate title, and the translated titles for 7 other languages.

- The script auto-locates the header row of the royalty report and then the correct royalty column. The Data Frame is reloaded with this new information

- The script then creates 4 new Data Frames to hold the processed data. These Data Frame correspond to reports I wanted to create:

- List of matching titles from the royalty report together with royalty dollar amounts.

- List of unmatched titles from the royalty report together with royalty dollar amounts. It is critical that the matched plus unmatched titles tie to the exact amount of the royalty payment, down to the penny. 

- Basis on which the matching was done. Right now, everything is done by exact matching but the title that was used as the basis for matching was shown. For example, if the Latin American Spanish title served as the basis for matching then that would show up on this report as the basis for matching.

- List of titles from the ACI inventory that were not matched in the royalty report.

- The script then starts the matching process by preprocessing each title it reads in the original 2 Data Frames and placing the results in the 4 new Data Frames to hold the processed results

- The script outputs a Matched Royalty Report Excel workbook with 4 worksheets, one sheet each to correspond with the Data Frames mentioned above. 

- Following is a Loom video demo to show the process in action. Please be careful whom you show this video to because there is actual data shown in the video. https://www.loom.com/share/3ff277889e594dcdb720c59278a5e64c?sid=df15ae93-ab44-494b-9128-44ba693444af

Following are likely future enhancements:

- Provide totals for all numerical data

- Calculate a Similarity Score as an additional matching criterion to be used only after all other attempts were exhausted.

- Create a journal entry to book revenues for the streaming service for the month.

- Post the journal entry to QuickBooks Online so an accountant can review prior to manually posting the journal entry. 

The business outcomes so far of the royalty accounting script are as follows:

- The time required to process the monthly royalty reports has been reduced by about 90%, from weeks or days to just hours.

- The company is now confident that it can manage more titles and is planning to purchase additional movies and shows. 

Since some of the awards look at use of AI across various domains, I thought I would mention other ways I am currently using AI:

·      Automated a purchase order process for a company in which P.O.s are sent to a specific email address and the Make workflow and accompanying Open AI Chat Completion Prompt to take the 4 embedded HTML tables, extract the key fields, and log them to a Smartsheet. The key to completing this workflow was the Chat Completion Prompt, which had to detail the complex data patterns of the 4 HTML tables that made up the various parts of the P.O.

·      Used DALL·E to create icons and logos for products I am responsible for. Attached are several screenshots with examples of logos that originated from my DALL·E prompts.

·      Using ChatGPT to propose ideas to my rheumatologist to treat my knee arthrofibrosis, which has been very challenging to treat. I have uploaded a document I have shared with my rheumatologist. 

·      Use ChatGPT multiple times daily to research issues from multiple disciplines from health to law to marketing and business.

·      Lately, I have been using Google’s Gemini Deep Research to research issues ranging from factors to consider when choosing to enter the Argentine market selling non-destructive testing equipment (for a friend) and key challenges small and medium-sized businesses face leveraging AI.