Building Effective Budget Spreadsheets in Excel

Author: David H. Ringstrom

In this presentation, Excel expert David Ringstrom, CPA, shows you how to create resilient, future-proofed budget spreadsheets. He shares design techniques, including how to separate inputs from calculations, build out a separate calculations spreadsheet, and create both an operating and a cash flow budget. The cash flow budget will calculate when to borrow against a line of credit, pay down the line of credit, and pay dividends when cash is available.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Publication Date: August 2018

Designed For
Practitioners seeking to build budget spreadsheets that can be updated effortlessly and that contain easy-to-follow supporting calculations.

Topics Covered

• Preserving key formulas using hide and protect features
• Learning how range names can minimize errors, save time in Excel, serve as navigation aids, and store information in hidden locations
• Streamlining formula writing by implementing the Use in Formula command
• Using the SUMIF function to summarize data based on a single criterion
• Avoiding the complexity of nested IF statements with Excel's CHOOSE function
• Learning how the Table feature allows you to transform filtering tasks
• Learning a simple design technique that greatly improves the integrity of Excel's SUM function
• Improving the integrity of spreadsheets with Excel's VLOOKUP function
• Comparing and contrasting IFNA, IFERROR, and ISERROR functions and learning which versions of Excel support these worksheet functions
• Going beyond simple rounding with the ROUNDUP and ROUNDDOWN worksheet functions

Learning Objectives

• Define how to isolate all user entries to an inputs worksheet, while protecting all calculations and budget schedules on additional worksheets
• Identify and apply range names and the Table feature to create resilient and easy-to-maintain spreadsheets
• Calculate borrowings from, and repayments toward, a working capital line of credit
• Recognize and apply keyboard shortcuts to building an effective budget spreadsheet
• Identify what the CHOOSE function returns when Index_Num argument exceeds the number of Index arguments
• Describe SUMIF's arguments and returns
• Recognize how to improve the integrity of SUM formulas in Excel
• Identify which menu in Excel various commands appear
• Recognize which part of the formula Excel calculate first, when a portion of a formula is wrapped in parentheses
• Identify the character which signifies an absolute reference within a formula
• Describe the VLOOKUP function with the following arguments

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience with Excel Spreadsheets Recommended.

Advance Preparation
None