Not wanting to reinvent the wheel
I have a home-grown spreadsheet along the old inland revenue "form 930" lines, which records the taxpayer's total SA liability and breakdown for each year, linked to another worksheet within the workbook that records how those liabilities (along with interest, surcharges, penalties etc) are discharged, which basically mirrors the information contained on HMRC's "view account" section of the taxpayer's SA record online.
Problem is, the record of payments is poorly designed at present, based on the simple arrangement of liabilities listed in columns along the top, and payments listed in rows beneath. This is problematic because of the ever-growing number of columns in use as the tax years grow. There are two particular problems arising from this effect. One is that you cannot conveniently print it out without the size of text getting vanishingly small, and the other being that there is a finite number of columns that Excel will permit. OK there is also a finite number of rows, but I am never going to reach that, while I am very shortly going to reach the limit on columns.
A better design would be just to have a limited and constant number of columns in use, handle everything else through rows, and then autofilter the data so as to zero in on the desired tax year or payment, or whatever.
I have sort of started off a basic template to illustrate the likely feel of it, here:
http://www.keepandshare.com/doc/1744779/sarecord-jpg-february-11-2010-6-39-pm-133k?da=y
A benefit of this arrangement is that if I have another worksheet that contains a table of interest rates I should in theory be able to reproduce the interest calculations charged or credited by HMRC.
I am loath to reinvent the wheel if someone else has already done this, so I thought I would ask here. Also if you can see any obvious flaws in the way I am arranging it by all means chip in.
With kind regards
Clint Westwood
Attempt No 2
OK so this is how it would look while allowing each individual payment or repayment to be entered as a single total
http://www.keepandshare.com/doc/1746097/sarecordver2-jpg-february-12-2010-8-50-am-136k?da=y
To see how a single payment would be allocated, I would filter on Ref using criteria (eg) MyRef4 in the example shown. Alternatively, to see how 1996-97 has been settled I would filter by Tax Year and subfilter if required by Instalment. It is slightly kludgy because the filtered data (on anything other than payment ref) suggests that there are unreconciled amounts in the suspense column, but I can live with that.
Now what I could really do with is being able to download the data from the HMRC online SA record in csv format.
With kind regards
Clint Westwood
Use a pivot table
Dear Clint
I've just had a very quick look at what you want to do. The biggest improvement I could suggest is use a pivot table, you could then report on it however you want without being restricted to the format you have.
For example, you could select a year or reference, or look at a particular year by reference.
You don't need to change the input, just pivot rather than filter.
Best Regards
-- Glen John Feechan BA Hon. ACA
Would Access not be re-inventing the wheel?
neileg
As Clint is nearly there with the data entry, would Access not be a bit of overkill. Adding a pivot table to what he has would solve his reporting challenges and be virtually no extra work.
Regards
-- Glen John Feechan BA Hon. ACA
I am not averse to Access
One thing that I have not really got to grips with in Access is having Access and Excel talk to one another on a "live" basis. The charges in the example data sheet will be populated by other worksheets which are heavy in computational content and which are not themselves suitable for Accessifying.
I will have a look at the pivot table possibility, thanks. I have not used pivot tables enough so am a bit rusty, and am having difficulty visualising how it would work. Bear in mind that today I might want to see how a payment made on 31 July 1997 has been allocated across liabilities, but tomorrow I might want to see the allocation of a totally different payment, or how a particular charge has been settled by a variety of payments. I don't want to end up with a whole load of pivot tables, and the spreadsheet will need to be usable by others reasonably simply. I have not given up on the pivot table idea; it may work if the row and column headings in the pivot table can be derived from formulae or other cell references rather than hard coded, but I have never tried that before so do not know if it would work.
With kind regards
Clint Westwood
Add additional columns to data rather than to the pivot table
The simplest way to do the kind of selection you are after for the pivot table is to do the calculation bit in columns alongside the data, rather than in the pivot table (these columns could be hidden). For example, you could have a cell where you enter the payment date and a column entitled, say, SelectedDate. This would be an IF statement copied down, returning a "Y" if the payment date for that row equalled the date entered in the cell.
Your pivot table could then use this field as a PAGE field which then gives you a dropdown which you can set to "Y".
If you want a refresher on Pivot Tables, I've got a free Video at:
http://www.pivot-tables.biz/FreeVideo.htm
I hope that helps.
Regards
--- Glen John Feechan BA Hon. ACA
Thanks all
I will definitely delve deeper into the pivot table possibility.
As to the previous poster; am I doing unnecessary work? Well ...
As I said in the OP I have a working solution even now, but based on an old design that is creaking at the knees, mainly because I am running out of columns to contain the data due to lack of foresight in the original design phase. I have been using that solution up to now, and up to now I do not regret having used it. I have used it for a sufficient length of time that I think it would by now have been brought home to me if it was a significant mistake. It would probably be too complicated to try to explain why.
Apart from the initial setup for any particular taxpayer, which I grant is a time-consuming and unrecoverable exercise if you go back to the year dot, the ongoing maintenance once it is up to date is pretty trivial.
Sorry but I don't have access to Iris, nor do I have the authority in our firm to buy it, so you have the advantage where it comes to evaluating its potential. If I did have it I doubt that it would come cheap.
The software that we use for preparing tax returns does not have the capability to log payments as per HMRC records and reconcile them to the liabilities. But even ignoring that drawback, I tend to find that having an Excel-based record of liabilities is far more flexible and fast at playing around with what-if scenarios and planning work. We do not typically get the following tax year's rules updated on our compliance software until that year is about to start, and yet we either have the tax rules for a year or two in advance announced or we can at least speculate. It doesn't hurt to have the payments record bolted into the planning spreadsheets, which I have at present.
With kind regards
Clint Westwood
No need to allocate!
It shouldn't really be necessary to allocate the payments manually to the charges. There is presumably some fairly simple rule involved here (such as allocate payments to the oldest charges first - or if this is too simple, there should be a rule which can be specified). Therefore the only data which should need to be entered is the payment amount and date, and the computer should be calculating everything from this.
Separating data input, calculation processes and output onto separate sheets will simplify the input and produce a more flexible solution. The payment data can best be entered as a simple list, separate from the charges. The calculation will be a bit more fiddly, but there are several ways to do it. A separate output sheet is also needed, to display the required results selectively - this again could be done in a number of ways, such as a pivot table, an extract using database functions, or a suitable report generator.
I agree, in principle
that automatic allocation is preferable, as that would enable you to compare the allocation with that which has been assigned by HMRC. Although such a comparison would still require you manually to enter the HMRC allocation. I would not say that auto-allocation is a trivial task, even if the rules are simply expressed.
With kind regards
Clint Westwood
Inland Revenue Comparison
Hopefully, if the rules are correct then the Inland Revenue allocation is likely to be the same. Could one therefore rather than enter the Inland Revenue allocation, simply display the results of the auto allocation and then manually compare with the Inland Revenue. If they are the same, one could set an indicator on the payment to show it has been reconciled, so the exercise doesn't need to be repeated. If they are different, some more serious action is needed - the possibilities seem to be:
- rules are wrong: change them and repeat
- data wrongly entered: correct it and repeat
- Inland Revenue are wrong: challenge them?
I agree that auto-allocation is more than a simple spreadsheet, it involves a merge of charges and payments driven by a running balance of the amount unpaid/unallocated, though I have done similar.
Best Regards
Andrew Burnett
The issue is made more complicated
by the fact that no particular allocation is set in stone. At least not for potentially some years. For example, if I repair a tax return so that the liabilities change for a past year which until then had been fully settled, then so the payments that were originally allocated to the original liabilities get reallocated. And indeed interest charges that were originally calculated then get re-calculated, and payments that were allocated to those original interest charges then get reallocated. The same effect is observed if you make an application to reduce payments on account, which will commonly differ from the finally returned liabilities.
I suspect that the process of allocation will have to be done by a macro that the user invokes from time to time, rather than by some sort of live recalculation each time the workbook recalculates.
Treatment of repayment supplements may require careful thought also. With a bit of head-scratching I may be able to get the spreadsheet to calculate expected repayment supplements, but in order to go the extra mile, those supplements will then have to be treated as further payments on account, suitably dated and then allocated against liabilities (or repayments), in order to mirror the HMRC treatment. That does not sound trivial at present.
With kind regards
Clint Westwood.
Adjustments to past years
True. However I guess simply amending the charges for past years would cause further problems, as even with manual allocation you would have payments which no longer matched the charges and since you would have lost the original figures from the spreadsheet, you would no longer be able to reconcile the spreadsheet to the original paperwork should that be necessary in the future. If the past year was once fully reconciled, it would be a shame to lose these reconciling figures. This suggests that the process of changing past years should preferably create adjustments held separately from the original figures. And once one had done that, it then becomes possible to handle these correctly in matching payments. A similar comment applies in respect of payments on account.
Having said this, and also taking into account your comments about repayment supplements, it is clear that a complete solution would be a (reasonably straightforward) mini-system involving some form of programming, assuming the rules are known and assuming there are no issues with the remainder of the process of which this forms part (e.g. the derivation of the liabilities).
Best Regards
Andrew Burnett
If I might respond to my own post
One of the aspects of the suggested new design that I dislike is that where a single payment is allocated to several different charges I am not going to be able to enter the payment as a single total, but will have to devote several rows which total the payment made. Under the old system I could enter a single payment in a single row.
It is not a critical problem, just an irritation. I would assign a unique "MyRef" to each payment, so that if I wanted to see how each payment has been allocated, I can autofilter MyRef by that payment, and the visible subtotal will then confirm the total single payment made. The reason I need several rows is so that when I autofilter to identify how a particular charge has been settled I will want the visible subtotal to add up to zero if the selected liability has been discharged, and that will only be possible if only that element of the payment is included in the filtered result. That in turn is only possible if those elements of the payment that are allocated against other charges are listed in separate rows that are filtered out.
At the moment I cannot see a resolution to this, and would be interested in suggestions. I am pretty confident that any solition will require that payments and charges be listed in separate sheets, with possibly a third sheet that picks up relevant parts of both of the other two. But I don't see the way forward there. At the moment, using a single sheet, the best that I can come up with is by entering a single payment allocated initially to "suspense" column, and then use several rows of journals to strip that total down into its constituent parts allocated across the other columns.
With kind regards
Clint Westwood