Rounding off budget numbers for Dynamics GP Excel import wizard

blog pictureOver the years certain people in my family have accused me of being lazy. I’m not going to name names but I would imagine it is easy to guess who the accusers might be for those of you who are similar in nature to myself.

My counter argument is I’m just efficient. Why do things that aren’t necessary? Why not do things the easiest way possible?

I’m not sure if what I’m going to show you is necessary or even efficient but I thought it was a fun solution to making sure budget numbers are even on an Excel spreadsheet before importing into GP.

A client came to me with a problem: After importing budget numbers where a total number is divided by twelve to get even numbers throughout all periods this leaves a decimal place on each individual period. When the number is not divisible by twelve GP doesn’t have whole dollar amounts in the total because each period is only allowing for two decimal places.

image

For example, the numbers circled above all have infinitive decimal ranges and when they imported, because the decimals were cut off at two digits, the totals come out not in whole numbers.

Here’s the solution we came up with. We copied over the spreadsheet to a new worksheet and made the budget numbers a fixed two decimal places. This made the totals go off by around four cents higher or lower for the total.

image

Formula copied to each cell: =FIXED('2015-1'!D8,2)

That didn’t get us fully what we needed either so we took this same spreadsheet and threw in another couple of columns as shown below:

image

Column Q has a nice little formula that rounds off the total (column P) to whole numbers and Column R give a new value we replaced Period 12 numbers with.

Column Q formula: =IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) > 0.95, INT(SUM(C10:O10)) + 1, IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) < 0.05, INT(SUM(C10:O10)),SUM(C10:O10)))

Column R formula: =IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) > 0.95, O10 + 0.04, IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) < 0.05, O10 - ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))), O10))

These formulas were in the Q10 and R10 cells.

This took a little Excel work but since we could drag down cell references it was way easier than manipulating the period twelve numbers manually.

Now the real confession: Some of you may be saying to yourself “I thought you said you were efficient (Lazy)?” You’re right, It would have taken me a very long time to figure out these formulas myself (or even at all). What I really did was pass this over to my developer and he figured it out for me.

How’s that for being efficient?

Undoing a bank reconciliation after it’s already been posted for Dynamics GP

blog pictureWhen I was a freshman in a U.S. college I hung out with a group of friends from my little hometown in Canada. We were appalled by the lack of knowledge of our American roommates (and most of our fellow student body) about our beloved homeland. On October 13th that year we decided to celebrate Canadian Halloween. We dressed up in costumes and went around trick or treating (mostly around girls housing complexes.)

There is no special Canadian Halloween (unlike Canadian Thanksgiving, Independence Day, Remembrance day, Family Day etc.) but my recollection is that most of the people we trick or treated to were quite open to our Canadian culture when we announced it was Canadian Halloween. If they didn’t have candy we said we’d take canned soup or any type of food. I don’t think we had to go shopping that week and we met a ton of girls. Although I’m sure most of the girls we met decided that they would never date a Canadian after meeting our motley crew.

What does this have to do with opening back up a bank reconciliation after it has already been posted? I’m going to discuss a method below that some of our consultants use to open back up a bank reconciliation. You can believe me without question or you can test this out first on a test company.

******************************

SQL queries:

1) Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):

select * from CM20500 where CHEKBKID = ‘XXXXX’

order by RECONUM

2) Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):

update CM20200 set Recond = 0 where RECONUM = ‘##.#####’

update CM20200 set ClrdAmt = 0 where RECONUM = ‘##.#####’

update CM20200 set clearedate = 0000-00-00 where RECONUM = ‘##.#####’ update CM20200 set RECONUM = 0 where RECONUM = ‘##.#####’

3) Remove the Recon from history (replace the ##.##### with the exact RECONUM determined in #1 above):

delete CM20500 where RECONUM = ‘##.#####’

4) Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount (Replace MM with the desired Month and DD with desired day. Repace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):

update CM00100 set Last_Reconciled_Date = ‘2010-MM-DD 00:00:00.000′ where CHEKBKID = ‘XXXXX’

update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID = ‘XXXXX’

**********************************

Although I’ve told anyone that asks for the past several years that you can’t unpost a bank reconciliation that’s not entirely true. The above method works great. It just takes a little SQL knowledge and you can be re-reconciling in no time.

But don’t just take my word for it. I once swindled a whole apartment complex out of all their precious chocolates and candy.

Happy Canadian Halloween.