Calculate X1-5 and Y? If total cost is given.
 

Hi,

I was wondering if someone could help me.

I have 5 projects with different set of revenues.

Project Rev Cost Profit margin
P1 50000 X1 Y
P2 10000 X2 Y
P3 100000 X3 Y
P4 20000 X4 Y
P5 50000 X5 Y

I have total cost of X1-5 = 270000,-

And now I want the 270 000 to be distributed on my 5 project so that they
all have the same profit margin Y.

--
Regards Anders

Calculate X1-5 and Y? If total cost is given.
 

By Profit Margin, do you mean the difference between revenue and cost,
or the percentage profit compared with cost?

Pete

On Jan 16, 12:18*pm, Anders wrote:
Hi,

I was wondering if someone could help me.

I have 5 projects with different set of revenues.

Project * * * * Rev * * * * * * Cost * * * * * *Profit margin
P1 * * * * * * *50000 * * * * * X1 * * * * * * *Y
P2 * * * * * * *10000 * * * * * X2 * * * * * * *Y
P3 * * * * * * *100000 * * * * * * * * *X3 * * * * * * *Y
P4 * * * * * * *20000 * * * * * X4 * * * * * * *Y
P5 * * * * * * *50000 * * * * * X5 * * * * * * *Y

I have total cost of X1-5 = 270000,-

And now I want the 270 000 to be distributed on my 5 project so that they
all have the same profit margin Y.

--
Regards Anders

Calculate X1-5 and Y? If total cost is given.
 

% for example 20% on all of them.
--
Regards Anders


"Pete_UK" wrote:

By Profit Margin, do you mean the difference between revenue and cost,
or the percentage profit compared with cost?

Pete

On Jan 16, 12:18 pm, Anders wrote:
Hi,

I was wondering if someone could help me.

I have 5 projects with different set of revenues.

Project Rev Cost Profit margin
P1 50000 X1 Y
P2 10000 X2 Y
P3 100000 X3 Y
P4 20000 X4 Y
P5 50000 X5 Y

I have total cost of X1-5 = 270000,-

And now I want the 270 000 to be distributed on my 5 project so that they
all have the same profit margin Y.

--
Regards Anders

Calculate X1-5 and Y? If total cost is given.
 

The sum of the revenue in your example is 230000, so you will be
making a loss if your cost is 270000. Carrying through with this
example, though, your profit is -40000, which gives -14.8% profit. Is
this the profit margin you want to apply to all projects?

Pete

On Jan 16, 1:57*pm, Anders wrote:
% for example 20% on all of them.
--
Regards Anders



"Pete_UK" wrote:
By Profit Margin, do you mean the difference between revenue and cost,
or the percentage profit compared with cost?

Pete

On Jan 16, 12:18 pm, Anders wrote:
Hi,

I was wondering if someone could help me.

I have 5 projects with different set of revenues.

Project * * * * Rev * * * * * * Cost * * * * * *Profit margin
P1 * * * * * * *50000 * * * * * X1 * * * * * * *Y
P2 * * * * * * *10000 * * * * * X2 * * * * * * *Y
P3 * * * * * * *100000 * * * * * * * * *X3 * * * * * * *Y
P4 * * * * * * *20000 * * * * * X4 * * * * * * *Y
P5 * * * * * * *50000 * * * * * X5 * * * * * * *Y

I have total cost of X1-5 = 270000,-

And now I want the 270 000 to be distributed on my 5 project so that they
all have the same profit margin Y.

--
Regards Anders- Hide quoted text -

- Show quoted text -

Calculate X1-5 and Y? If total cost is given.
 

Oh boy! My bad..

Yes your right. Say total cost is 150000 then. So 34,78% profit margin.

How do i divide my cost out to get that profit margin on all projects.


--
Regards Anders


"Pete_UK" wrote:

The sum of the revenue in your example is 230000, so you will be
making a loss if your cost is 270000. Carrying through with this
example, though, your profit is -40000, which gives -14.8% profit. Is
this the profit margin you want to apply to all projects?

Pete

On Jan 16, 1:57 pm, Anders wrote:
% for example 20% on all of them.
--
Regards Anders



"Pete_UK" wrote:
By Profit Margin, do you mean the difference between revenue and cost,
or the percentage profit compared with cost?

Pete

On Jan 16, 12:18 pm, Anders wrote:
Hi,

I was wondering if someone could help me.

I have 5 projects with different set of revenues.

Project Rev Cost Profit margin
P1 50000 X1 Y
P2 10000 X2 Y
P3 100000 X3 Y
P4 20000 X4 Y
P5 50000 X5 Y

I have total cost of X1-5 = 270000,-

And now I want the 270 000 to be distributed on my 5 project so that they
all have the same profit margin Y.

--
Regards Anders- Hide quoted text -

- Show quoted text -

Calculate X1-5 and Y? If total cost is given.
 

Okay, that will give you a profit of 80000 on a cost of 150000, so I
work that out as a margin of 53.3% (i.e. compared with cost).

I put your headings in A1:D1 and your example data in A2:B6, with a
formula =SUM(B2:B6) in B7 to give a total income of 230000.

Put your total cost (150000) in C7, and this formula in D7:

=(B7-C7)/C7

and format this as percentage. Put this formula in C2:

=B2/(1+D$7)

and copy this into C3:C6. You can also copy the formula from D7 into
D2:D6 to give you the percentages, for checking. Finally, put the
heading "Profit" in F1 and this formula in F2:

=B2-C2

and copy this down F3:F6 to give you the actual profit for each
project - this column should add up to 80000 in F7.

All you need to do now is to change the value in C7 to see the effect
overall.

Hope this helps.

Pete

On Jan 16, 2:49*pm, Anders wrote:
Oh boy! My bad..

Yes your right. *Say total cost is 150000 then. So 34,78% profit margin.

How do i divide my cost out to get that profit margin on all projects.

--
Regards Anders



"Pete_UK" wrote:
The sum of the revenue in your example is 230000, so you will be
making a loss if your cost is 270000. Carrying through with this
example, though, your profit is -40000, which gives -14.8% profit. Is
this the profit margin you want to apply to all projects?

Pete

On Jan 16, 1:57 pm, Anders wrote:
% for example 20% on all of them.
--
Regards Anders

"Pete_UK" wrote:
By Profit Margin, do you mean the difference between revenue and cost,
or the percentage profit compared with cost?

Pete

On Jan 16, 12:18 pm, Anders wrote:
Hi,

I was wondering if someone could help me.

I have 5 projects with different set of revenues.

Project * * * * Rev * * * * * * Cost * * * * * *Profit margin
P1 * * * * * * *50000 * * * * * X1 * * * * * * *Y
P2 * * * * * * *10000 * * * * * X2 * * * * * * *Y
P3 * * * * * * *100000 * * * * * * * * *X3 * * * * * * *Y
P4 * * * * * * *20000 * * * * * X4 * * * * * * *Y
P5 * * * * * * *50000 * * * * * X5 * * * * * * *Y

I have total cost of X1-5 = 270000,-

And now I want the 270 000 to be distributed on my 5 project so that they
all have the same profit margin Y.

--
Regards Anders- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -