Ad hoc analysis in Excel: answer a business question

Ad hoc analysis in Excel answers one business question with a defined export, a checked summary, and a decision the reader can act on. Start with the question, calculate the comparison, and inspect its underlying groups before explaining the difference.

Define the question and the stopping point

A US distributor asks: did September's higher freight bill come from higher service rates, more shipments, or a different service mix? The operations manager needs to decide whether to challenge carrier pricing or investigate shipping choices.

Compare August and September 2026, both closed months. Use billed freight divided by shipments as the average cost measure, and compare the same sites and service labels. Exclude unrelated warehouse expenses. Record whether surcharges are included; this example includes all freight charges in the supplied summary.

The stopping condition is a reconciled site-and-service comparison. It does not require a broader review of every operating expense. The ad hoc business intelligence overview covers how this work fits management decisions; here the focus is the workbook procedure.

Prepare the export without losing its controls

Keep the supplied export on a sheet named Raw. It has one row per month, site, and service, already summarized from shipment detail. Month is an actual Excel date representing the month's first day. Before using it, confirm that the carrier detail and monthly accounting control agree with its freight amounts.

Put these columns in A:E, with the header in row 1 and data in rows 2:9:

Do not append a monthly subtotal as another source row. Keep missing sites or service labels on an exception list. A summary that omits those records can balance internally while missing part of the business.

Build a formula summary with visible criteria

On a separate sheet, put Ohio in A2 and Texas in A3. Use B for August shipments, C for August freight, D for August cost per shipment, E for September shipments, F for September freight, G for September cost per shipment, and H for freight change.

Enter these formulas in B2 and C2, then fill down:

=SUMIFS(Raw!$D$2:$D$9,Raw!$B$2:$B$9,$A2,Raw!$A$2:$A$9,DATE(2026,8,1))
=SUMIFS(Raw!$E$2:$E$9,Raw!$B$2:$B$9,$A2,Raw!$A$2:$A$9,DATE(2026,8,1))

In E2 and F2, use the same formulas with the month argument changed to DATE(2026,9,1). Microsoft's SUMIFS reference describes adding cells that meet all specified criteria; here those criteria are site and month.

Calculate D2, G2, and H2 respectively, then fill down:

=IF(B2=0,NA(),C2/B2)
=IF(E2=0,NA(),F2/E2)
=F2-C2

A zero shipment count produces an exception rather than a misleading zero unit cost. Extend every source range together when the export grows. The SUMIFS workbook guide covers more detailed criteria patterns.

Ohio accounts for the entire $360 increase. Its average cost rises from 1,200 / 120 = $10.00 to 1,560 / 150 = $10.40 per shipment. Company averages are $10.19 and $10.42, respectively, rounded to cents. Calculate ratios from totals rather than averaging site ratios.

Test the explanation against the service rows

In Raw!F2, enter the following and fill through F9:

=IF(D2=0,NA(),E2/D2)

Ohio Ground remains $8.00 per shipment in both months; Expedited remains $20.00. Texas stays at $9.00 and $22.00. These are billed service averages, so unchanged averages do not prove that every individual charge was identical.

Ohio's expedited share increases from 20 / 120 = 16.7% to 30 / 150 = 20.0%, rounded to one decimal place. Ground freight increases by 960 minus 800 = $160, and Expedited by 600 minus 400 = $200. Together they explain $360: more shipments and a greater expedited share accompany the higher bill, without a higher average within either Ohio service.

The useful next action is to inspect why Ohio used more expedited shipments. These rows cannot establish whether customer commitments, stock shortages, or avoidable choices caused that change. For a visual presentation of checked movements, use the separate Excel waterfall procedure.

Prove the answer before sending it

September's source rows total $2,500, equal to Ohio's $1,560 plus Texas's $940. August totals $2,140, equal to $1,200 plus $940. The difference is $360, matching the sum of the site changes.

Independently reconstruct September's average using its source quantities and unrounded service averages:

=SUMPRODUCT(Raw!D6:D9,Raw!F6:F9)/SUM(Raw!D6:D9)

Microsoft documents SUMPRODUCT as multiplying corresponding array components and summing the products. The result must equal September freight divided by September shipments. Covirage is not affiliated with Microsoft.

Save the question, export period, criteria, reconciliations, finding, and decision owner beside the summary. Agreement between formulas is a calculation check; agreement with the original billing control is the completeness check.

Where it goes wrong

• Moving the question. Keep new questions separate so the original request has a clear answer.
• Mixing units. Packages, orders, and shipments require different denominators; use the export's stated unit consistently.
• Losing exceptions. Blanks and unassigned sites belong in an explicit review group until resolved.
• Inferring cause from an average. Inspect service quantities and rates before recommending a pricing change.

Answer your own workbook question in Covirage

Bring a sample of your own sales or cost workbook; Covirage maps the columns, and its tools compute the group comparisons and check the figures. The external AI model explains the result with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your export and question.

See Excel analysis in Covirage for supporting context.