Information technology has significantly impacted internal auditors both in what we audit as well as tools we use on a daily basis. One of the first business applications developed for the personal computer was the spreadsheet. Lotus 1-2-3 spreadsheets were commonly used by auditors in the mid-1980's replacing columnar pads. Spreadsheets have been standard tools for auditors since the early days of personal computers. This resource page is devoted to both using spreadsheets in auditing as well as auditing spreadsheets. We will provide articles on the subject, sample templates, spreadsheet based accounting and audit tools and numerous resources for ensuring the validity of your spreadsheets. If you have any resources or tools to add to this page, contact us.
Using ActiveData for Excel: A video library of 14 of ActiveData's most powerful featuresVIDEO TUTORIALS:
Quickly analyze your data
- Summarize data by groups
- Perform complex age analysis
- Detect gaps and duplicates
- Quickly find statistical information
Save time manipulating data within your worksheets
Perform powerful audit and fraud detection techniques
Manage your Excel workbooks and worksheets efficiently
- Top Audit Tests Using ActiveData for Excel eBook
- Fraud Detection and Cash Recovery Using ActiveData for Excel Self Study Course
CAATTs Training and Guides
Excel Spreadsheets Shared by Auditors
Audit Satisfaction Survey Spreadsheet
Controls Test Spreadsheet
Data Warehouse Risk Analysis Spreadsheet
Help Desk Metrics Spreadsheet
Mass Transit Advertising Revenue Audit Program Spreadsheet
Pharmacy Review Spreadsheet
Risk Model (Stock) Spreadsheet
Statistical Sampling Spreadsheet
Travel Audit Program Spreadsheet
Trucking Operations Review Spreadsheet
Spreadsheet auditing Tools
Spreadsheet Studio is a collection of advanced audit and review tools for Excel spreadsheets.Spreadsheet Detective auditing program, that also includes links to other sites.
Spreadsheet Selector Object Library www.addix.com provides the ability to extract data and formulas from Excel spreadsheets without the need to open the spreadsheets using Excel.
Other spreadsheet resources
Excel-Easy site focuses on writing clear and concise tutorials (Introduction, Basics, Functions, Data Analysis and VBA) on how to use Excel
Add Business Rules ARulesXL turns Microsoft Excel into a powerful tool for delivering business rule applications that combine decision support with computational analysis.
Miricle Solutions - Web site that provides solutions for addressing spreadsheet errors. Includes articles, tools and more for internal auditors.
Spreadsheet123.com is a business website that offers spreadsheet and template solutions to business owners. There are hotel management related spreadsheets useful for auditors.
dslimited Excel add-in specialists provide links to spreadsheet products and services.
Excel-L mail list is a high volume email discussion forum on using MS
Excel. To subscribe, send an email to LISTSERV@PEACH.EASE.LSOFT.COM and put
the following in the body of mail (no subject line needed):
SUBSCRIBE EXCEL-L Firstname Lastname
Excel for Accountants is a site dedicated to helping accountants make profitable use of Excel.
Getting the OOPS! Out of Spreadsheets How to use built-in audit tools to minimize mistakes.
The New Guidelines for Writing Spreadsheets
Microsoft Excel Training & Tutoring, Add-ins, Templates & Software - large site housing countless amounts of free content for Microsoft Excel and business related software.
spreadsheetstyle.com is John Raffensperger's commercial auditing site which includes a free ReferenceBrowser add-in and a set of links with ratings of content and style that is a excellent portal to tutorials, advanced tips and tricks, free downloads and add-ins, education and research.
Spreadsheet Modelling Best Practice 100-page document (PDF) on spreadsheet style (look in the Downloads section).
Spreadsheet Modelling Institute of Chartered Accountants in Ireland Factsheet Series on IT
Spreadsheets for Accountants - PowerPoint presentation
Spreadsheet Research page Ray Panko’s (University of Hawaii) page is a primary resource about error rates in spreadsheets both from lab research and field studies.
Spreadsheet Studio is a collection of advanced audit and review tools for Excel spreadsheets.
The following spreadsheets were created in Microsoft Excel. The spreadsheets came from various web sites or contributions from other auditors and all are free to use. Although they are not copy protected that does not mean that they are not copyrighted. Please be advised of the following:
- The spreadsheets are designed to work with Windows.
- Download the xls files by right clicking your mouse and then choose to save the target file to your hard drive.
- Download ZIP files by left clicking your mouse. You need to have Winzip installed to unzip the file.
If you have questions about the following spreadsheets, please contact email@example.com
- Capital Budgeting Analysis (xls) - Basic program for doing capital budgeting analysis with inclusion of opportunity costs, working capital requirements, etc.
- Rating Calculation (xls) - Estimates a rating and cost of debt based on the coverage of debt by an organization.
- LBO Valuation (xls) - Analyzes the value of equity in a leverage buyout (LBO).
- Synergy (xls) - Estimates the value of synergy in a merger and acquisition.
- Valuation Models (xls) - Rough calculation for choosing the correct valuation model.
- Risk Premium (xls) - Calculates the implied risk premium in a market. (uses macro's).
- FCFE Valuation 1 (xls) - Free Cash Flow to Equity (FCFE) Valuation Model for organizations with stable growth rates.
- FCFE Valuation 2 (xls) - Free Cash Flow to Equity (FCFE) Valuation Model for organizations with two periods of growth, high growth initially and then stable growth.
- FCFE Valuation 3 (xls) - Free Cash Flow to Equity (FCFE) Valuation Model for organizations with three stages of growth, high growth initially, decline in growth, and then stable growth.
- FCFF Valuation 1 (xls) - Free Cash Flow to Firm (FCFF) Valuation Model for organizations with stable growth rates.
- FCFF Valuation 2 (xls) - Free Cash Flow to Firm (FCFF) Valuation Model for organizations with two periods of growth, high growth initially and then stable growth.
If you have questions about the following spreadsheets, please contact firstname.lastname@example.org
- Time Value (xls) - Introduction to time value concepts, such as present value, internal rate of return, etc.
- Lease or Buy a Car (xls) - Basic spreadsheet for deciding to buy or lease a car.
- NPV & IRR (xls) - Explains Internal Rate of Return, compares projects, etc.
- Real Rates (xls) - Demonstrates inflation and real rates of return.
- Template (xls) - Template spreadsheet for project evaluation & capital budgeting.
- Free Cash Flow (xls) - Cash flow worksheets - subsidized and unsubsidized.
- Capital Structure (xls) - Spreadsheet for calculating optimal capital structures using different percents of debt.
- WACC (xls) - Calculation of Weighted Average Cost of Capital using beta's for equity.
- Statements (xls) - Generate a set of financial statements using two input sheets - operational data and financial data.
The following spreadsheets are from various other sites.
- Bond Valuation (zip) - Calculates the value or price of a 25 year bond with semi-annual interest payments.
- Buyout (zip) - Analyzes the effects of combining two companies.
- Cash Flow Valuation (zip) - Walks through a valuation of cash flows under three models- capital cash flows, equity cash flows, and free cash flows.
- Financial Projections (zip) - Spreadsheet model for generating projected financials along with valuation based on WACC.
- Leverage (zip) - Shows the effects on Net Income from using debt (leverage).
- Ratio Calculator (zip) - Calculates a standard set of ratios based on input of financial data.
- Stock Value (zip) - Calculates expected return on stock and value based on no growth, growth, and variable growth.
- CFROI (xls) - Simplified Cash Flow Return on Investment Model from HOLT Associates.
- Financial Charting (zip) - Add on tool for Excel 97, consists of 6 files.
- Risk Analysis (exe) - Analysis and simulation add on for excel, self extracting exe file.
- Black Scholes Option Pricing (zip) - Excel add on for the pricing of options.
- Cash Flow Matrix - Basic cash flow model.
- BFAT - Business Financial Analysis Template for start-up businesses from Small Business Technology Center.
- Forex (zip) - Foreign market exchange simulation for Excel
- Hamlin (zip) - Financial function add-on's for Excel
- Tanly (zip) - Suite of technical analysis models for Excel
- Financial History Pivot Table - Microsoft Financials
- Income Statement What If Analysis - Microsoft Financials
- Exl-Plan Super (zip) - Generates financial projections including ratio analysis. Uses excel templates with numerous menu options.
- SLG Ratio Master (exe) - Excel workbook for creating 25 key performance ratios.
- DCF - Menu driven Excel program for Discounted Cash Flow Analysis; from the book Analysis for Financial Management by Robert C. Higgins.
- History - Menu driven Excel program for Historical Financial Statements; from the book Analysis for Financial Management by Robert C. Higgins.
- Proforma - Menu driven Excel program for Pro-forma Financial Statements; from the book Analysis for Financial Management by Robert C. Higgins.
- Business Valuation Model (zip) - Set of tabbed worksheets for generating forecast / valuation outputs. Includes instruction sheet. Bizpep
- LBO Model - Excel model for leveraged buy-outs - John P. Burns
- Comparable Companies - Excel valuation model comparing companies - John P. Burns
- Combination Model - Excel valuation model for combining companies - John P. Burns
- Balanced Scorecard - Set of templates for building a balanced scorecard.
- Present Value Tables (rtf) - Set of present value tables written in rich text format, compatible with most word processors. Includes examples of how to use present value tables.
Additional Spreadsheet Articles/Resources
Is this Spreadsheet a Tax Evader? An article by Ray Butler on UK Customs audit of VAT return spreadsheets (look in the Downloads section).
Back to top
Download tick marks: Download Dr. Lehman's personal.xls file and library of tick marks.
Make Your Mark in Spreadsheets: Document files with comments and tick marks Electronic comments and explanations can be attached easily to complex spreadsheets to help in the audit and review of financial statements. Journal of Accountancy Article - January 2001
JaxWorks Small Business Spreadsheet Factory is an excellent resource with free spreadsheets for business analysis. I highly recommend auditors bookmark this site!
Targeting Spreadsheet Data Excel’s AutoFilter feature can gather together widely dispersed data in a spreadsheet so you don’t have to manually search through the entire file. The tool can save you hours of work. Journal of Accountancy Article-June 2002
From time to time, The CPA Journal presents software reviews, spreadsheet templates, supplemental material and other information helpful to computer users. Click on the filename to retrieve the file.
Property Transactions at Year-end: An Update to the Worksheet
Approach for Capital Gain Rate Differentials | (article)
Supplemental Case Study: cgrd_cs.htm
Tables for Tests of Controls | (article)
Filename: controls.xls [310K]
Spreadsheet Template for Installment Loam Amortization Tables | (article)
Filename: loanamor.xls [108K]
A Spreadsheet Program for Bond Discount and Premium
AuditAid: A tool for computerized audit sampling
New Competent Authority Procedures
Accounting: Valuing Stock Options: A Revised