vrijdag 11 oktober 2013

Excel: Display Profit Plus or Minus Per Company

How do you display for each company which one had a total positive profit (revenue minus costs) and which one a negative. My idea:



Here are the data from A1:D11:

Revenue Costs Profit
Company 1  €      500  € 400  €  100
Company 2  €   1.000  € 760  €  240
Company 3  €      890  € 900  €   -10
Company 4  €      640  € 700  €   -60
Company 5  €      570  € 600  €   -30
Company 6  €      875  € 800  €    75
Company 7  €      690  € 600  €    90
Company 8  €      500  € 600  € -100
Company 9  €   1.050  € 700  €  350
Company 10  €      900  € 700  €  200

From F1:H11 I created formulas:

Revenue - Costs Plus Revenue - Costs Plus Revenue - Costs Minus
=IF(D2:D11>0;B2:B11;NA()) =IF(D2:D11<=0;B2:B11;NA()) =IF(D2:D11<=0;C2:C11*-1;NA())
=IF(D3:D12>0;B3:B12;NA()) =IF(D3:D12<=0;B3:B12;NA()) =IF(D3:D12<=0;C3:C12*-1;NA())
=IF(D4:D13>0;B4:B13;NA()) =IF(D4:D13<=0;B4:B13;NA()) =IF(D4:D13<=0;C4:C13*-1;NA())
=IF(D5:D14>0;B5:B14;NA()) =IF(D5:D14<=0;B5:B14;NA()) =IF(D5:D14<=0;C5:C14*-1;NA())
=IF(D6:D15>0;B6:B15;NA()) =IF(D6:D15<=0;B6:B15;NA()) =IF(D6:D15<=0;C6:C15*-1;NA())
=IF(D7:D16>0;B7:B16;NA()) =IF(D7:D16<=0;B7:B16;NA()) =IF(D7:D16<=0;C7:C16*-1;NA())
=IF(D8:D17>0;B8:B17;NA()) =IF(D8:D17<=0;B8:B17;NA()) =IF(D8:D17<=0;C8:C17*-1;NA())
=IF(D9:D18>0;B9:B18;NA()) =IF(D9:D18<=0;B9:B18;NA()) =IF(D9:D18<=0;C9:C18*-1;NA())
=IF(D10:D19>0;B10:B19;NA()) =IF(D10:D19<=0;B10:B19;NA()) =IF(D10:D19<=0;C10:C19*-1;NA())
=IF(D11:D20>0;B11:B20;NA()) =IF(D11:D20<=0;B11:B20;NA()) =IF(D11:D20<=0;C11:C20*-1;NA())

and from J1:K11:

Revenue - Costs Minus Profit Plus Profit Minus
=IF(D2:D11>0;C2:C11*-1;NA()) =IF(D2:D11>0;D2:D11;0) =IF(D2:D11<=0;D2:D11;0)
=IF(D3:D12>0;C3:C12*-1;NA()) =IF(D3:D12>0;D3:D12;0) =IF(D3:D12<=0;D3:D12;0)
=IF(D4:D13>0;C4:C13*-1;NA()) =IF(D4:D13>0;D4:D13;0) =IF(D4:D13<=0;D4:D13;0)
=IF(D5:D14>0;C5:C14*-1;NA()) =IF(D5:D14>0;D5:D14;0) =IF(D5:D14<=0;D5:D14;0)
=IF(D6:D15>0;C6:C15*-1;NA()) =IF(D6:D15>0;D6:D15;0) =IF(D6:D15<=0;D6:D15;0)
=IF(D7:D16>0;C7:C16*-1;NA()) =IF(D7:D16>0;D7:D16;0) =IF(D7:D16<=0;D7:D16;0)
=IF(D8:D17>0;C8:C17*-1;NA()) =IF(D8:D17>0;D8:D17;0) =IF(D8:D17<=0;D8:D17;0)
=IF(D9:D18>0;C9:C18*-1;NA()) =IF(D9:D18>0;D9:D18;0) =IF(D9:D18<=0;D9:D18;0)
=IF(D10:D19>0;C10:C19*-1;NA()) =IF(D10:D19>0;D10:D19;0) =IF(D10:D19<=0;D10:D19;0)
=IF(D11:D20>0;C11:C20*-1;NA()) =IF(D11:D20>0;D11:D20;0) =IF(D11:D20<=0;D11:D20;0)

When you want, you can replace all the formulas by names.
  • Then I created a stacked bar graph based on F1:I11.
  • Changed the colors
  • Chose categories in reverse order.
  • Y-axis labels to Low.
  • Maximum for X-axis to 1200 and minimum to 1000.
  • Number format of the X-axis to 
#.##0;#.##0
  • Then added J1:K11.
  • Added both to the secondary axis.
  • Again maximum for X-axis to 1200 and minimum to 1000.
  • Added the secondary Y-axis.
  • Again categories in reverse order.
  • Deleted the secondary X-axis
  • Deleted the secondary Y-axis.
  • Added data labels.
  • Formatted them with:
_ € * #.##0_ ;_ € * #.##0_ ;_ € * "-"??_ ;_ @_ 
  • Added the graph title using the formula:
=Blad1!$A$37
  • In A37 the formula:
=Blad1!$B$1 & " Minus " & Blad1!$C$1 &": " &Blad1!$D$1

You can also download the file revenueplusminus.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing


Een reactie posten