In this course we explore ways of automating monthly, quarterly, and yearly financial reporting processes to achieve the ultimate goal of “one-click reporting”. We start with a brief overview of the many data and reporting challenges facing accountants and analysts. From there we discuss how we can set up financial reports in Excel based on accounting system exports (direct reports or financial data).

This leads to an introduction of Excel formulas, Pivot Tables, and simple VBA macros. Then we work with each formula to transcribe system-generated report or data into standard financial reports. This course serves as review for accounting and finance professionals who need to sharpen their Excel skills in automating monthly/quarterly/yearly financial reporting. An excellent course in how to automate Excel reports.

Course Series

This course is included in the following series:

3 CoursesAutomate Excel

  1. Automating Financial Reporting in EXCEL (Part I): From Data to Report in ONE Click
  2. Automating Financial Reporting in EXCEL (Part II): Self-Service Reporting for Executives
  3. Automating Financial Reporting in EXCEL (Part III): Fully-Integrated Annual Filing, Board Book, and Board Deck
Learning Objectives
  • Discover best practices in linking a financial statement to a system-generated report;
  • Recognize the differences between exact match and approximate match in lookup formulas;
  • Identify the best practice in building a Pivot Table;
  • Identify a dynamic formula used in referencing cell values in a Pivot Table;
  • Discover 3 ways of assigning a macro – to a button, to a worksheet, or to a workbook.
Last updated/reviewed: March 13, 2024
77 Reviews (251 ratings)

Reviews

4
Anonymous Author
Interesting course. Will have to see how the other courses are to give a good overall opinion. It's a good reminder of the benefit of index and match. It was nice to see how to automate the pivot table refresh button. Some of the formula additions I will have to review again and not sure how I can apply to my work yet, but I imagine there is a way and might make sense after the next 2 courses in this series. Perhaps a little more explanation into some of the formulas conjunctions would have been helpful.

5
Anonymous Author
Great course that I really enjoyed! These are great tips to the many of us out there that have to use these sorts of workarounds due to limited IT staffing/budgeting versus already having system reports already set up in the formatting that we need to do our work. My hope is that systems/reporting becomes easier so we don't have to rely on Excel as much.

4
Anonymous Author
I had a little trouble with the sound - I had to turn the volume up a lot to hear and understand some parts, and then a different section would come on, and the sound would be much louder and I would need to adjust the sound lower. I also found it difficult to stay engaged throughout the whole course.

5
Member's Profile
I use Excel nearly every day in the private sector to create reports and do analysis with. Some of these practices I use every day. However, there were some very helpful tips and tricks the presenter showed. I am looking forward to the next two parts.

5
Anonymous Author
Bingo! This is a very helpful course which covers all the necessary tips to update budget models efficiently. I always want to master the 2 useful formulas - Index() and Match(). And this course nails it well.

5
Anonymous Author
Pretty basic introduction to automated reporting in Excel. Activating a macro on clicking on a workbook was an interesting addition to reporting in Excel. Good intro to GETPIVOT functionality.

5
Member's Profile
A little technical with the VBA pieces. I didn't quite get that so I'll need to watch again. I picked up several things that I didn't know about Excel and I believe they will be very helpful!

1
Member's Profile
Other excel course provide excel worksheet example which are very helpful. Trying to following formulas on the screen was not always efficient as the screen was very small and hard to read.

4
Anonymous Author
Decent course, lots of implied steps or content, must think about how to apply to specific situation and conditions. Exam question construction may leave doubt as to intent of examiner

4
Member's Profile
I thought this was an interesting and instructive session. I learned some new uses for formulas I was not completely familiar with. Overall - worth the investment in time.

4
Anonymous Author
I thought this was very helpful. You do have to be concentrating to follow all of the information, especially transitioning from one step to the next in the advanced section.

5
Anonymous Author
This is my first Illumeo course and I am very impressed. Gone are the boring cpe courses. I am actually excited to take CPE and learning something useful. Thank you Illumeo!

5
Anonymous Author
Covered a lot of ground. I really liked using pivot tables to generate a final income statement. It would be very helpful to have the Excel examples as part of the course.

5
Anonymous Author
These are formulas that I use daily and he provided a great review on how they work. He provided enough information for a newer user to start more automated reporting.

Anonymous Author
There are few techniques that is very helpful. However, I think more time is needed for some of the steps that are more complicated and not commonly used formulas.

4
Member's Profile
This was a very useful course although a little outdated. With the addition of xlookup a lot of this discussion is out of date (vlookups, index, match, etc)

4
Member's Profile
Good overview of a complex topic, but allotting more time would have allowed for additional examples which would help comprehension of this complex topic.

5
Anonymous Author
I've been using Excel for a long time and know how to do many advanced functions, but had never heard about this functionality. Very well done video.

5
Member's Profile
Great course presentation! I never thought Excel Formulas and macros can be so easy! Thank you for presenting this in a very easy to understand manner!

4
Member's Profile
Some decent techniques. I appreciated the INDEX/MATCH explanations and the framework on how to standarize the system generated data.

4
Anonymous Author
Автор не разбирает подробно формулах. Речь идет лишь об общих вопросах

4
Anonymous Author
The instructor gives some good, usable tips. I think the subject matter is too complex though for just an hour long seminar.

4
Member's Profile
This course was a nice course that built upon itself by first going over the basics all the way through advanced techniques.

4
Member's Profile
I learned a lot from this presentation. There were some cool tricks show such as how to build a button to assign a macro.

5
Member's Profile
There is a lot more packed into this course than one might think. Good explanation and use of index match function.

4
Anonymous Author
Very good course on some tough subjects. I will have to go through this more than once to grasp all the concepts.

5
Anonymous Author
Helpful step by step approaches detailed from basic to intermediate to advanced for building one click reporting.

4
Member's Profile
Great information, but I'll need to spend some extra time on the formulas to incorporate into my daily toolbox.

Member's Profile
I taught Excel and VB Script at a college level so it I can honestly say that it was a good short course.

4
Anonymous Author
It's good for over-viewing how OCR is formulated and easy to understand with simple examples.

5
Member's Profile
This course is a good case study for using formulas and pivot tables for populating reports.

3
Anonymous Author
It was very basic and only useful if your data rarely changes or the format rarely changes.

5
Anonymous Author
Great Course! It would be nice if advanced level was explained with various examples. Thanks

5
Anonymous Author
I enjoy Mr Wu's courses. They have been very easy to follow and incorporate into my work.

4
Anonymous Author
Great examples and ideas, although the basic section might have been a little too long.

5
Member's Profile
Very good course. Always wanted to learn more regarding the Index and Match functions.

5
Member's Profile
I really like the clarity of the course. It was easy to understand complicated terms.

4
Member's Profile
Loved the trick of attaching a refresh macro to clicking on the tab in a workbook.

5
Member's Profile
Very Informative Course and learned new things.Thank you so much for the course.

5
Member's Profile
This course has some great ideas for improving Excel-based financial reports.

4
Member's Profile
The course is fantastic and useful. The deliverannce is clear and practical.

5
Member's Profile
The class was very informative and gave me tips that I haven't tried before.

4
Anonymous Author
Helpful information about automating reports in Excel. Recommend.

5
Member's Profile
This course is very easy to follow and understand for beginners.

3
Member's Profile
It's a Good Course, but I thought It would have a higher level.

5
Member's Profile
i really like the model please add even the indirect function

4
Member's Profile
Good overview of advanced pivot table and reporting functions

5
Anonymous Author
I like how simple and straight to the point this course is.

5
Anonymous Author
Very informative guidance to prepare the necessary reports.

3
Member's Profile
works for what i need to renew my cpa and cia licenses.

5
Anonymous Author
This was awesome; I learned new tricks and formulas.

4
Member's Profile
great course! Gave me some ideas to explore. Thanks!

3
Member's Profile
Good and helpful training looking forward for more.

4
Anonymous Author
This is perfect for new to excel and pivot tables.

5
Member's Profile
This course teach me good new tricks, thank you.

5
Anonymous Author
Useful and uncharted ways to get the job done.

5
Member's Profile
Great course! I learned new excel techniques.

4
Anonymous Author
Found GetPivotdata quite interesting.

4
Anonymous Author
I enjoyed the course. Great overview.

5
Anonymous Author
A helpful dive into a complex topic.

5
Member's Profile
Useful information which I will use

5
Member's Profile
This is great! I learned a lot.

4
Member's Profile
Lot of detail in a short time.

4
Anonymous Author
The illustrations are great.

4
Anonymous Author
Good topic very helpful.

5
Anonymous Author
Good source for template

4
Anonymous Author
Good tips on automation.

5
Member's Profile
Effective presentation

3
Member's Profile
Kind of slow moving

4
Anonymous Author
Excellent course!

5
Anonymous Author
Some great tips.

5
Anonymous Author
I learned a lot

4
Anonymous Author
It was helpful.

4
Member's Profile
It is great.

4
Anonymous Author
Enjoyed it.

3
Anonymous Author
knows excel

4
Anonymous Author
helpful.

Prerequisites
Course Complexity: Advanced

No Advanced Preparation or Prerequisites are needed for this course.

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Questions and Answers(2 Questions)
Member's Profile

Do you recommend using named data ranges when doing these types of models.

Member's Profile

Thanks for the question, Ted.
Named ranges are better than regular reference of rows/columns, while Tables, with features of names and ranges, work better than named ranges, in my opinion.
Lenny

Instructor for this course
Course Syllabus
Basic
  Basic: VLOOKUP()6:18
  Basic: INDEX() & MATCH()7:35
  Basic: Pivot Table4:37
  Basic: Simple Macro/VBA7:52
Intermediate
  Intermediate: Excel modeling based on system reports – Simple link2:06
  Intermediate: Excel modeling based on system REPORTS – Formula link, Helper Column8:48
Advanced
  Advanced: Excel modeling based on system DATA – Parameters1:10
  Advanced: Excel modeling based on system DATA – Data Processing8:45
  Advanced: Pivot Table, GETPIVOTDATA9:06
  Advanced: VBA, Macro to achieve One-click Reporting4:23
CONCLUSION
  Summary - One-click Reporting1:16
SUPPORTING MATERIALS
  Slides: From Data to Report in ONE ClickPDF
  From Data to Report in ONE Click Glossary/IndexPDF
  Part I WorkbookXLSM
REVIEW & TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam