Share this content
5

Sage line 50 TB's for club with multiple income streams

Sage line 50 TB's for club with multiple income...

Didn't find your answer?

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

Replies (5)

Please login or register to join the discussion.

avatar
By David Carter
28th May 2004 10:35

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.

Thanks (0)
avatar
By ChrisDL
18th May 2004 13:41

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

Thanks (0)
avatar
By neileg
18th May 2004 14:30

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.

Thanks (0)
JPW
By jpwattam
17th May 2004 09:27

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.

Thanks (0)
avatar
By mikerees
14th May 2004 14:02

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

Thanks (0)
Share this content