Excel Agility: Financial Statement Automation Techniques
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
In this valuable course, you’ll learn from Excel expert David Ringstrom, CPA, how to build effective, easy-to-use financial statements. As an alternative to building cumbersome accounting worksheets for each month of the year, David demonstrates how to use Excel functions, including VLOOKUP, OFFSET, and SUM, to quickly create accounting reports that allow you to switch to any reporting period with only two mouse clicks. In addition, he shows you how to export data from your accounting package, improve the integrity of your spreadsheets, incorporate Check Figures and Alarms into you work, and more.
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 webcast.
Who Would Be Interested in This Course:
Practitioners who may benefit from creating monthly financial statements in Excel more effectively.
- Modifying queries that return data from other sources into an Excel worksheet.
- Exploring the nuances of data exported from accounting programs, such as extraneous worksheets, blank columns, and extraneous rows.
- Using Conditional Formatting to draw attention to reports that don’t balance to the source data.
- Returning data to an Excel spreadsheet from Microsoft Query.
- Using the Query Wizard within Microsoft Query to choose the relevant columns from your accounting report export.
- Creating an in-cell list by way of Excel’s Data Validation feature.
- Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values.
- Building and exporting a 12-month Profit & Loss report from QuickBooks Online and QuickBooks Desktop as a model for what to look for when exporting from other applications.
- Updating the financial report to use data for a new calendar or fiscal year.
- Extracting data dynamically for a given month or year to date by way of the OFFSET function.
- Using the SUMIFS function to sum values for a single account or a range of account numbers.
- Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt.
- Identify how to apply a variety of Excel functions—all focused on helping you use Excel far more effectively.
- Recall the purpose of the MONTH function in Excel.
- Define the purpose of the OFFSET function in Excel.
Introduction
Excel Versions 00:01:07
Source Data Exported from Quickbooks 00:02:23
Quickbooks Desktop 12 Month P&L 00:05:16
Quickbooks Online 12 Month P&L 00:13:15
Creating the Workbook 00:15:59
Including Microsoft Query 00:17:20
Looking Within an Excel Workbook 00:23:02
Choosing Columns 00:26:32
Returning Data to Microsoft Excel 00:31:00
Modifying an Excel Query 00:34:01
Modifying an Excel Query (cont.) 00:37:10
Helper Formulas 00:39:37
Creating an In-Cell List of Report Periods 00:46:32
Creating an In-Cell List of Report Periods (cont.) 00:51:58
MONTH Function 00:56:16
Data Worksheet: Current Month Column 01:02:12
Data Worksheet: YTD Column 01:06:20
Report Buildout 01:09:44
Consolidating/Departmental Report 01:16;08
Integrating Check Figures 01:21:54
Check Figure Alarm 00:24:09
Group/Ungroup Column 01:25:20
Data Connection Security Prompt 01:27:29
Swapping out Source Documents 01:28:48
Consolidate Multiple Entities 01:28:58
Consolidate Multiple Entities (cont.) 01:31:48
Consolidate Multiple Entities (cont.) 01:33:57
Consolidate Multiple Entities (cont.) 01:37:32
Presentation Closing 01:40:45
- Concatenation 00:40:25
- CONCAT Function 00:40:27
- Conditional Formatting 01:24:25
- COUNTA Function 01:22:10
- Data Validation 00:46:33
- Financial Statement 00:16:04
- IFERROR 00:43:14
- IF Function 01:16:52
- Income Statement 00:05:33
- INDEX Function 01:22:20
- LEFT Function 00:42:02
- Microsoft Query 00:17:20, 00:35:46, 01:30:03
- MID Function 01:16:41
- MONTH 00:56:20
- Name Box 00:52:30
- Name Manager 00:53:28
- OFFSET 01:02:14
- P&L Report 00:06:22
- Query Wizard 00:23:37
- SUM 01:06:41
- SUMIFS Function 01:10:20
- Trusted Document 01:28:03
- VALUE Function 00:42:31
- Workbook 00:16:07
- Union Query 01:30:12
CONCAT Function: The CONCAT function was introduced in the Office 365 version of Excel 2016. It's not available to users of perpetual licensed versions of Excel 2016 or earlier versions of Excel. This function supersedes the CONCATENATION function and is used to combine multiple pieces of text into one. An alternative to CONCAT and CONCATENATE is using ampersands to join pieces of text together into one.
COUNTA Function: The COUNTA function returns the number of blank cells within a given range of cells.
Concatenation: A technique that allows you to join two or more pieces of text together. Although its simplest to use the ampersand (&), you can also use the CONCATENATE function in Excel.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.
Financial Statement: Financial statements (or financial reports) are formal records of the financial activities and position of a business, person, or other entity. ... A balance sheet or statement of financial position, reports on a company's assets, liabilities, and owners equity at a given point in time.
IFERROR Function: Introduced in Excel 2007, the IFERROR function simplifies crafting formulas that may sometimes return an error, such as #N/A.
INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.
Income Statement: One of the three primary financial statements used to assess a company's performance and financial position (the two others being the balance sheet and the cash flow statement). The income statement summarizes the revenues and expenses generated by the company over the entire reporting period. (investinganswers.com)
LEFT Function: The Microsoft Excel LEFT function is a function which allows you to extract a substring from a string and starts from the leftmost character. This is a built-in function in excel which has been categorized as a String/Text Function.
MID Function: The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl". Extract text from inside a string. The characters extracted. =MID (text, start_num, num_chars)
MONTH : A worksheet function that extracts the month portion from a date.
Microsoft Query : A feature on Excel's Data menu that enables you to connect Excel spreadsheets to external data sources such as accounting software, databases, text files, Excel workbooks, and more.
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.
P&L Report: P&L is short for Profit and Loss. Such reports are also interchangeably called Income Statements.
Query Wizard: You can use Microsoft Query to retrieve data from external sources. By using Microsoft Query to retrieve data from your corporate databases and files, you don't have to retype the data that you want to analyze in Excel. You can also refresh your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet
SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.
Trusted Document : The Trusted Document feature in Excel 2010 and later allows you to suppress security prompts that arise from using data connections, workbook links, and/or macros within a given workbook. By marking a document as trusted Excel in turn suppresses the prompts. When a security prompt appears, do not click Enable Content, but instead click on File, and then choose Enable Content from the Info menu, and then use Enable All Content.
Union Query: The purpose of the SQL UNION and UNION ALL commands are to combine the results of two or more queries into a single result set consisting of all the rows belonging to all the queries in the union. When two Queries are combined, there could be rows of record that are exactly the same (duplicates).
VALUE Function: The Microsoft Excel VALUE function converts a text value that represents a number to a number. The VALUE function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
