Visually presenting EXCEL data to a jury

Visually presenting EXCEL data to a jury

Didn't find your answer?

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.

avatar
By carnmores
31st May 2016 11:30

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

Thanks (1)
Replying to carnmores:
David Winch
By David Winch
31st May 2016 11:48

But I want something visual for the jury to understand. I could show them columns of dates & numbers but since I cannot grasp what significance, if any, these columns of figures have I do not expect them to be meaningful to a jury.
I need to transform data into information.
David

Thanks (0)
paddle steamer
By DJKL
31st May 2016 12:03

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.

Thanks (1)
By Mouse007
31st May 2016 12:12

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.

Thanks (1)
Simon Hurst
By Simon Hurst
31st May 2016 12:19

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)

Thanks (1)
Replying to shurst:
David Winch
By David Winch
31st May 2016 12:26

Thanks. I was thinking that a pivot table might be the way forward - but on further reflection I am shying away from my idea of a week-by-week analysis.
Thanks though for the idea.
David

Thanks (0)
David Winch
By David Winch
31st May 2016 12:23

Yes. I am thinking that cash stolen could be banked on or after the date of the theft (but not before). Hence my thinking of cumulative figures.
On reflection I now think my idea of going week-by-week was not helpful. I can go day-by-day & a graph (because it has a linear axis) will accommodate the spasmodic nature of the data.
All suggestions very welcome!
David

Thanks (0)
Replying to davidwinch:
Simon Hurst
By Simon Hurst
31st May 2016 12:53

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!

Thanks (1)
Replying to shurst:
Simon Hurst
By Simon Hurst
31st May 2016 13:06

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.

Thanks (1)
By Tim Vane
31st May 2016 12:39

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?

Thanks (1)
Replying to Tim Vane:
paddle steamer
By DJKL
31st May 2016 12:46

Tim Vane wrote:

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.

Thanks (1)
By mwngiol
31st May 2016 13:04

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?

Thanks (1)
Portia profile image
By Portia Nina Levin
31st May 2016 14:09

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.

Thanks (2)
Replying to Portia Nina Levin:
David Winch
By David Winch
31st May 2016 14:35

I am instructed as an expert witness, so my over-riding duty is to the court to give unbiased evidence with the objective that the case be dealt with justly.
At the end of the day however if the jury are thinking "He was so clever I didn't understand what he was on about" then I have not done the job!
So I am hoping to present financial information in a way that the jury can understand & which helps them arrive at a just decision.
Technically the lawyers are not permitted to give evidence - the evidence has to come from the witnesses (including expert witnesses). But the lawyers refer to the facts in the evidence which the witnesses give in the court.
I want the jury to decide whether or not they are sure that the defendant is guilty. I am not going to be telling them whether I think he is.
David

Thanks (0)
avatar
By paulwakefield1
31st May 2016 14:13

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.

Thanks (1)
Replying to paulwakefield1:
David Winch
By David Winch
31st May 2016 14:30

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

Thanks (0)
Replying to davidwinch:
paddle steamer
By DJKL
31st May 2016 14:35

davidwinch wrote:

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.

Thanks (1)
avatar
By carnmores
31st May 2016 15:15

LOL PORTIA , agree completely .

Thanks (0)
avatar
By carnmores
31st May 2016 16:24

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

Thanks (1)
Replying to carnmores:
David Winch
By David Winch
31st May 2016 17:50

I was very surprised to be asked the question!
It was some years ago but my recollection is that the juror wrote down the question & passed it to the judge. He then invited her to read it out (possibly he didn't understand the question or perhaps his pronunciation of the Greek alphabet was not up to scratch!).
That was a case involving cash thefts from the takings of a small shop. The shop-keeper did not use a till roll so the 'true' takings were not known. However the prosecution case, in part, was that on days on which the defendant worked on the till the cash takings found in the till drawer & counted after his shift were less than would have been expected having regard to the daily takings on days when he was not at work & the pattern of daily takings 12 months earlier.
The defendant lived with his parents & said that they gave him money on an ad hoc basis which, he said, explained his personal expenditures & apparently unexplained income.
Ultimately the jury were unable to agree on a verdict - which resulted in his acquittal.
David

Thanks (0)
avatar
By RetirednRelaxing
01st Jun 2016 10:09

"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?

Thanks (2)
Replying to RetirednRelaxing:
David Winch
By David Winch
01st Jun 2016 11:57

There are various pieces of evidence which, depending upon interpretation, might also suggest that Fred is guilty - but the banking evidence is an important component in the prosecution case.
A simple list of transactions looks (to me at least) like a blizzard of data without any clear picture emerging.
But I think I should do more than report something like, "I've had a look but I dunno really".
David
P.S. We don't use the expression "beyond reasonable doubt" any more as juries might misunderstand it. Instead juries are told not to convict unless they are "sure".

Thanks (0)
By Democratus
01st Jun 2016 12:41

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).

Thanks (1)
Replying to Democratus:
David Winch
By David Winch
01st Jun 2016 14:46

Thank you for that.
I see a problem (or more than one) though.
Suppose £100 is (allegedly) stolen on Monday & £100 is banked on Wednesday - fair enough.
But what if the banking on Wednesday were £20 or £200? Surely that 'different amount' aspect would have to be factored in? But I can't see how I could do that.
David

Thanks (0)
Replying to davidwinch:
By Democratus
01st Jun 2016 15:10

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!).

Thanks (1)
avatar
By dnicholson
01st Jun 2016 13:23

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.

Thanks (1)
Replying to dnicholson:
David Winch
By David Winch
01st Jun 2016 14:53

If a statistical test is needed then I am going to have to cry off because I am not a statistician expert witness - I am a forensic accountant expert witness. I would (quite properly) get my knuckles rapped by the judge if I tried to give in evidence a statistical correlation test result when I would thereby be operating outside my area of expertise.
That was the mistake that Sir Roy Meadow made in the shaken baby death cases (where he said that if the probability of the death of one child was 1 in 1,000, then the probability of two children dying in the same household was 1 in 1,000,000 - but he was an expert in child health, not in probability theory).
Great discussion though!
David

Thanks (0)
avatar
By vstrad
01st Jun 2016 15:55

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.

Thanks (1)
Replying to vstrad:
By Democratus
01st Jun 2016 17:24

vstrad wrote:

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 :)

Thanks (1)
Replying to Democratus:
David Winch
By David Winch
01st Jun 2016 18:21

Yes, if the amounts banked exceed the amounts (allegedly) stolen then that is evidence that there is something else going on. It could be that money is being stolen from elsewhere, or that more money has been stolen than was realised, or that there is an additional source of legitimate money, or some other explanation. But it introduces definite uncertainty (a sort of unknown unknown?).
Whereas if (alleged) thefts exceed bankings the obvious suggestion is that some of the stolen monies were spent rather than banked.
David

Thanks (0)
The triggle is a distant cousin of the squonk (pictured)
By Triggle
01st Jun 2016 18:38

"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.

Thanks (1)
Replying to Triggle:
David Winch
By David Winch
01st Jun 2016 20:26

That's a good point. I have had a look & there are some interesting things to see (although not conclusive one way or the other). I shall refer to it in my report. Thanks.
David

Thanks (0)
avatar
By BrianL
02nd Jun 2016 07:04

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?

Thanks (1)
Replying to BrianL:
David Winch
By David Winch
02nd Jun 2016 07:38

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

Thanks (0)
Replying to davidwinch:
By Democratus
02nd Jun 2016 09:04

davidwinch wrote:

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!)

Thanks (1)