weighted average profit margin

  • Thread starter Thread starter mar
  • Start date Start date
M

mar

Hi. I'm looking for help with how to calculate weighted average profit
margin. Basically, in an Excel spreadsheet, column A contains Sales
numbers and column B contains Profit Margin % for each of those sales
numbers. I need the weighted average profit margin for all sales
combined.

Sales profit
$100 25%
$500 15%
$300 10%
 
In column C (on another unused column) calculate the profit for each sales.

=A2*B2
etc.

Then use this formula to calculate average profit margin:

=SUM(C2:C4)/SUM(A2:A4)

or without using column C use this formula:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

Regards,
Per
 
Your total profit was: 100*25% + 500*15% + 300*10% = 25 + 75 + 30 = 130
Your total sales where 900, so %profit is 130/900 = 14.4%

If you data starts with "Sales" in A1, the sales values in A2:A4 and the
margins in B2"B4
then this formula gives you the correct result
=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

best wishes
 
Per Jessen said:
In column C (on another unused column) calculate the profit for each sales.

=A2*B2
etc.

Then use this formula to calculate average profit margin:

=SUM(C2:C4)/SUM(A2:A4)

or without using column C use this formula:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

Regards,
Per

"mar" <[email protected]> skrev i meddelelsen
news:[email protected]...
> Hi. I'm looking for help with how to calculate weighted average profit
> margin. Basically, in an Excel spreadsheet, column A contains Sales
> numbers and column B contains Profit Margin % for each of those sales
> numbers. I need the weighted average profit margin for all sales
> combined.
>
> Sales profit
> $100 25%
> $500 15%
> $300 10%
Click to expand...

Hi,

I have one level higher problem. I want to find out Annualized Return On Investment. The data table would given insight into the problem.
a b c d(c/a) e(d/b) f (e x 365)
Invest Invest-days Profit ROI roi/day Annulized ROI
20000 20 1450 7.3% 0.4% 132.3%
50000 30 2000 4.0% 0.1% 48.7%

It would be a great help if somebody can help me by giving a formula to arrive at Total Annualized ROI of all the investments.

Thanks in advance !!
 
Back
Top