Guest

MS Excel Wiz? Need Help

Sat Apr 12, 2003 3:28 pm

I need some help here guys and gals on trying to set up an Excel spreadsheet (formulas and such). It would be a pretty easy job I think.

To give run-down of what I need.

PersonS (me) and PersonA (a friend) are doing a little bit of wheeling and dealing and I need a spreadsheet to track our sales, and also our profit.

There are a few variables (hence the need for formulas), so I will spell out what is in each column

ColumnA : Item description (doesn't come into play for the formulae)
ColumnB : Item number (doesn't come into play for the formulae)
ColumnC : Final price for item
ColumnD : Listing Fee
ColumnE : Other fee
ColumnF : Postage Fee
ColumnG : PersonS (my) profit
ColumnH : PersonA (my friends) profit

Where it gets difficult (in my mind) is:

ColumnD and ColumnE are costs which I will incur in selling the item.
ColumnF is a cost which my friend will incur, and is not included in the final price (Column C), but which I will receive from the buyer. (i.e. You buy something from us, you pay me the amount in ColumnC and ColumnF, but my friend incurs that ColumnF figure at his end)

We are splitting the profits 50/50, but what I need to know is what formulae to use to insure that each share of the profit, takes into account the costs which each of us have incurred. So whilst we are sharing the profits equally, we are each going to have different costs involved.

Whilst I am calling ColumnG and ColumnH our "profit", it would possibly be better to describe as what each of us will have left over after all costs are incurred, or how much money I will have to send to my friend.

I hope I have explained this ok....if not ask me.

But I would appreciate it if an Excel wiz out there could help with some formulae for me.

Cheers

 
B747-437B
Posts: 8777
Joined: Thu May 30, 2002 6:54 am

RE: MS Excel Wiz? Need Help

Sat Apr 12, 2003 3:50 pm

Column G : =((C1-(D1+E1))/2)+D1+E1
Column H : =((C1-(D1+E1))/2)+F1
"The A340-300 may boast a long range, but the A340 is underpowered" -- Robert Milton, CEO - Air Canada
 
gotAirbus
Posts: 791
Joined: Fri May 18, 2001 10:02 am

RE: MS Excel Wiz? Need Help

Sat Apr 12, 2003 3:56 pm

Formulas go like this example:


If you want cell A1 to display the totals of R2 and R5, put this equation in cell A1:
=R2+R5

If you know arithmetic and algebra, you can use brackets "( )" to seperate equations (just like what he did above).

(gotAirbus?)
(gotAIRBUS?) - (Got Commonality?) - (Have A Nice Flight!)
 
Guest

RE: MS Excel Wiz? Need Help

Sat Apr 12, 2003 4:06 pm

G3 =C3-SUM(D3:F3) = Profit
H3 =G3/2+SUM(D3:E3) = 1/2 Profit + Partner 1 costs
I3 =G3/2+F3 = 1/2 Profit + Partner 2 costs





ADG
(did I win?)  Laugh out loud
 
Guest

RE: MS Excel Wiz? Need Help

Sat Apr 12, 2003 4:35 pm

I just rang a mate who is an accountant, and I gave him some example figures, and Sean's formula comes up with the exact amount. Sorry Bron, but he wins the price in this instance. Sean, just let me know when are down this way and I'll shout ya a few beers.

Thanks guys
 
Guest

RE: MS Excel Wiz? Need Help

Sat Apr 12, 2003 4:46 pm

Bron, the only problem with your formula is that the postage that I am getting paid by the buyer is to go to my partner. I don't get a share of that. There is something in your formula which splits the postage 50/50, when it should be 100% for him 0% for me. I think it is the G3 column where you have factored postage into the profit, when it is a ccst which the customer pays for.
 
FlyBoeing
Posts: 835
Joined: Fri May 05, 2000 2:08 am

RE: MS Excel Wiz? Need Help

Sat Apr 12, 2003 9:24 pm


There are a few variables (hence the need for formulas), so I will spell out what is in each column

ColumnA : Item description (doesn't come into play for the formulae)
ColumnB : Item number (doesn't come into play for the formulae)
ColumnC : Final price for item
ColumnD : Listing Fee
ColumnE : Other fee
ColumnF : Postage Fee
ColumnG : PersonS (my) profit
ColumnH : PersonA (my friends) profit


I would estimate that you would go

Total Revenue - Total Costs = Total Profits

(C+F)-(D
+E+F)

which is basically (C-(D+E))/2 for you and

(C-(D+E))/2 for your partner, plus F to cover postage.

That is, if your partner is honest, he tells you what F is and you add it to both revenue and costs. He pays 100% of it and you add that to the total costs and reimburse him. Make sure you get receipts.

Hopefully you share the D+E costs equitably.

Happy E-Baying!
 
Guest

RE: MS Excel Wiz? Need Help

Sun Apr 13, 2003 8:02 am

No idea Scotty ... if mine is wrong then you should make a note never to put me in charge of your money  Laugh out loud




ADG

Who is online

Users browsing this forum: Yahoo [Bot] and 18 guests