P
AI for Operations Manager

Automated Weekly Reporting with Power Automate

From Data Sources to Formatted Report — Without Human Intervention

Tools: Microsoft Power Automate + Excel + Outlook | Time to build: 1-2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Excel and Outlook — see Level 2 guides: "Use Microsoft Copilot in Excel" and "Use Microsoft Copilot in Outlook"

What This Builds

An automated workflow that runs every Friday morning, pulls your operational KPI data from a shared Excel spreadsheet, populates a report template, and emails you a pre-formatted draft ready to review and send to leadership. You review for 5 minutes and hit forward. The 3-hour reporting process becomes a 10-minute one — permanently.

Prerequisites

  • Microsoft 365 account with Power Automate access (included in most Microsoft 365 business plans)
  • Operational data in a SharePoint Excel file (or a file that updates regularly)
  • A weekly report template in Word or another Excel file
  • Basic familiarity with Excel

The Concept

Power Automate is Microsoft's automation platform — like Zapier but built into Microsoft 365. Think of it as a set of rules: "When [trigger], do [actions]." In this case: "Every Friday at 8am, read the KPI data from the Excel file, format it into the report template, and email it to me."

You build the workflow once. After that, every Friday morning your report draft is waiting in your inbox before you've had coffee.

Build It Step by Step

Part 1: Set Up Your Data Source

Step 1: Create or locate your operational data Excel file. This file should be in SharePoint or OneDrive (not on your local computer — Power Automate needs cloud access).

The file needs a structured table (formatted as an Excel Table). Key columns:

  • Week_Ending (date)
  • Throughput_Actual
  • Throughput_Target
  • OTD_Actual_Pct
  • OTD_Target_Pct
  • Overtime_Hours_Actual
  • Overtime_Hours_Budget
  • Incidents

Step 2: Each week, you (or a supervisor) update the current week's row with actual data. This is the only manual step that remains in your process.

Step 3: Note the exact SharePoint URL of this file — you'll need it in Power Automate.

Part 2: Create Your Report Template

Step 1: In Word or a new Excel file, create your report template. Where the actual data will go, use placeholder text you'll recognize:

Copy and paste this
WEEKLY OPERATIONS REPORT — Week Ending {{WEEK_DATE}}

EXECUTIVE SUMMARY
This week, throughput reached {{THROUGHPUT_ACTUAL}} units against a target of {{THROUGHPUT_TARGET}} ({{THROUGHPUT_PCT}} of target). On-time delivery was {{OTD_ACTUAL}}% vs. the {{OTD_TARGET}}% target...

KPI PERFORMANCE
| Metric | Actual | Target | Variance |
| Throughput | {{THROUGHPUT_ACTUAL}} | {{THROUGHPUT_TARGET}} | {{VARIANCE}} |
...

Step 2: Save this template in SharePoint.

Part 3: Build the Power Automate Flow

Step 1: Go to make.powerautomate.com (or find Power Automate in your Microsoft 365 app launcher). Sign in with your work account.

What you should see: The Power Automate home page with "Create" and "My flows" in the left sidebar.

Step 2: Click Create → Scheduled cloud flow.

Set:

  • Name: Weekly Ops Report Draft
  • Starting: Next Friday
  • Repeat: Every 1 Week on Friday
  • Time: 7:00 AM (before you arrive)

Click Create.

Step 3: Add the "Get a row" action. Click + New step. Search for "Excel Online (Business)." Select Get a row.

Configure:

  • Location: SharePoint
  • Document Library: Your SharePoint library
  • File: Your data Excel file
  • Table: Your data table
  • Key Column: Week_Ending
  • Key Value: Use the "Expression" tab and enter: formatDateTime(utcNow(), 'MM/dd/yyyy') (this finds the current week's row)

Step 4: Add the "Send an email" action. Click + New step. Search for "Outlook." Select Send an email (V2).

Configure:

  • To: Your own email address
  • Subject: "Weekly Ops Report Draft — [click lightning bolt icon to insert Week_Ending from the Excel data]"
  • Body: Paste your report template text, replacing the {{PLACEHOLDER}} markers with the corresponding data fields from the Excel row (click the lightning bolt icon to browse available fields after each placeholder)

Step 5: Save and test. Click Save, then Test (top right). Select "Manually" and run the test. Check your email for the draft.

What you should see: An email in your inbox with the report template populated with your actual data from Excel.

Part 4: Add AI Narrative Generation (Optional Enhancement)

For the true automation experience, add a step that calls AI to generate the executive narrative:

After the "Get a row" step, add a HTTP action to call the OpenAI API:

  • Method: POST
  • URI: https://api.openai.com/v1/chat/completions
  • Headers: Authorization: Bearer [your OpenAI API key]
  • Body: A JSON payload asking GPT-4 to write the executive summary from your data

The AI response then gets inserted into the email body automatically.

Note: This step requires an OpenAI API key (~$5/month for typical report volume) and some JSON configuration. If this feels too technical, skip it — the data-only report draft is still 80% of the value.

Real Example

Your Friday before automation: 7:00 AM — Arrive, check email. 3 urgent items. 9:00 AM — Finally start on the report. 11:30 AM — Report draft finished, check it twice. 12:00 PM — Send. Half your morning gone.

Your Friday after automation: 7:00 AM — Arrive. Email notification: "Weekly Ops Report Draft — Week Ending 03/21/2026" waiting in inbox. 7:10 AM — Open the email. Data is populated. Review for accuracy, add 2 sentences about context, forward to leadership. 7:15 AM — Done.

Input: Friday morning arrival Output: Reviewed and sent ops report Time saved: 2.5–3 hours per week, every week, forever

What to Do When It Breaks

  • Flow doesn't trigger → Check that the schedule is set correctly. Power Automate schedules run in UTC — adjust for your timezone.
  • Excel row not found → The "Get a row" step is looking for today's date. Make sure the Week_Ending column is formatted as a Date type in Excel (not text). Also ensure you've added the current week's row before Friday.
  • Email body shows "[object Object]" → A data field is being inserted incorrectly. Check that each dynamic field is inserted using the lightning bolt selector, not typed manually.
  • Flow errors out → Click on the flow in Power Automate → Run history → Click the failed run → See which step failed and the error message.

Variations

  • Simpler version: Instead of Power Automate, use a Zapier "Schedule" trigger connected to Google Sheets (if you use Google Workspace). Same concept, different tools.
  • Extended version: Have Power Automate pull data from multiple sources — your ERP, your scheduling system, your quality system — and combine into one consolidated report. Requires setting up each data connection separately.

What to Do Next

  • This week: Build and test the basic flow with your Excel data file
  • This month: Verify the automation runs correctly for 4 consecutive Fridays, then communicate to your leadership team that your report will now arrive before 8am every Friday
  • Advanced: Add a step that saves a copy of every report to a SharePoint archive folder — you'll have a perfect historical record of every week's performance automatically

Advanced guide for Operations Manager professionals. These techniques use more sophisticated AI features that may require paid subscriptions.