Forums for the Business Analyst

  Modern Analyst Forums  Business and Sy...  Business Proces...  Reducing Component Complexity (Help Requested)
Previous Previous
Next Next
New Post 3/18/2017 10:34 AM
User is offline Mildo
2 posts
No Ranking

Reducing Component Complexity (Help Requested) 
Modified By Chris Adams  on 3/18/2017 2:22:22 PM)

Greatings all. I’m a mechanical engineering senior working as a coop at a large company. My group has been tasked with the analysis of some sales data, and production of a company component strategy with the objective to drive volume into common components with more advantageous financial terms for ourselves and our customers. We have limited business data analysis acumen on our team, but have been given the task regardless.

 I guess my first question is, am I even in the right place? My background is not business and the extent of my formal education on the matter is one single engineering based economics class. This type of number crunching is just not in our wheelhouse. So instead of wading into the subject with zero guidance, I figured I might seek information and resources from a community of knowledgeable and generous individuals such as yourselves. If I am in the wrong place, I would appreciate a kind point in the right direction.

The scenario is this: We are given a list of similar components that, for the purposes of simplification, we will assume are mutually interchangeable without loss of quality to our product. We are also given the sales data for each component, broken into individual customer contracts We buy each component at a set rate for low volume purchases, with supplier given price drop incentives for relatively massive volume increases. The sales price is determined through negotiation with the customer, and once added into the contract cannot be changed unless both parties agree to amend the contract. Now some of the contracts were negotiated so poorly that the sales price is less than the cost of the component, obviously leading to a net loss for buying and installing a component onto our product. The problem gets even more complex when you consider multiple contracts with different volumes and different sales prices. Some are profitable, and some are not. So the impact of the profitability (or lack thereof) needs to be scaled by the volume of that contract. This is what led me to think of calculating a weighted average by volume. Simply multiplying each contracts profit margin by its volume fraction, and calculating the sum of all contracts.

e.g. [(Profit1 x Volume1)+(Profit2 x Volume2)+…]

                        Total Volume

I think this “statistic” may give us a good indicator of the overall financial health of a component across all contracts, but again – I am far from competent enough in this area to trust my own intuition. That’s why I’m seeking advice. Are there any other relatively easy to understand analytic tools or statistics that might provide useful information? Keep in mind also that moving volume out of a bad contract may inadvertently affect the volume discounts on that component, and hurt healthy contracts resulting in a net decrease in profit. The ultimate goal is to commonize and simplify our component strategy across the entire company, and any suggested changes to current customer contracts must be of financial benefit to both parties, as well as meet the same quality standards of the component to be replaced (as I stated, for these purposes quality can be assumed equivalent).

I have created a representative data table of our scenario. The numbers are in no facet to scale, but I used several RANDBETWEEN functions and I believe this to be a fairly accurate representation of our data. Below is a screenshot for your perusal, but I have also attached the excel file of this bogey data, in case anyone wants to play with it. Any and all suggestions or resources would be greatly appreciated. I’m already certain I will receive a job offer from this company upon graduation, but a home run idea or two will give me some real leverage. Thanks!   Work Case Study.xlsx

New Post 3/18/2017 12:19 PM
User is offline Chris Adams
323 posts
5th Level Poster

Re: Reducing Component Complexity (Help Requested) 
This forum is fine for this kind of question.  Your approach is solid and the formula that you've presented will give you the right results to analyze.  

Next steps are to focus on the losers where your margin is negative (you are losing money).  How do you minimize the money lost?  1) renegotiate the contract with your customer, which may not be feasible until the contract comes to an end and is up for renegotiation/renewal.  2) interchange the component with another component where supplier pricing may be lower or where you already have many contracts and therefore the addition of more volume with allow you to benefit from the higher volume/lower pricing.

So let's focus on item (2).  Assuming you currently have 5 interchangeable components you will want to analyze the current cost to your company of each based on current volumes and also identify the volume tiers where you will be given a price break.  Assuming equal quality, you can then select for example 2 of the 5 component/suppliers to pool your purchasing around.  If moving the volume of purchasing of components C3, C4, and C5 over to only components C1 and C2 allow you to hit the necessary volume tiers to lower your costs on C1 and C2 then you've achieved your goals.

You probably want to select a single primary component/supplier to focus on (let's say C1). You can move most of your purchasing to the primary component C1 but always maintain a secondary component C2.  This is a risk mitigation play.  What if the supplier of C1 has a temporary issue.  You will need to fall back to C2 to fill the supply void. But the main point here is that you don't have to distribute the volume from C3, C4, and C5 equally to C1 and C2. You can move maybe 70-80% to C1 and the remainder to C2.  Based on you identification of C1 this would take maximum advantage of your lowest costs based on higher volumes.

As you are doing this you will have to ensure that you either completely eliminate all purchasing of some of your components (C3, C4, and C5), or if that's not feasible then ensure you don't drop your total volume purchasing of C3, C4, and C5 below any volume pricing tiers which would suddenly increase your costs.

Continue to track low margin or negative margin accounts.  Over time you can renegotiate the pricing.  This will likely be up to your sales guys, but having a communicated target margin will be helpful.  Also, they can negotiate contracts with your customers that incentivize higher volumes purchasing from your company. If a customer is going to order 80 units but gets a break at 100 units they may increase their purchasing from you. (I have no idea what your product is so apply this information as you see fit). Also, consider that you may not want to renew these contracts if they are losing money. But also understand that some products are loss leaders meaning that you intend to lose money on them because it brings money into the company elsewhere.

Chris Adams
Core Member –
LinkedIn Profile
New Post 3/18/2017 12:50 PM
User is offline Mildo
2 posts
No Ranking

Re: Reducing Component Complexity (Help Requested) 
Thanks so much for your input! Those all seem like intuitive and sound strategies, some of which I believe we've considered and/or are current practice. Much of this does depend on our sales and purchasing departments, and my group is attempting to be the go-between with all the answers. I was hoping I would receive good news on my average by volume metric. I think that will help us zoom in on areas to stop the bleeding first. After that, the strategy you laid out is very similar to the one that was forming on the dry erase board in my brain.  I think the next big tool we need will be a simple comparison tool allowing analysis of groups of components that we know for a fact are interchangeable. Then its time to start dropping the hammer on some of these legacy components. Thanks again for your time! :)
Previous Previous
Next Next
  Modern Analyst Forums  Business and Sy...  Business Proces...  Reducing Component Complexity (Help Requested)

Community Blog - Latest Posts

If you're looking to visualize, analyze, and share data insights with ease, Power BI might be the solution you need. In this article, we'll cover what Power BI is, why it's important, and how to get started with it. What is Power BI? Power BI is a business analytics service provided by Microsoft that helps users visualize and anal...
How to Write Business Analyst Resume
Business analysts must have an up-to-date, visually appealing resume to be able to stand out from the competition. A strong business analyst resume should accurately reflect your skills, experience and qualifications while also highlighting your accomplishments and successes. We will provide invaluable advice on how to build a strong, effective bus...
Are you testing your product in the most effective way?
Today covid-19 pandemic has completely flipped the business model. Various conventional Business models like banking and health had to adopt the digital era. Even the school and colleges have to create various applications so that they can provide online learning for their students. All these statements justify the modern business model as complete...



Copyright 2006-2023 by Modern Analyst Media LLC