A multi-channel profit and loss sheet in Excel, step by step
Build an Excel profit and loss file for Shopee, TikTok Shop and your website: sheet layout, SUMIFS and XLOOKUP formulas, splitting vouchers, fees and ads.
By The DataxLand team· · 8 min read
Most multi-channel sellers start calculating profit in Excel, and that is the right call: it is cheap, everyone can open it, and it forces you to understand every deduction. The problem is not Excel but how the file is built. A "total revenue minus total costs" file gives you a single profit number and cannot tell you which channel or which product is dragging profit down.
This post walks through building an Excel profit and loss file at the level of each product line in each order, then rolling it up by channel, product and month. The examples use Shopee and TikTok Shop — two of the largest marketplaces in Vietnam and South-East Asia — plus your own website, but the method works for any channel. At the end are the signs that it is time to stop doing it by hand.
The numbers in this post are an illustrative example with stated assumptions, not a real shop's figures. The formulas use functions available in Excel for Microsoft 365, Excel 2021 and 2024.
The principle: calculate per line, aggregate in the report
Every trustworthy profit number follows one chain (see how to calculate true profit):
Net revenue = price × qty − line discount − allocated order-level voucher − refunds
Contribution margin = Net revenue − COGS − platform fees − payment fees − shipping paid by the shop − return fees
Profit after fees = Contribution margin − allocated advertisingIn Excel, that means every row of the orders sheet must carry all the columns above. A report by channel or by product is then
just SUMIFS over pre-calculated columns. If you calculate profit on the report sheet by subtracting totals, you will never be able
to split profit by product.
A six-sheet layout
| Sheet | Each row is | Main columns | Source |
|---|---|---|---|
Orders | One product line of one order | channel, order_id, order_date, status, sku, qty, unit_price, line_discount | Order exports from each marketplace and your website |
Order_vouchers | One order with a shop voucher | order_id, shop_voucher | Order export (the seller-funded voucher column) |
Order_fees | One order | order_id, platform_fee, payment_fee, shop_shipping, return_fee, finalized | Marketplace settlement and payment reports |
COGS | One SKU over a date range | sku, from_date, to_date, unit_cost | Your purchase records |
Ads | One day of one channel | date, channel, spend | Shopee Ads, TikTok Ads, Meta reports |
Report | One summary cell | channel × month, sku × month | Calculated from the sheets above |
Turn each data range into a Table (Ctrl + T) and name it exactly as in the first column. A formula like Orders[sku] is easier
to read than B2:B5000 and expands automatically when you paste more rows.
Building the formulas step by step
Step 1: standardise order status
Every marketplace names its statuses differently. Add a small Status_map table with two columns: the original status and the
standard status (completed, cancelled, returned, in delivery). In Orders add a column:
status_std = XLOOKUP([@channel] & "|" & [@status], Status_map[key], Status_map[standard], "UNMAPPED")Filter this column for "UNMAPPED" every time you paste new data. Cancelled orders count no revenue; returned orders count revenue and then subtract the refund.
Step 2: split order-level vouchers across lines
A shop voucher applies to the whole order, so it must be split in proportion to each line's value:
line_value = [@qty] * [@unit_price] - [@line_discount]
order_total = SUMIFS([line_value], [order_id], [@order_id])
voucher_alloc = XLOOKUP([@order_id], Order_vouchers[order_id], Order_vouchers[shop_voucher], 0) * [@line_value] / [@order_total]
line_revenue = [@line_value] - [@voucher_alloc]Do not subtract vouchers funded by the marketplace: the buyer gets the discount but the shop still receives the full amount.
Step 3: COGS by sale date
Purchase prices change from batch to batch, so the COGS sheet keeps an effective date range. For the current row with an empty
to_date, fill in a far-future date (31 Dec 2099) so the formula works:
unit_cogs = SUMIFS(COGS[unit_cost], COGS[sku], [@sku], COGS[from_date], "<=" & [@order_date], COGS[to_date], ">=" & [@order_date])
cogs = [@qty] * [@unit_cogs]
has_cogs = [@unit_cogs] > 0The has_cogs column matters more than you think: a SKU without a cost gives a COGS of zero and a falsely good profit. In the
report, always show the share of revenue that has a cost.
Step 4: split order-level fees across lines
Marketplace settlements record fees per order. Split them across lines in proportion to each line's revenue in the order:
share = [@line_revenue] / SUMIFS([line_revenue], [order_id], [@order_id])
line_fees = (XLOOKUP([@order_id], Order_fees[order_id], Order_fees[platform_fee], 0)
+ XLOOKUP([@order_id], Order_fees[order_id], Order_fees[payment_fee], 0)
+ XLOOKUP([@order_id], Order_fees[order_id], Order_fees[shop_shipping], 0)
+ XLOOKUP([@order_id], Order_fees[order_id], Order_fees[return_fee], 0)) * [@share]An order not yet in the settlement returns zero fees, which inflates profit. Add a finalized column to know what percentage of
fees are already real numbers (the post on marketplace fees in 2026 explains why fees still change).
Step 5: allocate advertising
Ads are recorded by day and channel, not by order. A simple, consistent rule: split the channel's ad spend for the month in proportion to each line's revenue within that channel and month.
month = TEXT([@order_date], "yyyy-mm")
ads_month = SUMIFS(Ads[spend], Ads[channel], [@channel], Ads[month], [@month])
channel_revenue = SUMIFS([line_revenue], [channel], [@channel], [month], [@month])
ads_alloc = [@ads_month] * [@line_revenue] / [@channel_revenue]
profit_after_fees = [@line_revenue] - [@cogs] - [@line_fees] - [@ads_alloc]Write this rule at the top of the Report sheet. Anyone reading the report needs to know that profit by SKU depends on how ads are
split.
Step 6: the report sheet
Build a channel × month table with SUMIFS(Orders[profit_after_fees], Orders[channel], $A2, Orders[month], B$1), and do the same
for net revenue and contribution margin. A second table goes by SKU, sorted by profit ascending: the top row is the product to look
at first.
Example: one order with two products
Illustrative example, assumptions: a Shopee order containing one bottle of essential oil at 300,000 ₫ (COGS 140,000 ₫) and one candle at 60,000 ₫ (COGS 25,000 ₫), a 50,000 ₫ shop voucher, and total order fees in the settlement of 46,500 ₫.
| Column | Essential oil | Candle | Whole order |
|---|---|---|---|
| Line value | 300,000 ₫ | 60,000 ₫ | 360,000 ₫ |
| Allocated voucher | 41,667 ₫ | 8,333 ₫ | 50,000 ₫ |
| Line revenue | 258,333 ₫ | 51,667 ₫ | 310,000 ₫ |
| COGS | 140,000 ₫ | 25,000 ₫ | 165,000 ₫ |
| Allocated fees | 38,750 ₫ | 7,750 ₫ | 46,500 ₫ |
| Contribution margin | 79,583 ₫ | 18,917 ₫ | 98,500 ₫ |
Without splitting the voucher and fees, you would see the candle with a margin of 35,000 ₫ (60,000 − 25,000), nearly double the real figure. The error on one order is small, but multiplied by thousands of orders a month it is enough to push ads behind the wrong product.
Five common mistakes in a profit spreadsheet
- Pasting new data over the old range, so formula references shift rows. Use Tables and only append at the bottom.
- Duplicate orders when two exports overlap in dates. Add a count column
COUNTIFS([order_id],[@order_id],[sku],[@sku])and filter for values greater than 1. - SKUs that do not match between the marketplace file and the cost records (extra spaces, upper and lower case). Use
TRIMandUPPERon both sides. - Subtracting refunds twice: once from revenue and again as a cost. Refunds are only subtracted in net revenue; return shipping is a separate item.
- Dates in UTC: some files export timestamps in UTC, so an order placed at 6 a.m. on the 1st (local time) lands on the last day of the previous month.
When to stop doing it in Excel
Excel is fine with one or two channels, a few hundred orders a month and one person responsible for the file. Consider moving on when you see two or more of these signs:
- You spend more than half a day each week just exporting, pasting and fixing file formats.
- The file is so slow that every formula change means waiting; an Excel sheet holds at most 1,048,576 rows (Microsoft, read 16 Nov 2026), and the file hits that limit before your computer does.
- Two people edit it and nobody is sure which version is right.
- You want to know this week's profit on Monday morning, not at the end of the month.
The post When Excel is no longer enough goes deeper into these signs. The
XLOOKUP function used in this post is available in Excel for Microsoft 365, Excel 2021 and 2024
(Microsoft, read 16 Nov 2026);
in older versions, use INDEX and MATCH instead.
How DataxLand does this
DataxLand does exactly the six steps above, but automatically and with checks. You upload an Excel file (one sheet per table, plus
a data_dictionary sheet — see the Excel file guide) or connect Shopee and TikTok Shop directly. Si
proposes how to read the data: which column is cost, which status means cancelled or returned, which fees are still provisional;
you approve before anything is built. Vouchers, fees and ads are split to lines by fixed rules, cost is taken by sale date, and
data quality tests catch mismatched SKUs and duplicate rows before numbers reach the dashboard.
The result is a profit and loss dashboard by channel, product and week, with the share of revenue that has a cost and the share of fees that are still provisional. See the page replacing manual Excel and the comparison DataxLand vs Excel and Google Sheets.
FAQ
Is there a sample Excel profit and loss file for online shops?
The six-sheet layout and formulas in this post are enough to build your own. If you want to try it straight away on ready-made numbers, the DataxLand sample workspace runs on simulated data from a three-channel shop, including order, fee, cost and ad tables.
Should ads be split by revenue or by number of orders?
By revenue is the common choice and easy to explain. By number of orders suits shops whose products have similar prices. Either works, as long as it is fixed and written down; changing the split between months makes profit by SKU impossible to compare.
Can Google Sheets do the same as Excel?
Yes. Google Sheets has SUMIFS and XLOOKUP. The limit is size: Sheets slows down quickly as rows and formulas grow, so it suits
shops with a moderate order volume.
How often should the file be updated?
At least weekly for orders and ads, and every settlement period for fees. Update less often and you only find out a channel is losing money after it has lost money for a whole month.
Read next: How to calculate true profit on Shopee, TikTok Shop and Lazada · When Excel is no longer enough · Quickstart