Excel Agility: Building Valuable and Dynamic Budget Spreadsheets
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Taxation and Accounting, Software
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
Would you like to learn how to create resilient, accurate, and easy-to-maintain budget spreadsheets? If so, this excellent course by Excel expert David Ringstrom, CPA, is exactly what you need. Among other techniques, David shows you how to separate inputs from calculations, build out a separate calculations spreadsheet, create both an operating and a cash flow budget, transform filtering tasks, and preserve key formulas.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to 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 webcast.
Who Would Be Interested in This Course:
Practitioners seeking to build budget spreadsheets that can be updated effortlessly and contain easy-to-follow supporting calculations.
Your Benefits of Attending:
- Crafting formulas to compute gross margins, projected sales, commissions, and related amounts.
- Preserving key formulas using hide and protect features.
- Understanding why it’s worthwhile to build out supporting schedules to break down calculations used in budgets.
- Using range names to streamline formulas and bookmark key inputs within a workbook.
- Avoiding the complexity of nested IF statements with Excel’s CHOOSE function.
- Building formulas faster by way of the Use in Formula command.
- Building operating budgets quickly based on detailed supporting schedules that provide an audit trail.
- Mastering the IFERROR function to display alternate values in lieu of a # sign error.
- Understanding the use of the MONTH function to return the month portion of a date or month name.
- Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
- Using the SUMIF function to summarize data based on a single criterion.
Learning Objectives:
- Define how to isolate all user entries to an inputs worksheet, while protecting all calculations and budget schedules on additional worksheets.
- State what the CHOOSE function will return in the context of a specific formula.
- Identify how to calculate borrowings from, and repayments toward, a working capital line of credit.
Level:
Intermediate
Format:
On-Demand webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Previous Experience with Excel Spreadsheets
Advance Preparation:
None
Introduction
Excel Versions 00:01:01
Wally’s Widgets 00:01:37
Create Range Names - Create from Selection 00:05:39
Create Range Names - Name Box 00:12:10
Name Manager 00:16:06
Table Feature 00:20:56
Use in Formula Feature 00:26:20
Use in Formula Feature (cont.) 00:29:39
AutoSum Feature 00:33:14
Supporting Scheduled Formulas 00:36:44
Supporting Scheduled Formulas (cont.) 00:43:42
Collections Ratio 00:45:45
Introduction to the CHOOSE Function 00:49:45
Collections Ratio - MONTH Function 00:53:45
Collections Ratio - CHOOSE Function 00:57:37
Collections Ratio - IFERROR Function 01:00:11
Incorrect Collections Amounts Approach 01:04:25
Introduction to SUMIF 01:05:42
Collections Amounts - SUMIF Function 01:11:52
Collections Amounts Formula 01:14:12
Collections Amounts Totals 01:18:06
Hide Formulas 01:19:16
Protect Sheet - Supporting Schedules 01:21:32
Operating Budget Formulas 01:24:19
Smarter SUM Formulas 01:26:11
Operating Budget Formulas 01:27:53
VLOOKUP 01: 29:52
Add New Expense Account 01:33:03
Free Downloadable Budget Templates 01:35:06
Conclusion 01:39:37
- AutoSum 00:34:09, 01:18:22, 01:26:27
- CHOOSE Function 00:49:51, 00:57:41
- Create from Selection Command 07:55
- IFERROR Function 01:00:15
- MONTH Function 00:53:54
- Name Box 00:05:56, 00:12:48
- Name Manager 00:13:45, 00:16:08, 00:18:23
- Protect Sheet Command 01:21:38
- Protect Workbook 01:38:08
- Range Names 00:05:39
- SUMIF 01:05:42, 01:11:52
- Table Feature 00:04:47, 00:20:56
- Templates 01:35:06
- VLOOKUP 01: 29:52
- Worksheet 00:02:00
AutoSum: The AutoSum feature appears on both the Home menu and the Formulas menu as a Greek sigma symbol. When you click AutoSum, or press Alt-= Excel adds a sum function to the current cell or cells that you've selected.
CHOOSE Function : The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column.
Create from Selection Command: Quickly name a range or name multiple ranges at once using the "Create from Selection " command.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
MONTH : A worksheet function that extracts the month portion from a date.
Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.
Name Manager: The Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.
Protect Sheet Command: To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.
Protect Workbook: To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your Excel workbook with a password.
Range Names: A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.
SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Templates: Documents designed to serve as a starting point so that information does not have to be recreated. Templates also make it much harder for users to inadverently save over a master copy when replicating a document or spreadsheet.
VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.
