How to use Excel data tables [3 min vid]

[Brown_Pride]

In Accounting Excel is KING. Like if you don't know this shyt you should. I'm self taught over 15 plus years and every year I learn something new and at every job my abilities in excel have set me aside from the rest. No joke learn excel (and access too). There's really not a whole lot I can't do in excel.

I just got through rebuilding the cash statement for one of the largest utility companies in the world, all in excel. Extra 5% on my bonus this year just for that

LEARN EXCEL (and access too...but for sure LEARN EXCEL)

If you can't:
- use pivot tables
- record a macro (and the be able to tweak that macro or write your own from scratch)
- if/then
- Vlookups & Hlookups
- get your formula game up (there are a ton of functions you can learn the format to ....sumif, countif, sumproduct, subtotals, etc.)
- Know how to nest all those functions
- arrays (this shyt right here )


honestly i'd start by taking a visual basic class it helps out tremendously.

 

[TLR Is Mental Poison]

Big problem for me is I can't learn something unless I am under the gun and it will provide me with a means of accomplishing something I need to do. I'm struggling with motivation to learn SQL because even though I know it will be better for analytics (and have my resume popping on job boards) I don't have any projects I can do for it

I agree though, knowing Excel can vault your career tremendously. It can also teach you a lot about really basic programming (biggie being how to use Boolean logic).

 

[Skooby]

- record a macro (and the be able to tweak that macro or write your own from scratch)
- if/then
- Vlookups & Hlookups
- get your formula game up (there are a ton of functions you can learn the format to ....sumif, countif, sumproduct, subtotals, etc.)
- Know how to nest all those functions
- arrays (this shyt right here )

I know I can just google but...any tutorials you recommend?

 

[TLR Is Mental Poison]

I know I can just google but...any tutorials you recommend?

Honestly, Microsoft Help is a good start.

I feel like you need some projects though. I cant help you with that but there are probably resources online.

 

[Brown_Pride]

I know I can just google but...any tutorials you recommend?

you know most of the time I found myself saying, there's gotta be a better way to do this. Then i'd just google and find something and then it normally lead me down various paths. Like I said because I'm mostly self taught everything i learned was out of necessity.

So TLDR no i don't know of any good tutorials

 

[MrFantastic]

Subs

 

[Mensch Fontana]

A guy at work made a excel workbook that has a button that when you click on it, it imports all our reports off our carrier websites and puts them in a speadsheet, which we use for our reports. that shyt made work 10000x times easier

 

[Brown_Pride]

A guy at work made a excel workbook that has a button that when you click on it, it imports all our reports off our carrier websites and puts them in a speadsheet, which we use for our reports. that shyt made work 10000x times easier

Macros bro, macros.
They're easy to do too.

 

[Camile.Bidan]

at work (accounting) they got me learning how to code macros. It's gonna be tough but when I'm good at it its a wrap for my career . My manager is a genius dope that shyt...the whole infrastructure he has created using MS Excel macros, sheets and MS SQL queries. shyt is incredible and streamlines our financial reporting infinitely.

Are you working at a public company? Does your boss's boss understand all the Marcos, and could someone easily perform procedures to assess the completeness an accuracy of the data used?

The PCAOB has come up with some pretty excessive rules for SOX compliance (Staff alert no 11).

I see a lot of bright ideas to reduce work out there, but I don't think most people are considering these outrageous rules that the PCAOB has implemented.


even that data table in video... since it's performed in Excel and not an Application subject audit procedures, the SOX control manager would have to manually recalculate that whole table if it is used address a Financial Statement assertion.


And that table itself constitutes a major SOX 404 control failure, and I would recommend a material weakness in controls. That scenario was made with no consideration of production capacity. If that table was used create product standard costs, it's an automatic Material Weakness. It also indicates that variances, most notably absorption variances, are reviewed with no consideration for idle capacity as a period cost.

So don't worry about your job. As long as there are progressive liberals using an imprecise regulation-sledge-hammer to solve all problems, accountants will always have a job

 

[Ohene]

Are you working at a public company? Does your boss's boss understand all the Marcos, and could someone easily perform procedures to assess the completeness an accuracy of the data used?

The PCAOB has come up with some pretty excessive rules for SOX compliance (Staff alert no 11).

I see a lot of bright ideas to reduce work out there, but I don't think most people are considering these outrageous rules that the PCAOB has implemented.


even that data table in video... since it's performed in Excel and not an Application subject audit procedures, the SOX control manager would have to manually recalculate that whole table if it is used address a Financial Statement assertion.


And that table itself constitutes a major SOX 404 control failure, and I would recommend a material weakness in controls. That scenario was made with no consideration of production capacity. If that table was used create product standard costs, it's an automatic Material Weakness. It also indicates that variances, most notably absorption variances, are reviewed with no consideration for idle capacity as a period cost.

So don't worry about your job. As long as there are progressive liberals using an imprecise regulation-sledge-hammer to solve all problems, accountants will always have a job

firm isn't public...rather a big 4 public accounting firm.
what I do though isnt a client facing role. I'm part of the group that reports profitability/financial data for the firm itself as opposed to clients

 

[Camile.Bidan]

firm isn't public...rather a big 4 public accounting firm.
what I do though isnt a client facing role. I'm part of the group that reports profitability/financial data for the firm itself as opposed to clients

Ah I see. maybe you work for my firm.