In order to facilitate business personnel and sales personnel to quickly and accurately calculate the cost, expenses, and profits of all products, and to adjust marketing and pricing strategies in a timely manner, a simple and convenient product sales profit budget table can be designed.
① Product name, SKU code (seller’s warehouse code), number of pieces, unit price (US dollars), purchase price (yuan), and weight (kg) are all “variables” and must be filled in separately each time a new product is budgeted.
② “US dollar exchange rate” is a stable variable and generally does not change. In actual operation, it is recommended to update it once a week, based on the spot purchase price of Bank of China on a fixed day of the week. “Freight” is also a stable variable, and the calculation formula is: freight (￥) = number of pieces weight X74.5 yuan/kg + 8 yuan. As we mentioned earlier, this average freight used for budgeting (74.5 yuan/kg + 8 yuan) is relatively fixed and is generally updated once a month.
③ The remaining columns are all automatically calculated using formulas, which are the “budget results” we need, and are explained below.
Order amount ($) = number of pieces x unit price (E2=C2XD2).
Transaction amount = order amount x handling fee (5%) x US dollar exchange rate (G2E2X0.05XL2).
Gross profit (￥) = order amount x US dollar exchange rate x purchase price x number of pieces x transaction x freight (J2=E2XL2-F2XC2-G2-12).
Profit margin = gross profit / (order amount x USD exchange rate) [K2 = J2 / (E2XL2)]
Such a simple budget table can help cross-border e-commerce companies to easily achieve the following functions: First, when developing and maintaining each product, sales staff only need to enter the purchase price and gross weight after packaging to quickly and accurately calculate the product’s budget cost and profit on expenses; second, when important factors such as freight discounts and exchange rates change, you can use Excel’s formula application function to batch update the cost-profit estimates of all products, and accurately grasp the changes in the profit level of the entire store from a macro perspective; third, this table particularly emphasizes the need to enter the corresponding SKU code for each product, so that after the product has been sold for a period of time, it can be easily checked through Excel’s “check function” to quickly check the initial pricing and budget of the product, and help sellers adjust the product’s pricing and marketing methods when external factors such as market competition or supply prices change.
In addition, it should be noted that this budget table is only a basic version, and sellers can add more variables based on their product characteristics, personal preferences or financial work habits. For example, in order to better control the overall expenses of the entire store, some sellers will add “marketing and promotion expenses” or “the proportion of total cost of after-sales refund and resale to sales” (the “after-sales service fees and costs” mentioned above) to this budget table when making a budget. These practices can be used as a reference. Sellers can use the cross-border e-commerce product budgeting method, combined with the store’s past sales data and store growth expectations, to calculate the store’s overall profitability based on the product’s profit level.