I run many profitability reports on a regular basis. What I would like to do is have a calculated factor which I can sort by to determine which orders have the greatest effect on the overall GP%.I feel like I'm overthinking this. I tried simple GP$ and a weighted average contribution margin ratio but both seem to de-emphasize high sales figures with low profitability. Joshua
How to gauge the effect each order has on overall profitability
Answers
This should point you in the right direction...(or maybe this will be useless)....
You should look at it from 2 perspectives.... (a) company as a whole (b) specific orders
1. Think of your company's "current" capacity (to include support resources and not just direct resources)....and efficiency...and costs.
2. Think of bell curves. What is the revenue level that your company is operating at peak efficiency and at what level does a dollar of revenue reduce that GP contribution....or may require additional costs to satisfy the order.
3. It might help to breakdown costs per dollar revenue. Though this may be useless to your question about contributions of specific orders, this should give you an idea. and can work back the costs of said orders.
4. If you already have your cost breakdowns, you should be able compute each order's contribution to the GP and as I have said on #2, in context of the whole company's sales levels/capacity/costs.
5. In the real world, I don't think it is as simple as computing each order's contribution. As there are instances (for example) where sales may be needed just to keep a particular machine busy/productive....or to an extreme, just to keep the doors open.
To sum it up, specific order profitability is nice but should be taken in context.
Oh and BTW, as a comment... sales order profitability should be at the forefront of the process and NOT an after thought or after the fact.
Thanks Emerson. That is our goal to keep it at the forefront and is usually the case Just have so many details and was thinking would be nice to have something measurable to help focus attention on the outliers that most affect us. Was using something like =Average(((1-GP%) Cost),(GP% x GP$)) but were some exceptions where was not perfect. Thanks again
Joshua
If you put the report into excel, you can conditional formating to colorize cells based on criteria like you set above...