HOME/INSIGHTS/Case Study: Microsoft License Reconciliation
article

Case Study: Microsoft License Reconciliation

Blue network cables behind the title Microsoft License Reconciliation, nearly 30,000 licenses matched to customer invoices

Nearly 30,000 licenses matched to customer invoices, with an analysis of margin by customer

Client name withheld

Background

The client, a technology company, resells Microsoft licenses that it purchases through a cloud distributor and bills to its customers each month. During a routine review call, the company asked its CRCFO engagement lead whether the licenses it invoices to customers match the licenses it purchases from the distributor.

We reconciled every license for one monthly billing cycle rather than testing a sample. The reconciliation confirmed that customer billing was accurate. We also identified approximately $12,000 per year in vendor charges that were not billed to any customer, and we determined that gross margins on nonprofit, foundation, and municipal customers were significantly lower than margins on commercial customers.

Approach

We used three data sources for the billing cycle: the distributor’s cloud billing summary, the QuickBooks sales detail, and the QuickBooks purchases detail. We matched each license on the distributor’s bill to the customer invoice on which it was billed. The reconciliation covered nearly 30,000 licenses across approximately 20 customer accounts.

When the company corrected some of its source data during the engagement, we updated the reconciliation using the corrected records. We explained each variance individually and provided the client with a supporting schedule that presents the reconciliation by customer account.

Results

  • Customer billing: Accurate
  • Distributor cost lines matched to a customer invoice: All but one
  • Licenses reconciled: Nearly 30,000
  • Customer accounts: Approximately 20
  • License variance: Less than 0.5%, fully explained, with no billing errors
  • Gross margin for the billing cycle: 9.9%

The license variance is fully explained. Licenses purchased but not invoiced consist of licenses for a former customer (discussed below) and licenses from a plan upgrade at one account; the upgrade nets to zero on that customer’s invoice. These differences are partly offset by no-charge licenses included on two customers’ invoices and by two metered Azure lines that each differ by one unit.

Unbilled vendor charges

One of the distributor’s cost lines could not be matched to a customer invoice. The line relates to a former customer that canceled its contract with the company. The company continues to pay approximately $1,000 per month, or approximately $12,000 per year, for licenses that could not be canceled with the distributor when the customer canceled. The distributor’s bill already identified the line as having no customer invoice. We quantified its annual cost and recommended that the company resolve the remaining term with the distributor through a cancellation, a credit, or a confirmed end date.

Margin by customer type

Because the reconciliation matched cost and revenue for each account, we could also calculate gross margin by customer. The overall gross margin of 9.9% combines two groups of customers with markedly different margins.

Dot chart of gross margin by customer account: commercial customers at 12.3% and nonprofit, foundation, and municipal customers at 5.3%

Key figures

  • Commercial customers: Gross margin of 12.3%, with every commercial account at 9.4% or higher.
  • Nonprofit, foundation, and municipal customers: Gross margin of 5.3%, with all six of the lowest-margin accounts in this group.
  • Largest nonprofit account: Accounted for 96% of this group’s billing for the cycle.
  • Annual renewal: That account’s invoice for the cycle included its annual renewal and therefore represents nearly a full year of billing. Each percentage point of margin equals approximately $900 per year, and pricing at the commercial margin would add approximately $6,000 per year in gross profit.

The difference results from pricing and is not a billing error. The company may have sound reasons for its current pricing. Microsoft’s nonprofit pricing reduces the company’s cost on these accounts, and most of that discount appears to be passed through to customers. Because the overall margin combines both groups, the difference was not apparent from the total.

How AI was used

Like many resellers, the company recorded license quantities in the memo field in QuickBooks rather than in the quantity field. As a result, QuickBooks reported a quantity of one for each line, and standard reports could not be used to count licenses. Matching thousands of licenses manually from free-text descriptions would not have been practical.

We used AI to perform the line-by-line work: extracting license quantities from the memo text, matching licenses across the three data sources, and preparing a first draft of the explanation for each variance. We did not begin with a hypothesis, and we focused our work on the differences that the reconciliation identified.

CRCFO staff were responsible for the conclusions. The CRCFO engagement lead reviewed the findings before the report was sent to the client, and the pricing analysis and recommendations reflect CRCFO’s professional judgment. The engagement lead is also working with the company to change how it records license quantities.

Recommendations

  • Unbilled charges: Resolve the remaining term of the former customer’s licenses with the distributor so that the charges end.
  • Invoice references: Make the bookkeeper’s current practice of recording invoice numbers on the distributor’s lines a required procedure.
  • Nonprofit pricing: Review nonprofit pricing to confirm that it is intentional, beginning with the largest account.
  • Annual-term licenses: Track annual-term licenses on a separate schedule so that they do not distort the monthly reconciliation.
  • Other vendors: Apply the same reconciliation to the company’s other subscription vendors.

Relevance to other companies

Any company that resells subscriptions, or that reconciles vendor bills against customer invoices, may have similar issues, including vendor costs that are not billed to customers and margin differences that are not apparent in total results. In this engagement, a specific client question led to an improved control and a pricing discussion supported by data.

Visit www.crcfo.com to discuss a similar review for your company and to learn more about our financial services for technology companies or to schedule a consultation.

Basis of preparation: Management analysis prepared from client records. This analysis is not an audit, review, or compilation.

Questions about what you just read? Talk to a CFO.

One call, no charge, and a straight answer on whether we are the right fit.