This is part 1 of a very important 2-part series. There is just too much information to pack into one day.
Once you sign up for Part 1 for 9/18/2024, please sign up for Part 2 on 9/19/2024 at: https://www.irem.org/webinars/id-webfasw-0924hq03/automating_cam_reconciliations_in_excel_part_2
Please note this is a special 90 minute webinar set each day for 9/18/2024 and 9/19/2024.
09/18/2024
11 am PT / 12 pm MT / 1 pm CT / 2 pm ET
*approximate length: 90 minutes
Presenter: David Ringstrom, CPA
Common area maintenance (CAM) allocations can be a tricky endeavor in Excel because the devil is in the details. In this presentation author and Excel expert David H. Ringstrom, CPA will show you how to use techniques such as Power Query to create CAM reconciliation templates that 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 receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
Who should attend: Pofessionals seeking to streamline common area maintenance allocation spreadsheets.
Topics typically 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
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but should, know about Microsoft Excel. His goal is to empower you to use Excel more effectively.