Hi

I am a novice use of Excel but I need to create a calculation for my small
business so my employees quote our clients correctly. I own a limousine
company so we will often offer a discount off the base rate for a ride to
entice the customer however we only discount the base rate not the gratuity &
fuel charge. I need a calculation that I can have my employees just plug in
to quote correctly and quickly b/c once they quote we have to stand by it but
most of them are quoting incorrectly.

Example the correct way is this.

Base rate is $100
Gratuity is 20% or $20.00
Fuel is 10% or $10.00

With a 10% discount off the base rate it would be $90.00 plus 20% gratuity
and 10% the correct quote with a 10% discount would be $90.00 base, $20.00
gratuity b/c we do not discount gratuity and $10.00 for fuel we don't
discount this either. The correct total is $120.00

What employees are doing is discounting 10% off base rate for equaling $90
then adding 30% (gratuity & base) based on $90. This would total $117 b/c
they are discounting the gratuity & fuel charge and they should not be.

Can anyone help please?

Thank you
Jason


Thank you
Jason
--
JWD

A2 is base charge ($100)
A3 is discount (10%)
A4 is gratuity (20%)
A5 is fuel (10%)

Formula is:
=A2*A5+A2*A4+A2*(1-A3)

This will calculate your correct charge. Note that you can add in an IF
function possibly, if you want to setup a criteria as to whether or not a
discount is given.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jay" wrote:

Hi

I am a novice use of Excel but I need to create a calculation for my small
business so my employees quote our clients correctly. I own a limousine
company so we will often offer a discount off the base rate for a ride to
entice the customer however we only discount the base rate not the gratuity &
fuel charge. I need a calculation that I can have my employees just plug in
to quote correctly and quickly b/c once they quote we have to stand by it but
most of them are quoting incorrectly.

Example the correct way is this.

Base rate is $100
Gratuity is 20% or $20.00
Fuel is 10% or $10.00

With a 10% discount off the base rate it would be $90.00 plus 20% gratuity
and 10% the correct quote with a 10% discount would be $90.00 base, $20.00
gratuity b/c we do not discount gratuity and $10.00 for fuel we don't
discount this either. The correct total is $120.00

What employees are doing is discounting 10% off base rate for equaling $90
then adding 30% (gratuity & base) based on $90. This would total $117 b/c
they are discounting the gratuity & fuel charge and they should not be.

Can anyone help please?

Thank you
Jason


Thank you
Jason
--
JWD

Hi,

It sounds like your fuel and grat. are fixed, so you can use the simple
formula

=B1*(1.3-B2)

where B1 is the undiscounted rate and B2 is the percent discount entered as
a percent.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Luke M" wrote:

A2 is base charge ($100)
A3 is discount (10%)
A4 is gratuity (20%)
A5 is fuel (10%)

Formula is:
=A2*A5+A2*A4+A2*(1-A3)

This will calculate your correct charge. Note that you can add in an IF
function possibly, if you want to setup a criteria as to whether or not a
discount is given.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jay" wrote:

Hi

I am a novice use of Excel but I need to create a calculation for my small
business so my employees quote our clients correctly. I own a limousine
company so we will often offer a discount off the base rate for a ride to
entice the customer however we only discount the base rate not the gratuity &
fuel charge. I need a calculation that I can have my employees just plug in
to quote correctly and quickly b/c once they quote we have to stand by it but
most of them are quoting incorrectly.

Example the correct way is this.

Base rate is $100
Gratuity is 20% or $20.00
Fuel is 10% or $10.00

With a 10% discount off the base rate it would be $90.00 plus 20% gratuity
and 10% the correct quote with a 10% discount would be $90.00 base, $20.00
gratuity b/c we do not discount gratuity and $10.00 for fuel we don't
discount this either. The correct total is $120.00

What employees are doing is discounting 10% off base rate for equaling $90
then adding 30% (gratuity & base) based on $90. This would total $117 b/c
they are discounting the gratuity & fuel charge and they should not be.

Can anyone help please?

Thank you
Jason


Thank you
Jason
--
JWD