Roll a synthetic plan forward

What it does#

Shows the roll-forward rule without needing Excel. Finalized months from the base plan stay fixed, refreshed actuals replace moving months, and forecast percentages follow yyyy-MM month keys instead of column positions.

You need#

PowerShell 7.6.2 or newer
No Azure modules
Prior plan rows and refreshed actual rows that share ScopeKey and Month

Where it goes#

Save it as Merge-SyntheticPlanForward.ps1. Adapt the merge function inside your workbook generation step.

Merge-SyntheticPlanForward.ps1

# SPDX-License-Identifier: MIT
# Copyright (c) 2026 Sarang Datye
#Requires -Version 7.4
[CmdletBinding()]
param()

function Merge-PlanForward {
    param(
        [Parameter(Mandatory)]
        [object[]]$PriorRows,

        [Parameter(Mandatory)]
        [object[]]$RefreshedActuals,

        [Parameter(Mandatory)]
        [string[]]$FinalizedMonths,

        [Parameter(Mandatory)]
        [string[]]$ForecastMonths
    )

    $priorByKey = @{}
    foreach ($row in $PriorRows) {
        $priorByKey["$($row.ScopeKey)|$($row.Month)"] = $row
    }

    $actualByKey = @{}
    foreach ($row in $RefreshedActuals) {
        $actualByKey["$($row.ScopeKey)|$($row.Month)"] = $row
    }

    $scopeKeys = @(
        @($PriorRows.ScopeKey) + @($RefreshedActuals.ScopeKey) |
            Sort-Object -Unique
    )

    $merged = New-Object System.Collections.Generic.List[object]

    foreach ($scopeKey in $scopeKeys) {
        foreach ($month in ($FinalizedMonths + $ForecastMonths)) {
            $key = "$scopeKey|$month"
            $prior = $priorByKey[$key]
            $actual = $actualByKey[$key]

            if ($FinalizedMonths -contains $month -and $prior) {
                $merged.Add([pscustomobject]@{
                    ScopeKey = $scopeKey
                    Month    = $month
                    Kind     = 'finalized'
                    Amount   = $prior.Amount
                    ForecastPercent = $prior.ForecastPercent
                })
                continue
            }

            if ($actual) {
                $forecastPercent = if ($prior) { $prior.ForecastPercent } else { 1.00 }
                $merged.Add([pscustomobject]@{
                    ScopeKey = $scopeKey
                    Month    = $month
                    Kind     = 'refreshed'
                    Amount   = $actual.Amount
                    ForecastPercent = $forecastPercent
                })
                continue
            }

            if ($prior) {
                $merged.Add([pscustomobject]@{
                    ScopeKey = $scopeKey
                    Month    = $month
                    Kind     = 'forecast'
                    Amount   = [decimal]($prior.Amount * $prior.ForecastPercent)
                    ForecastPercent = $prior.ForecastPercent
                })
            }
        }
    }

    return $merged
}

$priorRows = @(
    [pscustomobject]@{ ScopeKey = '<subscription-id-1>|api'; Month = '2026-01'; Amount = 1000.00; ForecastPercent = 1.00 }
    [pscustomobject]@{ ScopeKey = '<subscription-id-1>|api'; Month = '2026-02'; Amount = 1100.00; ForecastPercent = 1.00 }
    [pscustomobject]@{ ScopeKey = '<subscription-id-1>|api'; Month = '2026-03'; Amount = 1100.00; ForecastPercent = 1.15 }
)

$refreshedActuals = @(
    [pscustomobject]@{ ScopeKey = '<subscription-id-1>|api'; Month = '2026-02'; Amount = 1125.00 }
    [pscustomobject]@{ ScopeKey = '<subscription-id-1>|api'; Month = '2026-03'; Amount = 1130.00 }
)

$merged = Merge-PlanForward `
    -PriorRows $priorRows `
    -RefreshedActuals $refreshedActuals `
    -FinalizedMonths @('2026-01') `
    -ForecastMonths @('2026-02', '2026-03')

$override = $merged | Where-Object { $_.Month -eq '2026-03' } | Select-Object -First 1
if ($override.ForecastPercent -ne 1.15) {
    throw 'The analyst override did not roll forward with the month key.'
}

$merged | Format-Table ScopeKey, Month, Kind, Amount, ForecastPercent -AutoSize

Check that it works#

Run:

powershell

pwsh -NoProfile -File ./Merge-SyntheticPlanForward.ps1

You should see January preserved as finalized, February refreshed, and the March forecast percentage preserved as 1.15.