You sell on Facebook, Instagram or WhatsApp, or from a small store, and most customers pay when the order arrives. At month end you want one number: how much did I really make after shipping, returns, discounts and ads? Revenue does not tell you that, because a returned order costs you money and a heavily discounted order to a far area can lose it. This free Excel sheet works the profit out order by order. (The template and its page are in Arabic.)
What the template gives you
- Start here. A short guide.
- Products (المنتجات). Cost, selling price, opening stock and reorder level for each product. It also shows what is tied up in open orders and warns when stock falls below the reorder level.
- Governorates (المحافظات). For each area: the shipping fee the customer pays, and what the courier company charges you for a delivered order and for a returned one.
- Orders (الطلبات). Every order with its value, shipping, product cost, shipping cost and profit. Returns, loss-making orders and input errors are coloured.
- Ads (الإعلانات). What you spent on each platform and on which day.
- Monthly summary (الملخص الشهري). Per month: delivered orders, delivery rate, profit before ads, ad spend, net profit after ads and ad cost per delivered order.
- Product profits (أرباح المنتجات). Return rate, profit and loss from returns per product, and a ranking from the most profitable.
A walkthrough with the sample numbers
All numbers are sample data from the template.
How an order is closed matters most. A delivered order's profit is its value (price times quantity, less discount) plus the shipping the customer paid, minus the product cost and minus the courier's charge to you. Take one sample case: a phone case sold at 80 with cost 25, delivered in Cairo where the customer pays 50 shipping and the courier charges you 35. Profit: 80 + 50 - 25 - 35 = 70. If the same order is returned, you lose only the return fee, 20 in Cairo, so its profit shows as -20. A cancelled order is zero. An order still being prepared or shipped has no profit yet.
The sample also has a loss-making delivery on purpose: a charger priced 130 with cost 60, sold with a 120 discount and delivered to Matrouh, where the courier charges 80. It works out to 10 + 110 - 60 - 80 = -20, and the row shows in red.
Now the monthly summary:
| Month | Delivered | Delivery rate | Profit before ads | Ad spend | Net profit after ads |
|---|---|---|---|---|---|
| September | 36 | 87.8% | 7,295 | 2,550 | 4,745 |
| October | 13 | 92.9% | 2,290 | 2,200 | 90 |
| Year | 49 | 89.1% | 9,585 | 4,750 | 4,835 |
The delivery rate is delivered orders divided by delivered plus returned. The sample's October only runs to mid-month, so do not read it as a trend. But it shows the lesson clearly: 2,290 of profit before ads, 2,200 of ads, and 90 left. Dividing ad spend by delivered orders gives about 71 per delivered order in September and about 169 in October, which is exactly the kind of number you want to see before you raise a budget.
Features and why they matter
- Profit per order, not per sale. Returns and far-area deliveries can no longer hide inside a good revenue figure.
- Errors flagged. An unknown product code, an unknown area, a discount larger than the order value, or a missing status all appear in an alert column.
- Stock follows status. Product balance is opening stock minus delivered minus what is in open orders; returns go back to stock and cancelled orders never leave it.
- Net profit after ads. The number that decides whether to keep spending.
- Works in Excel 2016 and later, Google Sheets and LibreOffice, free, no sign-up.
Using the online version
The template page opens directly on a worked example with numbers. Edit the white (input) cells right in the page and the grey calculated cells update immediately. You can import your own .xlsx made from this template, export your work to .xlsx, or download the original Excel file. Your data stays in your browser and is not sent to the server, so export before you close the page.
Open the online store profit template page, or browse the free templates list.
Common mistakes, and when a sheet stops being enough
- Leaving an order's status unchanged. The profit appears only when you change it to delivered, returned or cancelled.
- Writing a product code or area differently from its sheet. The shipping cost is then not calculated.
- Typing a discount as a percentage. Here the discount is an amount of money.
The sheet holds 200 products, 40 areas, 3,000 orders and 1,000 ad rows. If you are copying every order from messages and comments into it, the copying is the cost. The Softwiro answer is Softwiro E-Commerce: your own store that follows each order from creation to delivery, with shipping cost set per governorate. If you are also weighing card payments, read taking payments online in Egypt and the Gulf, and for couriers and cash on delivery see delivery management with your own couriers.
FAQ
Does it calculate profit for an order that has not arrived yet? No. The profit stays empty until the status becomes delivered, returned or cancelled. The pieces in that order are still counted against stock.
How does it count a return? It deducts the return fee that the courier company charges you, and the piece goes back to stock. The loss shows in the month's profit and in the product profits sheet.
What if I type a wrong product code or area? An alert appears next to the order and the shipping cost for it is not calculated until you fix the name.
Next step
Open the template page, put your own products and governorate fees in, and enter this week's orders. Update each order's status as it moves.