To Accompany Managerial Accounting

169 Pages·2011·5.24 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

Managerial Accounting,

Sixth 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

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

Table of Contents

The Acknowledgement of Copyrights, Patents, and Trademarks ...................................................... v 
NOTE TO THE INSTRUCTOR ....................................................................................................... vi 
INTRODUCTION .......................................................................................................................... viii 
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 ....................................................................................................................................................... 7 
INSTALLING, FINDING, AND SHORTCUTS TO MICROSOFT OFFICE ......................................... 7 
Chapter Outline ...................................................................................................................................... 7 
Terms and Conventions of this Text ............................................................................................. 7 
The Differences between Versions of Microsoft Office ............................................................... 9 
Purchasing Microsoft Office ....................................................................................................... 10 
Installing Microsoft Office .......................................................................................................... 10 
Opening Elements of Microsoft Office ....................................................................................... 11 
Chapter 3 ..................................................................................................................................................... 13 
BASICS OF EXCEL ............................................................................................................................... 13 
Chapter Outline .................................................................................................................................... 13 
Excel Basics ................................................................................................................................ 13 
Opening Excel ............................................................................................................................. 14 
Workbooks and Worksheets within Excel .................................................................................. 14 
Opening Excel Files .................................................................................................................... 15 
The Exercise and Problem Templates ......................................................................................... 17 
Demo Worksheet ........................................................................................................................ 19 
Excel Worksheets ........................................................................................................................ 19 
Pop-Up Menus ............................................................................................................................ 20 
Drop-Down Menu ....................................................................................................................... 21 
New Workbook ........................................................................................................................... 22 
Help ............................................................................................................................................. 22 
Saving Excel Files ....................................................................................................................... 23 
Formula Bar ................................................................................................................................ 24 
Row and Column Headers .......................................................................................................... 24 
File Extensions within Excel ....................................................................................................... 25 
Sizing Workbook Presentations .................................................................................................. 26 
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 
i
Table of Contents
Recently Used File List ............................................................................................................... 32 
Undo and Redo ........................................................................................................................... 33 
Add-ins ........................................................................................................................................ 33 
Chapter 5 ..................................................................................................................................................... 35 
COPY, CUT, PASTE, CLEAR, AND DELETE .................................................................................... 35 
Chapter Outline .................................................................................................................................... 35 
Copying and Pasting ................................................................................................................... 35 
Copying a Formula ..................................................................................................................... 37 
Absolute Reference ..................................................................................................................... 38 
Cut Command ............................................................................................................................. 40 
Clear and Cut .............................................................................................................................. 40 
Delete and Delete ........................................................................................................................ 40 
Chapter 6 ..................................................................................................................................................... 42 
LOCATIONS AND LOCATING ........................................................................................................... 42 
Chapter Outline .................................................................................................................................... 42 
Go To .......................................................................................................................................... 42 
Find ............................................................................................................................................. 42 
Find and Replace ......................................................................................................................... 43 
Named Ranges ............................................................................................................................ 45 
Chapter 7 ..................................................................................................................................................... 48 
CUSTOMIZING EXCEL........................................................................................................................ 48 
Chapter Outline .................................................................................................................................... 48 
Excel Defaults ............................................................................................................................. 48 
“Save As” to Change File Locations and Names ........................................................................ 49 
Excel Workbook / File Naming Recommendations .................................................................... 50 
Chapter 8 ..................................................................................................................................................... 51 
PRESENTATION ................................................................................................................................... 51 
Chapter Outline .................................................................................................................................... 51 
Comments ................................................................................................................................... 51 
Column and Row Size................................................................................................................. 52 
Charting ....................................................................................................................................... 53 
Pivot Tables ................................................................................................................................ 55 
Protection .................................................................................................................................... 57 
Read Only Files and Templates .................................................................................................. 58 
Drawing on Worksheets .............................................................................................................. 60 
Macros ......................................................................................................................................... 62 
Macros on Objects ...................................................................................................................... 63 
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 
ii
Table of Contents
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 
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 
iii
Table of Contents
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 ................................................................................................................................................. 128 
MICROSOFT WORD ........................................................................................................................... 128 
Chapter Outline .................................................................................................................................. 128 
Word File Storage ..................................................................................................................... 128 
Word Tables .............................................................................................................................. 129 
Word Table Formulas ............................................................................................................... 130 
Excel Tables into Word ............................................................................................................. 131 
Chapter 18 ................................................................................................................................................. 133 
TEXAS INSTRUMENTS BA II PLUS ................................................................................................ 133 
Chapter Outline .................................................................................................................................. 133 
Texas Instruments BA II Plus Basics ........................................................................................ 133 
Days Between Dates with the BA II Plus ................................................................................. 135 
Storing Values in the BA II Plus ............................................................................................... 136 
Balloon Payments with the BA II Plus ..................................................................................... 138 
Time Value of Money with the BA II Plus ............................................................................... 138 
Present Value of a $1 with the BA II Plus ................................................................................ 139 
Future Value of a $1 with the BA II Plus.................................................................................. 139 
Present Value of an Annuity of a $1 with the BA II Plus ......................................................... 140 
Future Value of an Annuity of a $1 with the BA II Plus .......................................................... 141 
Other BA II Plus Functions ....................................................................................................... 141 
Chapter 19 ................................................................................................................................................. 142 
HEWLETT-PACKARD HP-12C .......................................................................................................... 142 
Chapter Outline .................................................................................................................................. 142 
Hewlett-Packard HP-12C Basics .............................................................................................. 142 
Days Between Dates with the HP-12C ..................................................................................... 144 
Storing Values in the HP-12C ................................................................................................... 145 
Balloon Payments with the HP-12C ......................................................................................... 146 
Time Value of Money with the HP-12C ................................................................................... 147 
Present Value of a $1 with the HP-12C .................................................................................... 148 
Present Value of an Annuity of a $1 with the HP-12C ............................................................. 149 
Future Value of an Annuity of a $1 with the HP-12C .............................................................. 150 
Other HP-12C Functions ........................................................................................................... 150 
Index .......................................................................................................................................................... a 
iv
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

v
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 “XLS”. 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.
vi

INTRODUCTION
This book is written to accompany Managerial Accounting, Sixth Edition, by Jerry J. Weygandt, Donald
E. Kieso, and Paul D. Kimmel. 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 Managerial Accounting, Sixth Edition, by Jerry J.
Weygandt, Donald E. Kieso, and Paul D. Kimmel 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 3 Exercise E3-7, the seventh exercise within Chapter 3, the chapter file name is
Weygandt_Managerial_6e_Excel_Templates_Ch03.xlsx, the worksheet will be titled E3-7. For Problem
P4-1A, the first problem in the fourth chapter the chapter file name is Weygandt_Managerial_6e_Excel_
Templates_Ch04.xlsx and the worksheet title is P4-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