Weekly Sales Forecast Report Generator

Overview

This flow runs on a weekly schedule (every Monday at 8:00 AM ET), pulls all open opportunities from Salesforce, calculates weighted pipeline values by stage probability, writes a summary row to an Excel workbook on SharePoint, and emails a formatted HTML forecast report to sales leadership and finance stakeholders.

Use Case

Sales leaders and finance teams need a consistent, automated view of the open pipeline each week. This flow eliminates manual CRM exports by pulling opportunity data directly from Salesforce, calculating weighted forecast amounts (Amount x Probability%), logging a weekly snapshot to Excel for trend analysis, and distributing a formatted report via email. It demonstrates: scheduled Salesforce data retrieval, in-flow calculations with Select and Compose, Excel Online row insertion, and HTML email generation.

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Configure connections

Open the flow in the designer and authorize each connector (Salesforce, Excel Online, Outlook) with your tenant credentials.

Set environment variable values

Update the 4 env vars in the solution to match your environment - especially the SharePoint site URL, Excel workbook path, and recipient email list.

Prepare the Excel workbook

Create a workbook at the configured path with a table named per the env var. The table should have columns: ReportDate, OpenOpportunities, TotalPipeline, ForecastNotes.

Adjust the Salesforce filter

The $filter on Get Open Opportunities defaults to IsClosed eq false. Add additional filters (e.g., by Owner, Stage, or CloseDate range) as needed.

Customize the email template

Edit the Compose HTML Report action to modify the report layout, colors, or add additional metrics.

Change the schedule

Edit the Recurrence trigger to change frequency, day of week, or time zone.

Turn on the flow

Once all connections are authorized and env vars are configured, toggle the flow from Off to On.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

mul(float(coalesce(item()?['Amount'], '0')), div(float(coalesce(item()?['Probability'], '0')), 100))

$@{formatNumber(float(coalesce(item()?['Amount'], '0')), 'N2')}

length(body('Select_Forecast_Data'))

concat('Weekly Sales Forecast Report - ', utcNow('yyyy-MM-dd'))