Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-30-2007, 07:44 AM   #1
Simon_MT
Newly Registered User
 
Join Date: Feb 2007
Location: United Kingdom
Posts: 2,177
Thanks: 2
Thanked 162 Times in 158 Posts
Simon_MT has a spectacular aura about Simon_MT has a spectacular aura about
A little puzzle?

In the UK under certain circumstances you can use the Retail Margin VAT Scheme. There are several ways to apply this scheme, the first, the VAT is calculated on your Gross Profit and you apply this rate as your effective rate of VAT. The second, and the one I use, is per transaction, the amount of VAT you pay depends on the amount of profit received for each sale that the Retail Margin Scheme is applicable. The difference between the UK and some EU countries is the sales tax on art is a fixed but substantially reduced rate.

This worked all very well until Artist Royalties (AR) came along. The wizards at DAC's decided to follow the EU model and use a banded scale based upon the Euro Day Rate which may impact on the bands to calculate Artist Royalty. It would be easy if the sales staff quoted a price plus AR, oh no, they give inclusive prices, this price is binding as it has been accepted.

To calculate the AR you need to know the Profit but to calculate the Profit you need to know the AR. Two unknowns!

The sale is 75,000 inclusive of both VAT and Artist Royalty
The cost is 50,000.

The Euro Rate is 1.3962 based on 16th November 2007
The AR bands are 50,000 @ 4% and 200,000 @ 3%
Vat 17.5% is calculated on the Profit element only
Atist Royalty is exempt from VAT

How much is the Profit?
What is the Vat Payable?
What is the Amount of Artist Royalty due?

This is the link to the AR Calculator.

http://http://resale.dacs.org.uk/ARRCalculator.aspx

I found a way in Excel to work this out but it is less than elegant and to me using Excel is like surrendering.

Simon

Simon_MT is offline   Reply With Quote
Old 11-30-2007, 08:59 AM   #2
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
Bit of school algebra here. I'm ignoring the mix of Euro and GBP and assuming only the 4% rate, just to establish the approach. If Selling Price is Sp, etc then this is the algebra:

Obviously Sp=Cp+Ar+Pr*117.5%
=> Ar=Sp-Cp-Pr*1175%
But also Ar=Pr*4%
Therefore Pr*4%=Sp-Cp-Pr*117.5%
=> Pr*4%+Pr*117.5%=Sp-Cp
=> Pr*121.5%=Sp-Cp
=> Pr=(Sp-Cp)/121.5%

Does that help stimulate your thoughts?
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 11-30-2007, 10:29 AM   #3
Simon_MT
Newly Registered User
 
Join Date: Feb 2007
Location: United Kingdom
Posts: 2,177
Thanks: 2
Thanked 162 Times in 158 Posts
Simon_MT has a spectacular aura about Simon_MT has a spectacular aura about
Neileg,

Thanks for the reply, the problem is that if the AR is a flat rate (eg 4%) then you could simply say:

Net = Sp / (1 + Ar)
Pr = (Net - Cp) / 1.175

Where I get stuck is when AR is on a sliding scale ie up to €50,000 it is 4% then, in this case, the remainder is calculated at 3%. Once you change the Net

Pr changes
Vat changes
and so does the Net
and therefore so does the basis of Ar

I have written a script to calculate AR just to come to terms with the mechanics of sliding scales but its inutiile until you can establish the amount to base the calculation.

Its nice to see some good ole algebra - brings back memories.

Simon

Simon_MT is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Another Security Puzzle! PearlGI General 13 05-22-2008 07:39 PM
Formula Puzzle Karen Howard Crystal Reports 0 11-17-2006 09:01 AM
Interesting Launch Schedule Schema Design Puzzle Ayat Tables 0 11-02-2005 08:29 AM
Table design puzzle L'apprentis Tables 11 11-01-2005 12:47 AM
A simple lookup Puzzle : nifty Queries 2 11-12-2004 02:30 AM




All times are GMT -8. The time now is 06:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World