Spreadsheet-Based Internal Controls

David Ringstrom
Instructor:
David Ringstrom
Duration:
60 Minutes
Product Id:
500671
Access:
6 months

More Trainings by this Expert

Price Details
$190 Recorded
Price Detail Options
Overview:

You'll not only learn how to control a user's actions, but you'll also learn how to make your spreadsheets as future-proof as possible. You'll also learn techniques that can significantly save you time when making a spreadsheet as "user-proof" as possible.

The session will lead off with a discussion of look-up formulas in Excel. Spreadsheet-based data is often dynamic, so functions such as VLOOKUP help spreadsheet stay resilient and minimize future maintenance. However, VLOOKUP can easily trigger errors such as #N/A or #REF!, so you’ll see how to contain these errors to avoid ripple effects through your entire workbook. One of the biggest risks within spreadsheets is the need to circle back and rewrite formulas because an unexpected amount of additional data was added to a spreadsheet. You’ll see how the Table feature in Excel can future-proof Excel spreadsheets so that you can create resilient set-and-forget formulas in Excel. Documenting proper use of spreadsheets can be tedious, and most users don’t have the time to reference external instructions. Instead you’ll see how to use Data Validation to control a user’s actions and document the spreadsheet all at once. Crafty users can by-pass Data Validation but Excel has a hidden audit step you can apply to easily determine when someone has “colored outside the lines” in your spreadsheet.

Locking spreadsheets down is often a tedious and frustrating exercise, because it’s important to lock all formulas and cells that you don’t want the user to change, but also be sure to unlock any cells you do want the user to be able to modify. You’ll learn now to toggle the locked/unlocked status of cells with a keystroke, and then see how to use Conditional Formatting in Excel to determine the locked/unlocked status of your entire spreadsheet at a glance.

David will teach from primarily from Excel 2010, but will demonstrate new features in Excel 2013 when warranted, as well as disclose any differences in Excel 2007. Coverage of Excel 2003 will be limited to questions raised by the audience.

Why should you Attend: Spreadsheets can pose catastrophic risks to organizations. J. P. Morgan Chase’s “London Whale” debacle that caused billions in trading losses resulted from a poorly designed Excel spreadsheet. Most spreadsheets present a free-for-all atmosphere where users can do anything they want in any worksheet cell. Fortunately there are a number of internal control measures that you can apply to Excel spreadsheets to simultaneously manage risk and help the end-user work more efficiently.

Learn internal control measures that you can apply immediately from an expert who has spent more than two decades immersed in spreadsheets of all shapes and sizes.

Areas Covered in the Session:

  • Improve the integrity of spreadsheets with Excel's VLOOKUP function.
  • Future-proof VLOOKUP by using Excel's Table feature instead of referencing static ranges.
  • Compare and contrast IFNA, IFERROR, and ISERROR, and see which versions of Excel support each of these worksheet functions.
  • Minimize ongoing spreadsheet maintenance with Excel's Table feature.
  • Specify a range of whole numbers that a user can enter in a worksheet cell.

Who Will Benefit:
  • CFOs
  • Controllers
  • Investment Analysts
  • Financial Analysts
  • Business Analysts
  • Forecasting & Planning
  • M&A Specialists (Mergers & Acquisitions)
  • Risk Managers
  • Strategic Planners
  • Capital Expenditure Planners
  • Cost Accountants
  • Managerial Accountants


Speaker Profile
David Ringstrom CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 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.


You Recently Viewed