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
