management reporting

How to automate a Monday report from five sources

AA
Aurum Avis Labs Author
6 min read

The Monday report from five sources

You can automate a Monday report when the output is the same file every week: the same columns, the same order, the same recipients, and one person who checks the figures before it goes out. If the question in the room changes every Monday, the report is a conversation. A workflow will turn that conversation into a file that looks certain and is wrong.

This is for the office lead, the controller, or the managing director in a Swiss SME who still glues the weekly numbers by hand. A CRM export, two spreadsheets nobody merged, a supplier PDF, and a mail with the figure “that is not in the system”. The same person does it. If they are ill, the report waits.

Copilot in the document can write a cleaner paragraph about numbers you already pasted. It does not pull five systems that use different logins and permissions. The glue stays. In the set with invoices and quote follow-up: three workflows worth automating.

Name the five sources before you talk about a dashboard

Write the actual files from last Monday, not the systems you wish you had. Most offices land on a set like this.

  1. Export

    The CRM or the shop system, saved to a known folder, same filename pattern each week.

  2. Two sheets

    Usually sales or operations, maintained by hand because the system does not have the column.

  3. A PDF

    Supplier, bank, or a portal someone downloads. It does not arrive by itself.

  4. A mail

    One figure a colleague types because it is not in any export yet.

  5. One output

    The sheet or PDF leadership already knows, with a fixed column order.

If you cannot point at last week’s five, you are designing a report that does not exist yet. Build the manual version until it has survived four Mondays in the same shape. Then the automation has something to copy.

Rights matter as much as columns. The person who builds the flow needs a login that is allowed to read each source, or a drop folder the owner already fills. “We will figure out access later” means the report still depends on someone clicking Export while logged in as themselves.

What good enough means

You can see whether it is good enough on a Thursday, not at kickoff.

  • The same columns, in the same order, with written names that do not change because someone preferred a shorter header
  • A clock time: before 10:00 Monday, not “when leadership asks”
  • A named person who opens the file and compares totals to the source before it is sent
  • A rule for a missing source: the report waits, or that cell stays blank. It does not get a guessed number

Figures that enter the report do not come unchecked from a model. A model may draft the two-sentence commentary after the totals match. The send still waits for the person on the checklist.

If the truth between the systems lives in a workbook only one person understands, that is often Excel acting as a shadow system. You do not need an AI that explains the workbook. You need the same import and the same columns, with a second person who could run Friday if the first is away.

A wholesale Monday in Aarau

Picture a wholesale firm with about 35 people in Aarau. No analyst. Every Monday the office manager builds one sheet for the two managing directors.

The sources, in the order she actually opens them: an open-invoices export from bexio, a pipeline export from the CRM, a visits sheet and a discounts sheet that sales keep in Excel, a supplier PDF of back orders she downloads from a portal, and a short mail from the warehouse with a count that is not in the system. She pastes, deletes last week’s rows, checks that three totals still match the exports, and sends the file. On a clean week it takes under an hour. On a bad week a column has moved and she is still in it at noon.

The output worth automating is that sheet: eight columns she can name without opening the file, same recipients, same deadline. The build can place the two exports and the two sales sheets into those columns, and leave two cells empty until the PDF and the warehouse mail are in. She still checks the totals. She still types or approves the warehouse number, because that number has no system behind it.

What you leave alone: the question the directors sometimes ask on Tuesday (“what if we drop this product line”). That is a new cut of the data. It belongs in the meeting, and it should not become the Monday file’s layout.

Same test as which process to automate first. This one repeats, it hurts when the columns move, it ends when the sheet is sent, and the files already exist. The Tuesday hypothetical fails the test because the question is new each time.

When to leave it alone

Leave it when the recipients, the columns, or the question change most weeks. Leave it when a source is a conversation: a colleague who “will know the number” and does not write it down. A workflow will freeze last week’s question and present it as this week’s answer.

Leave it when you want a live dashboard of everything, and nobody has agreed the eight columns. That project has no edge. You will spend the build negotiating the report, which is a management task, and the Monday paste will still happen in the meantime.

A missing permission is a pause, not a reason to scrape someone’s inbox. If the portal has no export and a person must download the PDF, the flow starts at the folder where they drop it. The download stays a person until the vendor gives you a file.

What you bring to the Check

Bring last Monday’s sources, the names of the recipients, and the time the file has to leave. One sample with the columns marked is enough. Black out customer names if you want. The half hour is about your report, not a dashboard pitch.

That is the Process Check: 30 minutes, one workflow, a case and a package size if a build is justified. If the columns still move every week, the half hour can end with “write the sheet down for a month, then come back”. That is a usable result.

management reporting spreadsheets process automation
AA

Written by

Aurum Avis Labs

Builds and ships at Aurum Avis Labs. Writes here about what we learn working with founders and SMEs in the DACH region.

Customize your cookie preferences. Essential cookies cannot be disabled as they are required for the website to function properly.

Essential Cookies

Required for basic website functionality, security, user authentication, and error tracking.

Always active

Analytics Cookies

Help us understand how visitors interact with our website to improve user experience. Includes Google Analytics and Microsoft Clarity session recordings.

Marketing Cookies

Used to track visitors across websites to display relevant and engaging advertisements.