Hi all

I am an Excel "expert" and know my way around Double Entry but I am
not a Tax Expert. I am trying to put together a CV showing what I can
do to take care of the humdrum office trivia, as well as the more
serious stuff.

My introduction is here and if anyone would care to proof read it for
errors I would be very grateful. I would also appreciate a link to
Sage Nominal Codes that are detailed enough to show Benefits in Kind.
I presume these would be individual nominal accounts, one for each
employee.

And if you can think of any further complications that can be
included, that would also be good.

starts

One of your employees spends the day driving around with a potential
customer looking at various sites. They stop for a meal in a service
station and on his company card he pays for:

2 Snacks Meals 10.00
Petrol 30.00
Sweets 2.00

One of the meals is Entertaining and is not allowable for VAT or for
Tax. It needs to be posted Gross of VAT to the Entertaining account
(Sage code 7403), where it will appear in the Profit & Loss Account
and be disallowed in the Tax Computation.

The other meal is Subsistence which is allowable for VAT and Tax. It
should be posted Net of VAT to the Travel Account as an Allowable
Expense (Sage code 7400) and the VAT recovered.

The sweets are PIID Benefits in Kind, subject to Class 1a NIC unless
the amount is repaid. It needs to be posted Gross of VAT to the
Benefits in Kind Account (Sage Code 70??) as an Allowable Expense.

The Petrol will be particular to the circumstances of your firm and
your employee. Maybe the company owns the car, maybe the empoyee does.
Maybe you recover all the VAT and pay a Scale Charge, maybe you
recover a proportion of the VAT. It is likely that you pay a mileage
allowance, in which case VAT is recoverable on the allowance and the
remainder is Allowable for Tax, but then the whole petrol payment is a
Benefit in Kind.

So this simple everyday transaction is a minefield of things to get
wrong - things your Accountant may need to put right at £50 or £100 an
hour if you mess it up now.

What you need is an Excel spreadsheet to handle all this analysis and
batch the entire month into a single accurate posting...but this is
quite a serious spreadsheeting challenge!

ends

And if you can think of any further complications that can be
included, that would also be good.

And it might be double-plus ungood. Much depends on how much you expect
your readers to know or expect you to know: simple will be good for most
but not impress an expert. And for a CV it all frankly strikes me as
OTT. It says next to nothing about your competences.


The other meal is Subsistence which is allowable for VAT and Tax. It
should be posted Net of VAT to the Travel Account as an Allowable
Expense (Sage code 7400) and the VAT recovered.

You appear to be addressing the treatment for the employer. But what
about the employee? Are you assuming there is a dispensation in place
for the employee's subsistence? If so, say so. If not the amount ought
to be returned by employer and employee and the employee claim a
matching deduction; and you need to address NICs (see below).

The sweets are PIID Benefits in Kind, subject to Class 1a NIC unless
the amount is repaid. It needs to be posted Gross of VAT to the
Benefits in Kind Account (Sage Code 70??) as an Allowable Expense.


But if the sweets were consumed as part of the meal they might be
allowed or covered by the dispensation. Better example might be
something
manifestly personal - eg a light bulb to replace a blown one in his loo
at home.

More generally, you have made life harder for yourself by picking use of
a company
credit card as the example. The result is subtly different rules from
those for
"benefits" the employer provides in kind. Eg

a. you are (potentially) into Class 1 NICs rather than Class 1A - ie
both employer and employee may have to pay NICs on the £2. See eg
Appendix 1 of CWG5.


b. the return on the P11D (NB not "PIID") (or P9D if you want to be
comprehensive) ought to be under "vouchers and credit cards"


What you need is an Excel spreadsheet to handle all this analysis and
batch the entire month into a single accurate posting...but this is
quite a serious spreadsheeting challenge!


It's also quite a serious tax challenge and I'm retired so E&OE

If you have no experience of payroll I am surprised you are you putting
yourself forwards as someone who can deliver what an employer can buy
elsewhere already proved. Indeed, I'd urge you to download and play
with some of the free trials of commercial packages before you claim you
can deliver the equivalent.
--
Robin
PM may be sent to rbw0{at}hotmail{dot}com

Plac wrote:

Hi all

I am an Excel "expert"

Wow, just what I need. Perhaps you (or anyone else for that matter)
can advise me on how I might achieve the following. Preferably a method
which works not only for Excel but for other spreadsheets (e.g. OpenOffice).

Having created (by hand) a sheet with four (or so) columns, whe
column 1 might contain a transaction sequence number,
column 2 an analysis category code,
column 3 the amount,
column 4 (and possibly a few other) various other details such as
date and miscellaneous reference information and general remarks.

What I'd like it to do is to re-organise the sheet into two (or so) plus
N columns, where N is the number of analysis category codes which are in
use.

I'd like it to create a column-headings row (or I could supply this myself
and get it to recognise them), and then for each row in the original,
I want it to take the code in column 2 and use it as an index into the
column headings, and to put the amount from column 3 into the column
thus identified. Columns 1 and 4 of the riginal become columns 1 and 2
of the result.

Not clear? Example:

Input:

+---+----+-------+-----+
| 1 | A1 | 12.34 | ... |
+---+----+-------+-----+
| 2 | A3 | 23.45 | ... |
+---+----+-------+-----+
| 3 | B2 | 11.99 | ... |
+---+----+-------+-----+
| 4 | A3 | 9.50 | ... |
+---+----+-------+-----+

Output:

+---+-----+-------+-------+-------+
| | | A1 | A3 | B2 |
+---+-----+-------+-------+-------+
| 1 | ... | 12.34 | | |
+---+-----+-------+-------+-------+
| 2 | ... | | 23.45 | |
+---+-----+-------+-------+-------+
| 3 | ... | | | 11.99 |
+---+-----+-------+-------+-------+
| 4 | ... | 9.50 | | |
+---+-----+-------+-------+-------+

On 14/12/2010 17:20, Ronald Raygun wrote:
Plac wrote:

Hi all

I am an Excel "expert"

Wow, just what I need. Perhaps you (or anyone else for that matter)
can advise me on how I might achieve the following. Preferably a method
which works not only for Excel but for other spreadsheets (e.g. OpenOffice).

Having created (by hand) a sheet with four (or so) columns, whe
column 1 might contain a transaction sequence number,
column 2 an analysis category code,
column 3 the amount,
column 4 (and possibly a few other) various other details such as
date and miscellaneous reference information and general remarks.

What I'd like it to do is to re-organise the sheet into two (or so) plus
N columns, where N is the number of analysis category codes which are in
use.

I'd like it to create a column-headings row (or I could supply this myself
and get it to recognise them), and then for each row in the original,
I want it to take the code in column 2 and use it as an index into the
column headings, and to put the amount from column 3 into the column
thus identified. Columns 1 and 4 of the riginal become columns 1 and 2
of the result.

Not clear? Example:

Input:

+---+----+-------+-----+
| 1 | A1 | 12.34 | ... |
+---+----+-------+-----+
| 2 | A3 | 23.45 | ... |
+---+----+-------+-----+
| 3 | B2 | 11.99 | ... |
+---+----+-------+-----+
| 4 | A3 | 9.50 | ... |
+---+----+-------+-----+

Output:

+---+-----+-------+-------+-------+
| | | A1 | A3 | B2 |
+---+-----+-------+-------+-------+
| 1 | ... | 12.34 | | |
+---+-----+-------+-------+-------+
| 2 | ... | | 23.45 | |
+---+-----+-------+-------+-------+
| 3 | ... | | | 11.99 |
+---+-----+-------+-------+-------+
| 4 | ... | 9.50 | | |
+---+-----+-------+-------+-------+


Have a look at Pivot Tables in Excel. I'm not 100% sure, but they might
just do what you want.
--
Cheers,
Roger
____________
Please reply to Newsgroup. Whilst email address is valid, it is seldom
checked.

On Dec 14, 11:45*am, "Robin" wrote:

snip

The other meal is Subsistence which is allowable for VAT and Tax. It
should be posted Net of VAT to the Travel Account as an Allowable
Expense (Sage code 7400) and the VAT recovered.

You appear to be addressing the treatment for the employer. *But what
about the employee? *Are you assuming there is a dispensation in place
for the employee's subsistence? If so, say so. *If not the amount ought
to be returned by employer and employee and the employee claim a
matching deduction; and you need to address NICs (see below).

Well excuse me, but why do you have to have any sort of dispensation
in place for Subsistence?

It is a right for you as a Sch D trader, or for your employer if you
are Sch E, to recover subsistence as an allowable Travel expense -
like a hotel bill if you are on a business trip.

I may not be an expert at tax but at least I know that.

On Dec 14, 5:20*pm, Ronald Raygun
wrote:
Plac wrote:
Hi all

I am an Excel "expert"

Wow, just what I need. *Perhaps you (or anyone else for that matter)
can advise me on how I might achieve the following. *Preferably a method
which works not only for Excel but for other spreadsheets (e.g. OpenOffice).

Having created (by hand) a sheet with four (or so) columns, whe
column 1 might contain a transaction sequence number,
column 2 an analysis category code,
column 3 the amount,
column 4 (and possibly a few other) various other details such as
date and miscellaneous reference information and general remarks.

What I'd like it to do is to re-organise the sheet into two (or so) plus
N columns, where N is the number of analysis category codes which are in
use.

I'd like it to create a column-headings row (or I could supply this myself
and get it to recognise them), and then for each row in the original,
I want it to take the code in column 2 and use it as an index into the
column headings, and to put the amount from column 3 into the column
thus identified. *Columns 1 and 4 of the riginal become columns 1 and 2
of the result.

Not clear? *Example:

Input:

+---+----+-------+-----+
| 1 | A1 | 12.34 | ... |
+---+----+-------+-----+
| 2 | A3 | 23.45 | ... |
+---+----+-------+-----+
| 3 | B2 | 11.99 | ... |
+---+----+-------+-----+
| 4 | A3 | *9.50 | ... |
+---+----+-------+-----+

Output:

+---+-----+-------+-------+-------+
| * | * * | *A1 * | *A3 * | *B2 * |
+---+-----+-------+-------+-------+
| 1 | ... | 12.34 | * * * | * * * |
+---+-----+-------+-------+-------+
| 2 | ... | * * * | 23.45 | * * * |
+---+-----+-------+-------+-------+
| 3 | ... | * * * | * * * | 11.99 |
+---+-----+-------+-------+-------+
| 4 | ... | *9.50 | * * * | * * * |
+---+-----+-------+-------+-------+

You are reconfiguring arrays here - each of the above is the same
array (or matrix) looked at from a different perspective or dimension.

Arrays are what Excel *is*, and "pivot tables" (as the other poster
has said) are the ultimate array-handling mechanism.

Much more powerful however to use a macro to *process* your array, and
in Excel that would be easy...but I get the feeling you are not in
Excel.

Plac wrote:
On Dec 14, 11:45 am, "Robin" wrote:

snip

The other meal is Subsistence which is allowable for VAT and Tax. It
should be posted Net of VAT to the Travel Account as an Allowable
Expense (Sage code 7400) and the VAT recovered.

You appear to be addressing the treatment for the employer. But what
about the employee? Are you assuming there is a dispensation in place
for the employee's subsistence? If so, say so. If not the amount
ought to be returned by employer and employee and the employee claim
a matching deduction; and you need to address NICs (see below).

Well excuse me, but why do you have to have any sort of dispensation
in place for Subsistence?

Yes in order to avoid the need to report them.

It is a right for you as a Sch D trader, or for your employer if you
are Sch E, to recover subsistence as an allowable Travel expense -
like a hotel bill if you are on a business trip.


I do not understand your use of the term "recover". Do you mean
"deduct" (when computing trading profits)?

I may not be an expert at tax but at least I know that.

I shall of course defer to your greater knowledge. Do please forgive me
for thinking that you might not have read ITEPA, the Employment Income
Manual, Booklet 480, CWG5, etc etc. I shall try not burden you with
further comments.






--
Robin
PM may be sent to rbw0{at}hotmail{dot}com

On Dec 14, 9:44*pm, "Robin" wrote:
Plac wrote:
On Dec 14, 11:45 am, "Robin" wrote:

snip

The other meal is Subsistence which is allowable for VAT and Tax. It
should be posted Net of VAT to the Travel Account as an Allowable
Expense (Sage code 7400) and the VAT recovered.

You appear to be addressing the treatment for the employer. But what
about the employee? Are you assuming there is a dispensation in place
for the employee's subsistence? If so, say so. If not the amount
ought to be returned by employer and employee and the employee claim
a matching deduction; and you need to address NICs (see below).

Well excuse me, but why do you have to have any sort of dispensation
in place for Subsistence?

Yes in order to avoid the need to report them.

It is a right for you as a Sch D trader, or for your employer if you
are Sch E, to recover subsistence as an allowable Travel expense -
like a hotel bill if you are on a business trip.

I do not understand your use of the term "recover". *Do you mean
"deduct" (when computing trading profits)?

I may not be an expert at tax but at least I know that.

I shall of course defer to your greater knowledge. *Do please forgive me
for thinking that you might not *have read ITEPA, the Employment Income
Manual, Booklet 480, CWG5, etc etc. *I shall try not burden you with
further comments.

Well you still miss the point. Why pay for a piece of software which
covers billions of complicated possibilities of what might or might
not happen, and take the time to learn it, when you can simply
ascertain the facts in your own particular case, and write a schedule
that deals with it.

I do appreciate your comments and I'm going to go through them over
the weekend, when I have more time.

Thanks.

On Dec 14, 5:20*pm, Ronald Raygun
wrote:
Plac wrote:
Hi all

I am an Excel "expert"

Wow, just what I need. *Perhaps you (or anyone else for that matter)
can advise me on how I might achieve the following. *Preferably a method
which works not only for Excel but for other spreadsheets (e.g. OpenOffice).

Having created (by hand) a sheet with four (or so) columns, whe
column 1 might contain a transaction sequence number,
column 2 an analysis category code,
column 3 the amount,
column 4 (and possibly a few other) various other details such as
date and miscellaneous reference information and general remarks.

What I'd like it to do is to re-organise the sheet into two (or so) plus
N columns, where N is the number of analysis category codes which are in
use.

I'd like it to create a column-headings row (or I could supply this myself
and get it to recognise them), and then for each row in the original,
I want it to take the code in column 2 and use it as an index into the
column headings, and to put the amount from column 3 into the column
thus identified. *Columns 1 and 4 of the riginal become columns 1 and 2
of the result.

Not clear? *Example:

Input:

+---+----+-------+-----+
| 1 | A1 | 12.34 | ... |
+---+----+-------+-----+
| 2 | A3 | 23.45 | ... |
+---+----+-------+-----+
| 3 | B2 | 11.99 | ... |
+---+----+-------+-----+
| 4 | A3 | *9.50 | ... |
+---+----+-------+-----+

Output:

+---+-----+-------+-------+-------+
| * | * * | *A1 * | *A3 * | *B2 * |
+---+-----+-------+-------+-------+
| 1 | ... | 12.34 | * * * | * * * |
+---+-----+-------+-------+-------+
| 2 | ... | * * * | 23.45 | * * * |
+---+-----+-------+-------+-------+
| 3 | ... | * * * | * * * | 11.99 |
+---+-----+-------+-------+-------+
| 4 | ... | *9.50 | * * * | * * * |
+---+-----+-------+-------+-------+

Two other methods;

Copy then PasteSpecial/Transpose might gove you what you want.

But the easiest and best method is to take a button from the Control
Toolbox, double click it to go to its code, then inside the Private
Sub...End Sub text write:

Set R = Range("A12").CurrentRegion.Offset(1, 0)
Set R = R.Resize(R.Rows.Count-1, R.Columns.Count)

....which defines your existing database on or around Cell A12...

Set ptr = Range("H12")

For i = 0 to R.Rows.Count - 1
For j = 0 to R.Columns.Count - 1
S = R.Cells(1).Offset(1, j)
'###
'here you can process S
'###
ptr.Offset(j, i).Value=S
Next
Next

....and you have written your own Pivot Table!

On Tue, 14 Dec 2010 12:31:37 -0800 (PST), Plac wrote:

On Dec 14, 11:45*am, "Robin" wrote:

snip

The other meal is Subsistence which is allowable for VAT and Tax. It
should be posted Net of VAT to the Travel Account as an Allowable
Expense (Sage code 7400) and the VAT recovered.

You appear to be addressing the treatment for the employer. *But what
about the employee? *Are you assuming there is a dispensation in place
for the employee's subsistence? If so, say so. *If not the amount ought
to be returned by employer and employee and the employee claim a
matching deduction; and you need to address NICs (see below).

Well excuse me, but why do you have to have any sort of dispensation
in place for Subsistence?

It is a right for you as a Sch D trader, or for your employer if you
are Sch E, to recover subsistence as an allowable Travel expense -
like a hotel bill if you are on a business trip.

I may not be an expert at tax but at least I know that.


Oh dear, oh dear. Comes in to the finance group for advice and then
tries to get up the nose of people trying to help him.

I bet you have succeeded