Office Accounting Tips and Tricks:

• Create a Custom List in Excel
,
• Using Business Templates for Setting up a Company
,
• Flat Rate VAT in Office Accounting UK

By John Thuneby

Creating a custom list in Excel

Have you ever found that the lists in Office Accounting are very helpful, but you are missing that one column you need--for example, the second address field on the customer list you want for a mail merge?

There is a way to get the missing data. Office Accounting is based on an open architecture, so you can use Excel to create your own data-aware lists in just a few simple steps.

Open a new Excel document.

Click on the Data tab in the ribbon. In the Get External Data section, click From Other Sources, From SQL Server.

In the Data connection wizard enter (local)\MSSMLBIZ as the Server name and click Next.

Select the database in the list and pick a view for your custom list, in this case the CustomerAccountView. Click Next.

Pick a file name (or leave it as is), add a description and a friendly name. Click Finish.

In the Import Data dialog, select Table, Existing worksheet and click OK.

(Note: Several columns hidden above)

You now have a custom data-aware list in Excel that you can filter, hide and show its columns and refresh the data as needed.

Note: It is recommended you use the views in Step 3 above (rather than the tables), as the views usually already contain information that naturally belongs together - the data you need.

Note: Excel 2003 has the same capability

Using business templates for setting up a company

Are you a consultant or accounting (accountancy) professional with clients using Office Accounting? Have you spent some time playing around with the product and want to keep your setup? The new business templates in Office Accounting can help you do that.

Here are the steps:

1.       Set up a new company (or import it from a competitive product)

2.       Modify the chart of accounts

3.       Modify the support lists (terms, countries etc)

4.       Modify tax/VAT setup and rates

5.       Modify currencies and rates

6.       Click File, Utilities, Export as template and save the file.

Now you have a custom XML business template with all your settings set up.

To use the template, set up a new company. When the setup wizard asks for a business type, click the Add button to the right and select the template you have saved.

Flat Rate VAT in Office Accounting UK

In Office Accounting, there are two options to handle the flat rate VAT scheme used by many UK small businesses. Before taking you through the options, I will start with a brief introduction (according to the HMRC website).

Quick Introduction to Flat Rate VAT

If your VAT taxable turnover is less than £150,000, you could simplify your VAT accounting by calculating your VAT payments as a percentage of your total VAT inclusive turnover. Although you cannot reclaim VAT on purchases - it is taken into account in calculating the flat rate percentage - the Flat Rate Scheme can reduce the time that you need to spend on accounting for and working out your VAT. Even though you still need to show a VAT amount on each sales invoice, you don't need to record how much VAT you charge on every sale in your accounts. Nor do you need to record the VAT you pay on every purchase.

Flat rate VAT can be reported using the regular (accrual) accounting scheme or by using the cash turnover method (similar to the cash accounting scheme for VAT).

Note that HMRC has published new flat rates from December 1^st 2008.

Flat rate in Office Accounting, option 1 (as HMRC intended but with issues)

The way the government sees the world, it is easier if you don’t keep track of VAT on purchases and just record it on sales. Here is how it works:

Setting up VAT

Setup is the same as with regular VAT, just use 15% VAT from December 1^st 2008, and use accrual basis for regular flat rate VAT and cash basis for cash turnover method.

Keeping VAT records

On the sales side, keep on adding 15% (formerly 17.5%) or 5% VAT as applicable.

On the purchase side, you have to treat all purchases as zero rated, rather than using the regular VAT rate, thus adjusting the VAT code (and potentially the amounts) on every purchase.  A regular purchase invoice may look like this (with US date settings):

£100.25 + 15% VAT = £115.29

As a zero rated purchase invoice, it will look as follows:

£115.29 + 0% VAT.

The tricky part here is that you have to remember to overwrite both the VAT code and the unit price on every single line on each purchase invoice in order to get it right. If you forget, you may up overpaying for your purchases or you will have to adjust the amount at the end using a journal entry.

However the worst part is that you end up inflating the unit cost of your purchases, making the profitability of each item seem worse than it actually is.

On the sales side you create regular invoices with 15% (formerly 17.5%) VAT.

So a list of transactions may look like this:

Filing VAT

In order to figure out how much you owe in VAT using the flat rate scheme, I recommend you run the VAT detail report as depicted above and export it to Excel. Let us say that my business type is computer consultancy and the flat rate VAT is 11.5% as of December.

The VAT you owe is thus (£2173.00 + £329.95)*11.5% = £287.38 as seen above.

This means that you need to make an adjustment for the difference between the collected VAT amount and the owed amount (£325.95-£287.38 = £38.57) before you create your VAT return. If you adjust the amounts before creating the return, you and your accountant can keep track of what was actually filed to HMRC in Office Accounting (rather than just on paper).

To make the adjustment, create a journal entry where you reduce the liability (i.e. debit) accordingly:

The resulting VAT 100 report looks like this:

Now how should you fill out the return to HMRC? Again I recommend exporting to Excel.

Normally you will need to adjust box 6 and 7 in your filings (but not in Office Accounting).

When you can see you have the right result, go ahead and create the VAT return in Office Accounting and pay the amount to HMRC.

The Challenges

As a final note, I will show you why I don’t recommend this method (besides the considerable extra work with changing amounts and VAT codes). When you look at the product and service profitability for stock items, the margin is reduced greatly due to the fact that the cost includes VAT:

In the report above the margin on computers is reduced to 10.4% where it in reality was 28.7% or £223. So what happened to the difference of £116.26? Well, part of it was given to the government because you didn’t get a refund for your VAT purchases and part of it ended up in the “interest income” account as an adjustment.

So by following the guidelines from the government you end up in a position where it is harder to tell if you are making money and you don’t know whether or not the flat rate is actually beneficial to you.

 

Flat rate in Office Accounting, Option #2 (a better way)

The way the government sees the world, it is easier if you don’t keep track of VAT on purchases and just record it on sales. Here is how it works:

1.       Keep VAT record as you normally would using the normal scheme by tracking VAT

2.       Make adjustments on a journal entry

3.       Create VAT return

The beauty of this method is that you use Office Accounting the same way you normally would, it doesn’t impact your profitability, and as an added benefit you can see if you have a gain or loss by using the flat rate scheme. How is that possible? Well, it is all in the adjusting journal entry.

Let us look at the transactions without zero rating purchases.

So we have the same sales invoices and the same purchase invoices, except the majority of purchases are not exempt (one could argue that rent shouldn’t even show up here).

 

Making the adjustment

Start by exporting the report to Excel.

The VAT owed is still the same, so you have to adjust for the difference between £329.95 and £287.38, but additionally you need to adjust for the input or purchase VAT. The resulting Journal entry thus looks like this:

You debit the reduction in sales / output VAT as before, credit the full amount of purchase / input VAT and post the balance to an account to keep track of the gain or loss by using the flat rate VAT (I created an Other Income account for this purpose).

The resulting VAT 100 report looks as follows:

Export the VAT 100 to Excel so you can keep track of what you have filed to HMRC. The same two boxes have to be changed.

 

As a final remark I will show the effect on the Profit and Loss report:

The loss on flat rate VAT will be reduced as more of the stock is sold, but now it is simple to keep track of whether the flat rate scheme is in your interest.

Do remember that the first year the government offers a 1% discount in flat rates, so if this was the first year in business, things would have looked slightly better.

 

John Thuneby is a Sr. Program Manager for Microsoft Office Accounting. To see more Office Accounting tips and tricks, visit his blog at http://blogs.msdn.com/thuneby/default.aspx

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.