I'm 'trying' to piece together a better method of pulling working papers together using Sage and Excel.
Ignoring HP agreements, aged debtors and such as that's a different fish.
I've an audit trail and nominal activity excluding no transactions, fits nicely into Excel, as it should - the trouble is, is that there are too many empty cells, too large merged cells here and there ( I have merged down to the same size, yet this creates its own problems!), and too much tidying to do by doing it all manually to make it efficient.
Having played with a few macros leads me to believe that I can only work on one nominal at a time, is this the best I can do?
There is a Sage add on - Workpapers, but I think this is only available in Australia.....
Could I make this workable by using the report designer, then send to Excel for manipulation?
All this automation is leading me to wonder how relevant working papers are today!!!
Thanks
__________________
Johnny - Owner of an overly-active keyboard.
A man who can read, yet doesn't, is in no way wiser than a man who can't.
been a while... But nowhere near as long as since the last time that I last looked at Sage. However, when I did I do remember having similar issues especially with merged cells and whilst I cannot recall everything that I did, one stand out thing was that I added a column to the left (column A) and put a sequence number in it. You can then sort the data to get all of the similar issues together and fix them in one hit then sort it back.
If you think about that, rather than unmerging cells you will actually be deleting most of the rows that have merged cells instead. Much easier.
The Sage report that I tended to use was the "Nominal Activity Excluding No Transactions".
You are also going to want to insert two columns to insert the N/C and Name on every line effectively losing the useless Sage report formatting and creating a table of the information from the report.
Creating entries on every line is simpler than it sounds. you just insert the additional two lines with formulae in thenm saying that if the column with the N/C: exists on that row then set the field values for the two cells to the nominal code and name. If its not then copy down the values from the field above.
Once populated copy and paste over the top of itself as values and you have populated those two columns and can then start deleting the rows that you don't need by sorting and block deleting.
It sounds more complex than it is and should turn into a job that only takes a few mins once you get the hang of it.
The above said, as stated its been a while since I last did this myself but I understand your issues and just a heads up to know that I've done it myself and it can be made relatively painless.
good luck with it,
all the best,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Between your suggestions and the report designer, I was able to 'ditch' all of the awkwardness of the out of the box - 'nominal excluding no transactions' report by way of editing.
:)
__________________
Johnny - Owner of an overly-active keyboard.
A man who can read, yet doesn't, is in no way wiser than a man who can't.
What exactly is it you're looking to do? I don't want to contravene any self promotion rules here (Shaun take this post down if I'm out of line) but it sounds like our Onion software would give you everything you're looking for, and more (30 day free trial available). There's a full audit trail for every figure in the accounts in Excel. It's why we developed the product in the first place.
There are other products that may be worth investigating too. Try Optegra Financial; Trax UK; BvXL; Pendragon Systems; and, others to see if the benefits on offer would make it worth your while.
Regards,
Edited to add the word "if" in the last sentence.
-- Edited by Onion4Sage on Monday 6th of February 2017 11:22:53 AM
Ultimately what I'm aiming to do is to create a set of working papers for each set of accounts drawn up.
Sort of how people from the past, and I'm sure still do in some cases, use pen and paper to back up their work.
I want to be able to justify my end figures on a TB, after entering the annual invoices, movements and such so it links up, mathematically, with the previous TB.
Sort of like a mini audit - thanks
__________________
Johnny - Owner of an overly-active keyboard.
A man who can read, yet doesn't, is in no way wiser than a man who can't.
worry not, the promotion is incidental to a requested response not the whole reason for a thread so no issue at all.
Also of course as a long term contributor and always helpful to everyone regardless of the subject matter I've got no issue with you promoting your companies wares, very much the same as Tom when he used to own 12pay.
kindest regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Ultimately what I'm aiming to do is to create a set of working papers for each set of accounts drawn up.
Sort of how people from the past, and I'm sure still do in some cases, use pen and paper to back up their work.
I want to be able to justify my end figures on a TB, after entering the annual invoices, movements and such so it links up, mathematically, with the previous TB.
Sort of like a mini audit - thanks
Hi Johnny,
What you get out of the box with Onion is presentation grade accounts, based on the selected Sage COA, for both the current and previous years, expandable from summary level to nominal code level and with sheets detailing the monthly and YTD movements, or balance sheet values, at each month end. Additionally, because Onion captures every individual posting for the two year period, if you want to drill down to see what postings connect TB figures at two different points in time, there are PivotTables that allow you to drill down from any figure to see the underlying postings. There are also Aged Debtor and Aged Creditor analyses detailing the outstanding items making up the balance at any month end.
Whilst the data in the Sage Demo company is not great, we have built an Onion workbook from it that you can download from our web-site to get an idea of what is on offer. We'd still recommend the trial with your own Sage data as preferable - the demo workbook is another option.
You'd probably have a bit of work to do but I think the one grab would get you all the data you would need for your file.
I'm not in practice any more. Onion didn't exist when I was, so the answer to your question is no. However, I reckon if I needed a set of working papers with comparatives, I'd have a good start if I had an Onion workbook to start from. Any of the sheets can be copied and the rows for everything other than the required sub-set of accounts then deleted to create the working paper you require.
We have complementary templates that we can configure for custom reporting. Perhaps we could create a template to produce the required lead schedules and supporting schedules? I'd be happy to engage in a discussion about how much can be sensibly automated and set up as a re-usable working paper that could be re-used from year to year.
