View Full Version : ...Help calculating costs...

Jeni.Black

12-04-2011, 07:30 AM

I am trying to use Excel to set up a formula to calculate the costs of goods. I can't seem to get the formula in the correct format to work! Help!

I need to take the cost + $1.20 x 12 + 15 / 12 = ____ then multiply it by 1.6 for the final price. Anybody?!?! I've tried it a million times and ways and it keeps giving me in incorrect result!

Thank you!!!

kjaada

12-04-2011, 07:36 AM

Not exactly sure but I think there are brackets needed

eg (cost+120)x12 etc.

Jeni.Black

12-04-2011, 07:39 AM

...but i'm not sure where is the problem! lol! i've tried them numerous ways and still can't seem to get it right :(

Paul.Cov

12-04-2011, 08:27 AM

Well, explain the purpose of each number.

Is the $1.20 a mark-up?

Is the 12 to do with months?

Is the 15 to do with GST?

Are any of those numbers meant to be percentages?

inphinity

12-04-2011, 08:52 AM

Ok so for an item with a cost of $5, we should get:

$5 + $14.40 + $1.25 = $20.65, which in Excel should be a nice simple formula like:

<Cell> + $15.65 (since we only have 1 variable and that variable is just added to the formula, the rest can just be made into it''s final value)

Unless you actually meant you want

((((<Cost> + $1.20) * 12) + 15)/12)

In which case we'd get:

$5 + $1.20 = $6.20

$6.20 x 12 = $74.40

$74.40 + 15 = $89.40

$89.40 / 12 = $7.45

dugimodo

12-04-2011, 09:33 AM

What I think people is telling you is the old BODMAS thing. The order in which formulas do operations is governed by it, so anything that needs to be done first should be in brackets.

eg 10+10/5 = 12

(10+10)/5 = 4

because the division is done before the addition unless you add brackets.

SP8's

12-04-2011, 09:43 AM

+ 1 with Paul .... we really need to know what the numbers stand for ... I'm would assume that the final price (multiply by 1.6) is your markup to selling price. As for the others, these could be freight costs, GST, Fixed and Fluctuating costs of having them in stock ... without knowing whether we're dealing with numbers or percentages, it's damn difficult to guess it correctly.

If you don't want to info to be public ... PM someone who can help you.

SP8's

12-04-2011, 09:47 AM

Jeni ... sorry, just realised that you only have 2 posts and you need to have 10 before you can PM someone.

dugimodo

12-04-2011, 09:55 AM

A simple way using excel is to break it into steps and just use more cells

eg Value in A1

A2=A1+1.20

A3= A2x12

A4=A3+15

A5=A4/12

It's not as elegant and makes the spreadsheet bigger, but can be a lot easier to read and troubleshoot. Effectively you are using cells like brackets.

SP8's

12-04-2011, 10:10 AM

5 1.2 12 15 12

74.4 89.4 7.45 11.92

5 1.2 12 15 12

=SUM(A1+B1)*12 =SUM(C2+D1) =SUM(D2/12) =SUM(E2*1.6)

If the last number corresponds to what you would like for an item with a cost of $5 ... then shorten the formula by taking the "=SUM" out and get help by using Excels formula help (blue question mark top right of spreadsheet)

Sorry ... formatting didn't work !

SP8's

12-04-2011, 10:23 AM

=SUM(A1+B1)*((12+15%)/12)*1.6 ..... see if that calculates properly on the figures above.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.