Nearly every team has a report that somebody assembles by hand. Every Monday, or the first of the month, a person pulls numbers from three places, pastes them into a spreadsheet, fixes whatever broke, formats it and sends it round. It takes half a day. Everyone knows it's wasteful, and it keeps happening because the person doing it is busy and nobody has asked them what's involved.
In one role I automated a group of legacy reports like this. The saving came to more than 600 hours a year, and what surprised me was where those hours had been going. It wasn't the typing.
Where the time actually goes
If you watch someone build a manual report, the hours break down roughly like this.
Fetching data. Logging in to systems, running exports, waiting. Often tedious more than hard.
Cleaning. Dates in different formats, names that don't match between sheets, duplicates, a column that moved. This is where most of the time disappears, and it's invisible because it happens in small, annoying chunks.
Checking. "That doesn't look right." The person goes back, compares with last week, finds the bug and fixes it. The checking is real work and needs to stay in your automated version, in a different form.
Formatting and sending. Copying into the template, adjusting the charts, writing a few lines of commentary, emailing it.
Automation can remove most of the first, second and fourth. The third one you must replace, not delete.
A sensible order of work
1. Write down the current process. Literally watch the person do it and note every step, including the odd ones ("I always delete the last row because it's a total"). Those odd steps are rules you'll need to reproduce.
2. Ask whether the report is still needed. I'm serious. A surprising number of regular reports are read by nobody. Sometimes the best automation is to stop sending it. Ask the recipients, or look at who opens it.
3. Get the data automatically. Use a database query, an API, or a connector to a platform export. Write the query once. If you need to schedule a download from a tool, check first whether it has a scheduled export.
4. Clean in code. A short Python script with pandas can handle most of this. The rules you wrote down in step 1 become lines of code. Keep each rule visible and commented, so the next person understands why the last row is dropped.
5. Add checks. This is what separates a trustworthy automation from a dangerous one. Compare this week's totals with last week's and flag large jumps. Check that row counts are in a believable range. Check that no key field is empty. If a check fails, stop and tell someone. Don't send a wrong report on time.
6. Produce the output. That might be a refreshed dashboard, a formatted spreadsheet or a PDF. If it's a dashboard, the report often stops being a thing you send at all, and people look when they need to.
7. Schedule and monitor it. A scheduler like cron, Airflow, or whatever your company uses will run it. Send yourself a short success message each time at first. Silence should never mean "working."
Where automation goes wrong
It fails quietly. A source system changes a column name, the script runs, and the report goes out with blanks or zeros. Everyone assumes it's fine until a manager notices three weeks later. Alerts on failure and sanity checks on results prevent this.
Only one person understands it. If it lives on someone's laptop with no notes, you've swapped one fragile process for another. Keep the code in a shared repository with a short README that says what it does, where the data comes from, and how to run it.
It automates a bad report. If the numbers were never trusted, speeding them up helps nobody. Fix the definitions first.
You automate the judgement away. The commentary, the "so what", often needs a human. I usually automate the numbers and leave a space for a person to write the two lines of interpretation. That's the most valuable part anyway.
Is it worth it?
A rough test: multiply the hours per run by how often it runs in a year, then compare with the time to build and maintain the automation. A report that takes three hours every week costs about 150 hours a year. If you can automate it in two or three days, it pays for itself within the first couple of months, and you also get consistency, which is hard to put a price on.
Start with the report that annoys people the most. It's usually the right one.