Automate Accounting Tasks using Python
Build Solutions to Automate Repetitive Tasks for Financial Audits and Share them with Your Finance Colleagues to Improve their Productivity
If you want to impact with solutions to automate repetitive tasks, go to your Finance Department.
Based on my experience, they are still relying on manual processing of complex and heavy Excel files.
In this article, I will share a solution based on my experience extracting data from highly unstructured Excel Files to support operational and financial audits.
💌 New articles straight to your inbox for free: Newsletter
Scenario
Problem Statement
You are working as a Data Analyst for a major Logistics Company,expenses and your colleagues from the Finance Team request your support to build a model to predict the P&L of Warehouse Operations.
You need to extract information from monthly reports built by accounting teams, listing all the detailed costs by category
- 20 Warehouses included in your study
- Audit of the last 36 Months
- 720 Excel Files in Total
- 60 Item Costs to track
- 3 categories of expenses: Investments, Rental, Purchasing
Objective
Your objective is to build a tool that automatically extracts data from each of the 720 Excel files, formats it, and combines it into a single report.
This report will be used by finance to analyse costs over the last 3 years and identify trends.
Your tool will help you gain visibility in a single report without requiring additional resources to manually produce it.
Your Solution
You will design a simple Python script that will perform:
- Open every Excel Report located in a folder
- Processing and cleaning data
- Build the monthly report following the format presented above
- Merge the monthly report with the global data frame
- Saving final results in an Excel file
Import Monthly Excel Reports and Process Data
A few important points here:
- Header parameters of pandas only take the 5th (very useful with Excel files)
- Fill nan with 0 to perform calculations on numeric values
- Trim column names: this report will be used by finance to analyse costs over the last 3 years and identify trends.
If you have admins doing manual input (‘Unit Cost’ and ‘Unit Cost ‘ look the same for your users).
Format columns and perform the calculations
This part is significantly linked to the report I processed.
You can find the code for reference here:
262588213843476
Share this tool
Now that you’ve built your tool, you would like to share it with Finance teams.
It used to take 2 weeks full-time (1 headcount) to perform these tasks at my previous company.
Could you imagine the impact you would have if you could implement this simple tool?
In this short article, I explain how to convert this simple script into an executable (.exe) that can be run without Python on the machine.
Samir Saci
Where to go next
- Supply Chain Strategy: How to Turn One Sentence From the Owner Into a Model: A method for supply chain strategy that starts from what the owner says, finds the parameters inside it, connects them into one loop
- Business Planning with Python — Inventory and Cash Flow Management: How can you use data analytics to help small businesses manage inventory, predict liquidity needs, and maximize profitability?
- Are you Aware of the Potential of Your Data Expertise in Driving Business Profitability?: A reflection of a supply chain data scientist who randomly discovered the power of data analytics to help small and large businesses.
About Me
Let’s connect on LinkedIn and Twitter. I am a Supply Chain Engineer who is using data analytics to improve logistics operations and reduce costs.
If you’re looking for tailored consulting solutions to optimise your supply chain and meet sustainability goals, feel free to contact me.
