One for you EXCEL experts.
I have a case in which a defendant, call him Fred, is alleged to have stolen various amounts of cash on various dates from the shop where he worked & to have banked some of the cash in his personal bank account.
I have a list of dates & amounts of cash missing from the till (allegedly stolen by Fred) and dates & amounts of cash banked by Fred in his personal account.
The suggestion is that the bankings support the theft allegation.
The problem is that, to the naked eye, there does not appear (to me at least) to be any pattern to either the thefts or the bankings - nor any obvious linkage between thefts & bankings.
I want to put these lists into some sort of visual evidence which can be understood by a jury in the Crown Court.
The EXCEL spreadsheet comprises dates in column A, alleged amounts stolen from the till in column B, and amounts banked by Fred in his personal account in column C.
There are entries in column A (dates) only where there is an item in either column B (thefts) or column C (bankings), or both.
In total there are just under 100 lines in the spreadsheet covering a period of about 15 months.
My thinking is that perhaps I could calculate for each week in the 15 month period the amount stolen & banked in that week, and then calculate cumulative totals of stolen to date & banked to date, and then present a graph of those cumulative figures over the 15 months to see if they march in step.
I can do that in a rather pedestrian way in EXCEL but no doubt there is a quicker way than faffing about creating extra columns with numerous formulae & then graphing those columns.
Can anybody save me some time?
David
Replies (35)
Please login or register to join the discussion.
keep it as simple as possible , 2 columns perhaps amount missing from till and unexplained bankings in another . i really shouldn't teach my mother to suck eggs presumably it will all be in the bundles. Good luck
I am the last person to answer anything re using excel and graphs, (I am useless) but could you not just add two extra columns (only 2), one being [***] stolen to date and one being [***] banked to date, and then use the dates alongside [***] to plot the two time/data sets in different colours
The formulae will be basic in the extreme (previous [***] plus that date's entry)
This would hopefully (told you, I am useless) display two curves marching in tandem (or maybe not "hopefully" if you are working for the defence)
Possibly solid block graphics with say total stolen as the top section of the solid graph in orange and total banked as lower section of the solid graph in blue would be pretty visual.
An icon alongside the graph with a character with a mask across his eyes , a black/white hooped sweater and a bag over his shoulder marked "swag" (Think Burglar Bill in the children's book) would be visual but I suspect might not go down that well in court.
How about a block diagram? For each (say week) on x axis show two columns - one for cash “stolen” and one cash “banked”. y axis being amount.
Visually you could then see if the amounts rise and fall in tandem or are completely at random.
To get data for this have 2 more columns on spreadsheet to total each week’s figures.
How about a PivotTable grouping the dates by week (group by days, every 7 days). This would give the total figures for each week for each column which you could then turn into an appropriate PivotChart (maybe a line chart given you can't base a scatter chart on a PivotTable directly)
You could create a line chart of the cash banked and then create a copy of your table of data, insert a column to the left that =each date + an offset of a number of days. The day offset value should be entered into a cell to enable it to be changed easily (perhaps using a spin button). You could create a second chart showing the transfers for this offset date, superimpose one chart over the other and then see if, as you change the offset value, the lines start to correlate. Probably won't work, but would be impressive if it did!
To respond to my own suggestion, in fact there is no need to copy the data, just insert the extra column and then create the two charts, one using one data column and the other the other. You would need to fix the axis minimum value to make it work. Alternatively, just using the original data, create the two charts using the same date column but the value columns separately. Set the fill of the top one to no fill so you can superimpose it, and then drag it around to see if it fits.
What about inserting a picture of the defendant wearing a stripy vest and a mask, then changing the heading of the last column to read "swag"? Too prejudicial?
What about inserting a picture of the defendant wearing a stripy vest and a mask, then changing the heading of the last column to read "swag"? Too prejudicial?
I hold copyright over that innovation, see my earlier post on this thread.
Not a suggestion for visual presentation but...if there's no apparent correlation between amounts stolen and amounts banked, maybe look into whether there's a reduction in cash withdrawn to coincide with the theft dates?
What about an animation which shows the dates, with Fred's hand taking money out of the till and/or paying money in at the bank.
You could have a back pocket detail to show all the cash that Fred's nabbed from the till, but hasn't yet paid into the bank.
PS Are you the barrister for the defence or the prosecution, or are you just an expert witness?
I'd have thought the legal people would have to give the court all the facts in any event, and you are just there to give your opinion on the credibility of the suggestion that Fred took the money from the till and then banked it, based on those same facts?
I did not think that opinions needed to look pretty.
How about calculating the correlation between the two sets of data, using the CORREL function, based on various days offset (to allow for the banking period)?
The results could then be shown as a simple column graph with 0 as no correlation (i.e. a goody) and 1 as perfect correlation (i.e. a baddy and bang to rights) with your 2 or 3 offset results somewhere in between.
It would need a little bit of care setting up and would need conversion to absolute numbers (to avoid confusion if there is an element of negative correlation) but would give a simple graphical result.
There is of course a risk of getting drawn into statistical arguments.
I did a case once in which a juror asked if a chi-square test of independence had been undertaken. But I expect jurors not be keen on statistical analysis.
I rather think that if a statistical analysis is necessary that in itself suggests the evidence is not strong enough to convict, as the jury cannot be sure of the defendant's guilt.
David
Very reasonable point, a jury returning with a guilty verdict, subject to a 99% confidence interval, might well give rise to a subsequent visit to their lordships.
David , i wasn't aware that jurors could ask anything except vis the judge , i would be very surprised if he forwarded such a question
"The suggestion is that the bankings support the theft allegation". Unless the amounts are particularly large that suggestion seems a bit tenuous to me. He wins £100 on the horses, or his granny gives him a £100, or he does a cash job for someone, and banks it and it happens to be a similar amount to that allegedly stolen from his employer? I would need a lot more than that to find him guilty beyond reasonable doubt.
If, however, you could show £100 stolen on the 1st, and £100 banked on the 1st or 2nd, AND, show that happening time and time again, then that might be compelling evidence.
Surely that is what you want the spreadsheet to show. Why confuse things by adding running totals, graphs and so on?
Fantastic Question David.
Should the approach adopted not try and show that there is a correlation between the sums banked and the shortfall, but that the correlation is best shown by the time frame between each shortfall and a spurious or suspicious lodgement.
If so I would show the difference in days between each shortfall and the mysterious lodgement - and a pattern should emerge, e.g every time (or a statistically significant number of times) a sum is missing within X days (or between X & Y days) a lodgement is made in the suspects account.
Assuming a pattern does emerge then this would be a better graph to show and perhaps insert a correlation of coefficiency (if my > 30 years ago statistics is recalled correctly - so possibly not this but something).
Quite understand that David and that's the crux of the problem with a cash fraud like this - no direct traceability, but you can / can you show that there is a link between the dates of the theft and the dates of the lodgements.
Perhaps you are correct though in saying that that's one for a statistician rather than an accountant, albeit one of the UK's foremost forensic accountants!
But the link between cumulative lodgements and cumulative thefts is no more tenuous than the dates in that case is it not?
Love the discussion - way better than working out month end WIP (which is what i should be doing!).
You said:
"I did a case once in which a juror asked if a chi-square test of independence had been undertaken. But I expect jurors not be keen on statistical analysis.
I rather think that if a statistical analysis is necessary that in itself suggests the evidence is not strong enough to convict, as the jury cannot be sure of the defendant's guilt."
Surely you need a statistical test to show the correlation. If I was on a jury and you just showed a list of dates (however presented) and claimed they were connected I wouldn't believe you. I think your task is to clarify the maths behind the correlation claim.
Not sure I can help with your specific question David but, presumably, if the cumulative bankings exceed the cumulative alleged theft then that is a pointer towards innocence.
Not sure I can help with your specific question David but, presumably, if the cumulative bankings exceed the cumulative alleged theft then that is a pointer towards innocence.
or merely that not all funds are stolen :)
"Just one more thing," said Columbo.
It would be interesting to know whether cash being banked ceased just after he was accused or just after he was removed from being in a position to take anything.
Sometimes the negative proves the positive.
And you need some data before and after the period of Fred's alleged thefts. Did he get significantly richer during the period of the 'thefts'? Did his payments into his bank account change in pattern (frequency & amounts) in a way that can't be explained by his legitimate earnings?
Good points. I do have some bank statements covering a limited period both before & after the indictment period.
For legal reasons I am only able to examine the documents & information which is presented to me. (I am not able to request additional documents or information which I might like to have - I am not undertaking my own investigation, I am reporting my findings based on the documents & information provided to me.)
David
That seems like a waste of a good expert witness, it seems rather akin to asking someone on Aweb for advice without giving them all the information first. (and we all know what happens then, thank goodness the Courts are not run by Awebbers!)