to accompany Financial Accounting, Seventh Edition

169 Pages·2012·4.49 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

Financial Accounting,
Seventh Edition

Paul D. Kimmel, PhD, CPA, Associate Professor of Accounting,
University of Wisconsin – Milwaukee, Milwaukee, Wisconsin

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
Copyright © 2012 Rex A Schildhouse

Copyright © 2012 John Wiley & Sons, Inc. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system or
transmitted in any form or by any means, electronic, mechanical, photocopying,
recording, scanning or otherwise, except as permitted under Sections 107 or 108
of the 1976 United States Copyright Act, without either the prior permission of
the Publisher, or authorization through payment of the appropriate per-copy fee
to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923,
[phone], fax [phone]. Requests to the Publisher for permission
should be addressed to the Permissions Department, John Wiley & Sons, Inc.,
111 River Street, Hoboken, NJ 07030, [phone], fax [phone].
Table of Contents

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

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

ii
Table of Contents
Chapter 9 ..................................................................................................................................................... 65
PRINTING ISSUES ................................................................................................................................ 65
Chapter Outline .................................................................................................................................... 65
Page Setup ................................................................................................................................... 65
Page Break Preview .................................................................................................................... 66
Print Area .................................................................................................................................... 67
Print Preview ............................................................................................................................... 69
Printing ........................................................................................................................................ 70
Chapter 10 ................................................................................................................................................... 71
INVENTORY .......................................................................................................................................... 71
Chapter Outline .................................................................................................................................... 71
Filter ............................................................................................................................................ 71
Sort .............................................................................................................................................. 73
Master Sort Column .................................................................................................................... 74
SumIf........................................................................................................................................... 74
CountIf ........................................................................................................................................ 75
Subtotal ....................................................................................................................................... 75
Vlookup....................................................................................................................................... 79
Chapter 11 ................................................................................................................................................... 81
DISPLAY ................................................................................................................................................ 81
Chapter Outline .................................................................................................................................... 81
Freeze Panes ................................................................................................................................ 81
Split Pane .................................................................................................................................... 82
Conditional Formatting ............................................................................................................... 83
Displaying Zero Values .............................................................................................................. 84
Gridlines on the Screen ............................................................................................................... 85
Hiding Columns and Rows ......................................................................................................... 85
Hidden ......................................................................................................................................... 86
Indent within a Cell ..................................................................................................................... 88
Truncate ...................................................................................................................................... 88
Workspaces ................................................................................................................................. 89
Chapter 12 ................................................................................................................................................... 91
ANSWERS .............................................................................................................................................. 91
Chapter Outline .................................................................................................................................... 91
Formula Auditing ........................................................................................................................ 91
Formatting Cells .......................................................................................................................... 92
Merge Cells ................................................................................................................................. 95
Formatting within a Cell ............................................................................................................. 96
Solver .......................................................................................................................................... 96
Null Value ................................................................................................................................... 97
Or ................................................................................................................................................ 98
And .............................................................................................................................................. 98
If Statement ................................................................................................................................. 99
Chapter 13 ................................................................................................................................................. 101
TEXT ..................................................................................................................................................... 101
Chapter Outline .................................................................................................................................. 101
Concatenate ............................................................................................................................... 101
Text to Columns ........................................................................................................................ 103
Paste Special ............................................................................................................................. 104
Today and Now ......................................................................................................................... 105

iii
Table of Contents
Chapter 14 ................................................................................................................................................. 106
DEPRECIATION .................................................................................................................................. 106
Chapter Outline .................................................................................................................................. 106
Asset Acquisition Sheet ............................................................................................................ 106
Depreciation .............................................................................................................................. 107
Straight-Line Depreciation ........................................................................................................ 107
Declining-Balance Depreciation ............................................................................................... 108
Variable Declining Balance Depreciation ................................................................................. 109
Sum-of-Years’-Digits Depreciation .......................................................................................... 110
Units-of-Activity Depreciation ................................................................................................. 110
Chapter 15 ................................................................................................................................................. 111
LOANS AND THE TIME VALUE OF MONEY ................................................................................ 111
Chapter Outline .................................................................................................................................. 111
Cash Flow within Formulas ...................................................................................................... 111
Loan Payments .......................................................................................................................... 112
Payment to Principal ................................................................................................................. 114
Interest Payment ........................................................................................................................ 115
Cumulative Payment to Principal ............................................................................................. 116
Cumulative Payment to Interest ................................................................................................ 116
Present Value ............................................................................................................................ 117
Future Value .............................................................................................................................. 118
Bond Table ................................................................................................................................ 119
Chapter 16 ................................................................................................................................................. 120
ADVANCED EXCEL ........................................................................................................................... 120
Chapter Outline .................................................................................................................................. 120
Average and AverageA ............................................................................................................. 120
Convert ...................................................................................................................................... 120
Embedded Formulas ................................................................................................................. 122
Intermediate Formulas .............................................................................................................. 123
Keystrokes and Shortcuts .......................................................................................................... 125
Linking Worksheets and Workbooks ........................................................................................ 126
Chapter 17 ................................................................................................................................................. 129
MICROSOFT WORD ........................................................................................................................... 129
Chapter Outline .................................................................................................................................. 129
Word File Storage ..................................................................................................................... 129
Word Tables .............................................................................................................................. 130
Word Table Formulas ............................................................................................................... 131
Excel Tables into Word............................................................................................................. 132
Chapter 18 ................................................................................................................................................. 134
TEXAS INSTRUMENTS BA II PLUS ................................................................................................ 134
Chapter Outline .................................................................................................................................. 134
Texas Instruments BA II Plus Basics ........................................................................................ 134
Days Between Dates with the BA II Plus ................................................................................. 136
Storing Values in the BA II Plus ............................................................................................... 137
Balloon Payments with the BA II Plus ..................................................................................... 139
Time Value of Money with the BA II Plus ............................................................................... 139
Present Value of a $1 with the BA II Plus ................................................................................ 140
Future Value of a $1 with the BA II Plus ................................................................................. 140
Present Value of an Annuity of a $1 with the BA II Plus ......................................................... 141
Future Value of an Annuity of a $1 with the BA II Plus .......................................................... 142
Other BA II Plus Functions ....................................................................................................... 142
iv
Table of Contents
Chapter 19 ................................................................................................................................................. 143
HEWLETT-PACKARD HP-12C .......................................................................................................... 143
Chapter Outline .................................................................................................................................. 143
Hewlett-Packard HP-12C Basics .............................................................................................. 143
Days Between Dates with the HP-12C ..................................................................................... 145
Storing Values in the HP-12C ................................................................................................... 146
Balloon Payments with the HP-12C ......................................................................................... 147
Time Value of Money with the HP-12C ................................................................................... 148
Present Value of a $1 with the HP-12C .................................................................................... 149
Present Value of an Annuity of a $1 with the HP-12C ............................................................. 150
Future Value of an Annuity of a $1 with the HP-12C .............................................................. 151
Other HP-12C Functions ........................................................................................................... 151
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 7. Microsoft Windows 7, 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 7,” “ 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