Excel Skills for CPAs

1. MSF University Moore Stevens Frost, CPA Excel Skills for CPAs Special Topics in Excel Instructor: John Seydel, Ph.D. Arkansas State University

2. Topic Overview • Reference Materials • Spreadsheet Basics • The New Version: Excel 2007 • Some Advanced Excel Skills • Functions • Goal seeking • Decision tables • List management • PivotTables • Macros

3. First, Some Good References • Files for this workshop www.suse1.astate.edu/~jseydel/excel.php • Books • Absolute Beginner’s Guide to VBA • Paul McFredries • Que Publishing • Excel for Auditors • Dwayne K. Powell & Bill Jellen • See www.MrExcel.com/auditors.shtml • Microsoft Excel 2007 Inside Out • Mark Dodge & Craig Stinson • Microsoft Press • Running Microsoft Excel 2000 • Mark Dodge & Craig Stinson • Microsoft Press • AuditNet website www.auditnet.org/spreadsheets.htm • Excel addin: ActiveData for Excel (developed for auditors) • See www.informationactive.com • Will work with Excel 2007 and Vista

4. Excel Versions: 2003vs2007 • Major differences • The ribbon • Where things are • Files • Formats • Extensions (.xlsx) • Why use the newer version? • Why hold off on using the newer version? • Using the newer version as if it were the older • Collapsing the ribbon • Saving files under the old format

5. Spreadsheet Basics • Refer to GameCo1.xls • Guidelines for good design • Alternatives for copying/pasting • Edit | Copy . . . Edit | Paste • Using the fill handle • Paste Special | Values • Format painter • Cell referencing • Relative • Absolute • Mixed • Difference between rounding and display formatting • Freeze panes (see Expenses.xls) • Inserting/deleting worksheets • Cell protection • Add-ins

6. Beyond the Basics • Excel functions of particular interest • Financial • Incorporating logic • Decision support tools • Goal-seeking • Decision tables (aka data tables) • Managing large amounts of data • List management • PivotTables • If time: Macro programming

7. Important Excel Functions • Financial • A simple loan payment problem • Start with a new worksheet • Create label cells for Rate, NumPeriods, Loan • Name the adjacent cells and enter reasonable values • The payment function: PMT(Rate,NumPeriods,Loan) • Now, create label cells for CashFlows (range), Cost, Salvage, Life and then try the following • NPV(Rate,CashFlows) • IRR(CashFlows) • DDB(Cost,Salvage,Life,Period) • Others • VLOOKUP(Value,Table,ReturnColumn) • IF(Condition,ValueIfTrue,ValueIfFalse) • Statistical functions can also be very handy

8. Decision Support: Goal Seeking • Let’s consider a simple loan payment problem • Inputs: • Loan • Annual interest • Term of loan • Output: monthly payment; use PMT() • Suppose a given payment is desired, but one of the inputs (e.g., term) can be negotiated • Use Excel’s “Goal Seeking” tool

9. Decision Support: What-If Analysis • Consider the same situation • To see how a given value varies when we change one or two inputs, we can use Excel’s Data | Table feature • Essentially a guided search process • Try one value for an input • See what happens and then try another value • Keep track of results • Repeat until the optimum (or at least satisfactory) result is found • A decision table can be very useful • A basic model is developed • Results are then generated according to a predefined scheme • These results are then tabulated

10. Decision Tables:Creating a One-Way Table • First, create a table parameters section • Start – specifies the starting term • Step – dictates the term increment used • Then, create a column of terms to be considered • First cell references the “Start” parameter • Next cell references the previous and adds the “Step” parameter; copy this formula to the bottom of the Term column • This will be the “Input column” referred to by Excel • In the header cell for the next column, reference the original cell with the payment • Create the table • Select all of the input column, including the header cell, plus all of the next column (cells B10:C19 on the example) • Click on Data | Table from the Excel menu • Indicate which input on the original model is represented by the table’s input column (cell C5 on the example) • Cllick on OK

11. Decision Tables:A One-Way Table Example

12. Decision Tables:Some Final Comments • Should be dynamic • Input column and input row values should be driven by table of parameters (“start” and “step” values) • Most references don’t consider this and thus miss out on an easy and important means of incorporating flexibility • The Excel file for this example is available from the website • Consider also a two-way table

13. Working with Large Amounts of Data: List Management • Building and maintaining lists (Expenses.xls) • Using the Form tool • Sorting lists and other ranges • Filtering data • Using AutoFilter • More complex criteria • Subtotaling lists • If time • Functions • COUNTIF(critRange,criterion) • SUMIF(critRange,criterion,sumRange) • Validating data entry

14. Working with Large Amounts of Data: PivotTables • PivotTables (and PivotCharts) allow us to drill down and/or slice and dice large sets of data • Creating PivotTables (Books.xls) • Working through the wizard • Layout • Changing layouts • Rearrange row/column fields • Pivot (transpose) fields • Selecting specific field values • Formatting PivotTables • Grouping and ungrouping • Totals, grand totals, and subtotals

15. An Intro to Macro Programming • First, set macro security level to medium • Open MacroDemoA.xls • Select cell B3 • Start the macro recorder • Tools | Macro | Record • Provide some basic info • Click on OK • Perform the steps to be recorded • Enter =TODAY() but don’t hit Enter; use the checkmark • Copy then Edit | Paste Special | Values • Boldface and center • End the macro recording • Test the macro

16. Followup on the Macro • Process overview • Look at the code • Modify and save • Try again • Some things to try, time permitting • Remove boldfacing • Copy into MacroDemoB.xls • Add InputBox functionality

17. For More Info • John Seydel • Department of Computer & Information Technology • P.O. Box 130 • State University, AR 72467 • (870) 680-8072 • jseydel@astate.edu • Website for the workshop • www.suse1.astate.edu/~jseydel/excel.php

18. Appendix

19. Excel 2003

20. Excel 2007

21. Note: Microsoft & Gaming • It’s clear that Microsoft has been moving into the gaming arena • Major success with XBox • Now: Office 2007 (including Excel) • Represents a modern form of “Hide and Seek”!

22. Spreadsheet Design Guidelines • Organize the data, then build the model around the data. • Keep data separate from logic • Do not embed numeric constants in formulas. • Things which are logically related should be physically related. • Use formulas that can be copied. • Column/rows totals should be close to the columns/rows being totaled. • Use color, shading, borders and protection to distinguish changeable parameters from other model elements. • Use text boxes and cell notes to document various elements of the model. • Name cells and ranges • Document cell/range names

23. An Example of Mixed References

24. A Closer Look at Mixed References

25. Decision Support:The Basic Excel Model

26. Functions: Statistical • Refer to SkullNet.xls • Name A4:A53 as Range • Try the following • COUNT(Range) • SUM(Range) • AVERAGE(Range) • MEDIAN(Range) • STDEV(Range) • MIN(Range) • MAX(Range) • MODE(Range) • PERCENTILE(Range,%) • COUNTIF(Range,Criteria) • SUMIF(Range,Criteria)

27. By the Way, Have You Played with AutoSum? • Applies function to immediately adjacent range • Available functions • SUM() • AVG() • COUNT() • MAX() • MIN() • If no adjacent, waits for arguments

28. Decision Tables:The Need for a Two-Way Table • Often problems have more than one decision variable (i.e., controllable input) or more than one uncontrollable input • Alternatively, there may be a need to combine what-if analysis (altering the decision variable’s value) with sensitivity analysis (altering the value of an uncontrollable input) • This is where a two-way table can be helpful • Let’s reconsider the term alternatives, but now in light of possible interest rates • Two primary questions are to be answered • What term will result in the desired monthly payment? • Is it worthwhile to shop around for interest rates? • Note the example . . .

29. Decision Tables:A Two-Way Table Example

30. Decision Tables:Creating a Two-Way Table • First, create a table parameters section • Start – specify the starting values for both inputs being addressed • Step – specify the increments to be used for both inputs • Then, create a column of input values to be considered • This should be dynamic as with the one-way table • Leave the header cell blank • This will be the “Input column” referred to by Excel • Next, starting one column over from the input column’s header cell, create a row of input values to be considered • This should also be dynamic • This will be the “Input row” referred to by Excel • In the header cell above the first column, reference the original output cell • Create the table • Select all of the input column, including the header cell, plus all of the remaining columns (cells B10:H19 on the example) • Click on Data | Table from the Excel menu • Indicate which input on the original model is represented by the table’s input row (cell C4 on the example) input column (cell C5 on the example) • Cllick on OK