Hard coding data and Accounting (employees, employer, accountant, jobs)
Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
So lesson learned, but I started a new job 3 months ago and they use a very advanced method of Accounting, utilizing crazy SumIF and VLookup formulas. I've been struggling with this, which management is aware, but they mentioned it will take time. So I was working on a Account Reconciliation file and hardcoded the formula so that it tied to the balance. Internet Audit caught it and did an hour long investigation on it. I was totally truthful about it as I had no reason not to. About an hour later, I received a call from my boss's boss who was not too thrilled with what I did, but said to please put formulas back in the spreadsheet. She was definitely upset with me and said that this is basically against the integrity of the company and that it's spilled milk. Basically all I can do is just not too this again in my opinion, but I am definitely worried I screwed myself out of a job. I'll be popping the Xanax tnite
Yeah, accounting is one of those jobs where they really don't want you to screw anything up. I'd recommend having a copy of the accounting software they use and look up tutorials or other methods to learn the advance SumIF and Vlookups (I hate Vlookup!) so that your job would be a bit easier.
So lesson learned, but I started a new job 3 months ago and they use a very advanced method of Accounting, utilizing crazy SumIF and VLookup formulas. I've been struggling with this, which management is aware, but they mentioned it will take time. So I was working on a Account Reconciliation file and hardcoded the formula so that it tied to the balance. Internet Audit caught it and did an hour long investigation on it. I was totally truthful about it as I had no reason not to. About an hour later, I received a call from my boss's boss who was not too thrilled with what I did, but said to please put formulas back in the spreadsheet. She was definitely upset with me and said that this is basically against the integrity of the company and that it's spilled milk. Basically all I can do is just not too this again in my opinion, but I am definitely worried I screwed myself out of a job. I'll be popping the Xanax tnite
Let's separate a few things...
1) What you're talking about isn't "advanced accounting" because SumIfs and VLookUps are Excel functions. Those are just advanced "Excel" files. The underlying principles of accounting are the same for all USGAAP companies. If you're struggling, I'd suggest looking into some online excel classes. SumIfs and Vlookups aren't that complicated once you realize how they work, and they're two of the most useful formulas n excel. No accountant these days should be unfamiliar with them since you see them everywhere.
2) The problem with your hard coded number is that you basically just said Since A should equal B, but it doesn't, I'm just going to type over A so that it equals B. The purpose of these types of formulas is to check the amounts from two sources, and yes, hard coding a number does undermine the integrity checks entirely. I'm not surprised she was unhappy because those types of things can become huge issues(potentially even legal ones if people start thinking it's intentional fraud).
Yes, you are not going to be able to be employed in the accounting field without mastering Excel, including formulas. There are many resources online if your employer doesn't provide any training. Once you understand how they work, increasingly complex formulas won't be that hard. If I have to write an Excel formula that has, for example, lots of nested "if"s, I break it down into pieces, testing each one as I go, so if it doesn't work properly, I can more easily find where the problem is.
If you don't understand something, ASK FOR HELP! Any employer would far rather you ask for help than hard code something to make it balance when it didn't! Everyone needs a second pair of eyes sometimes, even people who are very accomplished at crunching numbers in a spreadsheet. When you've been looking at something for a long time, it becomes harder to see the tiny details for the big picture, and asking someone fresh to it to take a look can often produce that "duh" moment when what you've overlooked becomes very obvious .
Hard-coding stuff is a big no-no in accounting because how would you be able to trace it to the supporting docs? Ask someone to help you learn sumifs and vlookups, I was scared of them as well but recently learned them myself and they made me so much more efficient. I don't think Excel is very user-friendly when it comes to formulas, you need to first understand what the formula is trying to do, what is it used for. I will give you two quick examples with the two formulas you mentioned, this should clarify things a bit.
SUMIF
Let's say you have a list of 4 G/L accounts that you need to provide information for, and there is a giant list of 100 accounts where your 4 accounts are included, but they are all over the place. Sure, you can sort the original list, filter, subtotal, but what you can also do is run the sumif formula which basically states-if you find account number such and such in the list, sum up all the lines that contain this account, so your criteria is the account you are summing for, your range are all the accounts in the giant list, and you are summing up the column that contains numbers in the giant list.
V lookups
You have been asked to provide a report that shows salary and bonus for the past 5 years for a certain department. You have this data, but it's in 5 different files for each year. You can of course, copy and paste, copy and paste till tomorrow, or, you can run a v-lookup which basically says-if you see this name within the other file's range of columns and cells, bring me from that other file numbers from a certain column, the key is that the numbers you want to bring are to the left of your reference column. So, for example, if name is column 1 on the file that you need to submit, and on the other file, the name is column 1, the salary is column 2, the bonus is column 3, and there are 100 employees, your range would be from cell A2 to cell C101 (assume row A is a title row), and you are saying-if you see John Smith anywhere within the area covering A1 to C101, bring me 2nd column, which is salary.
I wouldn't even say those are "advanced excel skills" - more intermediate. If you don't know how to do it, there are plenty of youtube videos to show you how.
Hi OP. So the accounting world is moving towards a very IT heavy industry. Ive actually considered getting some IT certificates because of this. In my first job we had a very poorly installed ERP system that required us to export reports and put them into an Access database in order to "brush them up against" a different report to get what we needed. Thats advanced. Using pivot tables, vlookups, sumifs...I could teach you that in a few hours, any (good) mentor could.
The real issue here is, as mentioned, you violated integrity. The whole point of accounting is to give fair, "clean" numbers to the stakeholders so that they can make good, well informed decisions about the company. If you force a number then you have violated the whole point of accounting.
Please register to post and access all features of our very popular forum. It is free and quick. Over $68,000 in prizes has already been given out to active posters on our forum. Additional giveaways are planned.
Detailed information about all U.S. cities, counties, and zip codes on our site: City-data.com.
Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
.
