Excel for accountants (Excel (All))

Topic

New Reply

Hi,

I am in the process of developing a short course in Excel for accountants. In order to make the course content more revevant to accountants (and since I am not one myself), I would like to ask for assistance regarding excel content to include that is useful to accountants. If you are an accountant or are familiar with content like certain useful formulas or built in features and forecasting that is useful for them, please list it in your reply. I will appreciate it greatly so as to compile a list of topics to include into this course.

Many tx.

Reply | Quote

Replies

Thinking out loud:

– Financial functions
– Rounding
– Spreadsheet auditing (!)
– Pivot tables
– Charting
– Sorting and filtering
– Getting data from databases (MSQuery)

Reply | Quote

Thanks Jan and Hans.

Jan, just a question asked from ignorance…
Do you think accountants will use sorting and filtering much. I always thought of accountants using the traditional budget style cross-ref type spreadsheet, not the list style such as a database table. (Just a curious thought)

Thanks for the insights and info.

Reply | Quote

They will if their data is a long list coming from an external system.

I suspected they’d benefit from knowing all there is to know about sorting and filtering.

In addition to my previous list:

– Methods for weeding out wrong data (for which autofilter may come in handy).

Reply | Quote

– date and time functions and their use in accounting

Reply | Quote

Perfect. The list is becoming quite comprehensive already. Thanks for all the contributions so far. I appreciate it.

Thanks Tom for those extra bits of info.

I understand Jan Karel. I can see that it would be useful if that were the case where they get raw data from a processing system.

PS: This course may turn out to be loooonger in duration than anticipated with all the content I can add.

Reply | Quote

It may be even more useful if they’ve keyed in the data themselves

Reply | Quote

Reply | Quote

Hi Rudi,
I am an accountant and I download databases from our main software platform multiple times per day. We have about 16 people in accounting (half are degreed accountants) and we all use the filter function extensively, likewise, we use pivot tables to summarize these databases. When we “discovered” the pivot tables (accidentally) about 8 years ago, we thought they were “magic”. I have no idea how in the world we existed without them prior to that. I use filters and pivot tables EVERY single day… and so does ALL of our accounting staff. Accounting generates and distributes information for our entire worldwide organization to our executives, sales staff, customer service staff, HR staff, etc., so they too, know how to use pivot tables as well. I would say that this is one of the most powerful tools in Excel, as it allows the not so advanced Excel user to use it. As an accountant, I use the pivot table as a problem solving tool, rather than just a reporting tool. It works great for finding a needle in a haystack.
Good luck with your project!
Lana

Reply | Quote

Many thanks for this response Lana. I will most definitely ensure that Pivots or included based on your and Jan Karels replies.
Appreciated.

Reply | Quote

I agree with all the above. As an accounting supervisor using everything from VisiCalc onward, the application of spreadsheets has to me been a mixed blessing. Google “what we know about spreasheet errors” to see some interesting studies done for KPMG and other firms about error-rates and their causes. The SUMPRODUCT function is also very powerful. It avoids using Array formulas while at the same time creating very useful capabilites to pull out very specific information out of very large GL type databases.

Reply | Quote

Thanks for the heads up.

I agree with your pointer on the SUMPRODUCT function. Through other sources I have already added this to a proposed outline. I appreciate the confirmation and additions.

Cheers

Reply | Quote

URL made into link by HansV by placing before it and after it

Errors in spreadsheets are a big deal. They should certainly be an even bigger deal for accountants. Here’s an article about detecting spreadsheet errors.
http://www.journalofaccountancy.com/Issues…sheetErrors.htm%5B/url%5D

Accountants should also understand how to use Date functions, date math, etc. EOMONTH function should be required so that consistent dates are used.

Reply | Quote

Thanks for your input Cathy. The link is a good find.

Reply | Quote

Rudi, to all the excellent suggestions I will add cell formatting especially financial, date, and time formats. Since you are not in the US you might also provide some warnings about the US centric MM/DD/YYYY date entry issue. What kind of industry will these accountants be from?

Reply | Quote

>>> What kind of industry will these accountants be from?

All kinds. The courseware would be presented by an IT company to clients that send delegates on software training. The clients are from all forms of business.
Tx for the formatting suggestions. I agree that it could standardise sheets and avoid possible errors in that line.

Cheers

Reply | Quote

Jan Karel has already covered the main points, but in addition:

The above is very good.
Most accountants I work with are interested in Pivot Tables.
However, I find they waste time by not using Custom Views more often.
Other items to consider:
A Outlines and/or Grouping
B Data Subtotaling and the Subtotal Function
C Consolidation

Regards,

Tom Duthie

Regards,

Tom Duthie

Reply | Quote