Excel for Finance and Accounting Advanced Tutorial

Summary

Elissa Smith presents a practical Excel tutorial focused on visualizing and analyzing financial and accounting data. The course covers chart types, PivotTables and PivotCharts, data sorting and filtering, regression analysis, Excel’s Analyze Data feature, stock charts, and a basic purchase price variance calculation.

Charts and data visualization

• Bar and column charts: Useful for comparing items or showing changes over time. Select only the relevant headings and values—not extra titles or totals that could distort the chart—then insert a chart. Embedded charts can be resized and moved. The goal is to communicate the data clearly without requiring viewers to inspect the underlying spreadsheet.
• Area charts: Similar to line charts, but the area beneath each line is filled. They help show trends, though line charts may be preferable for printing. Excel’s chart elements, styles, colors, and filters can be used to adjust a chart. Filtering hides selected series without deleting the underlying data.
• Pie and doughnut charts: Pie charts show parts of a whole and work best with simple data. Doughnut charts can represent multiple rows of data and are often more suitable for complex comparisons.
• Combo charts: Combine chart types, such as columns and a line, to compare related measures—for example, sales totals and commission fees. Excel lets users choose which measure appears as a line or column and whether to use a secondary axis.
• Waterfall charts: Show how positive and negative values affect a running total. The instructor demonstrates selecting individual data points and marking them as totals so the chart accurately represents how values contribute to the result.
• Football field charts: Floating-bar or stock charts used to display ranges from several valuation methods side by side. The tutorial demonstrates adding category labels, removing unnecessary legends and gridlines, labeling high and low values, formatting those labels as currency, adjusting axes, and adding a reference line such as a target share price.
• Sparklines: Small charts placed inside cells to reveal patterns alongside the data. Excel offers line, column, and win/loss sparklines; win/loss is suited to positive and negative values. Sparklines can highlight high and low points. To remove one, use the Sparkline ribbon’s Clear command rather than pressing Delete.
• Stock charts: Visualize stock-price information over time, such as high, low, and closing prices. The instructor demonstrates choosing a chart that matches the available data and adding and formatting a trendline.

PivotTables and PivotCharts

• Before creating a PivotTable, ensure the source data has column headers and no entirely blank rows. Blank cells are acceptable, but a fully blank row can interrupt Excel’s recognition of the data range.
• To create a PivotTable, click in the source data and choose Insert PivotTable or Recommended PivotTables. Excel can place the result on a new worksheet, leaving the source data unchanged.
• Build the analysis by dragging fields into the Rows, Columns, and Values areas. Value fields can be summarized with functions such as Sum or Average and formatted—for example, as currency.
• PivotTables can be rearranged quickly by moving fields between areas. If the source data changes, the PivotTable does not update automatically; right-click it and choose Refresh.
• A PivotChart uses selected fields from a large dataset to create a chart without trying to plot every column. Drag fields into the chart’s axes, legend, and values areas. The associated PivotTable updates as the fields change. Refresh the PivotChart or its PivotTable when the source data changes.

Sorting and filtering

• Apply sorting and filtering to the full dataset rather than selecting isolated cells, so each record’s row stays together.
• Use Data → Filter to add dropdowns to column headers. These dropdowns allow ascending or descending sorts and filters that include or exclude selected items.
• For numerical criteria, use Number Filters to set conditions such as showing values at or above 500. Use the Clear command or the relevant column dropdown to remove filters.
• PivotTables also provide sorting and filtering through their row and column dropdowns. A field can be added to the PivotTable’s Filters area to filter the entire table. Select multiple items when needed, and use Select All to clear the filter.

Regression analysis

• Regression estimates the relationship between a dependent variable and one or more independent variables and can help model how they relate.
• To use Excel’s regression tool, enable the Analysis ToolPak through File → Options → Add-ins, then select the add-in. The Data Analysis command will then be available on the Data ribbon.
• Choose Regression, specify the Y input range (the dependent variable) and X input range (the independent variable), indicate whether the ranges contain labels, and choose an output location.
• The output includes statistics such as Multiple R, which indicates the strength and direction of a linear relationship, and R Square, which indicates how much of the variation in the dependent variable is explained by the model. The output also contains an ANOVA section and other statistical measures.
• Excel’s scatter charts can help visualize the relationship, and a trendline can be added. The instructor cautions that interpreting regression output involves statistical concepts and requires care.

Analyze Data

• Excel’s Analyze Data feature can suggest questions, PivotTables, and charts based on a selected dataset.
• Users can choose a suggested analysis or type a question, such as asking for the average of a field. Excel can insert the resulting PivotTable or chart into the workbook.
• Suggestions may not always be useful, but the tool can provide starting points for exploring data.

Purchase price variance (PPV)

• Purchase price variance measures the difference between the standard and actual purchase prices in relation to quantity. The tutorial’s example formula is:

(Standard purchase price − actual purchase price) ÷ quantity

• Enter the formula with parentheses, format the result as a percentage, and fill it down for other rows.
• The instructor describes the calculation as a basic introduction to pricing variance and notes that favorable variances can indicate savings. A fuller analysis may involve additional factors.

Speakers and sources featured

• Elissa Smith — Instructor and primary narrator; identified as an IT facilitator.
• Learnit Training / Learnit — Training channel referenced in the introduction and closing.
• Microsoft Excel — Software demonstrated throughout the tutorial.