SOLVED Basic Math Program BACK AGAIN :(

Hello
I have a program that i need to be able to figure out the tax rate, and then find out how much tax is owed to the customer.

There are a few tricks when doing this, there are many different shipping rates, that go from 3.95 to 4.95 5.95 etc.
So what I wanted to do was enter information from the invoice and have it automaticly caluclated.

Now this is the tricky part, if the customer ordered say 3 parts, i wanted to be able to find out the tax, then have another box where i could put in the price of the part(if they only returned 1 part instead of the whole order), and then have it figure out the tax on just that part.

Here is some of the data on the invoice:

Tax Amount .68
Sales Amount 4.49
Invioce Amount 9.12

So the first thing i need the to figure out is how to i figure out what percentage of tax was charged, then have that tax amount * it self by all the different shipping rates.
Then have another box where i can put in a single part price, and hit calcuate and get the tax on just that part.

Thanks for the help!

*cough*

http://www.vbforums.com/showthread.php?t=342550

why didnt you just revive your old thread?...

Can you make up numbers and show exactly how you want it to work and how you arrive at your solution. And does it have to be in Visual Basic? From the sounds of it you can do everything here you want in excel, and add more stuff to it, and it would be easier to code and debug in excel in my opinion.

Yeah, I have an excel spreed sheet that can do it, but i would rather have a VB program that does it. :)

Can you post the excel sheet and i can get a better look at what you want from the program by chance?

Ok, someone returns a part to our warehouse, and i want to figure out how much tax i have to give them back, on the invoice it dosent show what the tax rate was, it was somehow calculated on our bigger mainframe program.

SO i get the invoice, it shows me the tax amount .68 and the sales amout (this is how much the part totals are) 4.52, and the invoice amount, which is the total of shipping sales amount and shipping and handling.

So what i want to do is first figure out what the tax rate is.
Then we have multiple shipping tiers depending on what type of account, we have 3.95 5.95 4.95 6.95 etc

I want to take that tax amount and figure out how much tax is due upon the shipping cost.

Then finally, i want to enter how much a single part is (in a seprate box), since there was 2 on the order, 1 part would be 2.26, so i want take that amount, enter it into a seprate box, and hit calcualte and it takes the tax rate and does its calcuation to figure out the tax rate on just that 1 part :)

Ok, So from what i can understand is you have a amount on the invoice for the prices of all items sold to them... and a total cost, which includes the price of all items sold, shipping and handling and tax. You want to take 1 item from that cost and then refund them the price the item costs and the tax they paid for that item? So if there were 2 items at 2.50 each, total 5 before shipping and handling and taxes. The tax Rate is 5% and the shipping and handling is $5.00. So the total would be 10.50 after taxes? Since the tax rate is applied to shipping and handling as well? So they come back to have the 1 item refunded at 2.50. You want to return 2.50 and the 5% tax on it. ( not refunding the shipping and handling charges and the taxes associated with that? ) So you would take the 2.50 + (2.50 * .05) = Refunded Amount? Is that correct?

Yes, you almost got it, the thing is, it dosent give me there percetage of tax they were charge it just show me the amount, .63 etc.

Plus it dosent show me the shipping charges, i have to see what type of account they are and then figure out the tax on the shipping. so i was saying while it calculated the tax, it can also figure out the tax rate found and apply it to the different shipping tiers 4.95 5.95 7.85 12.95 etc. then i can just look down the list and say oh there it is :)

But everything else is correct!

Do they even charge sales tax on the shipping amount? I'm not sure that they do.

Yep we do, we have to charge tax on the "base price" and our base price is parts + shipping :) not my rule but company rule lol
:)

I did a quick program to illustrate what i think your asking. Im sure thats not all your after... Please explain what else your looking for please. See the attached file.

The tax rate should be static. Just divide the amount taxed by the total, and multiply by 100 to get the percentage for the tax. I think I got around 15 whenI tried it earlier.

Joe that what you need ?

I think he's going to ignore all responses here and just start a 3rd thread to ask the same question once more.

Then he'll get a new group of people to give him the same answers he got twice before.

Maybe he's discovered a scheme to defraud the local government out of tax dollars, or a way for someone to profit from excesses paid? I'd check into it. Call the local authorities and ask them how to compute owed taxes.

who knows... lol

Justin H. Thanks, that is about 95% of what I'm looking to do.

Now, all i want to do is take that tax % and figure out the tax on the different shipping tiers, so when they hit "Calculate tax" it will calculate the tax on the shipping rates aswell.

The different shipping rates are:
4.95, 5.95, 8.95, 10.95, 12.95
:)

You are refunding the shipping and handling? Not that it matters, as we've said, the tax rate remains the same (static) for all items. Post the figures that you are trying to reproduce, and we will come up with the forumula to let you do it.
If you can get the right numbers using a calculator, then the app should be able to handle it as well.
Just posted an example, but I'd like to see actual numbers. Somethig appears to be missing between what you do and what you say you need. :wave:

Look it up, you have to figure out tax on what your "base price is" our base price is part + s&h :)

Ok here is the excel spreed sheet I am looking at.

All that is on this excel spreed sheet the person needs to ender is:

Invoice
Sales Amount
Other (this is the price of the part)

You see how you dont have to enter the price of the shipping, it just calculates it on all the different shippings? Then you just look to see what type of account they are (we do here) and look at the right shipping.

Also, i get the correct amount back if i go Sales Amount + Sh to put in the total order box in the program justin made. But you cant calcualte tax like that right?

here is the file location

http://www.thefilebucket.com/userfil.../TaxCredit.xls

Quote:

---

Originally Posted by joefox

Justin H. Thanks, that is about 95% of what I'm looking to do.

Now, all i want to do is take that tax % and figure out the tax on the different shipping tiers, so when they hit "Calculate tax" it will calculate the tax on the shipping rates aswell.

The different shipping rates are:
4.95, 5.95, 8.95, 10.95, 12.95
:)

---

Like that?...

Ok now im not sure what program is wrong, the excel spreed sheet, or the VB program.

You get your tax percent from total amount sold * amount taxed. So If your total amount sold was just the sales amount and Shipping and handling then yes that would be the correct tax amount. As for which program is right. Im not sure, the differences your getting, how big are they? The program i made uses the tax before its rounded or anything to find the tax on the shipping and handling. So, im not sure which youd want.

Quote:

---

Originally Posted by joefox

Ok now im not sure what program is wrong, the excel spreed sheet, or the VB program.

---

Don't you have test data and results to base this off of?

Find an old invoice and use its information to verify...

Ok here is an old invoice i pulled up. But it dosent show how much was credited because that was no an old system we used that we no longer have access to.

Part price was 4.49 (only 1 part on order)
Tax on order was .68
S&H was 3.95
Sales amount was 4.49
Invoice total amount was 9.12

:)

and did it work?

I dont know because our old system dosent work anymore, and someone made the excel spreed sheet up.

So is there a sure way to verify the calcuations are correct?

Just use a calculator and then make up some numbers that work, then use them with the excel sheet and see what results you get, then use them with the program see what results you get and then match them up with the calculator to see whos right and which you want to use...

Ok its incorrect, the way i check is.
I take the tax percentage that was calcualted and then * it by the sub total, so it should make .68 tax but it dosent it way less.

So tax amount was .68
Then S&H was 3.95
Sales amount is 4.49
Invoice amount is 9.12

SO if i take the the tax % found by the program its: 7.46%
Then i take that and * it by Sales amount + s&h, this should give me the the tax of .68 cents, but it dosent.

I think the excel spreed sheet is wrong, i think it calculates the tax% by adding s&h + sales amount then / by the tax amount. and thats wrong. because you need to figure out the % from the whole invoice amount correct?

So in excel they have it:
S&H + Sales Amount / tax

In the VB Program:
Invoice AMount / tax
' this way seems to be correct

you're sounding like a broken record....We know how you're checking it.

Maybe you should explain in better detail what you want. Or possibly post this in the math section of the forums, as this isnt really a programming question.

It is a a programing question lol

If you look at the excel spreed sheet, and you put in the data i put above, it seems that it dosent calculate the tax and the "other" amount correctly.

I think i got it, thanks!

Well If you have it can you please change the title of the post and add the word Resolved and placed a Check box to the title as well

Ok well.... lol

The VB Program calculates it just the same as excel program, but the VB program is always 1 cent higher?

So checking the equation is always 2 cents higher.

However, in the excel spreed sheet, it equals out and matches the tax amount on the invoice.
So maybe i dont need the larger tax % number?

Well that could be because im not taking the rounded version of the tax percent to do the calculations, which, depending on how it was rounded, (up or down) can throw it off a penny. If the invoice is using it the same way as the excel sheet then your numbers will be the same, if you want those results, then you just need to take the rounded version instead of the unrounded version to do your calculations. The program is more accurate, but if compared to another program you do to your invoice theres no way to know. But that would be my guess....

Yeah thats how the invoice calc's it.
So i would use:

Label3.Caption = TaxPercent ' use this

OLD

Holder = Label12.Caption * TaxNumber
Holder = Format(Holder, "$###.##")
Label17.Caption = Holder

NEW

Holder = Label12.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label17.Caption = Holder

Ah shucks i think i messed it up:
It gives me an data error :(

Here is what i changed it to:

VB Code:

Dim TaxPercent As String
Dim TaxNumber As String
Dim Refund As String
Dim Holder As String
 
Private Sub Command1_Click()
TaxPercent = Text2.Text / Text1.Text
TaxNumber = TaxPercent
TaxPercent = Format(TaxPercent, "##.##%")
Label3.Caption = TaxPercent
Label6.Caption = TaxNumber
 
Holder = Label12.Caption * TaxPercent ' was taxnumber
Holder = Format(Holder, "$###.##")
Label17.Caption = Holder
 
Holder = Label13.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label18.Caption = Holder
 
Holder = Label14.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label19.Caption = Holder
 
Holder = Label15.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label20.Caption = Holder
 
Holder = Label16.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label21.Caption = Holder
 
Holder = Label24(0).Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label29.Caption = Holder
 
Holder = Label25.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label30.Caption = Holder
 
Holder = Label26.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label31.Caption = Holder
 
Holder = Label27.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label32.Caption = Holder
 
Holder = Label28.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label33.Caption = Holder
 
Holder = Label34.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label39.Caption = Holder
 
Holder = Label35.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label40.Caption = Holder
 
Holder = Label36.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label41.Caption = Holder
 
Holder = Label37.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label42.Caption = Holder
 
Holder = Label38.Caption * TaxPercent
Holder = Format(Holder, "$###.##")
Label43.Caption = Holder
 
End Sub
 
Private Sub Command2_Click()
Refund = Text3.Text + (Text3.Text * Label6.Caption)
Label10.Caption = Refund
Label8.Caption = Format(Refund, "$######.##")
 
Label23.Caption = Label8.Caption - Text3.Text
End Sub
 
Private Sub Command3_Click()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Label8.Caption = ""
Label3.Caption = ""
Label10.Caption = ""
Label6.Caption = ""
Label23.Caption = ""
 
Label17.Caption = ""
Label18.Caption = ""
Label19.Caption = ""
Label20.Caption = ""
Label21.Caption = ""
 
Label29.Caption = ""
Label30.Caption = ""
Label31.Caption = ""
Label32.Caption = ""
Label33.Caption = ""
 
Label39.Caption = ""
Label40.Caption = ""
Label41.Caption = ""
Label42.Caption = ""
Label43.Caption = ""
End Sub

This should do it i think...

you have an error because you are computing with a % in your equations... did you try the program i uploaded?

It still calc's it 1 cent higher :(

And now?

BINGO THAT WORKED
THANKS FOR ALL YOUR HELP!!!!! :wave: :wave: :wave: :wave: :wave: :wave: :wave: :wave: :wave: :wave: :wave: :wave: ;) ;) :eek:

Ok well I thought we had the VB Program figured out, however it seems its incorrect;

I have 2 seprate invoices, the calculations work on the 1 of the invoices but not on another.

The way i can double check to see if its correct is:
Once the get the tax % and then get the tax % on the part, i get the difference between that part price with the tax added on, and minus it from the original price of the part, this will give me how much tax was on the part.
I take that number, add it to the shipping and handling tax back, and it should equal the total tax amount, but on the second invoice it dosent.

First Invoice:
Total Amount 9.12
Total Tax Amount .68
Shipping 3.95
Sales Amount/Price of part 4.49
When calculated tax back on shipping is 35 cents, and tax back on part is 33 cents, that equals .68 cents, the same as the tax amount, so this one is correct.

Second Invoice:
Total Amount 15.54
Total Tax Amount .98
Shipping 4.95
Sales Amount/Price of part 9.61
The Tax amount back on the shipping is .30 cents, tax back on the part is.610000000
these added together are .91 cents, and it should be .98

Are you sure you didn't input it wrong?

Also im sorry to say that is an error with your invoice program. Do it with a calculator, The total Amount / The Tax amount = Tax Percent

Sales Amount + Shipping = Total amount before tax

Tax = Total Amount before tax * Tax Percent

Total Amount = Tax + Total Amount before tax

You will see the program is right... and the invoice program is wrong

The invoice program is correct, its the VB program that is wrong..

This is the way to double check:

Once you get your tax %, then add sales amount + s&H and * it by the tax% and you should get the tax amoubnt, but you dont, its lower.

Frist Invoice:
Total Amount 9.12
Total Tax Amount .68
Shipping 3.95
Sales Amount/Price of part 4.49
Tax Percent is 7.46% ' this value is found using the vb program you sent
7.46% * Shipping + price of part ($8.44) = .63 (Wrong)

Second Invoice:
Total Amount 15.54
Total Tax Amount .98
Shipping 4.95
Sales Amount/Price of part 9.61
Tax Percent is 6.31% ' this value is found using the vb program you sent
6.31 * Shipping + price of part ($14.10) = .89 (wrong)

Ok, i think i found the error. When you figure out tax using the total cost and the tax amount you have to subtract the tax from the total cost before you do the tax calculation... Try this out and see if it works...

VB Code:

Dim ship As Currency, sale As Currency, tax As Currency
ship = 3.95
sale = 4.49
tax = 0.68
trate = Round(tax / (ship + sale), 2) ' =.08 or 8% tax rate, when rounded to 2 decimal places
Debug.Print trate
Debug.Print Round(ship * (trate + 1), 2) + Round(sale * (trate + 1), 2)
Debug.Print "Invoice total " & Round((ship + sale) * 1.08, 2)

in this example the las 2 amounts printed are equal

VB Code:

Dim ship As Currency, sale As Currency, tax As Currency
ship = 3.95
sale = 4.47
tax = 0.68
trate = Round(tax / (ship + sale), 2)
Debug.Print trate  ' or
Debug.Print Round(tax / (inv - tax), 2)
 
Debug.Print Round(ship * (trate + 1), 2) + Round(sale * (trate + 1), 2)
Debug.Print "Invoice total " & Round((ship + sale) * 1.08, 2)

in this exmple the the different ways of calculateing the invoice total produce different results

on multi line invoicing it depends if the tax is calculated on each line then summed up, or if the tax is caculated once for the total of the taxable amount, many programs calculate the tax on each line, so to get the accurate tax percentage is difficult, the more lines the greater inaccuracy if calculating from the total

hope this helps with your caclulations
pete

I am not sure how the old system calculated tax, all i know is if i take the 2 totals and of tax that is credited it should equal the correct amount.

Justin H. It seems the "credit back on part is right" but the shipping credit is now wrong, when i add the 2 differences together to dosent equal the right price

Everytime i check it in the excel spreed sheet i posted, the values come back and equal the Total tax that was charged, maybe VB can duplicate Excel lol

Did you try that last attached Program Yesterday at 7:07 pm?

Yes, I tried it.

Values
**INVOICE 1***
Invoice Amount(total order) 9.12
Tax amount (total tax) .68
S&H $3.95
Sales Amount (Price on parts) $4.49
** THIS COMES OUT RIGHT ON THE CALC.

**INVOICE 2***
Invoice Amount(total order) 15.54
Tax amount (total tax) .98
S&H $4.95
Sales Amount (Price on parts) $9.61
** THIS COMES OUT INCORRECT ON THE CALC.

**INVOICE 3***
Invoice Amount(total order) 36.21
Tax amount (total tax) 2.21
S&H $11.95
Sales Amount (Price on parts) $22.05
** THIS COMES OUT INCORRECT ON THE CALC.

Ok, there was a problem it with using the rounded number to do the tax, which i put it that way a long time ago when you said it was off a cent and i was trying to match the excel sheet... Try this one...

Still incorrect!

Im not sure why the first one works but the others 2 dont, maybe the first one is just a fluke.

The last worked for me on the ones you provided...

Im sorry for some reason i uploaded the old version instead of the one i wanted to give you, i edited the message with that upload and posted the correct one please download that one and try it then thanks

... That work Joe??

The repost did it.
It worked on all 4 examples!

Thanks again!