Maximize Excel: Budget Spreadsheets
Presented by David H. Ringstrom, CPA
 

Duration: 100 minutes
 

In this presentation, author and Excel expert David Ringstrom, CPA, teaches you how to create resilient and easy-to-maintain budget spreadsheets. David will contrast assigning names to cells, which both simplify formula writing and review, as well as spreadsheet navigation with the Table features, which offers similar benefits. You'll see how the Use In Formula command streamlines formula writing, and how the VLOOKUP, XLOOKUP, and SUMIF functions minimize formula maintenance.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. 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:
Practitioners seeking to build budget spreadsheets that can be updated effortlessly and that contain easy-to-follow supporting calculations.
 
Topics covered:
 

• Understanding why it’s worthwhile to build out supporting schedules to break down calculations used in budgets.

• Employing the SUMIF function to sum values related to multiple instances of criteria you specify.

• Assigning names to cells to streamline formulas and bookmark key inputs within a workbook.

• Crafting formulas to compute gross margins, projected sales, commissions, and related amounts.

• Utilizing Excel's Name Manager feature to review and manage named ranges within a workbook.

• Protecting sensitive information by hiding formulas within an Excel workbook.

• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP.

• Building operating budgets quickly based on detailed supporting schedules that provide an audit trail.

• Saving time writing formulas by choosing named ranges with the Use In Formula command.

• Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.

• Saving time by using the AutoSum formula to sum multiple columns at once with a mouse click or a keyboard shortcut.

• Accessing free downloadable budget templates that can be customized as needed.

 
Learning objectives:

• Recall the menu in Excel where the Table feature resides.

• State what SUMIF returns if a match cannot be found.

• Identify the data integrity improvement that the Table feature adds to VLOOKUP.

 
 
Level:
Basic
 
NASBA Field of Study:
Specialized Knowledge (2 hours)
 
Program Prerequisites:
None

 
Advance Preparation:
None