I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

If you want to get a little fancier, say, for example, some people didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you have 6
people that want to be full partnes, and 2 people that wanted to be less than
a full partner, say one is 75%, and one is 50%. Then the subsequent shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

--
John C


"Mike H" wrote:

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

John C,
Thank you vey much for your help and such a quick response. Your thought was
right on. The Partners will not be equal share holders. I have tried your
formula and am having a problem. Could I ask for a little more help?
Following are the actual percentages that the potential partners want to
contribute.
Purhcase price is $386,450. there are 9 potential partners their
contribution amounts are as follows.
20%
9%
18%
3%
3%
26%
11%
3%
8%

I can not seem to make your formula work
Thanks again
Bill







--
Thank you for your help.
Bill Booth


"John C" wrote:

If you want to get a little fancier, say, for example, some people didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you have 6
people that want to be full partnes, and 2 people that wanted to be less than
a full partner, say one is 75%, and one is 50%. Then the subsequent shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

--
John C


"Mike H" wrote:

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

Oh yea, one other thing just to clarify. If one of the partners drops out the
others will need to add to their contribution proportionately to make up the
difference. The total price needs to be $386,450.
--
Thank you for your help.
Bill Booth


"Bill Booth" wrote:

John C,
Thank you vey much for your help and such a quick response. Your thought was
right on. The Partners will not be equal share holders. I have tried your
formula and am having a problem. Could I ask for a little more help?
Following are the actual percentages that the potential partners want to
contribute.
Purhcase price is $386,450. there are 9 potential partners their
contribution amounts are as follows.
20%
9%
18%
3%
3%
26%
11%
3%
8%

I can not seem to make your formula work
Thanks again
Bill







--
Thank you for your help.
Bill Booth


"John C" wrote:

If you want to get a little fancier, say, for example, some people didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you have 6
people that want to be full partnes, and 2 people that wanted to be less than
a full partner, say one is 75%, and one is 50%. Then the subsequent shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

--
John C


"Mike H" wrote:

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

by simple math here, adding the %'s = 101%....unless there is some rounding
going on??
"Bill Booth" wrote in message
...
Oh yea, one other thing just to clarify. If one of the partners drops out
the
others will need to add to their contribution proportionately to make up
the
difference. The total price needs to be $386,450.
--
Thank you for your help.
Bill Booth


"Bill Booth" wrote:

John C,
Thank you vey much for your help and such a quick response. Your thought
was
right on. The Partners will not be equal share holders. I have tried your
formula and am having a problem. Could I ask for a little more help?
Following are the actual percentages that the potential partners want to
contribute.
Purhcase price is $386,450. there are 9 potential partners their
contribution amounts are as follows.
20%
9%
18%
3%
3%
26%
11%
3%
8%

I can not seem to make your formula work
Thanks again
Bill







--
Thank you for your help.
Bill Booth


"John C" wrote:

If you want to get a little fancier, say, for example, some people
didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you
have 6
people that want to be full partnes, and 2 people that wanted to be
less than
a full partner, say one is 75%, and one is 50%. Then the subsequent
shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

--
John C


"Mike H" wrote:

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on
partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We
may have
up to 10 partners, so each would contribute $38,645. I would like
to build a
table that allows "what if" scenario. For example if we only get 8
partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can
I develop
a formula that will allow me add to remove partner contribution
levels and
still keeping the total amount $386,450? I would like to do this
real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

Firstly, your percentages as given add up to 101%, not 100%, but that is
neither here nor there.
This is what I recommend:
Cells A1:A9, type in your given given percentages. In my example, I have
assumed partner 9 is actually 7%, instead of 8%. However, do not type these
in as percentages, type them in as whole numbers, like so:
20,9,18,3,3,26,11,3,7, this totals, effectively, 100 shares.
A10: =SUM(A1:A9) ... 100
B10: =386450.00 ... (total price)
B11: =SUM(B1:B9) ... (total of all individual amounts, should be equal to B10)
D1: =$B$10/$A$10 ... (price per share = $3,864.50)
B1: =A1*$D$1 ... (#shares * price per share) ... copy down through B9
C1: =B1/$B$10 ... format cell as percentage ... copy down through C9
C10: =SUM(C1:C9) ... should be 100%

Now, if someone decides to not join in, all you need to do is highlight the
row that that person was, columns A through C, and press the DELETE key.
Your value in A10 will decrease by the appropriate 'shares' number, B11 will
remain at the full amount, C10 will remain at 100%. Your per share cost in D1
will go up. And your percentages, that looked nice and neat originally in
cells C1:C9, will change. However, if you calculate it out, any remaining
amounts will still be in proportion to each other, as they were originally.
For example #2 @ 9% and #3 @ 18%, assuming they are one of the ones that
left, #3 will still be paying twice as much as #2.

Hope this helps!



--
John C


"Bill Booth" wrote:

John C,
Thank you vey much for your help and such a quick response. Your thought was
right on. The Partners will not be equal share holders. I have tried your
formula and am having a problem. Could I ask for a little more help?
Following are the actual percentages that the potential partners want to
contribute.
Purhcase price is $386,450. there are 9 potential partners their
contribution amounts are as follows.
20%
9%
18%
3%
3%
26%
11%
3%
8%

I can not seem to make your formula work
Thanks again
Bill







--
Thank you for your help.
Bill Booth


"John C" wrote:

If you want to get a little fancier, say, for example, some people didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you have 6
people that want to be full partnes, and 2 people that wanted to be less than
a full partner, say one is 75%, and one is 50%. Then the subsequent shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

--
John C


"Mike H" wrote:

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

John C, thank you so very much. The formula works perfectly. I will be ready
for my presentation on Monday. You've made my day.
Have a great weekend,
--
Thank you for your help.
Bill B

"John C" wrote:

Firstly, your percentages as given add up to 101%, not 100%, but that is
neither here nor there.
This is what I recommend:
Cells A1:A9, type in your given given percentages. In my example, I have
assumed partner 9 is actually 7%, instead of 8%. However, do not type these
in as percentages, type them in as whole numbers, like so:
20,9,18,3,3,26,11,3,7, this totals, effectively, 100 shares.
A10: =SUM(A1:A9) ... 100
B10: =386450.00 ... (total price)
B11: =SUM(B1:B9) ... (total of all individual amounts, should be equal to B10)
D1: =$B$10/$A$10 ... (price per share = $3,864.50)
B1: =A1*$D$1 ... (#shares * price per share) ... copy down through B9
C1: =B1/$B$10 ... format cell as percentage ... copy down through C9
C10: =SUM(C1:C9) ... should be 100%

Now, if someone decides to not join in, all you need to do is highlight the
row that that person was, columns A through C, and press the DELETE key.
Your value in A10 will decrease by the appropriate 'shares' number, B11 will
remain at the full amount, C10 will remain at 100%. Your per share cost in D1
will go up. And your percentages, that looked nice and neat originally in
cells C1:C9, will change. However, if you calculate it out, any remaining
amounts will still be in proportion to each other, as they were originally.
For example #2 @ 9% and #3 @ 18%, assuming they are one of the ones that
left, #3 will still be paying twice as much as #2.

Hope this helps!



--
John C


"Bill Booth" wrote:

John C,
Thank you vey much for your help and such a quick response. Your thought was
right on. The Partners will not be equal share holders. I have tried your
formula and am having a problem. Could I ask for a little more help?
Following are the actual percentages that the potential partners want to
contribute.
Purhcase price is $386,450. there are 9 potential partners their
contribution amounts are as follows.
20%
9%
18%
3%
3%
26%
11%
3%
8%

I can not seem to make your formula work
Thanks again
Bill







--
Thank you for your help.
Bill Booth


"John C" wrote:

If you want to get a little fancier, say, for example, some people didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you have 6
people that want to be full partnes, and 2 people that wanted to be less than
a full partner, say one is 75%, and one is 50%. Then the subsequent shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

--
John C


"Mike H" wrote:

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

"Bill Booth" wrote:

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.
--
Thank you for your help.
Bill

Bill Booth wrote:
I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.

The cost per share of the new business is $386,450/n with n being the number of
shares. Put $386,450 in cell A1 and the following in B1 and C1, copying down as
far as necessary:

B1=ROW()
C1=$A$1/ROW()