Instructor for this course
more

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: April 22, 2018

54 Reviews (153 ratings)Reviews

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.
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.
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.
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
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.
5
Member's Profile
This course is a good case study for using formulas and pivot tables for populating reports.
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.
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
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.
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 .

2 QuestionsCourse Questions and Answers

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

Course Syllabus
Basic
  6:18Basic: VLOOKUP()
  7:35Basic: INDEX() & MATCH()
  4:37Basic: Pivot Table
  7:52Basic: Simple Macro/VBA
Intermediate
  2:06Intermediate: Excel modeling based on system reports – Simple link
  8:48Intermediate: Excel modeling based on system REPORTS – Formula link, Helper Column
Advanced
  1:10Advanced: Excel modeling based on system DATA – Parameters
  8:45Advanced: Excel modeling based on system DATA – Data Processing
  9:06Advanced: Pivot Table, GETPIVOTDATA
  4:23Advanced: VBA, Macro to achieve One-click Reporting
CONCLUSION
  1:16Summary - One-click Reporting
SUPPORTING MATERIALS
  PDFSlides: From Data to Report in ONE Click
  PDFFrom Data to Report in ONE Click Glossary/Index
  XLSMPart I Workbook
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM