to accompany Managerial Accounting - Wiley: Home

147 Pages·2009·2.16 MB·English

by  Rex A Schildhouse

Solving Managerial Accounting
Problems Using Excel for Windows

to accompany

Managerial Accounting
Fifth Edition

Rex A Schildhouse, LCDR, U.S. Navy, Retired, M.B.A.
Miramar Campus, San Diego Community College District, San Diego, California

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, IL

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

Table of Contents

The Acknowledgement of Copyrights, Patents, and Trademarks ...................................................... x
NOTE TO THE INSTRUCTOR ....................................................................................................... xi
INTRODUCTION ........................................................................................................................... xii
Chapter 1 ....................................................................................................................................................... 1
SOME BASICS OF WINDOWS .............................................................................................................. 1
Chapter Outline ...................................................................................................................................... 1
Microsoft Windows Versions ....................................................................................................... 1
Windows Explorer ........................................................................................................................ 1
Copying The Data Disk To The Hard Drive ................................................................................. 2
Copying The Data Disk To Floppy Disks ..................................................................................... 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 Microsoft Office Pro and Microsoft Office ......................................... 8
Purchasing Microsoft Office ......................................................................................................... 8
Installing Microsoft Office............................................................................................................ 8
Opening Elements Of Microsoft Office ........................................................................................ 9
Chapter 3 ..................................................................................................................................................... 12
BASICS OF EXCEL ............................................................................................................................... 12
Chapter Outline .................................................................................................................................... 12
Excel Basics ................................................................................................................................ 12
Opening Excel ............................................................................................................................. 13
Workbooks And Worksheets Within Excel ................................................................................ 14
Opening Excel Files .................................................................................................................... 14
The Exercise and Problem Templates ......................................................................................... 16
Demo File.................................................................................................................................... 16
Excel Tabs ................................................................................................................................... 17
Pop-Up Menus ............................................................................................................................ 19
Drop-Down Menu ....................................................................................................................... 19
New Workbook ........................................................................................................................... 19
Help ............................................................................................................................................. 20
Saving Excel Files ....................................................................................................................... 20
Formula Bar ................................................................................................................................ 21
Row And Column Headers ......................................................................................................... 22
File Extensions Within Excel ...................................................................................................... 22
Sizing Workbook Presentations .................................................................................................. 23
v
Table of Contents
Chapter 4 ..................................................................................................................................................... 25
BASIC EXCEL DATA ........................................................................................................................... 25
Chapter Outline .................................................................................................................................... 25
Basic Data Entry ......................................................................................................................... 25
Sum Formula ............................................................................................................................... 26
Basic Formulas ............................................................................................................................ 27
Look To Formula ........................................................................................................................ 28
Mathematical Order Of Operation .............................................................................................. 28
Nested Parentheses ...................................................................................................................... 28
Recently Used File List ............................................................................................................... 29
Undo And Redo .......................................................................................................................... 29
Add Ins ........................................................................................................................................ 30
Chapter 5 ..................................................................................................................................................... 31
COPY, CUT, PASTE, CLEAR, AND DELETE .................................................................................... 31
Chapter Outline .................................................................................................................................... 31
Copying And Pasting .................................................................................................................. 31
Copying A Formula .................................................................................................................... 32
Absolute Reference ..................................................................................................................... 33
Cut Command ............................................................................................................................. 35
Clear And Cut ............................................................................................................................. 35
Delete And Delete ....................................................................................................................... 36
Chapter 6 ..................................................................................................................................................... 37
LOCATIONS AND LOCATING ........................................................................................................... 37
Chapter Outline .................................................................................................................................... 37
Go To .......................................................................................................................................... 37
Find ............................................................................................................................................. 37
Find And Replace ....................................................................................................................... 38
Name Ranges .............................................................................................................................. 39
Chapter 7 ..................................................................................................................................................... 41
CUSTOMIZING EXCEL........................................................................................................................ 41
Chapter Outline .................................................................................................................................... 41
Excel Defaults ............................................................................................................................. 41
View Tab Through Options ........................................................................................................ 42
Calculation Tab Through Options ............................................................................................... 42
Changing the Default File Directory ........................................................................................... 43
Edit Through Options ................................................................................................................. 44
Save As To Change File Locations And Names ......................................................................... 44
Excel Workbook / File Naming Recommendations .................................................................... 44
Chapter 8 ..................................................................................................................................................... 46
PRESENTATION ................................................................................................................................... 46
Chapter Outline .................................................................................................................................... 46
Comments ................................................................................................................................... 46
Column And Row Size ............................................................................................................... 47
Charting....................................................................................................................................... 47
Pivot Tables ................................................................................................................................ 49
Protection .................................................................................................................................... 50
Read Only Files And Templates ................................................................................................. 51
Drawing On Worksheets ............................................................................................................. 51
Macros......................................................................................................................................... 53
Macros On Objects ..................................................................................................................... 54
vi
Table of Contents
Chapter 9 ..................................................................................................................................................... 56
PRINTING ISSUES ................................................................................................................................ 56
Chapter Outline .................................................................................................................................... 56
Page Setup ................................................................................................................................... 56
Page Break Preview .................................................................................................................... 57
Print Area .................................................................................................................................... 58
Print Preview ............................................................................................................................... 59
Printing ........................................................................................................................................ 60
Chapter 10 ................................................................................................................................................... 61
INVENTORY .......................................................................................................................................... 61
Chapter Outline .................................................................................................................................... 61
Filter ............................................................................................................................................ 61
Sort .............................................................................................................................................. 62
Master Sort Column .................................................................................................................... 62
SumIf........................................................................................................................................... 63
CountIf ........................................................................................................................................ 63
Subtotal ....................................................................................................................................... 64
Vlookup....................................................................................................................................... 67
Chapter 11 ................................................................................................................................................... 69
DISPLAY ................................................................................................................................................ 69
Chapter Outline .................................................................................................................................... 69
Freeze Panes ................................................................................................................................ 69
Split Pane .................................................................................................................................... 70
Conditional Formatting ............................................................................................................... 70
Displaying Zero Values .............................................................................................................. 70
Gridlines On The Screen ............................................................................................................. 71
Hiding Columns And Rows ........................................................................................................ 71
Hidden ......................................................................................................................................... 71
Indent Within A Cell ................................................................................................................... 72
Truncate ...................................................................................................................................... 73
Workspaces ................................................................................................................................. 73
Chapter 12 ................................................................................................................................................... 76
ANSWERS .............................................................................................................................................. 76
Chapter Outline .................................................................................................................................... 76
Formula Auditing ........................................................................................................................ 76
Formatting Cells .......................................................................................................................... 77
Merge Cells ................................................................................................................................. 79
Formatting Within A Cell ........................................................................................................... 80
Goal Seek .................................................................................................................................... 80
Null Value ................................................................................................................................... 80
Or ................................................................................................................................................ 81
And .............................................................................................................................................. 81
If Statement ................................................................................................................................. 82
Chapter 13 ................................................................................................................................................... 84
TEXT ....................................................................................................................................................... 84
Chapter Outline .................................................................................................................................... 84
Concatenate ................................................................................................................................. 84
Text To Columns ........................................................................................................................ 86
Paste Special ............................................................................................................................... 87
Today And Now .......................................................................................................................... 87
vii
Table of Contents
Chapter 14 ................................................................................................................................................... 89
DEPRECIATION .................................................................................................................................... 89
Chapter Outline .................................................................................................................................... 89
Asset Acquisition Sheet .............................................................................................................. 89
Depreciation ................................................................................................................................ 90
Straight-Line Depreciation .......................................................................................................... 90
Declining-Balance Depreciation ................................................................................................. 91
Variable Declining Balance Depreciation ................................................................................... 92
Sum-Of-Year’s-Digits Depreciation ........................................................................................... 93
Units-Of-Activity Depreciation .................................................................................................. 93
Chapter 15 ................................................................................................................................................... 94
LOANS AND THE TIME VALUE OF MONEY .................................................................................. 94
Chapter Outline .................................................................................................................................... 94
Cash Flow Within Formulas ....................................................................................................... 94
Loan Payments ............................................................................................................................ 95
Payment To Principle .................................................................................................................. 96
Cumulative Payment To Principle .............................................................................................. 96
Interest Payment .......................................................................................................................... 97
Cumulative Payment To Interest ................................................................................................. 97
Present Value .............................................................................................................................. 98
Future Value ................................................................................................................................ 99
Bond Table .................................................................................................................................. 99
Chapter 16 ................................................................................................................................................. 101
ADVANCED EXCEL ........................................................................................................................... 101
Chapter Outline .................................................................................................................................. 101
Average & AverageA................................................................................................................ 101
Command Access ...................................................................................................................... 101
Convert ...................................................................................................................................... 102
Embedded Formulas ................................................................................................................. 103
Intermediate Formulas .............................................................................................................. 105
Keystrokes & Shortcuts ............................................................................................................ 107
Linking Worksheets .................................................................................................................. 108
Chapter 17 ................................................................................................................................................. 110
MICROSOFT WORD ........................................................................................................................... 110
Chapter Outline .................................................................................................................................. 110
Word File Storage ..................................................................................................................... 110
Word Upon Opening ................................................................................................................. 110
Word Tables .............................................................................................................................. 110
Word Table Formulas ............................................................................................................... 112
Excel Tables Into Word ............................................................................................................ 112
viii
Table of Contents
Chapter 18 ................................................................................................................................................. 114
HEWLETT-PACKARD HP-12C .......................................................................................................... 114
Chapter Outline .................................................................................................................................. 114
Hewlett-Packard HP-12C Basics .............................................................................................. 114
Days Between Dates With The HP-12C ................................................................................... 116
Storing Values In The HP-12C ................................................................................................. 117
Balloon Payments With The HP-12C ....................................................................................... 118
Time Value Of Money With The HP-12C ................................................................................ 119
Present Value Of A $1 With The HP-12C ................................................................................ 120
Present Value Of An Annuity Of $1 With The HP-12C ........................................................... 121
Future Value Of An Annuity Of $1 With The HP-12C ............................................................ 122
Other HP-12C Functions ........................................................................................................... 122
Chapter 19 ................................................................................................................................................. 123
TEXAS INSTRUMENTS BA II+ ......................................................................................................... 123
Chapter Outline .................................................................................................................................. 123
Texas Instruments BA II+ Basics ............................................................................................. 123
Days Between Dates With The BA II+ ..................................................................................... 125
Storing Values In The BA II+ ................................................................................................... 126
Balloon Payments With The BA II+ ......................................................................................... 128
Time Value Of Money With The BA II+ ................................................................................. 128
Present Value Of A $1 With The BA II+ .................................................................................. 129
Present Value Of An Annuity of $1 With The BA II+ ............................................................. 130
Future Value Of An Annuity Of $1 With The BA II+ .............................................................. 131
Other BA II+ Functions ............................................................................................................ 131
Index .......................................................................................................................................................... a
ix
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 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

x
NOTE TO THE INSTRUCTOR

The exercises and problems as Microsoft Excel templates are provided on the student resources web site
for the textbook. 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 contains an instructions worksheet and an area for the student to identify him or herself, the
course, the due date, and the instructor. This identification information is printed at the top of each page if
multiple pages are required by the exercise or problem. The exercise or problem identification is near the
top of the first page of every exercise or problem. Each template is also set up with footers stating the
exercise or problem identity, the page number of page numbers, and the time printed to assist in
compiling the many 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.
xi
INTRODUCTION
This book is written to accompany Managerial Accounting, fifth 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 textbook by Jerry J.
Weygandt, Donald E. Kieso, and Paul D. Kimmel that have been selected for presentation as Excel
templates. These exercises and problems 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 or the
exercise and problems information from this text. The exercises and problems are available on the student
resources web site for this text. Each exercise or problem is identified with its own file name. For
Exercise E4-12, the twelfth exercise within Chapter 4, the file name is Wey_MA5e_4e_12.xls (Chapter,
Exercise, Number). Problem P5-2 has the file name Wey_MA5e_5p_2.xls (Chapter, Problem, Number).
All of these files usually contain the extension “xls” for Microsoft Excel.
All of the data files were constructed in Microsoft Excel 2007 utilizing the “.xls” extension. The files
should be accessible from Excel 5.0 and Excel 95 up to, and including, the latest version of Microsoft
Excel, Excel 2007. The majority of commands and capabilities are common to many of the various
versions of Excel as well as other spreadsheet applications.
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 “Screen Print” 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.

xii