Glen Feechan
Member Since: 17th Jul 2009
Columnist
Likes: 0
Thanks: 4
Chartered Accountant with extensive experience in management reporting, Microsoft Excel and business process improvement.
Also runs a spreadsheet development business at http://www.needaspreadsheet.com.
Provides consulting on the above specialisms as well as providing cost savings to clients at no cost.
Editor and chief contributor to Not Just Numbers Ezine - The ezine for those who know it's not just about the numbers.
Specialties:
Cost Saving for SMEs, Overhead reduction, Excel, Management Reporting, Process Improvement, PISO.
My answers
What about improving your spreadsheets?
You say want something better than your current spreadsheets - could that simply be better spreadsheets?
Excel is a good tool for this if used well. If you want to send me an example of your current spreadsheets and what you are not happy with, I'd be happy to suggest improvements and/or quote for making the changes.
If you're interested, my email address is [email protected].
Blog post and template posted
Dear all
As per my earlier comments, I have posted a blog post on the simple method described above, as well as a simple template for you to download.
You can see both here:
http://www.notjustnumbers.co.uk/2013/05/excel-tip-managing-deadlines-in-excel.html
I hope it's useful.
I might post a simple template on my blog
Opporchancity - I'm goint to do a blog post in the next few weeks, see my comment above. I might also add a simple template to that.
Will do
Frankfx - I'll do that in the next few weeks. I might even post a simple template too!
Excel can work if you structure it to do what you're after
I'm not in practice myself, but I have a lot of experience on Excel (and trained in practice many years ago).
I would suggest the following general approach (it might be simplistic, but sometimes the simple systems are the most effective):
One row per client with columns for whatever details you want to store for each client including date columns for next AR due, next CT due, etc. etc.Introduce a calculated column to then shown the earliest date from these (use the MIN function)When you complete an AR, change the date to when the next one is due.Then run a pivot table from the data sorted by the calculated column (in ascending order).Each day, refresh the pivot table and it will show you all upcoming deadlines in order.
If this is not too simple an approach, I could flesh it out in a future blog post at Not Just Numbers.
Let me know whether you think it would address the problem.
Let me know if you want to discuss your requirements
John has recommended some great resources there, which I am sure you will find useful.
You may also find a blog post I wrote a couple of years back of use too:
Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?
I do a lot of bespoke work for clients setting up Excel reports from Sage (and other accounting and business software) as well as bespoke training whereby we can set up your reports together while you learn how to do it in future.
Please just drop me an email on [email protected] if you want a chat to discuss what you are trying to do (no obligation).
Best Regards
Glen
Add additional columns to data rather than to the pivot table
The simplest way to do the kind of selection you are after for the pivot table is to do the calculation bit in columns alongside the data, rather than in the pivot table (these columns could be hidden). For example, you could have a cell where you enter the payment date and a column entitled, say, SelectedDate. This would be an IF statement copied down, returning a "Y" if the payment date for that row equalled the date entered in the cell.
Your pivot table could then use this field as a PAGE field which then gives you a dropdown which you can set to "Y".
If you want a refresher on Pivot Tables, I've got a free Video at:
http://www.pivot-tables.biz/FreeVideo.htm
I hope that helps.
Regards
--- Glen John Feechan BA Hon. ACA
[email protected]
Not Just Numbers Ezine
Free Excel Pivot Table Video
Would Access not be re-inventing the wheel?
neileg
As Clint is nearly there with the data entry, would Access not be a bit of overkill. Adding a pivot table to what he has would solve his reporting challenges and be virtually no extra work.
Regards
-- Glen John Feechan BA Hon. ACA
[email protected]
Not Just Numbers Ezine
Free Excel Pivot Table Video
Use a pivot table
Dear Clint
I've just had a very quick look at what you want to do. The biggest improvement I could suggest is use a pivot table, you could then report on it however you want without being restricted to the format you have.
For example, you could select a year or reference, or look at a particular year by reference.
You don't need to change the input, just pivot rather than filter.
Best Regards
-- Glen John Feechan BA Hon. ACA
[email protected]
Not Just Numbers Ezine
Free Excel Pivot Table Video
Whoops!
Graeme
Apologies, my brain must have started the Bank Holiday weekend without me.
The table names I gave you earlier were from Line 500.
Hopefully John's link to David's article will be more useful.
Good luck.
--
Glen John Feechan BA Hon. ACA
[email protected]
Not Just Numbers Ezine
Free Excel Pivot Table Video