Options

Registered User regular
edited August 2010
So let's say I work for a distributor. Let's say I have a database of product sales and costs. Let's say this was one row of data in the table:

'09 sales: $100,000 '10 sales:$110,000
'09 cost: $50,000 '10 cost:$60,000

Sales increased 10%. Cost increased 20%. How do I arrive at a dollar figure that shows how much of the sales increase was due to the cost increase? I feel like this should be real easy but you wouldn't believe how much time I've spent thinking about it and trying to run hypotheticals in Excel, trying to get the answer to jump out at me and make sense... would it be (sales increase) / ('09 cost) * ('10 cost)? or something like that? Is there enough given information to answer the question? Is the question clear enough? I'm at a loss.

DiscoZombie on

## Posts

• Options
Registered User regular
edited August 2010
There isn't anywhere near enough information in there to tell you how much of the sales increase was due to the cost increase.

Thanatos on
• Options
Registered User regular
edited August 2010
Well, let me try to rephrase. We know sales went up 10% and cost went up 20%. Let's assume all other variables are the same (same gross margin per item sold, etc). If cost had stayed the same (gone up 0%), what would the sales have been? Sales would have gone up less, or even gone down, right? I feel like there should be a formula that would give us that % or dollar amt, I'm just not snart enough to come up with it.

maybe you're right though and there's not enough information. What *would* be enough information? because I do have access to all the transactional info. So I know the number of units and cost and sales price at each sale. I just don't know the right way to aggregate it to properly answer this question. Now that I think about it, I guess units are probably an important factor, because if you know that total costs incurred went up from $50k to$52k, you don't know how much of that \$2k increase was due to actual cost increase and how much was due to increased volume. My brain hurts.

DiscoZombie on
• Options
Registered User regular
edited August 2010
Kind of confused by this additional info. All other things equal from 09 to 10 and you're saying that there is a positive relationship between cost and sales? Then why would sales do anything if cost does nothing?

starmanbrand on
• Options
Columbia, SCRegistered User regular
edited August 2010
Sounds like what you're trying to do is assume a fixed cost : sales ratio for 2010, and then look at what you'd get using 2009's costs with 2010's ratio.
( 2010 sales / 2010 costs ) * 2009 costs
= ( 110,000 / 60,000 ) * 50,000
~ [B]91,700[/B]


There's no telling whether that figure means anything, though. I'm no accountant.

FunkyWaltDogg on
• Options
Registered User regular
edited August 2010
Kind of confused by this additional info. All other things equal from 09 to 10 and you're saying that there is a positive relationship between cost and sales? Then why would sales do anything if cost does nothing?

I'm saying that, when the cost of a product is increased, the sales price of the product is also proportionally increased so that the profit margin we make per item stays the same (not always true but a safe assumption). So, with that assumption, and assuming the same number of units were sold both years, if the cost increased 20%, the sales would also increase 20%. So yeah, if cost did nothing, sales would also do nothing, in this scenario.

DiscoZombie on
• Options
Registered User regular
edited August 2010
You would need to know what your profit margin was

Teslan26 on
• Options
Registered User regular
edited August 2010
Sounds like what you're trying to do is assume a fixed cost : sales ratio for 2010, and then look at what you'd get using 2009's costs with 2010's ratio.
( 2010 sales / 2010 costs ) * 2009 costs
= ( 110,000 / 60,000 ) * 50,000
~ [B]91,700[/B]


There's no telling whether that figure means anything, though. I'm no accountant.

Dude, I think you might be right - it may just be that simple... thanks! and yeah, I couldn't tell you whether that figure means anything either, and neither could my boss. Our department isn't generally involved with financial data. But whatever, he asks me to develop this report and here I am -_-

DiscoZombie on
• Options
Jersey CityRegistered User regular
edited August 2010
FWD's number matches up with my number (91,666.66) because what you're trying to do is assume that costs are directly related to sales. Well, as you know it's rarely that simple, but since you're only sticking with these numbers, that's the idea -- if you had kept costs the same you could assume that you would earn only 91.7k, all else being equal.

Today is actually the last class of my MBA program so while I haven't done any problems specific to a "cost/sales ratio," it's a pretty common "quick analysis" to do stuff like that. Basically what you're doing is saying "Alright, this is the ratio for last year. And this is the ratio for this year. If we keep the numbers the same, you can see that we had fewer sales had costs remained the same."

Of course then you do a deeper cost analysis to see if that actually is true, but yeah, you're basically just doing an easy ratio for sake of comparison.

EggyToast on
|| Flickr — || PSN: EggyToast
• Options
Registered User regular
edited August 2010
It is really unlikely to be that simple, though; I mean, you're assuming zero overhead, there (i.e. fixed costs that don't fluctuate on a per-item basis). I'm guessing your business has at least some overhead, and you're going to need to take that into account to get a useful number, I think (it's been awhile since I took accounting).

Thanatos on