dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
843
share rss forum feed


Nordy

join:2007-10-20
kudos:1

[Excel] What amount to tell the customer to get the amount I wan

downloadcalculator.xlsx.zip 7,108 bytes
Excel File
I'm hopelessly stuck on this.

I need to calculate the amount I need to get from the customer, so that after deducting transport charges, commission on the final sale value and bank charges on the final sale value, I will have the amount I want to get.

The amount I want in hand = 100
Commission @ 7% on final sale value
Bank Charges @5% on final sale value
Transport Charges = 10
Final sale value = ? (How do I get this figure)

The final sale value is the amount I'll tell the customer, so I'll receive $100 after deducting 7% on the final sale value and 5 % on the Final sale value and the transport charges.

The commission and bank charges are calculated on the final sale value. Eg: If the final sale value is 100, commission @ 7% will be 7 and bank charges will be 5.

I'm trying to do this in Excel and got stuck good. Can you please help?


mbernste
Boosted
Premium,MVM
join:2001-06-30
Piscataway, NJ

Re: [Excel] What amount to tell the customer to get the amount I

In your example, the additional cost would be $22 ($7 commission, $5 bank charges and $10 transport). So you'd charge $122.



norwegian
Premium
join:2005-02-15
Outback
reply to Nordy


What are costs? Etc.

Tab 1 - Costs
Tab 2 - Transport
Tab 3 - Total = Tab 1 + Tab 2
Tab 4 - Commission = 7 x tab 3 / 100
Tab 5 - Bank Charges = 5 x tab 3 / 100
Tab 6 - Grand Total = Total of Tab 3 + Tab 4 + Tab 5

I know this is a little extra than the reply above, but generally transport can be included in the full sale, or not.
Commission can work off the initial costs, bank charges too, but commission etc should be the last calculated if you want to stay in business. Otherwise, transport is a separate tab and just added at the end. Still do you have to add taxes or some such as well?

--
The only thing necessary for the triumph of evil is for good men to do nothing - Edmund Burke



Kilroy
Premium,MVM
join:2002-11-21
Saint Paul, MN
reply to Nordy

I don't know that you'll be able to do this with a simple spreadsheet. You can get close, but this will be a pain to get right. This is because your commission and bank charges rely on the final sale and not product plus transportation.

So, your cost is 100
Your transport is 10
7 percent of 11 is 7.7
5 percent of 110 is 5.5

But, that's not what you need, but it can get you close. Add them all togther and you come up with $122.20. Now, you're closer to the amounts you want.

If you figure your commission and bank charges on $125

You'll have $8.75 for commission and $6.25 for bank charges. Added to your $100 cost and $10 transportation you get $125. I don't know how to tell you to do it in a speadsheet becuase your commission and bank charges are based on the total and not the cost plus transportation.
--
“Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.” ¯ Robert A. Heinlein



norwegian
Premium
join:2005-02-15
Outback


You understand transport is a cost on it's own, a lot work like that, and most will not request more, but will add the fuel levy.

----------------------------

Nordy;

I think there is a billion ways to do this, however, formulation is on all known facts....either this is a first attempt to define a bill or school homework, 101.

Take no disrespect from my words; defining costs is always easier than tabulated formulas for an end result. Work out the bills then apply a simple profit percentage and include costs; best to double costs initially until you understand the project at hand; and then refine the bill on costs against the importance of the client and needs fore you both to survive.

It will all wash out.
--
The only thing necessary for the triumph of evil is for good men to do nothing - Edmund Burke



Nordy

join:2007-10-20
kudos:1
reply to Nordy

Its like this:

=(Nett + Freight) / (1 - Comm% - Bank%)

for my $100 example

=(100 + 10) / (1 - 0.07 - 0.05)
=125



Kilroy
Premium,MVM
join:2002-11-21
Saint Paul, MN

Looks like you have the fomula, what's the problem?

E2 =(a2+d2)/(1-.07-.05)
B2 =e2*.07
C2 =e2*.05
F2 =a2+b2+c2+d2

With F2 being a check to make sure E2 is correct
--
“Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.” ¯ Robert A. Heinlein



old_tech
Premium
join:2013-03-31
Springfield, IL
reply to Nordy

You have to think backwards. What is the original cost for the goods sold, then what is the tax on the goods, then what is the S&H before taxes are totaled on the goods sold. From there, you would total three, but to get the commission, you get it from only the cost of goods sold, not from the total of Cost, S&H, taxes.

The only thing is, you do not want the customer to see the markup or commission made on the sale, so you keep that info in another worksheet.

See the following »office.microsoft.com/en-us/excel···987.aspx Also »office.microsoft.com/en-us/templ···690.aspx

As for doing it in Access »www.brainbell.com/tutorials/ms-o···ield.htm

»www.brighthub.com/office/home/ar···933.aspx

Something a little more complicated »dbaspot.com/ms-access/422031-tab···ase.html



H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK
reply to Nordy

downloadcalculator.xlsx.zip 8,103 bytes
Fixed
You would put this formula in your cell:
=SUM(A2*(SUM(B2:C2)))+D2+A2

Here is your workbook with it in it.
--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot


Nordy

join:2007-10-20
kudos:1

downloadcalc.xlsx.zip 6,536 bytes
The right method
H2OuUp2 See Profile's excel sheet comes close. The right answer for 100 and 10 Freight and 7% Commission and 5% Bank Charges is 125. I've uploaded my own sheet showing how I did it. Thanks for helping


H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK

2 edits

I see what I did, I was charging commission on $100 not what you wanted (mark up to get to $100), guess I need to read better - DOH!

Anyway,

You don't want to charge commission on Freight do you? That's adding 12% to the freight charges.

#1. You should have: =A2/(1-SUM(B2:C2))+D$2 or $123.64 you would get $100.00 (minus commission + Freight)

#2. If you put: =(A2+D$2)/(1-SUM(B2:C2)) or $125.00 you would get $101.20 (minus commission + Freight). This one is paying commission on freight charges.

Not sure which one you are actually wanting, but you don't need the helper columns. I attached a new workbook with the results.
_____________________________________________________
To see what I'm saying, keep what you want at $100 and change the shipping charges to $1,000.

--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot


old_tech
Premium
join:2013-03-31
Springfield, IL

Already stated how this is figured out. Do not see why everyone is making it harder than it actually is.

It goes Cost of goods sold, Commission on Cost of Goods sold. Taxes are figured on cost of goods sold, but without the commission. S&H is set by the carrier and of course then through in the terms if they are Net 15 before/after deliver, Net 30, etc..

This is why companies like Intuit have already done all of the hard work for you and have software to do the job, that used be done by hand, by a room full of accountants.