Pragmatic Excel - Automating Financial Statements

Who Would Be Interested in This Course:

Practitioners who will benefit from learning how to create monthly financial statements more effectively in Excel.

Topics Covered:

 •      Creating an in-cell list by way of Excel’s Data Validation feature.

 •      Improving the integrity of spreadsheets with Excel’s VLOOKUP function.

 •      Discovering how to incorporate Check Figures and Alarms into your work.

 •      Overcoming VLOOKUP’s quirks by using SUMIF to look up numeric values.

 •      Using Excel’s VLOOKUP function to look up an item description based on an input provided by the user.

 •      Using Conditional Formatting to draw attention to reports that don’t balance to the source data.

 •      Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.

 •      Learning why, in many cases, you should export reports intended for spreadsheet analysis to a .CSV file instead of an Excel workbook.

 •      Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt.

Learning Objectives:

·     Define how to create accounting reports for any month of the year on a single worksheet.

·     Apply Excel’s OFFSET function to isolate monthly amounts and sum YTD amounts.

·     Recall how the VLOOKUP function can be used to look up an item description based on user input.

Level:

Intermediate

Format:

On-Demand Webcast

Instructional Method:

Self-Study

Program Prerequisites:

Experience Working with Excel Spreadsheets Recommended

Advance Preparation:

None