I'm implementing line 50 for a club with multiple income streams and so a standard P&L report doesn't work. Proposed solution was to produce a TB, copy into excel and map all the accounts to the correct slot on the management accounts. However, if I produce a TB Sage only lists accounts with balances, and so each month the mapping would need to be edited. The NL balance listing lists all accounts, but the current balance.
Sage wont write a specific report for me, has anyone else done it?
Sarah Bolsover
9th Jul 2006
Sage line 50 TB's for club with multiple income streams
Sage line 50 TB's for club with multiple income...
Share this content
Replies (5)
Please login or register to join the discussion.
Use vlookup to do your mapping
Neil is right, I think. If you use the vlookup function in Excel you can automatically map the new balances each month to a list of nominal accounts in Excel. To familiarise yourself try Excel vlookup tutorial.
One small point: it might be better to use the YTD balance each month rather than the period totals. If any transactions get posted to prior periods - easily done in Sage - you will find that the sum of your period balances starts to disagree with the YTD total.
So it may be prudent to calculate this month's figures as YTD this month less YTD last month, if you see what I mean.
Alternatively, maybe use the Department field for each income source? But I'm not sure how good Sage is at producing P&L's by department.
ODBC!
You could use an ODBC link to Excel to get the nominal ledger balances in to your spreadsheet. That way you could refresh them anytime you want if you change something. Although ODBC looks scary for something like this it should be fairly easy to set up
And another variation
Hold a full list of accountcodes in your spreadsheet. Import the TB report. Use a lookup to insert the value from your report into the full list. You need to test for a value to prevent N/A errors, though.
Excel tip
You can also get around the problem with improved mapping in Excel. From what you say it sounds like you're using "=[cell ref]" to pick up values.
Instead, use SUMIF in your management accounts to pick up the account codes you need on each line. The best thing is to add a hidden column in your management accounts where you specify the account code that should be reported, and just copy the formula down the sheet.
Save TB as a csv
Hi Sarah
Preview the TB to screen for the relevant period.
Then use the Save as button.
From the Save as Type - choose csv format
Type in a filename and browse to where you want to save it to.
Open the file up in Excel and all the accounts are listed including those with a zero balance.
Hope this is OK for you.
Mike Rees
Mercia Group Ltd