Darryl Collins
darryl at whittleconsulting.com.au
Thu Dec 22 21:10:07 CST 2011
Yes, rounding was the oil company problem as well. In short the Aussie HO used to look after all the deliveries to all the piddly little Pacific Islands (right out to Guam etc). A lot of these places would only want less than 500,000 BOE delivered each time the ship showed up. Say Island X wanted 250,000 K of BOE this month. In the Spreadsheet used to track all this stuff the girl would key in 250,000 BOE but the workbook was rounded to display millions of BOE as the minimum display (fair enough I guess as most places do use millions and millions of BOE). Anyway, as expected the 250K would show as Zero in the workbook. Of course whilst the data was in XL the reports would still calculate correctly as the underlying value was indeed 250K, even though it was displayed as zero. But of course they used SAP for their accounting and control. So what happened was the girl would print out her Spreadsheet, send it to another dept who would then (re)key the data into SAP. Suddenly all those little oil deliveries were being entered as Zeros, not their real amounts. BAM! Big problem as millions of BOE started to vanish from the system. Easy enough to fix once I tracked down where the problem was (I ended up showing them how push the data from XL directly into SAP without all the printing, internal mail and rekeying business and also modded the format to show a different format if the zero value was not a true zero. Rounding and formats. Approach with some caution I say. Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson Sent: Friday, 23 December 2011 1:57 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] My Excel project...you won't believe this one I think when i used to prepare financial statements it was always an art to get all the numbers to round correctly. I had a finance manager who was awesome at it. Naturally formulas went both across and down and all the numbers had the same precision as well as formatting. But the guy could make little adjustments here and there and always get it to come out perfectly. I'd spend hours and still never get things to balance. Personally I would prefer if all numbers went on the sheet with all the precision they merit, without concern whether formatted numbers add up to the foematted total. But I guess perception is reality and if the financial statements look like they don't add up people question the processes that underlie them. Thing is, while you're doing year-end stuff you make changes all the time to final numbers ... so you start the adjustment dance all over. I dont mimd formatting but i feel rounding raw numbers and even intermediate results evil and I hate it. Figures never lie but liars must always figure. On Dec 22, 2011 8:36 PM, "Darryl Collins" <darryl at whittleconsulting.com.au> wrote: > Yes... Fully agree. They (the portfolio risk manager) were really > unhappy as the business had already been written, the risk paid for by the client. > Nothing they could do except wait out the term of the agreement. > > of course then it puts them in the situation next year when the client > want to renew and the business manager now has to decline it. > > Cheers > Darryl > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto: > accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms > Sent: Friday, 23 December 2011 12:27 PM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] My Excel project...you won't believe this one > > (some 4's would be a decline, but they would have taken on the risk as > a 3 would be returned). > > A disasterous conclusion IMHO. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com