Conditional Formula for VAT on Receipts

Question

gestyle Author

Level 1

1 points

Conditional Formula for VAT on Receipts

Hello!

I’m recording receipts in a table. Therefor, I’m using different columns for two different scenarii:

E:H : input including VAT (inserting data into column F)

I:M : input excluding VAT (inserting data into column I or/and K, as some receipts contain different VATs)

For this example, I kept both cases on the same row, for demonstration purposes. In real life, I’d use either E:H or I:M, not both for the same row.

So, at present, the VAT-level of 19% or 7% is selected for:

- E:H : by the little pop-up-menu in column E, with conditional styles turning the cell to rose (7%) or blue (19%).

- I:M : by inserting the value in either column I (for 7%) or K (for 19%).

It feels like a dumpy workaround and I presume there is a more elegant alternative, probably based on a checkbox enabling
- both calculation modes (inserts either “with”(F) or “without”(I or/and K) VAT)
- inserting data in only 2 cells instead requiring 3 different columns (F, I or/and K)*
- and reducing the actual amount of columns.

*: One single cell would be perfect but there is still receipts containing 7%-items and also 19%-items – therefor 2 different cells are still required.

Is there a much smarter and sleeker alternative?

🙂

G

ADDRESSBOOK-OTHER, Mac OS X (10.7.5)

Posted on Dec 17, 2013 6:14 PM

Reply

Answer 1

Barry

Level 9

59,867 points

Dec 18, 2013 12:32 AM in response to gestyle

Hi G,

OK, I think (after about three attempts) I have the table structure determined.

Column: Contents

A: Unique ID Number for each receipt.

B: Transaction date

C: Month (calculated from B)

D: Receipt number? Supplier? (does not enter into calculations?)

E: Input column: 1+tax rate for receipts where only the tax-in total is provided.

F: Input column: Tax-in amount for this category of receipt

G: Net (tax-out) amount calculated from E & F

H: Tax (Calculated from E & F)

I: Input column: Net amount subject to 7% VAT

J: Tax on amount in I (calculated)

K: Input column: Net amount subject to 19% VAT

L: Tax on amount in K (calculated)

M: Gross (Tax-in) amount (sum of columns I-L)

N: Cumulative Tax total to this row. (sum of J,K)

M and N appear to ignore any amounts in column H.

With the conditions you describe:

---Some receipts list only gross amounts, and must have the tax extracted from them

---Gross amounts in above receipts can include tax at either 7% or 19% (but at only one rate within a single receipt)

---Some receipts contain net amounts subject to 7% VAT AND net amounts subject to 19% VAT

---Each receipt is recorded on a single line

Given those restrictions, I think you are already at the minimum number of columns. The only change needed is to include column H in the cumulative tax calculation.

The largest design issue I see is the intermixing of input and calculation columns. Separating those would reduce the likelihood of accidently destroying one or more of the formulas with an accidentally misplaced click and keystoke.

If the receipts could be reduced to each having items of only a single category from the list below, it is probably possible to reduce the number of input columns to 2: One for the receipt amount (which could be either a before tax, net amount or a tax included gross amount), and a second column containing a five item pop-up menu:

" " (single space, indicating no entry yet made

Brutto: 07%

Brutto: 19%

Netto: 07%

Netto: 19%

This would allow a set of nested IF statement to:

Suppress the calculation if the first character is a space.
Choose the formula to calcualte the tax based on the first character of the menu item.
Determine the tax rate to use in the formula from the rightmost three characters of the menu item.

And allow the Gross amount currrently in column M to be calculated from the first input column plus zero (if that was already the gross amount) or the result of the tax calculation above (if the input was a net amount).

This could also be done with a pair of checkboxes replacing the buttom four choices on the pop-up menu, and a LEN(A)<1 test to determine if an entry has been made:

Three formulas, each entered in row 2 and filled down from there.

D2: =IF(LEN(A)<1,"",IF(B,A-A/IF(C,1.19,1.07),A*IF(C,0.19,0.07)))

F2: =IF(LEN(D)<1,"",A+IF(B,0,D))

G2: =IF(LEN(D)<1,"",SUM($D$2:D2))

Regards,

Barry

Reply

Answer 2

gestyle Author

Level 1

1 points

Dec 18, 2013 2:35 AM in response to Barry

Hey Barry!

I go on based on your points:

D is the Account Number (the destination).

N: Cumulative Tax total to this row. (sum of J,K)

Actually N counts J, L and N, for example =IFERROR(J18+L18+N17;0)
(https://discussions.apple.com/thread/5676735)

“M and N appear to ignore any amounts in column H.” : Yes, they only count for example:
M =SUM(I18;J18;K18;L18)
N =IFERROR(J18+L18+N17;0)

"---Some receipts list only gross amounts, and must have the tax extracted from them

---Gross amounts in above receipts can include tax at either 7% or 19% OR BOTH (but at only one rate within a single receipt).

---Some receipts contain net amounts subject to 7% VAT AND net amounts subject to 19% VAT

---Each receipt is recorded on a single line"

In case different rates apply on different items of one receipt, I jump over to the green section (entering data into I and K), yes.

Column E also includes a 0%-Tax ("--”) in case items require such one. I’m not sure if I should add a pair of green columns for that reason.

Column D is displaying the Account Number. There is also a column O concatinating these Account Numbers and their respective names, based on what’s entered into D. As O is standing out of my A4-Landscape-Layout, reducing the amount of columns and increasing the user-friendlyness would be great. The optimum state would be to gather all these information in one single A4-Landscape-Print-Layout.

O uses =IF(LEN(D4)<1;"";IFERROR(VLOOKUP(D4;KTO:BEZEICHNUNG;1;WRONG)&" "&VLOOKUP(D4;KTO:BEZEICHNUNG;2;WRONG);"<not found>"))
You know that one 😀

At the bottom of the table, 2 totals are added together:
N58 =SUM(N57;H57), where
- H57 represents the total VAT entered via F (where each receipt amount includes VAT)
- N57 represents the total VAT entered via I and K (where each item is entered without VAT and VAT is calculated from these amounts and displayed in J and L).

.

Reply

Answer 3

gestyle Author

Level 1

1 points

Dec 18, 2013 2:43 AM in response to gestyle

I clicked too early on “Post”, before finishing my response, sorry

I’m trying to take your 3 formulas into account, Barry 🙂

Thanks again!

Reply

Answer 4

gestyle Author

Level 1

1 points

Dec 19, 2013 12:08 PM in response to gestyle

I have accomplished 2 more steps:

First, inserted the 3 formulas into the sheet. That seemed to work fine …after it finally sunk in 🙂

Then, I went on in order to also insert a pop-up so I can choose the appropriate VAT-rate among more than two options. I duplicated the previous sheet (the one with checkboxes) so I could start from the same point. I thought it’s a good option, just in case I would destroy something. I named the new sheet POP-UP, accordingly to column G, now filled with a pop-up-menu. Editing the formula was kind of tricky but it finally seems to work. I hope.

I did that for two reasons:
1) Beside 19% and 7% VAT, I wanted to also have a 0% VAT-rate.
2) I think the pop-up just brings some more flexibility in case new VAT-rates come up someday. Editing the pop-up is done in a few seconds and it’s one single step – the G-cells containing the same formula-instance will kind of “update automatically” as there is no need to pull down anything that way. In fact, it’s no problem to add a few more VAT-rates to the pop-up list if it’s required for any reasons.ula I picked-up

This is the formula I picked up in G15:
=IF(LEN(E15)<1;"";IF(F15;E15-E15/(1+G15);E15*G15))

(English OSX seems to require "," instead of ";" – comma instead of semicolon)

MWSt means VAT. Sorry if it’s a bit confusing, but I did not updated all the keywords in the table yet.

😊

Message was edited by: gestyle …added the last 4 sentences.

Reply

Answer 5

Barry

Level 9

59,867 points

Dec 19, 2013 1:24 PM in response to gestyle

Hi G,

"This is the formula I picked up in G15:

=IF(LEN(E15)<1;"";IF(F15;E15-E15/(1+G15);E15*G15))

(English OSX seems to require "," instead of ";" – comma instead of semicolon)

MWSt means VAT. Sorry if it’s a bit confusing, but I did not updated all the keywords in the table yet."

Looks good.

The change in argument separator is due to North American use of the period (full stop) as the decimal separator. In areas where the comma is the decimal separator, the semi colon is used as the argument separator. As you were familiar with that use, and didn't seem perplexed by my use of the comma, I decided to leave the formulas as I'd written them. We're both capable of reading and translating to our localization.

MWSt = VAT became apparent fairly quickly—especially as it was followed by a % sign 😉

One caution regarding editing pop-up menus: Don't edit any pop-up that has already been used.

Once you've chosen a value, it's unlikely you'll want to change it for that receipt. If you edit the first pop-up menu in a column, then fill that down the whole column, it will replace the existing menus in the filled cells AND result in all of these being set to the same value as the edited menu.

Make your edits in the first row that needs the new values, and fill down from there.

Regards,

Barry

Reply

Answer 6

gestyle Author

Level 1

1 points

Dec 19, 2013 6:56 PM in response to Barry

Hey Barry,

The change in argument separator: I thought it’s a good idea to drop a line about this topic here for the next people who may read this article someday. I knew about the topic, but as I do not use Numbersevery day, that was one of the things I had forgotten between two sessions. I had to digg around for a while before it came back to my mind. So, I thought it happens perhaps the same to others. Not really you – even if it’s part of our discussion. 🙂

“Don't edit any pop-up that has already been used.”: if I already have a pop-up using e.g. 19%, 7% and 0% VAT-rates. Let’s say I’d like to add one new VAT-rate. Does it make any difference in case I update from the original Pop-up? :19% 7%
0% …to a new version with a new rate at the end, leaving the old entries at their original positions: 19%
7%0%15% (here the new insert)…or instead of this, a real insert, changing the original entries to create an ordered list: 19%
15% (here the new insert)7%0%
🙂

Reply

Answer 7

Barry

Level 9

59,867 points

Dec 19, 2013 8:34 PM in response to gestyle

"“Don't edit any pop-up that has already been used.”:

if I already have a pop-up using e.g. 19%, 7% and 0% VAT-rates. Let’s say I’d like to add one new VAT-rate."

In either case, make the edit (and the rearrangement) at the point in the table where you want to start using the new menu. As stated earlier, if you fill a menu down a column, all cells that it is filled in will contain the new menu and all cells filled into will have the setting of the menu that is being filled down. If you add the 15% and leave that menu set at 15%, then every cell that you fill that menu into will then contain a four item menu set to show 15%.

Regarding argument separators: I was pretty sure you knew that, as you hadn't mentioned any problem with it before. No harm in your mentioning it, though.

Regards,

Barry

Reply