Simply looking to create an area/block in the admin area of a product page that displays what the current gross profit is on that product. I.e. calculating the difference between the cost price and selling price of the item. So for example we have a cost attribute, and either a price or special_price attribute for the selling price. Some products have a special (sale) price active, some don't, so this probably requires an if statement. Initially I explored creating an attribute to do this, but that didn't really work out, so looking to hard code something into the admin page. Ideally creating small module to do this. Any ideas on the best way to go about or detail some of the code involved to get me started?
-
your question might be a bit too broad. What did you try so far? Split up your question and see what you can build yourself and ask specific questions on those topics. (1) Adding a block to the product edit page (2) getting sales qty for this product. (3) calculate profit from sale qty minus cost of product– Sander MangelOct 7, 2013 at 19:11
-
Your probably right. We haven't really started anything thus far, still trying to work out the best way to go about this and map it out. We would want to create this as a line in the "price" tab. Probably underneath the existing pricing lines. So the code block will probably go into the /app/design/adminhtml/default/default/template/catalog/product/price.html file. Starting point may be simply creating the attribute? With the input type simply being a text field. Then creating a block of code to appear within this attribute?– MagentoMacOct 7, 2013 at 19:24
-
I'd suggest splitting the project up in chunks and writing down the steps you need to do to have a structured approach. Any specific questions you might end up with we'll of course be happy to help you with ;)– Sander MangelOct 7, 2013 at 19:26
-
Ok cool. I'll repost stage 1 as a new question after having a bash at making a start to it.– MagentoMacOct 7, 2013 at 19:27
-
Creating an attribute for gross profit I have already worked out isn't the way to go.... :( Just need to hard code blocks into the areas needed I think. Nothing out there really doing this in Magento at all as far as I can see which is surprising! The closest I have found is this: cart2quote.com/magento-profit-calculator But it is more of a "cart quote" style extension. The layout and features look to be similar though to what I am trying to do.– MagentoMacOct 7, 2013 at 19:46
3 Answers
I just completed a module to calculate the profit and profit ratio on a product. The calculation is done when the product is saved and stores the values in 2 attributes (product_profit and product_profit_ratio). The module also adds 2 columns to the productgrid to display both the profit and ratio.
I published the source on BitBucket, see https://bitbucket.org/genmato/productprofit/.
I tested it on both 1.7.0.2 and 1.8, and might work on 1.6 also. Not compatibel with 1.5 and lower.
-
Hi Vladimir, this is very cool! And more or less exactly what we were looking to achieve. Works really well and easy to install. Only query is, we have have 100,000 products or so already input. For us to display the profit value and profit ratio, would we have to "save" each of these products again? Or is there a way of the profit figures just automatically showing all the time? Oct 28, 2013 at 21:31
-
Or, even simpler, is it possible to create a cron job to make this run once a day perhaps? Having to save each product 1 by 1 to display the gross profit isn't really a solution. Nov 11, 2013 at 8:52
-
I will see if I can do simething like a mass-update or so to get it saved. I also changed the calculation to do also a afterLoad. Nov 11, 2013 at 8:59
-
Since I do not have the privilege to comment I'll try to answer how I would approach this. First, I will use the native product attribute cost
to calculate the gross profit. Now, I need to add a gross_profit
column to the table sales_flat_order
and sales_flat_order_grid
. The later table is for display the gross_profit
data in admin. Now, depending if the site is in production or under development, I'll need to create the necessary database setup. Let's take the more interesting production setup, which will requires data upgrade, meaning I will create the data upgrade script to calculate the gross_profit
of all the previous orders and put that values in the 2 tables. The script can be reused in the model class
to calculate the gross_profit
for new orders. Basically, I'll list all the ordered items and accumulate the profit, minus the discount. Table sales_flat_order_item
is involved in the data upgrade script but will not be directly involved in the model class
. I think this makes a nice project for an experienced Magento developer, otherwise, I suggest buying an extension.
EDIT:
I apologize for not reading your requirements correctly. Here's what I would do to calculate GP at the product level. First, I set up a product attribute called accumulated_profit
in the backend. Then, I set up an event listener for sales_order_invoice_pay
which fires when payment is received for an order. From the invoice object that is passed from the event, I will check what items were being invoiced and the qty invoiced. For each item, I will then calculate the profit and add it to the accumulated_profit
. Third, I will provide a custom column in the admin to display accumulated_profit
in the product grid. Last, I will also create the data upgrade script, which will retrieved passed invoices and update the accumulated_profit
. The above ignores any refund. But this can easily be accommodated with another listener for event sales_order_creditmemo_refund
.
I think a complete approach would be create a new table to record all the invoiced items and refunded items and the GP for each along with the timestamp. This will provide a complete statistic on GP for each product. The table can then be either pulled for display some simple trends in the backend or be exported for analysis by third-party application.
EDIT AGAIN: I do not believe you want to calculate accumulated GP. So the above is incorrect. If you just need a simple GP on each product, then it can be easily accomplished by adding a custom column to the catalog grid in the admin. To do this, I'd would overwrite the class Mage_Adminhtml_Block_Sales_Order_Grid
and add a new column gross_profit
, and I'd write a custom renderer for it. If you need to export products that are below a certain threshold, then I'd write a script to calculate each product and export them to a csv file. To be a little bit sophisticated, I'd add an action in the dropdown Actions
in admin > Catalog > Manage Products
so that the script can be executed when the Submit
button is clicked.
-
Hi @kiatng, thanks for your answer. I think this approach is for calculating gross profit on an order level though? Rather than at product level. We basically want to display from the admin area of every product the calculated gross profit on that product, probably in the "prices" tab. We have found an extension that can calculate this on an order level here - blog.decryptweb.com/orderprofit-magento-extension But we have yet to find anything anywhere that does it on a product level. Probably not a massive demand for it, but we certainly need it in our store. Oct 14, 2013 at 8:36
-
For us, the purpose of what we're trying to do is let the product input team easily see products that are sitting at a GP level that is too low, and to then pull them in to a list on a seperate page which can then be exported into excel or the like to be quickly manually updated there. We have over 100,000 products in our store currently, so we do every action on fairly mass level. Oct 14, 2013 at 8:37
-
The simplest approach I can think of is to export all the products with 4 attributes: SKU, cost, price, special_price in the backend and then just use Excel to calculate the GP and filter the products that fall below the threshold.– kiatngOct 16, 2013 at 3:13
-
Thanks @kiatng we're looking to create it as a block/column in the admin area of a product in the prices tab. We are though also going to do what you have suggested in your original answer above and show GP on a sales order level too :) Oct 21, 2013 at 19:08
When it comes to calculating gross profit at the product level, there are a lot of things you need to consider, just outlining few below -:
- Product Cost
- Shipping
- Discount
- Cancellation
- Refunds
- VAT / TAX
- Exchanges
I wouldn't recommend it to do this at product level because calculating gross profit based on the above factors, it could slow down your product page in admin big time. I would rather invest in creating separate report which allows you to calculate gross profit based on product, order, order item etc.
We had similar requirement for our TAX man and we used the following module which gave us information we needed to make business decisions specially around which products to invest or which are not to be invested
Magento 2 Gross Profit Margin Report
Hope this helps you and others to get ahead of your business goals and plans. Best of luck!