1 CPE 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
- Automating Financial Reporting in EXCEL (Part I): From Data to Report in ONE Click
- Automating Financial Reporting in EXCEL (Part II): Self-Service Reporting for Executives
- 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
80 Reviews (263 ratings)
Reviews
Anonymous
(CFO)
Very Useful tools. I have only used 1/2 of these excel tools and this will make reporting even easier.
Anonymous
(Accountant)
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.
Anonymous
(Internal Audit Manager)
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.
Anonymous
(Senior Accountant)
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.
Dylan Spencer
(Controller at Red Leaf Advisors, LLC)
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.
Anonymous
(-)
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.
Anonymous
(Accountant)
Excellent course with extremely useful information. I will have to watch most parts several times to adopt to workplace. Lenny, please consider publishing your work in written format for purchase.
Anonymous
(cpa)
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.
Heidi Scott
(CFO at Graff Financial LLC)
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!
Ellen Rogers
(Sr Accountant / Finance Manager Consulta.. at RGP)
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.
Anonymous
(Senior Lean Development Officer)
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
William Lathrop
(- at Artful Home, LLC)
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.
Anonymous
(Board Member)
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.
Anonymous
(CPA owner)
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!
Anonymous
(Controller)
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
(Controller)
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
(Director)
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.
Justin Farr
(Controller at Ncontracts)
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)
Edward Restelli
( at Envocore)
Good overview of a complex topic, but allotting more time would have allowed for additional examples which would help comprehension of this complex topic.
Anonymous
(VP, Finance)
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.
MARIA LUISA DE GUZMAN
(Manager - Billing Staff & Financial .. at Harbor Rail Services of California)
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!
Ted Monohon
(VP -Finance / Controller at Fantex)
Some decent techniques. I appreciated the INDEX/MATCH explanations and the framework on how to standarize the system generated data.
Anonymous
(internal auditor)
Автор не разбирает подробно формулах. Речь идет лишь об общих вопросах
Anonymous
(Finance Manager)
The instructor gives some good, usable tips. I think the subject matter is too complex though for just an hour long seminar.
Taylor Aschliman
(Plant Controller at Pretzels, Inc.)
This course was a nice course that built upon itself by first going over the basics all the way through advanced techniques.
Kevin Moschella
(Sr. Director, FP&A at Evidera)
I learned a lot from this presentation. There were some cool tricks show such as how to build a button to assign a macro.
Ted Feher
(Owner at Feher, PC (CPA))
There is a lot more packed into this course than one might think. Good explanation and use of index match function.
Anonymous
(Owner)
Very good course on some tough subjects. I will have to go through this more than once to grasp all the concepts.
Anonymous
(Finance & Accounting Consultant)
Helpful step by step approaches detailed from basic to intermediate to advanced for building one click reporting.
Marquis Huntsman
(CFO at Castle Breckenridge Management)
Great information, but I'll need to spend some extra time on the formulas to incorporate into my daily toolbox.
Kyle Degruttola
(Accounting Manager at Park Avenue Securities)
I taught Excel and VB Script at a college level so it I can honestly say that it was a good short course.
Anonymous
()
It's good for over-viewing how OCR is formulated and easy to understand with simple examples.
Chris Grimes
(Rules Master at Cisco)
This course is a good case study for using formulas and pivot tables for populating reports.
Anonymous
(Controller)
It was very basic and only useful if your data rarely changes or the format rarely changes.
Anonymous
(Accountant)
Great Course! It would be nice if advanced level was explained with various examples. Thanks
Anonymous
(VP Finance and Accounting)
I enjoy Mr Wu's courses. They have been very easy to follow and incorporate into my work.
Anonymous
(CFO)
Great examples and ideas, although the basic section might have been a little too long.
Nicole Osborne
( at MAO Solutions, LLC)
Very good course. Always wanted to learn more regarding the Index and Match functions.
Jacqueline Ursic
(Risk Management Analyst at Novelis)
I really like the clarity of the course. It was easy to understand complicated terms.
Margaret Winn
(Accounting Professional at .)
Loved the trick of attaching a refresh macro to clicking on the tab in a workbook.
Very Informative Course and learned new things.Thank you so much for the course.
John Noble
(Controller at eComEngine)
This course has some great ideas for improving Excel-based financial reports.
Mutiu Bakare
(CFO at Afriland Properties PLC)
The course is fantastic and useful. The deliverannce is clear and practical.
Gene Cenotto
(Sr. Accounting Analyst at WA)
The class was very informative and gave me tips that I haven't tried before.
Anonymous
(Financial Reporting Manager)
Helpful information about automating reports in Excel. Recommend.
Cynthia Lam
(Executive Director, Corporate Controller at CytomX Therapeutic Inc)
This course is very easy to follow and understand for beginners.
Jose Maria Lopez
(Regional Manager of Accounting & Rep.. at VF)
It's a Good Course, but I thought It would have a higher level.
Thabit Ndilahomba
(Head of Finance at Brac)
i really like the model please add even the indirect function
Don Wademan
(CFO at Garner Industries)
Good overview of advanced pivot table and reporting functions
Anonymous
(Internal Audit Manager)
I like how simple and straight to the point this course is.
Anonymous
(None)
Very informative guidance to prepare the necessary reports.
Lucas Kysely
( at Edcetera)
Nice buildup throughout the course starting with the basics
Jennifer Bor
(Sole Practitioner at Accounting and Business Management Consu..)
works for what i need to renew my cpa and cia licenses.
Anonymous
()
This was awesome; I learned new tricks and formulas.
Lou Mortillaro, CMA
(Accounting Manager at American Financial Resources)
great course! Gave me some ideas to explore. Thanks!
Mark Edward Maglaqui
(Team Lead, Credit & Collections at NetSuite Inc.)
Good and helpful training looking forward for more.
Anonymous
(Consultant - Process Specialist)
This is perfect for new to excel and pivot tables.
Hicham Jawhar
(Financial controller at Basement Systems Quebec)
This course teach me good new tricks, thank you.
Anonymous
(None)
Useful and uncharted ways to get the job done.
Sheila Esposo
(NONE at NONE)
Great course! I learned new excel techniques.
Anonymous
(Manager, Accounting & Reporting)
Found GetPivotdata quite interesting.
Anonymous
(-Accountant)
I enjoyed the course. Great overview.
Anonymous
(Chief Financial Officer)
A helpful dive into a complex topic.
Charles Elwis
(Insurance Broker at Health Guys)
Useful information which I will use
Muriel Santos
(Senior General Ledger Accountant II at Netsuite Inc.)
This is great! I learned a lot.
Douglas Ronald
(---- at ------------)
Lot of detail in a short time.
Anonymous
(General Ledger Accountant)
The illustrations are great.
Anonymous
(Chief Financial Officer)
Good topic very helpful.
Anonymous
(Technical Accountant)
Good source for template
Anonymous
(Consultant)
Good tips on automation.
Ramakrishna Nalluri
(Senior Financial Analyst at LifeWorks Inc)
Effective presentation
Joseph Szczepaniak
(Controller at HN Precision)
Kind of slow moving
Anonymous
(President)
Excellent course!
Anonymous
(Senior Accounting Manager)
Some great tips.
Anonymous
(Chief Financial Officer)
I learned a lot
Anonymous
(Credit/Collections Analyst)
It was helpful.
Qasim AlQattan
(Senior Internal Auditor at BCFC)
It is great.
Anonymous
(Owner)
Enjoyed it.
Anonymous
(Senior Director & Controller, Financ..)
knows excel
Anonymous
(Credit & Collections Analyst II)
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)
Lenny WuManagement Reporting Analyst, Seaspan Ship Management
About Illumeo
Platform
Resources
Contact Us
- Email: info@illumeo.com
- Phone: 408-400-3993
- Adress: 1608 W Campbell
- Av #221, Campbell, CA 95008
© 2024 Illumeo, Inc.
Do you recommend using named data ranges when doing these types of models.
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