Data analysis Excel sample: checked business data

A data analysis Excel sample should give you both the records and the answers that prove your calculations work. The synthetic table below lets you compare revenue, direct costs and order counts across months, segments and regions. Copy the rows into a workbook, then use the dictionary and control totals to check each summary.

The table and its row definition

This example represents a fictional US business services company. One row is one month, segment and region combination. These are monthly operating summaries, rather than individual customer transactions; all amounts are USD and all records are invented.

Copy the table into cells A1:H9. Keep the headers in the first row, exclude Markdown divider lines, and enter the period values as actual dates. Format the date cells as mm/dd/yyyy so the month-end cutoff remains visible.

Select the range and use Home > Format as Table, confirming that it has headers. Microsoft's table instructions describe those steps. Leave totals outside the source table so a later summary cannot count them as records.

If you need individual order records for sales exercises, use the separate sample sales data guide. This table's purpose is to practice reconciliation of business summaries.

The field dictionary

Do not infer that revenue divided by orders is a standard price. The table has no units, service mix or contract terms. It can support average revenue per completed order, but cannot explain whether a movement came from pricing or the mix of work.

Add formulas beside the source rows

Enter GrossProfitUSD in I1 and GrossMargin in J1. In I2 and J2, enter these formulas and fill down through row 9:

=E2-F2
=IF(E2=0,"",I2/E2)

Format column J as a percentage. Show the final comparisons to two decimal places, while keeping full precision in the cells. The gross profit margin guide covers the underlying ratio and the distinction between margin and markup.

Create a monthly summary outside A:J. Put =DATE(2026,1,31) in N2 and =DATE(2026,2,28) in N3. Revenue, cost and order formulas for the first summary row are:

=SUMIFS($E$2:$E$9,$B$2:$B$9,N2)
=SUMIFS($F$2:$F$9,$B$2:$B$9,N2)
=SUMIFS($G$2:$G$9,$B$2:$B$9,N2)

Copy them to the February summary row. Microsoft documents SUMIFS as summing values that meet multiple criteria. Add a segment criterion to isolate January Enterprise revenue:

=SUMIFS($E$2:$E$9,$B$2:$B$9,DATE(2026,1,31),$C$2:$C$9,"Enterprise")

That formula returns $200,000 from SYN01 and SYN02. For a complete cleaning and summary sequence on a company workbook, follow the Excel business analysis workflow.

The control totals and expected answer

Your monthly output should match this table. Gross profit is revenue minus direct costs; margin divides gross profit by revenue.

The total margin is 215,300 / 620,000 = 34.73%, rounded. Compute it from the totals rather than averaging displayed percentages.

Now group the same revenue by segment:

Enterprise explains the full $20,000 increase. Midmarket's total is unchanged, even though its East and West rows move in opposite directions. A stable group total can therefore conceal a change in composition.

The checks are explicit: monthly revenue sums to $620,000; segment revenue sums to the same $620,000; and $620,000 minus $404,700 equals $215,300. There are eight source records and 95 completed orders. Any failed tie is a reason to inspect the selection and formulas before interpreting a chart.

Change one input and rerun the checks

In a separate practice copy, change SYN08 revenue from $46,000 to $40,000. Leave its cost, order count and every other record unchanged.

The $6,000 revenue reduction also reduces gross profit by $6,000 because the cost input is fixed. February Midmarket revenue becomes $94,000. Enterprise still adds $20,000, but Midmarket now offsets part of that gain. Restore the original input before checking against the original control totals.

Where practice analyses go wrong

• Counting summaries twice. Appending a monthly total to these rows and summing the whole range duplicates the same activity.
• Treating text as dates or amounts. A date-looking label may not match a DATE criterion; numeric text can silently disappear from totals.
• Changing the row definition. Monthly summaries and individual orders cannot share one undifferentiated table.
• Averaging margins. Compute the company ratio from company dollars, preserving the revenue weights.
• Reading association as cause. These fields show where a movement occurred. They do not identify contract changes, discount decisions or the underlying service mix.

Turn a checked summary into a decision using the Excel analysis report example. Covirage is not affiliated with Microsoft.

Check your own workbook in Covirage

Bring a sample of your own monthly export with period, segment, revenue and matching costs. Covirage maps the columns, and its tools compute and check the comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.

See Excel analysis with Covirage for the workbook approach.