Hello,

I got a problem with a calculation.
Let me explain:

The sales rate (hotel room) is 100 EUR, in this sales rate is the
breakfast included but it must be added a city tax (5%) on the sales
rate minus the breakfast rate (for one or two persons) and on this
city tax must be added 7% VAT. This together will give the rate the
customer has to pay.
This isn't difficult until now.

But now comes the bad point: The law says, that we have to give the
rate which is to pay to a private customer. So I need to make the
calculation backward from the end rate to the sales rate.

P.e. Sales rate EUR 100,00
Breakfast rate EUR 3,75 per person
5% city tax + 7% VAT is EUR 5,15 for a single room and EUR 4,95 for a
double room
And the end rae is EUR 105,15 for a single room and EUR 104,95 for a
double room.
I got a field "number of persons" in the database and a field for the
room rate where I can put in the sales rate - and the city tax will be
calculated and added or the end rate where than the sales rate should
be calculated.
(The database is in FM6, but it can be FM7 or FM8 as well.)

Thanks for your help.

Greetings from Cologne

Andreas

Andreas H. Zappel <az-deletethis (AT) ch-is (DOT) net> schrub:

What's the base for the city tax and the VAT?

Hard to understand. What would that be in German?
--
http://clk.ch

Andreas H. Zappel <az-deletethis (AT) ch-is (DOT) net> wrote:

Let's for shortness sake write S for your sales rate, E for your end
rate, B for the breakfast rate (3.75), C for your city tax (0.05 in your
example), V for your VAT factor (1.07) and n for the number of persons.
Now you calculate your end rate from:
S + (S-n*B)*C*V = E
In your single room example: 100 + (100 - 1*3.75)*0.05*1.07 = 105.15

To get at the backwards calculation we start by expanding the formula,
using (p-q)*r = p*r - q*r, to:

S + S*C*V - n*B*C*V = E

Combine the terms containing S:

S*(1 + C*V) - n*B*C*V = E

Rearrange to:

S*(1 + C*V) = E + n*B*C*V

Divide both sides by (1 + C*V):

S = (E + n*B*C*V) / (1 + C*V)

Mission accomplished.

Now if you want an end rate of EUR 100.00, your sales rate for a single
room should be:

S = (100 + 1*3.75*0.05*1.07) / (1 + 0.05*1.07)

= 100.20 / 1.0535 = 95.11

--
Hans Rijnbout
Utrecht, Netherlands

clk (AT) tele2 (DOT) ch (Christoph Kaufmann) wrote:

Es ist nicht nur fuer Aussenstehende schwer zu verstehen.

Es gibt einen Zimmerpreis inklusive Fruehstueck und MWSt.
Von diesem Preis ist das Fruehstueck abzuziehen (also 3,75 EUR mal
Anzahl der Personen) und man erhaelt den Logispreis inkl. MWSt.
Auf diesen Logispreis kommen 5% Kulturfoerderabgabe (eben die City
Tax) zuzueglich 7% MWSt..
Da laut deutschem Gesetz dem Kunden (Endverbraucher) die Endpreise zu
nennen sind muss ich also bei z.B. 100 EUR Zimmerpreis dem Kunden
einen Preis von EUR 104,95 fuer das Doppelzimmer bzw. EUR 105,15 fuer
das Einzelzimmer nennen (sprich in Reservierungssysteme einstellen).

Jetzt suche ich die Moeglichkeit die Rechnung so zu gestalten, dass
ich als Uebernachtungspreis entweder den Zimmerpreis oder den Endpreis
eingeben kann und dann FM abhaengig von der Personenzahl und einer
Auswahl ob die KFA enthalten ist den jeweils anderen Preis errechnet.
Vom Zimmerpreis hoch rechnen ist ja einfach, aber vom Endpreis runter
gar nicht mehr, da es dabei zu entsprechenden Differenzen kommt,
welche dann die Zahlen auf der Rechnung verfälschen.

Atschuess

Andreas

jrijnb (AT) xs4all (DOT) nl (Hans Rijnbout) wrote:

That's the solution.

Thank you.

Greetings from Cologne

Andreas

In article <a3eta6d3p5hhu6847br9pkhb3cdfo6ppja (AT) 4ax (DOT) com>,
az-deletethis (AT) ch-is (DOT) net wrote:

I'm not fully understanding this so may be completely wrong, but if City
Tax is 5%, then shouldn't the number in the calculations be 1.05 instead
of just 0.05???

Helpful Harry )

your.name (AT) isp (DOT) com (Your Name) wrote:

I also don't understand it 100%, but it works perfekt.
I checked it with the calculation I made in a spread sheet and printed
out for our information and it gave me exactly the same results.

Greetings from Cologne

Andreas