Photo by Rubaitul Azad on Unsplash
Why standard profit figures mislead ecommerce sellers
A sale that ends in a return does not simply cancel itself. The outbound shipment has already left, the payment gateway has already charged a fee, the warehouse team has already spent time picking and packing, and the returned item may arrive damaged. Every one of those costs is real even though the revenue disappears. If your profit model ignores this, your margin figures are overstated every time a customer sends something back.
This guide builds a bounded, worked model inside Google Sheets and publishes it as a live web calculator using GSheetPress. The model covers the costs most commonly retained after a return. It does not cover tax treatment of refunds, chargeback fees, or exchange-order fulfilment, all of which vary by jurisdiction and platform; verify those locally with your accountant or platform documentation.
Key terms explained
- Return rate: The percentage of units sold that customers send back, expressed as a number between 0 and 100 in this calculator.
- Gross revenue: Total sales income before any deductions, calculated as unit selling price multiplied by units sold.
- Retained cost: Any cost that you have already paid and cannot recover once a return is processed, such as outbound shipping or payment-gateway fees.
- Refund amount: The cash you return to the customer. This may equal the full selling price or only part of it depending on your policy.
- Return shipping cost: What it costs to bring the item back to your warehouse, whether you pay the carrier directly or reimburse the customer.
- Processing cost: Labour and handling time spent inspecting, repackaging, or disposing of returned goods.
- Damaged stock write-off: The cost price of units returned in unsaleable condition that must be discarded or heavily discounted.
- Adjusted profit margin: Net profit after all return-related costs are deducted, divided by gross revenue, expressed as a percentage.
- Contribution per unit: Selling price minus all variable costs for a single unit, including the expected return-cost burden spread across the full order volume.
- COGS (Cost of Goods Sold): The direct cost of producing or purchasing the goods you sell, excluding overheads.
What you will build
You will create a seven-input web calculator that accepts revenue and cost figures for a batch of orders, applies a return rate to estimate how many units come back, computes every retained and additional cost, and outputs adjusted net profit and adjusted profit margin. A second scenario column lets you compare a higher return rate against your baseline.
The finished calculator is suitable for reviewing a product line, running a pricing sensitivity check, or presenting contribution figures to a team. It is not a substitute for a full profit-and-loss account.
Prerequisites
- A Google account with access to Google Sheets.
- A GSheetPress account. Sign up at gsheetpress.com/admin/signup and confirm that your plan includes the Calculator entitlement. The Calculator feature is a paid entitlement; check the pricing page to confirm it is included in your current plan before you begin.
- The GS2Web SaaS add-on installed in Google Sheets, or the equivalent installed add-on linked to the same Google account you used to sign up. The account email must match; mismatched accounts are the most common reason the Calculator menu does not appear.
- A brand-new, dedicated sample spreadsheet. Open sheets.new to create one instantly. Never use a file containing real financial data.
Step 1 — Understand the cost model before touching the spreadsheet
Before entering anything into Sheets, map the numbers you will need. For a batch of orders the model uses these variables:
- Units sold — the number of orders dispatched.
- Selling price per unit — the price the customer paid.
- COGS per unit — what each unit cost you to source or manufacture.
- Outbound shipping cost per unit — the carrier cost to send the item (already spent whether or not it is returned).
- Return rate % — expected percentage of units returned.
- Refund per returned unit — the cash refunded to each returning customer.
- Return shipping + processing cost per returned unit — combined inbound return carrier cost and warehouse handling per returned item.
The model intentionally combines return shipping and processing into one input to keep the form concise. If you need them separated, add an eighth input using the same technique shown for inputs six and seven.
Damaged-stock write-off is handled as a percentage of returned units rather than a separate input, to avoid overcomplicating a beginner model. You will enter it as a formula in the results section.
Step 2 — Create the calculator template
- Open your dedicated sample spreadsheet.
- In the menu bar click Extensions, then hover over GS2Web SaaS (or your installed add-on name), then click Calculator, then click New Calculator.
- A dialogue asks for a name. Enter Returns Profitability Calculator.
- When asked for the number of inputs, enter 7. This count excludes the Calculate button.
- When asked whether to include graphs, choose No for this model.
- Click Create (or the equivalent confirmation button). GSheetPress inserts a new sheet tab and auto-generates the header row in A1:H1.
The generated headers in row 1 are: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules. Do not edit row 1. Because you chose 7 inputs, the add-on has also reserved rows 2–8 for inputs, row 9 for the Calculate button, row 10 for the merged Results Fields separator, row 11 for result column headings, and rows 12 onward for individual results.
Step 3 — Configure the seven input rows
Work through each row exactly as described. Column G (Inputs) is overwritten by the web form when a visitor submits values; never store result formulas in column G. Column E holds the default values that pre-fill the form. To test your formulas before publishing, copy the E-column values into G using Paste special → Values only.
Row 2 — Units sold
- A2 — Label: Units Sold
- B2 — Input Type: Number
- C2 — Required: Check the Required checkbox (tick it on)
- D2 — Data: Leave blank
- E2 — Default: 500
- F2 — ToolTips: Total number of orders dispatched in the period
- H2 — Rules: Leave blank
Row 3 — Selling price per unit
- A3 — Label: Selling Price per Unit
- B3 — Input Type: Number
- C3 — Required: Check the Required checkbox
- D3 — Data: Leave blank
- E3 — Default: 45
- F3 — ToolTips: Price the customer paid, in your local currency
- H3 — Rules: Leave blank
Row 4 — COGS per unit
- A4 — Label: COGS per Unit
- B4 — Input Type: Number
- C4 — Required: Check the Required checkbox
- D4 — Data: Leave blank
- E4 — Default: 18
- F4 — ToolTips: Cost of goods sold per unit — what it cost to source or make each item
- H4 — Rules: Leave blank
Row 5 — Outbound shipping cost per unit
- A5 — Label: Outbound Shipping per Unit
- B5 — Input Type: Number
- C5 — Required: Check the Required checkbox
- D5 — Data: Leave blank
- E5 — Default: 4.5
- F5 — ToolTips: Carrier cost to dispatch each unit outbound; this is a retained cost even on returned orders
- H5 — Rules: Leave blank
Row 6 — Return rate %
- A6 — Label: Return Rate %
- B6 — Input Type: Number
- C6 — Required: Check the Required checkbox
- D6 — Data: Leave blank
- E6 — Default: 10
- F6 — ToolTips: Enter a whole number, e.g. 10 means 10 percent of units are returned. Do not enter a decimal such as 0.10.
- H6 — Rules: Leave blank
Row 7 — Refund per returned unit
- A7 — Label: Refund per Returned Unit
- B7 — Input Type: Number
- C7 — Required: Check the Required checkbox
- D7 — Data: Leave blank
- E7 — Default: 45
- F7 — ToolTips: Cash refunded to each returning customer; often equals the selling price but may be partial
- H7 — Rules: Leave blank
Row 8 — Return shipping and processing cost per returned unit
- A8 — Label: Return Shipping & Processing per Unit
- B8 — Input Type: Number
- C8 — Required: Check the Required checkbox
- D8 — Data: Leave blank
- E8 — Default: 6
- F8 — ToolTips: Combined inbound carrier cost and warehouse handling cost per returned item
- H8 — Rules: Leave blank
Row 9 — The Calculate button (generated, do not delete)
Row 9 was auto-generated by the add-on. Confirm that it reads:
- A9 — Label: Calculate (leave as generated)
- B9 — Input Type: Button
- D9 — Data: main
- E9 — Default: Reset (leave as generated; this is the button's reset label, not a value you change)
Do not alter or delete row 9.
Step 4 — Add helper values in the results area
The add-on reserved row 10 as a merged separator, row 11 for result column headings, and rows 12 onward for results. You will now enter labels in column A and formulas in column B starting at row 12.
All formulas read their inputs from column G (G2 through G8), which is populated by the web form at runtime. When testing locally, paste the defaults from E2:E8 into G2:G8 using Paste special → Values only first.
To enter a formula: click the target cell, type the equals sign followed by the rest of the expression shown below, then press Enter. The leading = is part of the formula.
Result labels and formulas
Enter the following in the stated cells. Column A holds the label; column B holds the formula.
- Click A12 and type:
Gross Revenue - Click B12 and enter:
=G2*G3 - Click A13 and type:
Total COGS - Click B13 and enter:
=G2*G4 - Click A14 and type:
Total Outbound Shipping - Click B14 and enter:
=G2*G5 - Click A15 and type:
Estimated Units Returned - Click B15 and enter:
=INT(G2*(G6/100))INT rounds down to a whole unit count. For example 500 units at 10 % returns 50.
- Click A16 and type:
Total Refunds Paid - Click B16 and enter:
=B15*G7 - Click A17 and type:
Total Return Shipping & Processing - Click B17 and enter:
=B15*G8 - Click A18 and type:
Damaged Stock Write-off (est. 20% of returns at COGS) - Click B18 and enter:
=INT(B15*0.2)*G4This assumes 20 % of returned units arrive unsaleable and must be written off at COGS. If your damaged rate differs, change the 0.2 to your own figure. Because this is a fixed assumption rather than an input, it is documented here as a model limitation.
- Click A19 and type:
Adjusted Net Profit - Click B19 and enter:
=B12-B13-B14-B16-B17-B18 - Click A20 and type:
Adjusted Profit Margin % - Click B20 and enter:
=IF(B12=0,"N/A",ROUND((B19/B12)*100,2))The IF guard prevents a division-by-zero error when gross revenue is zero. The result is rounded to two decimal places.
- Click A21 and type:
Contribution per Sold Unit - Click B21 and enter:
=IF(G2=0,"N/A",ROUND(B19/G2,2))
Step 5 — Add an alternative return-rate scenario column
Comparing profitability across return-rate assumptions is easier with a parallel column. Column C will hold a higher return-rate scenario. You hard-code the alternative rate directly in the formulas; this keeps the web form at seven inputs while still giving a comparison.
The alternative rate used below is 20 %. Change the value 20 in each formula if you want a different comparison rate.
- Click C11 and type:
Scenario: 20% Return Rate - Click C12 and enter:
(Gross revenue is the same in both scenarios.)=G2*G3 - Click C13 and enter:
=G2*G4 - Click C14 and enter:
=G2*G5 - Click C15 and enter:
=INT(G2*(20/100)) - Click C16 and enter:
=C15*G7 - Click C17 and enter:
=C15*G8 - Click C18 and enter:
=INT(C15*0.2)*G4 - Click C19 and enter:
=C12-C13-C14-C16-C17-C18 - Click C20 and enter:
=IF(C12=0,"N/A",ROUND((C19/C12)*100,2)) - Click C21 and enter:
=IF(G2=0,"N/A",ROUND(C19/G2,2))
Step 6 — Verify with a worked example
Paste the following values into G2:G8 using Paste special → Values only to simulate a web form submission before you publish.
| Cell | Input label | Test value |
|---|---|---|
| G2 | Units Sold | 500 |
| G3 | Selling Price per Unit | 45 |
| G4 | COGS per Unit | 18 |
| G5 | Outbound Shipping per Unit | 4.5 |
| G6 | Return Rate % | 10 |
| G7 | Refund per Returned Unit | 45 |
| G8 | Return Shipping & Processing per Unit | 6 |
Expected results with these inputs:
| Metric | Baseline (10 % returns) | Scenario (20 % returns) |
|---|---|---|
| Gross Revenue | 22 500 | 22 500 |
| Total COGS | 9 000 | 9 000 |
| Total Outbound Shipping | 2 250 | 2 250 |
| Estimated Units Returned | 50 | 100 |
| Total Refunds Paid | 2 250 | 4 500 |
| Total Return Shipping & Processing | 300 | 600 |
| Damaged Stock Write-off | 180 (10 units × 18) | 360 (20 units × 18) |
| Adjusted Net Profit | 8 520 | 7 790 |
| Adjusted Profit Margin % | 37.87 % | 34.62 % |
| Contribution per Sold Unit | 17.04 | 15.58 |
Verify each B-column cell matches the value in the table above. If any figure differs, re-read the formula in that cell and confirm the G-column values are correct. For more on building interactive calculators in GSheetPress, see the Calculator details page.
Troubleshooting common problems
Results show zero when G cells are empty
The G column is empty until you either paste test values there or publish and submit the form. Always use Paste special → Values only to copy E2:E8 into G2:G8 before testing. Typing directly into G cells also works for a quick check.
The Calculator menu does not appear in Extensions
Confirm the add-on is installed under Extensions → Add-ons → Manage add-ons. Confirm that the Google account signed into the browser matches the account registered at GSheetPress. A mismatch silently hides the entitlement. If the problem persists, visit the GSheetPress support page for guidance.
Adjusted profit is a large negative number
Check that the return rate was entered as a whole number (10 for 10 %) rather than a decimal (0.10). Entering 0.10 produces a near-zero return count and distorts profit figures. The ToolTip in F6 warns users, but verify the G6 value directly.
Damaged write-off seems too high or too low
The 20 % damaged assumption is baked into the formula. If your actual damaged rate differs significantly, update the 0.2 constant in B18 and C18 to match. This is a known model limitation: the damaged rate is not a user input.
Division by zero shows in margin cells
Both B20 and C20 include an IF guard that returns the text N/A when gross revenue is zero. If you see a raw #DIV/0! error, the IF guard is missing; re-enter the formula from Step 4.
INT function rounds down aggressively on small batches
For very small unit counts (fewer than 20 units) INT can undercount returns by one unit. This is a rounding artefact of integer arithmetic. If precision matters for small batches, replace INT with ROUND(...,0) in B15 and C15. Google's official INT function documentation explains the behaviour in detail.
Frequently asked questions
Can I add payment-gateway fees as an extra retained cost?
Yes. Add an eighth input row (row 9 in the template, which shifts the button to row 10 and all subsequent rows down by one). Label it Gateway Fee per Unit, set Input Type to Number, and reference G9 in an extra deduction line in the adjusted net profit formula. Because gateway fees are typically a percentage of the transaction value rather than a flat fee, you may prefer to enter the fee as a monetary amount already calculated outside this form, keeping the model straightforward.
Should I enter prices including or excluding tax?
Use prices excluding tax (net prices) throughout the model. Sales tax or VAT collected from customers is typically a liability, not revenue, and including it inflates your margin figures. If your platform reports gross-of-tax revenue, subtract tax first before entering the selling price. Your accountant can confirm the correct treatment for your jurisdiction.
How do I compare more than two return-rate scenarios?
Add further scenario columns (D, E, and so on) in the results area, changing the hard-coded percentage in each column's return-count formula. The web form still shows only the seven defined inputs; the extra columns appear only in the spreadsheet view and in any embedded table you display alongside the calculator. See the Table details page for guidance on embedding a results table next to your published calculator.
Publish and test your web calculator
- Share the spreadsheet so GSheetPress can read it. Click Share in the top-right corner of Sheets, change General access to Anyone with the link, set the permission to Editor, then click Done. Important: this setting means anyone who obtains the spreadsheet URL can edit its contents. Use only your dedicated sample workbook, never a file containing real data or other business information.
- Make sure the calculator tab is selected (click it at the bottom of the screen).
- In the menu bar click Extensions → GS2Web SaaS → Calculator → Publish Calculator.
- Wait for the confirmation message that publishing is complete.
- In the same Calculator menu, click Calculator Link. A new sheet tab opens listing your published calculators.
- Find the row whose Sheet Name column matches Returns Profitability Calculator.
- Open the URL in column C (Link), not the URL in column B (Edit Link). Column C is the public-facing calculator.
- If you do not see your calculator in the list, click Get Links inside the Calculator menu to refresh the sheet.
- On the public calculator page, enter the seven test values from the worked example in Step 6 and click Calculate.
- Confirm that every displayed result matches the expected figures in the worked-example table: adjusted net profit 8 520, adjusted profit margin 37.87 %, contribution per unit 17.04.
- If you want to embed the calculator in a website, copy the iframe code from column D of the Calculator Link sheet and paste it into an HTML block on your page. Republishing an existing calculator using the same sheet tab retains the same public link.
- If you need to update input defaults or formulas later, edit the spreadsheet, then republish using the same menu path. The public link does not change.
For further configuration options and account management, visit the GSheetPress shop details page.