CPE Catalog & Events

External data Text files Data preparation Accounting system data Getting graphic

Selected shortcuts Named references tables Data validation Conditional formatting basics

Better summing Skinny row Hide worksheets Highlight input cells Workbook organization Worksheet organization Nested functions Selection groups Workbook design principles

Conditional summing basics with SUMIFS Remove duplicates Lookup basics Improving VLOOKUP with MATCH Improving VLOOKUP with VALUE and TEXT Moving beyond VLOOKUP with INDEX

Trap errors with IFERROR The IF function List comparisons The ISERROR function Multicolumn list comparisons with COUNTIFS Indentation Perform lookups with SUMIFS

Determine the last day of the month with EOMONTH Date parts: MONTH and YEAR Build your own date with DATE Concatenation basics Improve SUMIFS with CONCATENATE

Dynamic Headers Horizontal Reports Mapping Tables Data Validation and Reporting Improving Error Check with Boolean Values and the Logical AND Function

PivotTable basics Row fields Value fields Column fields Filter fields Updating data Groups and subtotals Value formats

Report type comparison options Report layout and design Multiple value fields Calculated fields Calculated items

Show values as Sorting Filtering Hybrid reports with GETPIVOTDATA Web data

Leadership Development Firm Operations

Expert Witness Ethics and Responsibilities Civil Litigation and Discovery Process Deposition Techniques and Testimony Strategies

artificial intelligence in accounting financial analysis financial reporting GAAP compliance automation for accountants

Explore functions in Excel to accurately calculate the number of workdays per period for the financial model. Use functions to detect whether there has been a scheduling issue with any of the contractors listed on payroll. Evaluate ways to schedule payables for contractors that mimic how these costs impact the company from a cash perspective. Summarize accrued amounts for cost of goods sold (COGS) and selling, general & administrative (SG&A) for use on the company's income statement.

Understand how corkscrews can be used to monitor capital expenditure and track property, plant & equipment (PP&E) totals in a financial model. Use corkscrews to also monitor the company's cash balance and the amount of borrowing on a revolving line of credit. Calculate the amount of cash that may be needed from a revolving line of credit or excess cash amounts that could be used to pay down any existing revolver balance. Install model checks to determine whether or not the limit on a revolving line of credit has been exceeded.

Understand the difference between current & deferred income taxes and why both are needed in FP&A models. Use consolidated monthly financial statements to determine the company's health in terms of profitability and cash flow Use Excel functions to aggregate monthly figures into quarterly and annual estimates.

Prepare FP&A financial model data to present it in rich and compelling graphs for a dashboard. Use Excel formulas and intended errors to control which line items appear on the graphs and charts. Present monthly, quarterly, and annual data for actuals and forecasts and compare these to a budget forecast.

Review the optimal layout for FP&A models designed to review and analyze monthly data. Compare FP&A model design to other financial models to understand the differences and the unique requirements of FP&A models. Learn the best and most common Excel formulas and functions used in FP&A models.

Analyze monthly data to determine whether adjustments are needed to financial forecasts. Compare actual results to financial forecasts and budget estimates on a monthly basis. Understand best practices for rolling monthly financial models forward for a new fiscal year.

Understand various levels for applying protection in FP&A financial models in order to maintain financial model integrity. Configure protection to allow full access to model designers but restrict access to only inputs for others who are only updating for actual results. Review the best approaches to print settings in order to present the model findings to important stakeholders.