Any one good at maths and excel?

[_Deano]

Hi All
My maths is crap at the best of times, but i need to try and work out how to work out a figure in Excel. Can anyone help me with the below?

Cell B2 - Price of buying stock
Cell C2 - Amount of stock items which is the same in C3
Cell D2 - Commision of buying stock at a set price
Cell E2 - Varible cost to be added to Total amount Cell (which is F2)
Cell F2 - Total cost worked out as (B2*C2)+(D2+E2)

Cell B3 - Price that the stock item must be sold at to obtain profit of 2%
Cell C3 - Same as C2
Cell D3 - Same as D2
Cell E3 - not used
Cell F3 - Total amount from B3*C3
Cell G3 - This is profit which is calculated as (F3-F2)-D3

Now some how i need to work out that if i put an amount in B2 and an amount in C2 i will be able to get a figure in B3 (which works out as a 2% profit on each item), taking into account the commission and varible costs.

I hope that makes sense.

Thanks
Dean

Mods, if this needs to be moved, please move it to the corret location. Thanks

[johnfm]

Are you kidding?

You probably could have done it in the time it took to write your post.

You've done all the calcs there.

Just put them into excel and then check some sample data.

[V8mate]

Why does C2 have any relation to B3? And why are C2 and D2 repeated?

1. I'm not sure you're describing your business particularly well in Excel
2. You need to send someone your sheet, have a row discussion about it, delete it all, start again and then you'll have a correct operational sheet.

Edited by V8mate on Thursday 23 October 15:43

[mr_spock]

Here you go, this should do it:

profit book.xls [Link expires: 2008-11-22]
http://express.interoute.com/express/free/?id=22ef...

[Vee]

Do you want to calculate the price which give you 2% profit on all costs (var & comm), or excluding comm ?

As it stands your profit calculation G3 isn't right as is takes out commission.

B3 =(F2/C2)*102%

Edited by Vee on Thursday 23 October 15:58

[_Deano]

Thanks for that Mr Spock. Doh! i feel so dense.
I've just added a cell that divides Total by qty.

Thanks again

[V8mate]

mr_spock said:

Here you go, this should do it:

profit book.xls [Link expires: 2008-11-22]
http://express.interoute.com/express/free/?id=22ef...

Nonsense spreadsheet.

Why are quantity and commission repeated? It also assumes that commission and costs are in no way related to volume.

[_Deano]

It just shunted my brain in the right direction of thought. For some reason i didn't know how to work out an item price (profit/quanity)*1.02
hence me feeling silly. But i've got the answer i want now

[mr_spock]

_Deano said:

Thanks for that Mr Spock. Doh! i feel so dense.
I've just added a cell that divides Total by qty.

Thanks again

Happy to help.