Transaction-Currency Workbook Overstates Gross Margin
The CFO's SuiteAnalytics workbook 'Gross Margin by Class' shows 71.2% for the Enterprise class for Q1. The consolidated income statement shows 54.8% gross margin. The workbook uses a transaction search on invoices only, groups by Class, and sums the transaction-currency Amount field. It includes CAD, GBP and USD invoices without conversion. It also excludes vendor bills and credit memos. The Enterprise class includes $600,000 of intercompany revenue billed to the UK subsidiary at a 20% markup; the related cost sits in the UK subsidiary's COGS. Consolidated Q1 revenue is $8,000,000 and COGS is $3,616,000. The workbook's Enterprise revenue is $4,200,000 and COGS is $1,210,000. The Controller wants a one-page explanation for the CFO and a corrected workbook.
1. Quantify the main reasons the workbook margin differs from the consolidated margin. Which number is more reliable?
Read the worked answer
Three drivers. First, currency: the workbook sums transaction-currency Amount across CAD, GBP and USD, so a CAD invoice for 1,000 contributes 1,000 rather than its USD equivalent; this inflates revenue and COGS unevenly. Second, completeness: invoices only, with no vendor bills or credit memos, so COGS is understated. Third, intercompany: $600,000 of Enterprise revenue billed to the UK sub at a 20% markup is included, while the related cost sits in the UK's COGS and is not eliminated. Removing the $600,000 alone takes Enterprise margin from 71.2% to about 66.4%; the rest is currency and missing cost.
The consolidated income statement is more reliable because it uses base currency, all transactions, and eliminations. The workbook is a diagnostic, not a GAAP report.
2. Rebuild the workbook correctly: specify fields, filters, joins, and handling of multi-currency and eliminations.
Read the worked answer
Rebuild with base-currency amounts, not transaction-currency Amount. Include all revenue and COGS transaction types - invoices, credit memos, cash sales, vendor bills, and item receipts or fulfillments as appropriate - and group by Class only after confirming Class is populated on both revenue and COGS lines. Exclude the elimination subsidiary or apply the elimination; exclude intercompany class pairs. Add a reconciliation column to the consolidated income statement by class and investigate any variance over a threshold. Use the accounting book and consolidated exchange rate consistently, and lock the workbook definition so users cannot swap Amount back in.
3. What governance and reporting controls should accompany this workbook so it is not used for external reporting?
Read the worked answer
Assign a report owner, require quarterly reconciliation to the GL, document the field definitions, and review changes through a change log. The CFO should see both the management view and the GAAP view, with a bridge that explains intercompany, currency and completeness differences. Do not let a transaction-currency workbook be used for external reporting; label it as management information and restrict distribution until reconciled.