February 13, 2018 @ 12:00 pm – 1:30 pm
Online Webinar
$25.00 Member | $50.00 Non-Member

The NH-VT and Maine Chapters are excited to bring this Excel webinar series to the other Region I HFMA chapters. Jeff Lenning has provided Excel training to our chapter members for the past two years with rave reviews. Don’t miss the opportunity to get nine hours of Excel training for financial professionals for just $150 (HFMA member rate)! For those who attended our Excel webinar series these past two years, you know t his is an incredible value! The training will be offered in six stand-alone webinars 90 minutes each over lunch time beginning in December and running through February. Each participant will receive a set of Excel templates, as well as a PDF summary handout for future reference. The webinars are $25 each for members and $50 each for non-members. Please refer to the detailed described of each webinar below.

Please note that these webinars are not recommended for beginner users. The material offered is for intermediate to advance level users. Webinars will begin at noon and end at 1:30 pm EST and will not be recorded for future use. Separate registration for each webinar is required so that you can sign up for one or all. Links to the registration are noted below:

12/5/2017 – Excel Series 1 – How Do You Do This in Excel
1/9/2018 – Excel Series 2 – Mapping Tables
1/17/2018 – Excel Series 3 – Excel PivotTables
1/25/2018 – Excel Series 4 – Transform Data in Excel Without Formulas or Macros
2/13/2018 – Excel Series 5 – Excel Budget Ideas
2/27/2018 – Excel Series 6 – The Two Most Important Excel Functions for Accountants

How Do You Do This in Excel – How do you get from your house to the store? Do you walk? Ride a bike? Drive a car? All of these methods will get you there, but, some are faster than others. The same is true in Excel. There are often several ways to accomplish any given task in Excel. But, not all options provide the same efficiency. For example, let’s say you need to compute a report subtotal, such as the fixed assets subtotal. How do you do this in Excel? Do you use the SUM function? If so, it may surprise you to learn that there is a more efficient option.  In this session, we’ll examine several common Excel tasks and then discuss our options.

Mapping TablesWhen you need a report that your accounting system doesn’t provide, you probably turn to Excel to create the report. In this session, we will explore the features, functions, and techniques needed to help automate these types of reports. For example, you want to export a trial balance and you want Excel to pull the values into the proper lines in a balance sheet, even when the labels are different and even when many accounts need to roll up to a single report line. By the end of the session, you’ll learn how to structure your reporting workbook so that your Excel formulas automatically summarize the exported accounting system data.

Excel PivotTables – In this session, we’ll explore the fundamentals of creating PivotTable reports. We’ll examine the report layout areas and how to update source data. We’ll also examine report layout options so that you can obtain just the right formatting and structure for your report. We’ll talk about how to group your report into monthly columns and how to filter PivotTables as well. By the end of the session, you’ll understand how to create basic PivotTable reports.

Transform Data without Formulas or Macros – If you haven’t played with the Get & Transform commands in Excel 2016 for Windows, they are probably worth checking out. Formerly available as the Power Query add-in, the Get & Transform tools built-in to Excel 2016 for Windows are incredibly powerful. At a high level, they enable us to retrieve data from a variety of sources and prepare it for use as needed. These tools provide new ways to approach tasks that were previously manual and time-consuming. In this session, we’ll apply these tools to several common tasks, including flattening data, splitting and combining columns, formatting data, unpivoting summarized data, and combining several csv exports. If you frequently export/import data from one system to another, you will want to understand how to use a Get & Transform query to streamline your work.

Excel Budget Ideas – Budget methods are the key to allowing department managers to prepare their budget in Excel efficiently. A budget method may be, for example, spread prior year actuals or match prior year actuals, or match prior year actuals less 10%.  In this webcast, we’ll explore how to allow the users to pick a budget method from a drop down list for each account, and then have Excel perform different math depending on the budget method selected. We use several features and functions at the same time to accomplish this, including data validation and a combination of functions. Even if you are not involved in the budgeting process, the underlying features and functions discussed have a wide range of uses.

The Two Most Important Excel Functions for AccountantsWe’ll discuss two Excel functions that provide incredible efficiency to accountants. One has been around for decades and is often cited as the most popular Excel function. It is the VLOOKUP function. VLOOKUP is indeed a favorite of many accountants.  After exploring the details of VLOOKUP, we’ll examine a relatively new alternative. We’ll compare these two functions and see which performs lookup tasks more effectively. It will be a competition, an epic battle between two of the most important Excel functions. Who will win?

Your attendance may qualify for 1.8 hours of Continuing Professional Education (CPE) credits for each session. We have computed the education credits based on an equivalent of 50 minutes for each education credit hour. You are responsible for maintaining adequate records and determining if the content of this program meets the professional licensing requirements of your state.