Forums for the Business Analyst

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

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.

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! :)

Community Blog - Latest Posts

It’s 4:30 pm on Friday and Mr. Manager comes along to tell you that he needs you to run some important ad-hoc analysis for him.
Previously this meant having to stay late at the office, writing cumbersome queries to extract business information from transactional data.
Lucky for you, you’ve recently started using Temporal Tables in SQL...

Current State
For many years now, the most commonly used metaphor on Business Analysis has been the “Bridge”. However, in recent past, some in the BA community have started revisiting the metaphor resulting in a debate on how relevant it is. Of course, the value business analysis can provide for an organization does not depend on how i...

Pega systems(Software Company) is the leading provider of business process management (BPM) and customer relationship management (CRM) software solutions. Pega systems motto is “Build For Change” and their goal is to “eliminate software coding” and “automate manual work”.
Pega systems has bee...