[Thinkingbig828110]

I have several gas stations that are all giving me the same issues. Each month, to account for inventory deliveries and subsequent payments, I have 3 types of invoices all provided by a third party company. The ‘Sales Invoice’ gives the gallons and price, which is straight forward. The AR Invoices are for credit card sales of gas, show a gross amount, how much if fees were remitted to the credit card companies, and then a net amount. All told there end up being 100-150 AR invoices I am supposed to use to a) determine the amount of CC fees to expense and b) use to reconcile the EFT drafts. To give you a visual, the EFT Draft statement will show a gas delivery purchase total, then a list of daily AR’s kept to pay for the gas. Since the third party company uses the CC ARs, plus a bank charge of any deficiency, to pay towards gas, I am supposed to reconcile the EFT’s using the bank statement and the AR invoices, which I am currently manually keying into Excel. While this is easy for me to do, it’s taking me too long to do all the leg work of keying in all the data in order to match the +100 AR invoices to about 10 EFT drafts each month.

Does anyone deal with a similar situation and have any advice on a way to do it to make it quicker? It’s currently taking about 2 hours to key in all the AR’s and match them against the EFT drafts and my boss is insitent that it shouldn’t take me this long, I’ve asked for advice on how to get it done faster, and he said I needed to figure it out. I’m at a loss…it’s a simple task, but tedious and time consuming to manually type in 100-200 entries.

[mla1169]

You should see a pattern first. Chances are the bank draws the EFT on a predictable schedule whether it's weekly, every 2 days, etc.

For example: the bank statement shows an eft on 3/5 that equals the a/r draft charges for 3/1-3/5. Then again on 3/10 that happens to equal the AR charges for 3/6-3/10. Once you find the pattern and I just about guarantee there is one, you should be able to quickly add up on a calculator those 5 days of drafts and see the equivalent of 1 EFT charge.

If you get to one that doesn't add up correctly there's your reconciling item, most likely something at the very beginning or very end of the month.

But being able to pinpoint the relationship exactly of your AR invoices to the bank statement is a huge time saver and probably what your boss is looking for you to become intuitive with.

FAR- 77
AUD -49, 71, 84
REG -56,75!
BEC -75

Massachusetts CPA (non reporting) since 3/12.

[mla1169]

You should see a pattern first. Chances are the bank draws the EFT on a predictable schedule whether it's weekly, every 2 days, etc.

For example: the bank statement shows an eft on 3/5 that equals the a/r draft charges for 3/1-3/5. Then again on 3/10 that happens to equal the AR charges for 3/6-3/10. Once you find the pattern and I just about guarantee there is one, you should be able to quickly add up on a calculator those 5 days of drafts and see the equivalent of 1 EFT charge.

If you get to one that doesn't add up correctly there's your reconciling item, most likely something at the very beginning or very end of the month.

But being able to pinpoint the relationship exactly of your AR invoices to the bank statement is a huge time saver and probably what your boss is looking for you to become intuitive with.

FAR- 77
AUD -49, 71, 84
REG -56,75!
BEC -75

Massachusetts CPA (non reporting) since 3/12.

[Thinkingbig828110]

MLA, Thanks for the reply. I am already doing it that way, and there has never been a discrepancy and there is never any left that gets paid back to the company. The part that I am having trouble with, and getting pressure from my boss on, is manually entering all of the AR drafts (From a PDF file, with ONE AR per page and 4 ARs/Pages per day, totaling around 150) to Excel. The EFT draft Statements that show how the Gas was paid for will also show all the individual credit card ARs that were used to pay for the gas delivery and the charge that shows up on the bank statement. The bank isn't the one that does the payment, a billing company charges the bank account.

Is what I am doing necessary in order to verify/reconcile the relationship between the EFT's and ARs or is there a faster more efficient way to get this done? That's the part my boss is coming down on me about, the amount of time it takes me to manually enter all of the info from the PDF file to the spreadsheet, and I don't know another acceptable alternative.

[Thinkingbig828110]

MLA, Thanks for the reply. I am already doing it that way, and there has never been a discrepancy and there is never any left that gets paid back to the company. The part that I am having trouble with, and getting pressure from my boss on, is manually entering all of the AR drafts (From a PDF file, with ONE AR per page and 4 ARs/Pages per day, totaling around 150) to Excel. The EFT draft Statements that show how the Gas was paid for will also show all the individual credit card ARs that were used to pay for the gas delivery and the charge that shows up on the bank statement. The bank isn't the one that does the payment, a billing company charges the bank account.

Is what I am doing necessary in order to verify/reconcile the relationship between the EFT's and ARs or is there a faster more efficient way to get this done? That's the part my boss is coming down on me about, the amount of time it takes me to manually enter all of the info from the PDF file to the spreadsheet, and I don't know another acceptable alternative.

[Mayo]

With what you have there isn't much else you could do. The only options I can think of are:

1. If the PDF is not an image, a data capture program, like Monarch, could possibly extract the amounts you need automatically and export to Excel. However, this assumes you have the program and training to pull this off.

2. Call the third party company that sends the AR data to you. They should be able to provide the information you need in a more usable format.

Your boss just sounds like an A-hole to me. Not to be demeaning, but realistically interns know absolutely nothing. You should give them tasks they can tackle; not stuff they can't figure out. If he/she was worth their salt, they'd make the call or give you permission to call the third party company to get a better report.

Mayo, BBA, Macc

[Mayo]

With what you have there isn't much else you could do. The only options I can think of are:

1. If the PDF is not an image, a data capture program, like Monarch, could possibly extract the amounts you need automatically and export to Excel. However, this assumes you have the program and training to pull this off.

2. Call the third party company that sends the AR data to you. They should be able to provide the information you need in a more usable format.

Your boss just sounds like an A-hole to me. Not to be demeaning, but realistically interns know absolutely nothing. You should give them tasks they can tackle; not stuff they can't figure out. If he/she was worth their salt, they'd make the call or give you permission to call the third party company to get a better report.

Mayo, BBA, Macc

[Anonymous]

I believe that your process for reconciling the drafts with the invoices is correct based on the details you provided. There may be an easier way of doing it but I would not be able to tell you that without actually seeing a report and your spreadsheet. That being said, I think that your main problem appears to be getting the data from the PDF into your spreadsheet so you can do your reconciliation.

I agree with Mayo that using a data capture program would be optimal but most companies will not get that type of program for one person unless they feel that it would be cost effective. Reducing the time it takes you from 2 hours to 1 or even half an hour will probably not be enough of a reason for them. Most third party billing companies will at least provide you with a csv file that can easily be converted to an Excel spreadsheet. If they refuse, then I am assuming that since you receive them in PDF format that you have Adobe and the file you are receiving is not an image. Hopefully you have Adobe Pro which allows you to edit a PDF. If so, you can save the PDF as a spreadsheet then clean it up so you can get rid of all of the information you don't need. If you have a different version of Adobe such as just Reader then I believe you can select the data then copy and paste it into a spreadsheet. The clean up can take a bit longer with this route. If you aren't afraid to use macros then you may be able to create a macro to pull the information you need. If you were able to create a macro that would pull the information for you into your reconciliation then it could reduce the time it takes you to do the data entry portion to just minutes and the most time consuming part would be be spent checking that everything balances. Your boss would think you were a genius.

[Anonymous]

I believe that your process for reconciling the drafts with the invoices is correct based on the details you provided. There may be an easier way of doing it but I would not be able to tell you that without actually seeing a report and your spreadsheet. That being said, I think that your main problem appears to be getting the data from the PDF into your spreadsheet so you can do your reconciliation.

I agree with Mayo that using a data capture program would be optimal but most companies will not get that type of program for one person unless they feel that it would be cost effective. Reducing the time it takes you from 2 hours to 1 or even half an hour will probably not be enough of a reason for them. Most third party billing companies will at least provide you with a csv file that can easily be converted to an Excel spreadsheet. If they refuse, then I am assuming that since you receive them in PDF format that you have Adobe and the file you are receiving is not an image. Hopefully you have Adobe Pro which allows you to edit a PDF. If so, you can save the PDF as a spreadsheet then clean it up so you can get rid of all of the information you don't need. If you have a different version of Adobe such as just Reader then I believe you can select the data then copy and paste it into a spreadsheet. The clean up can take a bit longer with this route. If you aren't afraid to use macros then you may be able to create a macro to pull the information you need. If you were able to create a macro that would pull the information for you into your reconciliation then it could reduce the time it takes you to do the data entry portion to just minutes and the most time consuming part would be be spent checking that everything balances. Your boss would think you were a genius.

[teeteenounouche]

If you can't save the PDF as an Excel file as Colorado suggested, are you able to highlight the info in the PDF file, ctrl c and ctrl v into Excel? If so, you can then clean up the Excel file and use the info you need.

Florida:
AUD: 73, 81! Thank you Lord!
BEC: 73, 77! Thank you Lord! and WTB
REG: 71, 82! Thank you Lord! and A71
FAR: 72, 78! Thank you God and my Mommy in Heaven!

CPA Excel, Ninja Notes & Audio, Wiley Test Bank, CPAreviewforfree

[teeteenounouche]

If you can't save the PDF as an Excel file as Colorado suggested, are you able to highlight the info in the PDF file, ctrl c and ctrl v into Excel? If so, you can then clean up the Excel file and use the info you need.

Florida:
AUD: 73, 81! Thank you Lord!
BEC: 73, 77! Thank you Lord! and WTB
REG: 71, 82! Thank you Lord! and A71
FAR: 72, 78! Thank you God and my Mommy in Heaven!

CPA Excel, Ninja Notes & Audio, Wiley Test Bank, CPAreviewforfree

[How many letters do you need]

I can't help much on the process but agree with Mayo. Your boss is a gigantic Asshat. He should be able to provide you with advice and if even after explaining this terrible process he doesn't get why it's taking you so long he clearly doesn't understand a damn thing. I don't know who puts these people who clearly have no leadership ability in positions of power…

MBA,CMA,CPA, CFF?, ABV?

[How many letters do you need]

I can't help much on the process but agree with Mayo. Your boss is a gigantic Asshat. He should be able to provide you with advice and if even after explaining this terrible process he doesn't get why it's taking you so long he clearly doesn't understand a damn thing. I don't know who puts these people who clearly have no leadership ability in positions of power…

MBA,CMA,CPA, CFF?, ABV?

[Amay]

Agree with the above. Your solution is figuring out a way to convert that PDF to excel. Try a simple copy and paste first, if not look into some type of file conversion program. You may be able to find free downloads online? Just be careful that it's a reliable download, has tons of reviews, etc.

BEC: 73, 81
AUD: 85
FAR: 71, 77
REG: 74, 75...finally DONE! 😀

*This is my 2nd attempt at the CPA exam. For all of you who have failed this exam many times, given up on it, or taken a break like me, remember that it is still possible to finish what you started...failure is the opportunity to begin again more intelligently 🙂

[Amay]

Agree with the above. Your solution is figuring out a way to convert that PDF to excel. Try a simple copy and paste first, if not look into some type of file conversion program. You may be able to find free downloads online? Just be careful that it's a reliable download, has tons of reviews, etc.

BEC: 73, 81
AUD: 85
FAR: 71, 77
REG: 74, 75...finally DONE! 😀

*This is my 2nd attempt at the CPA exam. For all of you who have failed this exam many times, given up on it, or taken a break like me, remember that it is still possible to finish what you started...failure is the opportunity to begin again more intelligently 🙂

[MrsBunny]

Another possible way is to save the pdf file as a text file (text plain) and copy the text to excel.

I still think a better solution is to contact the third party vendor to see whether they can send you the file in csv format

BEC - 80 (10/27/2010)
REG - 82 (11/30/2010)
FAR - 75 (04/01/2011)
AUD - 82 (05/27/2011)
CA Ethic - 90 (12/15/2013) * I am such a procrastinator
CPA Licensed

[Author]

Replies