Photo by Rohit Choudhari on Unsplash
What this calculator does and what it excludes
This guide shows you how to build a web-ready shipping cost and order profit calculator inside Google Sheets using the GS2Web SaaS add-on from GSheetPress. When a user enters package dimensions, weight, item revenue, and cost of goods, the calculator returns the billable weight, estimated shipping charge, any surcharges, and the resulting contribution margin for that order.
What is included: actual weight vs dimensional weight comparison, a configurable dimensional factor, a flat packaging cost, a delivery base rate, an oversized surcharge, and a simple revenue-minus-costs margin calculation.
What is excluded: fuel surcharge percentages that change weekly, real-time carrier API pricing, multi-zone rate tables, duty or customs fees, returns cost, and payment processing fees. All rate figures used in the worked example are illustrative placeholders chosen to demonstrate the formulas; replace them with your carrier's current published tariff before relying on any output.
Key terms explained
- Actual weight: The physical weight of a parcel measured on a scale, usually in kilograms or pounds.
- Dimensional weight (DIM weight): A calculated weight based on the parcel's volume. Carriers use it when a large, light box would occupy more space than its mass suggests. The formula is Length × Width × Height divided by a carrier-defined divisor called the DIM factor.
- DIM factor: A number published by each carrier (for example 5000 when dimensions are in centimetres and weight is in grams, or 139 for inches and pounds) that converts cubic volume into a comparable weight unit.
- Billable weight: The greater of actual weight and dimensional weight. This is the weight the carrier charges you for.
- Base rate: The per-kilogram or per-pound delivery charge before any surcharges.
- Surcharge: An additional fixed or percentage fee applied for specific conditions such as oversize, residential delivery, or remote area.
- Packaging cost: The material cost of the box, void fill, and tape used to pack the order.
- Revenue: The total amount the customer pays for the order, including any shipping fee you charge them.
- COGS (Cost of Goods Sold): The direct cost of the products in the order, not including shipping or packaging.
- Contribution margin: Revenue minus COGS minus all shipping and packaging costs. It shows how much each order contributes toward fixed overheads and profit before those overheads are deducted.
- Margin vs markup: Margin is profit expressed as a percentage of revenue. Markup is profit expressed as a percentage of cost. This calculator uses margin (profit divided by revenue, multiplied by 100).
- GS2Web Calculator: A feature of the GSheetPress GS2Web add-on that turns a specially structured Google Sheet tab into a hosted web form with live formula results. Learn more on the GSheetPress Calculator details page.
Prerequisites
- A Google account with access to Google Sheets.
- The GS2Web SaaS add-on installed from the Google Workspace Marketplace. Open any spreadsheet, go to Extensions → Add-ons → Get add-ons, and search for GS2Web SaaS. Install it and grant the requested permissions.
- Your GSheetPress account must be on a plan that includes Calculator entitlement. The account you use to install the add-on must match your GSheetPress login. Check your plan on the GSheetPress pricing page. If you do not yet have an account, sign up at GSheetPress sign-up.
- A dedicated sample workbook created solely for this exercise. Do not use a file containing private customer or financial data, because a later step requires sharing it with Anyone with the link as Editor.
Set up the helper rate table
Before building the calculator tab, add a small reference table in a separate sheet so your formulas can look up rates without hard-coding numbers directly into result cells. If a rate changes you only update the table, not every formula.
- In your workbook, click the + icon at the bottom to add a new sheet. Name it Rates.
- Enter the following values exactly as shown. Column A holds the label and column B holds the value.
| Cell | Label (column A) | Value (column B) |
|---|---|---|
| A1 | Setting | Value |
| A2 | DIM Factor | 5000 |
| A3 | Base Rate per kg | 4.50 |
| A4 | Oversize Threshold kg | 20 |
| A5 | Oversize Surcharge | 8.00 |
| A6 | Packaging Cost | 1.20 |
| A7 | Rate Assumption Date | 2024-01-01 |
The Rate Assumption Date in B7 is a plain text note reminding you when these figures were last verified. Update it whenever you revise the rates. All monetary values are in your chosen currency; the calculator does not convert currencies.
To reference these values in formulas you will use cross-sheet references such as Rates!B2 and Rates!B3. For further reading on cross-sheet formulas, see the Google Sheets formula reference documentation.
Build the GS2Web Calculator
The GS2Web Calculator add-on generates a structured template in a new sheet. You fill in the configuration columns and then add your result formulas below the generated button row. This calculator has 7 inputs, so the layout is:
- Rows 2–8: input fields (one per row)
- Row 9: Calculate button (generated)
- Row 10: merged Result Fields separator (generated)
- Row 11: result column headings (you type these)
- Rows 12 onward: result labels and formulas (you add these)
Create the calculator tab
- Open your dedicated sample workbook.
- Click Extensions in the menu bar.
- Hover over GS2Web SaaS (or the installed add-on name).
- Click Calculator, then New Calculator.
- When prompted, enter the name Shipping Profit.
- When asked for the number of inputs, enter 7.
- When asked whether to include graphs, choose No.
- Click Create (or the equivalent confirm button). The add-on inserts a new sheet tab named Shipping Profit with auto-generated headers in row 1 across columns A through H: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules.
Do not rename, delete, or reorder these header cells. The add-on reads them by position. Note: some older template versions spell the column F header as ToopTips rather than ToolTips; both work identically.
Understand the column roles before you begin
- Column A — Labels: The field name shown to the user on the web form.
- Column B — Input Type: Controls the form widget (Number, Dropdown, etc.).
- Column C — Required: Check the checkbox to make the field mandatory before the form submits.
- Column D — Data: Comma-separated options for dropdowns, or the word main for the Calculate button row.
- Column E — Default: Pre-filled value shown in the form and your test value before publishing. E holds the form default; G holds the current submitted value during a live calculation. Never store result formulas in column G.
- Column F — ToolTips: Hint text shown to the user next to the field.
- Column G — Inputs: Overwritten by the web form on every submission. To test before publishing, copy E2:E8 and paste into G2:G8 using Paste special → Values only.
- Column H — Rules: Optional validation such as min or max values.
Row 2 — Length (cm)
- A2 — Label: Length (cm)
- B2 — Input Type: Number
- C2 — Required: Click the checkbox to tick it (required).
- D2 — Data: Leave blank.
- E2 — Default: 30
- F2 — ToolTips: Enter the outer length of the packed parcel in centimetres.
- G2 — Inputs: Leave blank (overwritten at runtime).
- H2 — Rules: min:1
Row 3 — Width (cm)
- A3 — Label: Width (cm)
- B3 — Input Type: Number
- C3 — Required: Click the checkbox to tick it (required).
- D3 — Data: Leave blank.
- E3 — Default: 20
- F3 — ToolTips: Enter the outer width of the packed parcel in centimetres.
- G3 — Inputs: Leave blank (overwritten at runtime).
- H3 — Rules: min:1
Row 4 — Height (cm)
- A4 — Label: Height (cm)
- B4 — Input Type: Number
- C4 — Required: Click the checkbox to tick it (required).
- D4 — Data: Leave blank.
- E4 — Default: 15
- F4 — ToolTips: Enter the outer height of the packed parcel in centimetres.
- G4 — Inputs: Leave blank (overwritten at runtime).
- H4 — Rules: min:1
Row 5 — Actual Weight (kg)
- A5 — Label: Actual Weight (kg)
- B5 — Input Type: Number
- C5 — Required: Click the checkbox to tick it (required).
- D5 — Data: Leave blank.
- E5 — Default: 2.5
- F5 — ToolTips: Weighed mass of the packed parcel in kilograms.
- G5 — Inputs: Leave blank (overwritten at runtime).
- H5 — Rules: min:0.1
Row 6 — Order Revenue
- A6 — Label: Order Revenue
- B6 — Input Type: Number
- C6 — Required: Click the checkbox to tick it (required).
- D6 — Data: Leave blank.
- E6 — Default: 85
- F6 — ToolTips: Total amount the customer paid including any shipping fee you charged them.
- G6 — Inputs: Leave blank (overwritten at runtime).
- H6 — Rules: min:0
Row 7 — Cost of Goods (COGS)
- A7 — Label: Cost of Goods (COGS)
- B7 — Input Type: Number
- C7 — Required: Click the checkbox to tick it (required).
- D7 — Data: Leave blank.
- E7 — Default: 35
- F7 — ToolTips: Direct product cost only. Do not include shipping or packaging here.
- G7 — Inputs: Leave blank (overwritten at runtime).
- H7 — Rules: min:0
Row 8 — Apply Oversize Surcharge?
- A8 — Label: Apply Oversize Surcharge?
- B8 — Input Type: Dropdown
- C8 — Required: Click the checkbox to tick it (required).
- D8 — Data: Yes,No
- E8 — Default: No
- F8 — ToolTips: Choose Yes if the carrier has flagged this parcel as oversize.
- G8 — Inputs: Leave blank (overwritten at runtime).
- H8 — Rules: Leave blank.
Row 9 — Calculate button (generated)
Row 9 is the button row generated by the add-on. Confirm the following values are already in place. Do not overwrite them.
- A9: Calculate (the button label as shown to users — leave as generated)
- B9 — Input Type: Button
- D9 — Data: main
- E9: Reset (this is the reset button label, not the Calculate label — do not confuse the two)
Row 10 — Result separator (generated)
Row 10 is a merged separator row labelled Result Fields, generated automatically. Leave it untouched.
Row 11 — Result column headings
- Click cell A11 and type: Result Label
- Click cell B11 and type: Value
Result rows — labels and formulas
Each result row has a plain-text label in column A and a formula in column B. Click each column B cell, type the formula beginning with =, and press Enter. All runtime values come from G2:G8 (the Inputs column), which the web form populates when a user submits the calculator.
Row 12 — Dimensional Weight (kg)
- Click A12 and type: Dimensional Weight (kg)
- Click B12 and enter:
=ROUND((G2*G3*G4)/Rates!B2,2)This multiplies Length (G2) by Width (G3) by Height (G4) and divides by the DIM factor in Rates!B2, then rounds to 2 decimal places. With defaults 30, 20, 15 and DIM factor 5000: (30×20×15)/5000 = 1.80 kg.
Row 13 — Billable Weight (kg)
- Click A13 and type: Billable Weight (kg)
- Click B13 and enter:
=ROUND(MAX(G5,B12),2)MAX picks the greater of actual weight (G5 = 2.5) and dimensional weight (B12 = 1.80). Result: 2.50 kg.
Row 14 — Base Delivery Charge
- Click A14 and type: Base Delivery Charge
- Click B14 and enter:
=ROUND(B13*Rates!B3,2)Billable weight multiplied by the base rate per kg (Rates!B3 = 4.50). Result: 2.50 × 4.50 = 11.25.
Row 15 — Packaging Cost
- Click A15 and type: Packaging Cost
- Click B15 and enter:
=Rates!B6Pulls the flat packaging cost from Rates!B6 (1.20). To let users enter a custom packaging cost, add it as an eighth input row and reference G9 instead.
Row 16 — Oversize Surcharge
- Click A16 and type: Oversize Surcharge
- Click B16 and enter:
=IF(G8="Yes",Rates!B5,0)If the user chose Yes in the dropdown (G8), the surcharge value from Rates!B5 (8.00) is applied; otherwise 0. With the default of No, result is 0.
Row 17 — Total Shipping Cost
- Click A17 and type: Total Shipping Cost
- Click B17 and enter:
=ROUND(B14+B15+B16,2)Sums base delivery charge (B14), packaging cost (B15), and oversize surcharge (B16). With defaults: 11.25 + 1.20 + 0 = 12.45.
Row 18 — Contribution Margin (currency)
- Click A18 and type: Contribution Margin
- Click B18 and enter:
=ROUND(G6-G7-B17,2)Revenue (G6 = 85) minus COGS (G7 = 35) minus total shipping cost (B17 = 12.45). Result: 85 − 35 − 12.45 = 37.55.
Row 19 — Contribution Margin %
- Click A19 and type: Contribution Margin %
- Click B19 and enter:
=IF(G6=0,"N/A",ROUND((B18/G6)*100,1))Divides contribution margin by revenue and multiplies by 100 to give a percentage. The IF guard prevents a division-by-zero error when revenue is 0. Result with defaults: (37.55 / 85) × 100 = 44.2%.
Row 20 — Shipping as % of Revenue
- Click A20 and type: Shipping as % of Revenue
- Click B20 and enter:
=IF(G6=0,"N/A",ROUND((B17/G6)*100,1))Shows what fraction of revenue is consumed by shipping and packaging. Result: (12.45 / 85) × 100 = 14.6%.
Worked example and expected results
Use these inputs to verify your formulas before publishing. Copy E2:E8 and paste into G2:G8 using Paste special → Values only to simulate a web submission with the default values.
| Input | Cell | Default value |
|---|---|---|
| Length (cm) | G2 | 30 |
| Width (cm) | G3 | 20 |
| Height (cm) | G4 | 15 |
| Actual Weight (kg) | G5 | 2.5 |
| Order Revenue | G6 | 85 |
| Cost of Goods (COGS) | G7 | 35 |
| Apply Oversize Surcharge? | G8 | No |
| Result label | Cell | Expected value |
|---|---|---|
| Dimensional Weight (kg) | B12 | 1.80 |
| Billable Weight (kg) | B13 | 2.50 |
| Base Delivery Charge | B14 | 11.25 |
| Packaging Cost | B15 | 1.20 |
| Oversize Surcharge | B16 | 0.00 |
| Total Shipping Cost | B17 | 12.45 |
| Contribution Margin | B18 | 37.55 |
| Contribution Margin % | B19 | 44.2 |
| Shipping as % of Revenue | B20 | 14.6 |
Alternative scenario — oversize heavy parcel
Change the following G cell values (paste as values only) to test an oversize order:
| Input | Cell | Alternative value |
|---|---|---|
| Length (cm) | G2 | 60 |
| Width (cm) | G3 | 50 |
| Height (cm) | G4 | 40 |
| Actual Weight (kg) | G5 | 22 |
| Order Revenue | G6 | 150 |
| Cost of Goods (COGS) | G7 | 60 |
| Apply Oversize Surcharge? | G8 | Yes |
DIM weight: (60×50×40)/5000 = 24.00 kg. Actual weight: 22 kg. Billable weight: MAX(22, 24) = 24.00 kg. Base charge: 24 × 4.50 = 108.00. Packaging: 1.20. Oversize surcharge: 8.00. Total shipping: 117.20. Contribution margin: 150 − 60 − 117.20 = −27.20. Contribution margin %: −18.1%. This negative result correctly signals that shipping costs exceed the gross profit on this order at the given rates.
Troubleshooting
Results show 0 or blank after pasting defaults
Make sure you used Paste special → Values only when copying E2:E8 into G2:G8. If you paste normally, the cells may contain formula references rather than numbers, causing downstream formulas to misread the inputs.
#REF! or #NAME? errors in result cells
Check that your Rates sheet is named exactly Rates (capital R, no trailing space). Open the Rates sheet and confirm values are in column B rows 2 through 6. A typo in the sheet name breaks all cross-sheet references.
Dimensional weight is unexpectedly large
Confirm that your length, width, and height inputs are in centimetres and that Rates!B2 is set to 5000 (the standard cm/g DIM factor). If your dimensions are in inches and your weight is in pounds, the correct DIM factor is 139. Update Rates!B2 accordingly and do not mix unit systems within a single calculation.
Contribution margin is negative on real orders
A negative contribution margin means the combined cost of goods, shipping, and packaging exceeds the revenue collected. This is a valid and important signal. Check whether your base rate in Rates!B3 matches your carrier's current tariff, and whether you are charging customers enough to cover shipping. This calculator intentionally surfaces that situation rather than hiding it.
Dropdown result for G8 returns empty in test
When pasting E8 (which contains the text No) into G8, make sure you are pasting as plain values. If G8 is empty, the IF formula in B16 will not match either Yes or No and will return 0, which is the same as No — so the result will still be numerically correct, but verify by checking G8 visually.
The Calculate button is missing or the form does not submit
Ensure row 9 has Button in B9 and main in D9. If either value is missing or misspelled, the add-on will not render the submit button on the public form. Re-enter those values and republish.
Frequently asked questions
Can I add more surcharges such as a fuel surcharge or residential fee?
Yes. Add additional rows in the Rates sheet (for example, B8 for fuel surcharge percentage) and add corresponding result rows below B20 in the calculator tab. If you want users to toggle individual surcharges, add more Dropdown inputs in the calculator tab, remembering that each new input shifts the button, separator, and result rows down by one. Update all row references in existing formulas accordingly. For help structuring more complex calculators, visit the GSheetPress Calculator details page.
How do I update the rates when my carrier changes their tariff?
Open the Rates sheet in your sample workbook and update the relevant value in column B. All result formulas reference Rates!B2 through Rates!B6 directly, so changing a rate cell immediately updates every calculation without editing any formula. Update B7 (Rate Assumption Date) to record when you verified the new figures. After editing, republish the calculator using Extensions → GS2Web SaaS → Calculator → Publish Calculator to push the changes live.
Why does the calculator use margin instead of markup?
Margin (profit divided by revenue) is the standard metric used in ecommerce and retail reporting because it expresses how many cents of every sales dollar remain after direct costs. Markup (profit divided by cost) is more common in manufacturing and wholesale pricing contexts. If your business reports markup, you can add an extra result row with the formula =IF(G7+B17=0,"N/A",ROUND((B18/(G7+B17))*100,1)) in B21, labelled Markup % in A21. This divides contribution margin by total cost (COGS plus shipping).
Publish and test your web calculator
Share the workbook correctly
The GS2Web add-on must be able to read your spreadsheet in order to publish it. Because this requires sharing the file, use only your dedicated sample workbook — not a file containing private or sensitive data.
- Click the Share button in the top-right corner of your workbook.
- Under General access, click the dropdown that currently shows Restricted and change it to Anyone with the link.
- Set the role dropdown to Editor.
- Click Done.
Important: Setting access to Anyone with the link as Editor means that any person who obtains the spreadsheet URL can view and edit its contents. This is why a dedicated sample workbook is mandatory. Never use this sharing setting on a file containing real customer orders, pricing agreements, or personal data.
Publish the calculator
- Click on the Shipping Profit sheet tab to make it the active sheet.
- Click Extensions in the menu bar.
- Hover over GS2Web SaaS.
- Click Calculator, then Publish Calculator.
- Wait for the confirmation message indicating the calculator has been published successfully.
Find and open the public link
- After publishing, the add-on opens or updates a sheet called Calculator Link (or similar) in your workbook. Open it.
- Locate the row that matches your Sheet Name: Shipping Profit.
- In column C, find the public Link. This is the URL you share with users.
- Do not use the URL in column B (the Edit Link), which opens the spreadsheet editor rather than the public calculator form.
- If the row is missing or the link appears stale, click Get Links inside the add-on menu to refresh the Calculator Link sheet.
Test the published calculator
- Open the public link (column C) in a new browser tab.
- Enter the default test values from the worked example: Length 30, Width 20, Height 15, Actual Weight 2.5, Order Revenue 85, Cost of Goods 35, Oversize Surcharge No.
- Click the Calculate button.
- Confirm the results match the expected values: Dimensional Weight 1.80, Billable Weight 2.50, Base Delivery Charge 11.25, Packaging Cost 1.20, Oversize Surcharge 0.00, Total Shipping Cost 12.45, Contribution Margin 37.55, Contribution Margin % 44.2, Shipping as % of Revenue 14.6.
- If any result differs, return to the spreadsheet, check the corresponding formula cell and G cell values, correct the issue, and republish using the same Publish Calculator step. Republishing an existing calculator retains its public link.
Embed the calculator on a website (optional)
If you want to embed the calculator directly on a webpage, return to the Calculator Link sheet and copy the value in column D (Iframe). Paste the iframe code into an HTML block on your website. The embedded calculator will display the form and show results without the user ever seeing the spreadsheet. For more details on embedding and managing published calculators, see the GSheetPress support pages. You can also explore how the Table feature complements calculators for displaying product or rate data on the GSheetPress Table details page.