Course Description
(This course is the first part of a two-part series. Register here for Part 2.)
Common area maintenance (CAM) allocations can be a tricky endeavor in Excel because the devil is in the details. In this course, the first of a 2 part series, author and Excel expert David H. Ringstrom, CPA, will show you how to use techniques such as Power Query to create CAM reconciliation templates you can easily funnel tenant data through. Design strategies will include using look-up functions to return prior year data and building in check figures.
David is the author of Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change.
Who should attend:
Professionals seeking to streamline common area maintenance allocation spreadsheets.
Level: Intermediate
Topics covered:
- Adding Totals to the CAM Reconciliation
- Calculating projected CAM
- Swapping out rent rolls in Power Query to apply most of your CAM work on one property to other properties
- Looking up the projected CAM pool amount with the SUMIF function
- Creating an in-cell list by way of Excel’s Data Validation feature
- Using Data Validation and the SUMIF function to create a CAM reconciliation schedule for your tenants
- Importing rent rolls into Excel by way of Power Query
- Improving the integrity of spreadsheets with Excel’s VLOOKUP function
- Calculating CAM true-up amounts
- Importing rent rolls into Excel by way of Power Query
Learning objectives:
- Identify the fewest arguments that you can provide for XLOOKUP to provide an exact match
- State the arguments for Excel's SUMIF function
- Recall which menu the Get Data command appears on in Excel 2019 and later
Review our course policies and procedures page for further information