Pro forma analysis: test a business plan against actuals

Pro forma analysis translates a business plan into projected financial results and compares those results with actuals. Start with explicit operating assumptions, calculate the statement, and explain the difference by business unit. Keep the approved case intact so the comparison measures what happened against what management expected.

Define the decision and the statement

For an internal operating review, a projected income statement is a useful starting point. It connects sales volume, selling prices, purchase costs, and operating expenses to profit. The annual operating plan establishes the broader commitments; this analysis tests their financial consequences.

A complete funding plan may also need projected balance sheets, cash flow statements, and capital expenditures. The SBA's business-plan guidance calls for those statements and explicit explanations of the projections. Here, the worked case stops at operating profit, before interest and income tax; use a separate cash flow forecast for collection timing, inventory funding, and capital spending.

This internal projection is also separate from SEC non-GAAP reporting. Public disclosure of non-GAAP measures by a registrant falls under rules including Regulation G, which addresses comparable GAAP measures and reconciliation. Labeling a workbook pro forma does not resolve those reporting requirements.

Start with a frozen assumptions file

Consider an illustrative US distributor reviewing the quarter ended September 30, 2026. Its approved plan and actual results cover the same two business units. All sales and expenses below are in USD, costs are positive, and all units sold carry the listed average unit cost.

Keep a plan-version date, business-unit code, period, revenue account, cost account, and expense account in the source files. Reconcile actual revenue and expenses with the closed ledger before comparing them. Keep intercompany transactions and any central costs on the same basis in both cases.

The assumptions occupy columns A through E on a worksheet called Plan:

Actuals occupy the same columns on Actual:

These are supplied management assumptions and closed-period inputs, not estimates generated from the comparison. Record an owner for each assumption: sales owns volume and price, procurement owns purchase costs, and finance owns accounting consistency.

Calculate the projected income statement

Revenue = Units sold × Net price per unit

Cost of goods sold = Units sold × Cost per unit sold

Operating profit = Revenue − Cost of goods sold − Operating expenses

In Excel, add these columns on both worksheets and fill down:

F2 = B2*C2
G2 = B2*D2
H2 = F2-G2
I2 = H2-E2

Columns F through I hold revenue, cost of goods sold, gross profit, and operating profit. When actual expense exports contain multiple rows per unit, aggregate them by unit, period, and account first. Microsoft documents SUMIFS for summing rows that meet multiple criteria; a ledger with unit, period, account, and amount in columns A through D can use:

=SUMIFS(Ledger!$D$2:$D$5000,Ledger!$A$2:$A$5000,A2,Ledger!$B$2:$B$5000,$K$1,Ledger!$C$2:$C$5000,$L$1)

Here, K1 contains the quarter key and L1 the required account code. Covirage is not affiliated with Microsoft.

Compare the same rows with actuals

The calculated statement is:

With plan in B, actual in C, and variance in D, use =C2-B2. A positive expense variance means higher spending; it is unfavorable even though a positive revenue variance is favorable. Dollar figures are exact here; round only displayed percentages in a larger workbook.

Retail operating profit is $73,000 against $120,000 planned, a $47,000 shortfall. Industrial contributes $96,000 against $100,000, a $4,000 shortfall. Their differences sum to the $51,000 company gap. For interpreting margins separately, use the gross profit margin method.

Prove the variance before choosing an action

The company bridge must close:

=-32000-12000-7000
=169000-220000

Both return -51,000. Revenue fell $32,000, cost of goods sold rose $12,000, and operating expenses rose $7,000.

Then test the assumptions behind the revenue gap. Retail's volume effect is (11000-12000)*50, or -$50,000. Its price effect is 11000*(49-50), or -$11,000. Industrial's corresponding effects are $37,500 and -$8,500. Together, -$61,000 plus $29,000 reconciles to -$32,000.

Management can now investigate Retail volume and realized prices rather than applying the same spending restriction everywhere. A revised outlook belongs in a separate version; budget versus forecast explains that distinction.

Where it goes wrong

• Overwriting the approved case. Revised assumptions erase the original test. Preserve both versions with dates and owners.
• Mixing accounting scope. Central allocations, returns, and intercompany entries must follow the same treatment in plan and actuals.
• Using purchase volume as cost of sales. Inventory bought is not necessarily inventory sold. Match unit costs to the units recognized in revenue.
• Treating profit as available cash. Customer collections, supplier payments, and capital spending require their own timing assumptions.

Compare your own plan and actual file in Covirage

Bring your approved assumptions workbook and matching actual-results export; Covirage maps the columns, and its tools compute and check the figures by business unit. The external AI model explains the differences with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See Covirage for finance teams for the broader reporting context.