Course Description
This course is the second part of a two-part series. Register here for Part 1.
In the second course of a two-course series, Excel author and expert David H. Ringstrom, CPA, builds on the concepts shown in Part 1 by showing you how to create common area maintenance (CAM) prorations for lease end. You'll also see how to create interactive summary reports by using PivotTables and Slicers. You'll see how to import expenses into your CAM spreadsheet with Power Query, and how to create a gross-up schedule and calculate base year increases.
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:
Professionals seeking to streamline common area maintenance allocation spreadsheets.
Level: Intermediate
Topics covered:
- Building a CAM gross-up schedule from scratch
- Building a series of self-updating dates with Excel's DATE function.
- Summarizing CAM amounts and true ups with pivot tables
- Incorporating decisions into calculations with Excel's IF function
- Filtering data within PivotTables by way of the Slicer feature
- Importing income statements (profit & loss reports) into Excel by way of Power Query
Learning objectives:
- State what must be entered in the day argument of the DATE function to return the last day of the preceding month
- Identify the location of the PivotTable command within Excel's ribbon menu interface
- Define the purpose of Excel's Slicer feature
Review our course policies and procedures page for further information