Pivot Tables and Stock Control (2) ' The answer
Reader Ray Yaukip needed a pivot table that gave the total of stock movements or of stock value between two dates. In Part 1 David Carter took a look at his data and outlined the problem. This week David looks at suggestions from readers, and also offers his own solution.
Reader solutions
We've had five proposed solutions to this problem and all of them would work, I think. Serban Roman and Steve Shillitoe both had the idea of adding an additional column to the source worksheet.
Continued...
The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.
Registration is FREE and allows you to view all content, ask questions, comment and much more.
Or if you are already registered, login here
where does the data come from?
Certainly, it wouldn't make a great deal of sense to be typing all this data manually into Excel. As you say, a Stock Control package would handle it better, and be professionally written.
But maybe Ray's source data comes in the form of an exported file from somebody else's system?
We don't know the precise details. But it was a good opportunity to have some fun.
Am I missing the point ?
Forgive me for not understanding the challenge here, but why are you not using a stock control system to do all this for you ?
I totally agree that the underlying data needs to be well recorded, but if you are using excel or access to give you these types of answers then you must have a fair amount of stock movements. If you are using any computerised system to record these movements (receipts, despatches, orders, etc) and it doesn't do this for you then I'd move system to one that does. This is very basic stock movement reporting and any decent system should do this without question.
The general challenge with going to excel or access is you are now entering the realms of 'programming' and understanding the errors and choices that come with building software (even if it is using tools in a programme like excel). These two articles demonstrate perfectly what you need to comnsider. The articles are useful for me, but not necessarily to record stock movements
I know this is probably an un-heplful rant, but I'd get a system that doess this type of reporting for you and stop building external applications that need constant work and a fair amount of understanding of building excel or access models
Kind Regards
Daniel Clark
Ryba Macaulay Ltd
info@ryba-macaulay.co.uk
No right or wrong way ....
of doing this - they are all equally valid.
Presumably the role of the professional in these circumstances is to advise on the best solution - which (sic) should be a 'proper' stock control system
It may be fun to spend hours on an 'academic' exercise in Excel but is Excel the right tool for the job - and what is the client cost associated with the learning curve along the way.
As a client would you be happy going to a professional only to find out that you unknowingly underwrote their learning curve? Probably not!
Surely the answer has to be the simplest, quickest and most cost effective solution that does the job? Only by adopting this approach are the client's best interests served.