Share this content

Financial statements in Word, with formulas

Financial statements in Word, with formulas

Didn't find your answer?

I produce financial statements in Excel, which is great for referring to other cells (e.g. the year end date is simply a cell on a "Standing data" sheet).

However, it's not so great at the layout, especially where I have to insert notes to the accounts, which go over one page.

Word, on the other hand, is great at the layout, but not so good at cross-referencing data.

If anyone uses Word for financial statements, is it possible to have standing data from where Word can pick up the references? Also, in the balance sheet, etc, it would be great if the figure for Fixed assets picks up the summed figure from the Fixed asset note.

Is this a pipe dream?

Thanks
Jon

Replies (21)

Please login or register to join the discussion.

avatar
By occca
08th Aug 2014 09:58

VT

Do yourself a favour and buy VT

It's cheap, it's easy to use, and it will save you a lot of time

Thanks (3)
By Democratus
08th Aug 2014 11:00

just use Excel for the tables

and paste them into word as a picture or bitmap if you want. You an paste a sa table but with Word's awful formatting it gets tricky.

Not ideal but works

You can put formulae in a Word table.

Thanks (1)
avatar
By carnmores
08th Aug 2014 11:04

exactly
Stop frigging around get some software change your life for the better,

Thanks (0)
avatar
By jonathanw
08th Aug 2014 12:14

okayyyyy
Appreciate the suggestion about VT, occca.

I agree about the tables Democratus, Word is frustrating in that regard.

Thank you too for your helpful comment, carnmores. I'm sure software has changed your life for the better, too.

Thanks (0)
avatar
By carnmores
08th Aug 2014 12:31

LOL
Yes it has ! I avoid word like the plague for this sort of thing

Thanks (0)
By bro0010
08th Aug 2014 13:38

Sticking my head up above the parapet on this one...

Jonathan,

I've seen organisations get caught a few times not proof reading properly to make sure that all the changes necessary get reflected in the final Word document that is sent to the printers. All the correct figures were in their Excel accounts workings but, for the reasons you've outlined, the demands of narrative formatting made them revert to Word for the printer's version. As you've recognised, the ideal solution would be a Word document that updated automatically from Excel. 

I thought that, on the face of it, XBRL/XML tagging of the figures in the Excel document with reciprocal tagging in the Word document would do it - but I abandoned that approach (I can't remember the detail of why now. I think the export of tagged data from Excel was fine, it was the import into Word that I ran aground with).

Then I thought of another way. Create a name in Excel for each piece of data you want to transfer and programmatically create and populate a Word document variable for each. Create field codes for each piece of data in Word using the form {docvariable "NameOfExcelCell"}. This worked extremely well in trial, but, have you any idea how many individual figures there are in a set of accounts? I concluded that the set-up and maintenance of this system would be more bother than it was worth.

I ended up thinking that, whilst technically possible to do it for free with your existing Word and Excel, it is impractical to seek automated update of a significant set of Word financial statements from Excel in most circumstances. Trivial sets of accounts may be a different matter, but there, Excel alone may be up to the job.

The trouble with buying other software to do this is that often it is a once a year job done by people who already have good Excel and Word skills. You can't really blame them for thinking there must be an easy solution to what must be a fairly common problem. If you're doing it all the time then the alternative software route becomes much more attractive.

 

Thanks (1)
avatar
By jonathanw
08th Aug 2014 15:25

I think you're right...
...bro0010, I'd had similar ideas as you about doing it, but none worked in practice.

I was hoping someone might have a miracle cure, but looks like Excel (and tiredly check the layout) is the way forward; or indeed, separate software!

Thanks everyone.

Thanks (0)
avatar
By carnmores
09th Aug 2014 13:50

wasting time

good software is not an expense its an investment , ask a profession friend to show you or take a trial

Thanks (0)
John Stokdyk, AccountingWEB head of insight
By John Stokdyk
13th Aug 2014 15:49

Intersesting debate

I always like an MS Office challenge, jonathan, but the thought does occur about what you are doing to produce iXBRL files for so submit the accounts you're preparing alongside CT returns to HMRC?

For one-off accounts for small entities you could go with HMRC's free CT filing tool, but otherwise I think the online filing requirement scuppers any hope of finding a miracle Excel-Word cure. Simon Hurst atttempted a DIY approach to iXBRL tagging a few years ago and even our master of Excel gave up hope of doing so efficiently.

Carnmores and other accountants making that point probably chew through hundreds of accounts like this in a year. Rather than fiddling around with Excel and Word documents, they save a lot of time (= money) by going with commercial solutions.

I think QMS Software also does a very low cost final accounts/CT Excel program, and you may soon be able to output the necessary files from the likes of Xero, FreeAgent or Clear Books.

Thanks (2)
avatar
By duncanedwards
13th Aug 2014 21:04

Can't recall exactly how ...
But you can embed linked spreadsheet ranges in a Word document very easily. It will give you an option to update the linked data each time you open the Word document.

Thanks (0)
Replying to Ruddles:
avatar
By duncanedwards
14th Aug 2014 00:41

I think

duncanedwards wrote:
But you can embed linked spreadsheet ranges in a Word document very easily. It will give you an option to update the linked data each time you open the Word document.

In my ancient version of MSOffice:

You highlight a range in Excel and copy.  Then in Word, Edit> Paste Special> Paste Link>Microsoft Excel Worksheet Object

Thanks (0)
Replying to Ruddles:
By bro0010
14th Aug 2014 10:12

Watch out for too much linking...

duncanedwards wrote:
But you can embed linked spreadsheet ranges in a Word document very easily. It will give you an option to update the linked data each time you open the Word document.

Hi Duncan,

You're right. Unfortunately, my experience with this as an onlooker was that, as it got bigger, the Word document became unstable and crashed. This was maybe five or six years ago so perhaps things have improved by now.

Ian

Onion Reporting Software Ltd

Sage month end reporting in Excel

www.onionrs.co.uk

Thanks (1)
Avatar
By I'msorryIhaven'taclue
13th Aug 2014 21:49

Linking ETB to VT Final Accounts

Another vote for VT.

I too have grappled with Excel's poor WP capabilities over the years - in fact I still use Lotus 95 for about half of the clients because its WP capabilities are better. eg the height of "merged" cells, each containing a "note to the accounts" automatically adjust in Lotus; In Excel I have to set the height manually for each note. 

My spreadsheets are designed with summaries and control accounts first, flowing through to an ETB and FA register, before finally flowing through to tax comps and Final Accounts. All that's Excel (or Lotus) and I later manually enter the numbers to VT for IXBRL purposes. It struck me the other day that I should be linking my Excel ETB to VT's TB, so by-passing my own Excel Final Accounts and linking directly to VT's Excel accounts.

That should eliminate the formatting issues described by the OP, bot to mention saving me the chore of manually inputting the TB to VT. I'm trying that approach right now, with the FRSSE acounts I'm currently working on. 

Thanks (1)
avatar
By WhichTyler
14th Aug 2014 06:32

For information

The OP asked:  is it possible to have standing data from where Word can pick up the reference

 

It is possible in Word 2013 using bookmarks for the standing data and crossreferences to put them into the document. Earlier versions use Fields 

As others have said, there may be better options for financial statements, but this might be useful for standard letters, reports etc 

Thanks (1)
avatar
By jonathanw
14th Aug 2014 11:49

iXBRL files
Thanks everyone for your comments.

John, you make a good point on iXBRL files; however, this is not something I look at, as I'm based in Jersey, so I don't need to submit accounts to HMRC.

It does indeed appear to make more sense to get some software rather than pray for an Excel miracle! I will look into QMS and VT for the moment.

Regards,
Jon

Thanks (0)
avatar
By mjgibson
14th Aug 2014 12:29

If you're looking at software, check out CaseWare. I've been using it for the last two years and am very happy with it. With all these things, it is horses for courses though. Carnmores is correct when he states you should view it as an investment. We did have to invest some considerable time, but are now reaping the benefits. Best of luck!

Thanks (0)
avatar
By listerramjet
22nd Aug 2014 23:31

why bother

Word has vba and xml, but rolling your own templates is hard work, even with specialist software.  Best bet is to go with one of the commercial offerings, like Caseware or Digita.  They have done all the hard work for you, and they keep their templates up to date.

Thanks (0)
avatar
By mruddyiii
24th Aug 2014 00:35

Embedding Spreadsheets?

Have you considered embedding spreadsheets within your Word files?

http://office.microsoft.com/en-us/word-help/link-or-embed-an-excel-works...

Thanks (1)
avatar
By PMY
24th Aug 2014 01:42

Wouldn'[***] be easier to just merge the cells in the row up to the print area of one page and then just wrap the text?

Thanks (0)
avatar
By iain.mcgregor
24th Aug 2014 11:51

Excel & VT

I think you are making a mountain out of a molehill.  I use Excel to produce accounts It is quick and easy an v quick to make changes as the TB is tied into accounts and CT tax comp, changing the tax provision which is tied into the accounts.  Then I use VT for tagging which is a ten minute job. As the accounts are produced on one sheet I can save it as a PDF to email to clients.  Formatting notes is easy - either in a table or use the wrap text icon in VT add on in excel to format a paragraph.  So where is the problem?

Thanks (1)
avatar
By Annesly
04th Dec 2018 04:16

Hi Jon,

I had the same issue where word is the better presentation software and Excel can handle the data. I needed a product that can bridge the gap.

Have a look at "EasyFs Financials", as it can prepare the financial statements on Word and maintains the links to the tables so that it can be updated for changes, this might just be the solution for you.

There website is www.EasyFsFinancials.com

Keep me posted if you find this software useful.
Kind regards

Thanks (1)
Share this content