to accompany Accounting Principles, - Wiley: Home

165 Pages·2010·4.56 MB·English

by  Rex A Schildhouse

©
Primer on Using Excel in Accounting
By Rex A Schildhouse, LCDR, U.S. Navy, Retired, M.B.A.
Miramar College, San Diego Community College District, San Diego, California

to accompany

Accounting Principles,

Tenth Edition

Jerry J. Weygandt, PhD, CPA, Arthur Andersen Alumni Professor of Accounting,
University of Wisconsin, Madison, Wisconsin

Donald E. Kieso, PhD, CPA, KPMG Peat Marwick Emeritus Professor of Accountancy,
Northern Illinois University, DeKalb, Illinois

Terry Warfield, PhD, CPA, PricewaterhouseCoopers Research Scholar,
University of Wisconsin, Madison, Wisconsin

Table of Contents

The Acknowledgement of Copyrights, Patents, and Trademarks ..................................................... vi
NOTE TO THE INSTRUCTOR ...................................................................................................... vii
INTRODUCTION .......................................................................................................................... viii
Chapter 1 ..................................................................................................................................................... 10
SOME BASICS OF WINDOWS 7 ........................................................................................................ 10
Chapter Outline .................................................................................................................................... 10
Microsoft Windows Versions ..................................................................................................... 10
Windows Explorer ...................................................................................................................... 10
Copying the Data Files to the Hard Drive ................................................................................... 11
Copying the Data Files to Personally Transportable Media ....................................................... 12
Renaming Files within Windows ................................................................................................ 12
Search within Windows .............................................................................................................. 12
File Shortcuts .............................................................................................................................. 13
Chapter 2 ..................................................................................................................................................... 15
INSTALLING, FINDING, AND SHORTCUTS TO MICROSOFT OFFICE ....................................... 15
Chapter Outline .................................................................................................................................... 15
Terms and Conventions of this Text ........................................................................................... 15
The Differences between Versions of Microsoft Office ............................................................. 17
Purchasing Microsoft Office ....................................................................................................... 18
Installing Microsoft Office.......................................................................................................... 18
Opening Elements of Microsoft Office ....................................................................................... 19
Chapter 3 ..................................................................................................................................................... 21
BASICS OF EXCEL ............................................................................................................................... 21
Chapter Outline .................................................................................................................................... 21
Excel Basics ................................................................................................................................ 21
Opening Excel ............................................................................................................................. 22
Workbooks and Worksheets within Excel .................................................................................. 22
Opening Excel Files .................................................................................................................... 23
The Exercise and Problem Templates ......................................................................................... 25
Demo Worksheet ........................................................................................................................ 26
Excel Worksheets ........................................................................................................................ 27
Pop-Up Menus ............................................................................................................................ 28
Drop-Down Menu ....................................................................................................................... 29
New Workbook ........................................................................................................................... 30
Help ............................................................................................................................................. 30
Saving Excel Files ....................................................................................................................... 31
Formula Bar ................................................................................................................................ 32
Row and Column Headers .......................................................................................................... 32
File Extensions within Excel....................................................................................................... 33
Sizing Workbook Presentations .................................................................................................. 34

i
Table of Contents
Chapter 4 ..................................................................................................................................................... 35
BASIC EXCEL DATA ........................................................................................................................... 35
Chapter Outline .................................................................................................................................... 35
Basic Data Entry ......................................................................................................................... 35
Sum Formula ............................................................................................................................... 37
Basic Formulas ............................................................................................................................ 38
“Look to” Formula ...................................................................................................................... 39
Mathematical Order of Operation ............................................................................................... 39
Nested Parentheses ...................................................................................................................... 39
Recently Used File List ............................................................................................................... 40
Undo and Redo ........................................................................................................................... 41
Add-ins ........................................................................................................................................ 41
Chapter 5 ..................................................................................................................................................... 43
COPY, CUT, PASTE, CLEAR, AND DELETE .................................................................................... 43
Chapter Outline .................................................................................................................................... 43
Copying and Pasting ................................................................................................................... 43
Copying a Formula ..................................................................................................................... 45
Absolute Reference ..................................................................................................................... 46
Cut Command ............................................................................................................................. 48
Clear and Cut .............................................................................................................................. 48
Delete and Delete ........................................................................................................................ 48
Chapter 6 ..................................................................................................................................................... 50
LOCATIONS AND LOCATING ........................................................................................................... 50
Chapter Outline .................................................................................................................................... 50
Go To .......................................................................................................................................... 50
Find ............................................................................................................................................. 50
Find and Replace ......................................................................................................................... 51
Named Ranges ............................................................................................................................ 53
Chapter 7 ..................................................................................................................................................... 56
CUSTOMIZING EXCEL........................................................................................................................ 56
Chapter Outline .................................................................................................................................... 56
Excel Defaults ............................................................................................................................. 56
“Save As” to Change File Locations and Names ........................................................................ 57
Excel Workbook / File Naming Recommendations .................................................................... 58
Chapter 8 ..................................................................................................................................................... 59
PRESENTATION ................................................................................................................................... 59
Chapter Outline .................................................................................................................................... 59
Comments ................................................................................................................................... 59
Column and Row Size ................................................................................................................ 60
Charting....................................................................................................................................... 61
Pivot Tables ................................................................................................................................ 63
Protection .................................................................................................................................... 65
Read Only Files and Templates .................................................................................................. 66
Drawing on Worksheets .............................................................................................................. 67
Macros......................................................................................................................................... 69
Macros on Objects ...................................................................................................................... 70

ii
Table of Contents
Chapter 9 ..................................................................................................................................................... 72
PRINTING ISSUES ................................................................................................................................ 72
Chapter Outline .................................................................................................................................... 72
Page Setup ................................................................................................................................... 72
Page Break Preview .................................................................................................................... 73
Print Area .................................................................................................................................... 74
Print Preview ............................................................................................................................... 76
Printing ........................................................................................................................................ 77
Chapter 10 ................................................................................................................................................... 78
INVENTORY .......................................................................................................................................... 78
Chapter Outline .................................................................................................................................... 78
Filter ............................................................................................................................................ 78
Sort .............................................................................................................................................. 80
Master Sort Column .................................................................................................................... 81
SumIf........................................................................................................................................... 81
CountIf ........................................................................................................................................ 82
Subtotal ....................................................................................................................................... 82
Vlookup....................................................................................................................................... 86
Chapter 11 ................................................................................................................................................... 88
DISPLAY ................................................................................................................................................ 88
Chapter Outline .................................................................................................................................... 88
Freeze Panes ................................................................................................................................ 88
Split Pane .................................................................................................................................... 89
Conditional Formatting ............................................................................................................... 90
Displaying Zero Values .............................................................................................................. 91
Gridlines on the Screen ............................................................................................................... 92
Hiding Columns and Rows ......................................................................................................... 92
Hidden ......................................................................................................................................... 93
Indent within a Cell ..................................................................................................................... 95
Truncate ...................................................................................................................................... 95
Workspaces ................................................................................................................................. 96
Chapter 12 ................................................................................................................................................... 98
ANSWERS .............................................................................................................................................. 98
Chapter Outline .................................................................................................................................... 98
Formula Auditing ........................................................................................................................ 98
Formatting Cells .......................................................................................................................... 99
Merge Cells ............................................................................................................................... 102
Formatting within a Cell ........................................................................................................... 103
Solver ........................................................................................................................................ 103
Null Value ................................................................................................................................. 104
Or .............................................................................................................................................. 105
And ............................................................................................................................................ 105
If Statement ............................................................................................................................... 106
Chapter 13 ................................................................................................................................................. 108
TEXT ..................................................................................................................................................... 108
Chapter Outline .................................................................................................................................. 108
Concatenate ............................................................................................................................... 108
Text to Columns ........................................................................................................................ 110
Paste Special ............................................................................................................................. 111
Today and Now ......................................................................................................................... 112

iii
Table of Contents
Chapter 14 ................................................................................................................................................. 113
DEPRECIATION .................................................................................................................................. 113
Chapter Outline .................................................................................................................................. 113
Asset Acquisition Sheet ............................................................................................................ 113
Depreciation .............................................................................................................................. 114
Straight-Line Depreciation ........................................................................................................ 114
Declining-Balance Depreciation ............................................................................................... 115
Variable Declining Balance Depreciation ................................................................................. 116
Sum-of-Years’-Digits Depreciation .......................................................................................... 117
Units-of-Activity Depreciation ................................................................................................. 117
Chapter 15 ................................................................................................................................................. 118
LOANS AND THE TIME VALUE OF MONEY ................................................................................ 118
Chapter Outline .................................................................................................................................. 118
Cash Flow within Formulas ...................................................................................................... 118
Loan Payments .......................................................................................................................... 119
Payment to Principal ................................................................................................................. 121
Interest Payment ........................................................................................................................ 122
Cumulative Payment to Principal ............................................................................................. 123
Cumulative Payment to Interest ................................................................................................ 123
Present Value ............................................................................................................................ 124
Future Value .............................................................................................................................. 125
Bond Table ................................................................................................................................ 126
Chapter 16 ................................................................................................................................................. 127
ADVANCED EXCEL ........................................................................................................................... 127
Chapter Outline .................................................................................................................................. 127
Average and AverageA ............................................................................................................. 127
Convert ...................................................................................................................................... 127
Embedded Formulas ................................................................................................................. 129
Intermediate Formulas .............................................................................................................. 130
Keystrokes and Shortcuts .......................................................................................................... 132
Linking Worksheets and Workbooks ........................................................................................ 133
Chapter 17 ................................................................................................................................................. 135
MICROSOFT WORD ........................................................................................................................... 135
Chapter Outline .................................................................................................................................. 135
Word File Storage ..................................................................................................................... 135
Word Tables .............................................................................................................................. 136
Word Table Formulas ............................................................................................................... 137
Excel Tables into Word............................................................................................................. 138
Chapter 18 ................................................................................................................................................. 141
TEXAS INSTRUMENTS BA II PLUS ................................................................................................ 141
Chapter Outline .................................................................................................................................. 141
Texas Instruments BA II Plus Basics ........................................................................................ 141
Days Between Dates with the BA II Plus ................................................................................. 143
Storing Values in the BA II Plus ............................................................................................... 144
Balloon Payments with the BA II Plus ..................................................................................... 146
Time Value of Money with the BA II Plus ............................................................................... 146
Present Value of a $1 with the BA II Plus ................................................................................ 147
Future Value of a $1 with the BA II Plus ................................................................................. 147
Present Value of an Annuity of a $1 with the BA II Plus ......................................................... 148
Future Value of an Annuity of a $1 with the BA II Plus .......................................................... 149
Other BA II Plus Functions ....................................................................................................... 149
iv
Table of Contents
Chapter 19 ................................................................................................................................................. 150
HEWLETT-PACKARD HP-12C .......................................................................................................... 150
Chapter Outline .................................................................................................................................. 150
Hewlett-Packard HP-12C Basics .............................................................................................. 150
Days Between Dates with the HP-12C ..................................................................................... 152
Storing Values in the HP-12C ................................................................................................... 153
Balloon Payments with the HP-12C ......................................................................................... 154
Time Value of Money with the HP-12C ................................................................................... 155
Present Value of a $1 with the HP-12C .................................................................................... 156
Present Value of an Annuity of a $1 with the HP-12C ............................................................. 157
Future Value of an Annuity of a $1 with the HP-12C .............................................................. 158
Other HP-12C Functions ........................................................................................................... 158
Index .......................................................................................................................................................... a
v
The Acknowledgement of Copyrights, Patents, and Trademarks
This text addresses Microsoft Windows, Microsoft Office Pro, and Microsoft Office, primarily Excel,
additionally a little about Microsoft Windows XP. Microsoft Windows XP, Microsoft Windows,
Microsoft Office Pro and Microsoft Office and their components are products of Microsoft Corporation of
Redmond, Washington, U.S.A. Applications, names, programs, and titles such as “Microsoft,” “Microsoft
Windows XP,” “ Microsoft Windows,” “Microsoft Office Pro,” “Microsoft Office,” “Microsoft Word,”
“Microsoft Excel,” “Microsoft Access,” “Microsoft PowerPoint,” and the names “Windows,” “Office,”
“Word,” “Excel,” “Access,” and “PowerPoint” as application names are protected by the copyrights,
trademarks and / or patents of the Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-
6399 under U. S. and international law.
This text also addresses the Hewlett-Packard HP-12C Programmable Financial Calculator. Hewlett-
Packard, HP-12C, and Programmable Financial Calculator are items subject to the copyrights, trademarks
and / or patents of the Hewlett-Packard Company, 1000 NE Circle Blvd, Corvallis, OR 97330
Additionally this text addresses the Texas Instruments TI BA II Plus Advanced Business Analyst
Calculator. Texas Instruments, TI, BA II Plus, BA II+, and Advanced Business Analyst Calculator are
items subject to the copyrights, trademarks and / or patents of the Texas Instruments Corporation,
7800 Banner Dr., Dallas, TX 75251

vi
NOTE TO THE INSTRUCTOR
The exercises and problems as Microsoft Excel templates are provided on the student resources web site
for the textbook. All the selected exercises and problems are contained within a single file for each
chapter. The exercises and problems in the textbook are accompanied by a Microsoft Excel “X”. The
template format provides basic guidance in solving the exercises and problems and contains keys for
account title placement, value placement, and formula placement. This format is intended to provide your
students a structured environment to reduce the time required to accomplish the exercise or problem
without reducing the educational challenge and opportunity afforded by the exercise or problem. Very
few account titles and few account values are given in this format. Each student template file contains an
instructions worksheet and an area for the student to identify him or herself, the date, and the instructor,
and the course at the top of the template. This identification information is printed at the top of each page
if multiple pages are required by the exercise or problem. Each template is also set up with footers stating
the file identification, the page number of page numbers, the time, and the date printed to assist in
compiling the pages you may receive.
Solutions for the templates are available as downloads from the textbook’s instructor resources web
site. Each solution template contains the instruction sheet given to the student, the exercise or problem as
given to the student, and the solution. The solution template matches the placement of data in the student
template and closely correlates to the textbook solutions manual. This format is intended to assist you in
the evaluation of the student’s accomplishments without presenting an alternative to textbook presented
methodology or solutions manual materials. Due to significant digit differences between the textbook, the
solutions manual, and Excel, there may be slight differences in values. Most of these are pointed out in
the templates.
vii
INTRODUCTION
This book is written to accompany Accounting Principles, Tenth edition, by Jerry J. Weygandt, Donald E.
Kieso, and Terry D. Warfield. Throughout the book numerous subjects are addressed intended to increase
your ability and skills in using Microsoft Excel or most other spreadsheet applications in the
accomplishment of academic and professional tasks. Many of the later chapters assume that you fully
understand and have mastered the skills presented in the earlier chapters. Because of this assumption, it is
recommended that even proficient users of Excel read the book as they accomplish the assigned work.
This text addresses Microsoft Office 2007, focused on Excel 2007. Word 2007 is also addressed to
some degree. The Hewlett-Packard HP-12C Programmable Financial Calculator and Texas Instruments
TI II Plus Advanced Business Analyst Calculator are addressed in the last chapters.
There are numerous exercises and problems within the Accounting Principles, Tenth edition, by Jerry
J. Weygandt, Donald E. Kieso, and Terry D. Warfield that have been selected for presentation as Excel
templates. These exercises and problems, put into a single file for each chapter, have been restructured to
allow you to use the “look to” and “copy and paste” capabilities of Excel. The restructuring does not
change the exercise or problem material and your end result will be the same whether you utilize the
textbook as a source document, the exercise and problems information from the template, or from this
text. The chapter files are available on the student resources web site for this text. Each chapter’s file
contains the selected exercises and problems which are identified on their own worksheets within the
chapter file. For Chapter 1 Exercise E1-15, the fifteenth exercise within Chapter 1, the chapter file name
is Kimmel_Financial_6e_Excel_Templates_Ch01.xlsx, the worksheet will be titled E1-15. For Problem
P3-1A, the first problem in the third chapter the chapter file name is
Kimmel_Financial_6e_Excel_Templates_Ch03.xlsx and the worksheet title is P3-1A. All of the data files
were constructed in Microsoft Excel 2007 utilizing the “.xlsx” extension. The majority of commands and
capabilities are common to many of the various versions of Excel as well as other spreadsheet
applications. For earlier versions of Excel you may be able to download a compatibility pack from
Microsoft at http://support.microsoft.com/kb/923505 which may allow you to open the “xlsx” extension
files with your version of Excel.
Many of the specific subjects of this text have additional data files associated with them to further
show or demonstrate the capabilities of Excel. The data file associated with the “Paste” function would be
titled as “Paste” for example while the data file for “Pivot Tables” would be “Pivot Tables.” The title of
the reference file will be clearly provided in the subject section. These files are available on the textbook
student resources web site.
Numerous screen prints have been included to clarify the presentation of the material. If you need
assistance on a particular issue you can also accomplish the screen print function and take the document
into the classroom, to your information technologies assistance center, or attach it to an email. To perform
a screen print most reliably, first, open the application that you wish to receive the screen print. This
application is usually Microsoft Word and will be used for the explanation. Then return to the application
or screen that you want to screen print. Press the “PrtScn” (Print Screen) key on the keyboard. This key is
usually just above the Insert key on the keyboard but may be elsewhere on the keyboard. The image of the
screen is now held in the Windows Clipboard. Reselect the receiving application, Word in this case, click
into an open document to place the cursor and use the keystrokes Ctrl-V or click the “Paste” icon and the
image should be pasted into the document. Now you can save the document as a file for later use and/or
print it to show later or to document an event. Applications such as Microsoft Word, Windows WordPad,
Windows Paint, Microsoft Excel, and Microsoft Access all accept screen prints. Windows Notepad does
not accept screen prints.
viii