Using Microsoft Excel and Access 2013 for Accounting

386 Pages·2016·19.15 MB·English

by  Owen G.

UEEsingxxcceell && AAcccceessss
®
®
2013
F O R A C C O U N T I N G
Glenn Owen
Allan Hancock College
Australia Brazil Japan Korea Mexico Singapore Spain United Kingdom United States
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that
any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if
subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats,
please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for materials in your areas of interest.
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
UsingExcel(cid:2)&Access(cid:2)2013forAccounting ª 2015, 2012 Cengage Learning.
GlennOwen
WCN: 02-200-203
SeniorVicePresident,GlobalProductManagement,
HigherEd.:JackW.Calhoun ALLRIGHTSRESERVED.Nopartofthisworkcoveredbythecopyright
VicePresident,GeneralManager,SocialScience& hereinmaybereproduced,transmitted,storedorusedinanyformorby
QualitativeBusiness:ErinJoyner anymeansgraphic,electronic,ormechanical,includingbutnotlimitedto
photocopying,recording,scanning,digitizing,taping,Webdistribution,
ProductDirector:RobDewey
informationnetworks,orinformationstorageandretrievalsystems,
SeniorProductManager:MattFilimonov exceptaspermittedunderSection107or108ofthe1976UnitedStates
ContentDeveloper:TedKnight CopyrightAct,withoutthepriorwrittenpermissionofthepublisher.
ProductAssistant:AlexandraSmiley
MarketingManager:HeatherMooney Forproductinformationandtechnologyassistance,contactusat
CengageLearningCustomer&SalesSupport,1-800-354-9706
AssociateMarketDevelopmentManager:
CourtneyDoyleChambers Forpermissiontousematerialfromthistextorproduct,submitall
requestsonlineatwww.cengage.com/permissions
SeniorMarketingCoordinator:EileenCorcoran
Furtherpermissionsquestionscanbeemailedto
ArtandCoverDirection,ProductionManagement, [email protected]
andComposition:Cenveo(cid:2)PublisherServices
AssociateMediaDeveloper:MarkHopkinson MicrosoftExcel(cid:2)andMicroAccess(cid:2) arebothregisteredtrademarksof
RightsAcquisitionDirector:AudreyPettengill MicrosoftCorporationintheUnitedStatesandInternationally.
RightsAcquisitionSpecialist,TextandImage: Exceptwhereotherwisenoted,allmaterialisªCengageLearning.
AnneSheroff
ManufacturingPlanner:DougWilke LibraryofCongressControlNumber:2013955273
StudentEditionISBN13:978-1-285-18346-6
CoverImage(s):JohnnyGreig/GettyImages
StudentEditionISBN10:1-285-18346-0
StudentEditionwithCDISBN13:978-1-285-18347-3
StudentEditionwithCDISBN10:1-285-18347-9
CengageLearning
200FirstStamfordPlace,4thFloor
Stamford,CT06902
USA
CengageLearningisaleadingproviderofcustomizedlearningsolutions
withofficelocationsaroundtheglobe,includingSingapore,theUnited
Kingdom,Australia,Mexico,Brazil,andJapan.Locateyourlocalofficeat:
www.cengage.com/global
CengageLearningproductsarerepresentedinCanadaby Nelson
Education,Ltd.
TolearnmoreaboutCengageLearningSolutions,visit
www.cengage.com.
Purchaseanyofourproductsatyourlocalcollegestoreoratour
preferredonlinestorewww.cengagebrain.com.
Printed in the United States of America
1 2 3 4 5 6 7 17 16 15 14 13
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Brief Contents
Preface vii
1 Excel for Accounting 1
Part
Chapter 1 Excel Tour 3
Chapter 2 Excel Basics 24
Chapter 3 Financial Statement Analysis 60
Chapter 4 Depreciation 85
Chapter 5 Loan and Bond Amortization 107
Chapter 6 Cash Budgeting 131
Chapter 7 Other Topics: Present/Future
Values, Predicting Costs, and
Allowance for Uncollectible
Accounts 160
Part 2 Access for Accounting 185
Chapter 8 Access Tour 187
Chapter 9 Access Basics 199
Chapter 10 Tables 232
Chapter 11 Queries 264
Chapter 12 Forms 308
Chapter 13 Reports 343
Index 370
iii
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Contents
Preface vii Case Problem 3: Snick’s Board Shop 56
Case Problem 4: Rosey’s Roses 58
Part 1 Excel for Accounting 1 Chapter 3 Financial Statement
Analysis 60
Chapter 1 Excel Tour 3
Case: What SUP, Inc. 60
Case: What SUP, Inc. 3
Vertical Analysis 60
Understanding Excel’s Capabilities 4
Pie Charts 64
Starting, Navigating, and Working
Horizontal Analysis 67
with Excel Files 4
Column Charts 70
Getting Excel Help 15
Ratio Analysis 74
ExamplesofHowExcelIsUsedinAccounting 16
End Note 80
End Note 22
Chapter 3 Practice 81
Chapter 1 Practice 23
Questions 81
Questions 23
Assignments 81
Case Problem 1: Kelly’s Boutique 82
Chapter 2 Excel Basics 24
Case Problem 2: Wine Depot 83
Case: What SUP, Inc. 24 Case Problem 3: Snick’s Board Shop 83
Entering Information 24 Case Problem 4: Rosey’s Roses 84
Entering Data 24
Changing Column Width and Row Height 26 Chapter 4 Depreciation 85
Editing Data 27
Case: What SUP, Inc. 85
Controlling the Appearance of Data 28
Depreciation Calculations 85
Entering Formulas and Using Functions 30
Conducting a What-If Analysis with the
More Extensive Use of Formulas 32
Depreciation Function 88
Entering and Editing Formulas 32
Creating a Depreciation Summary 89
Manipulating Data and Structuring Worksheets 34
Calculating Depreciation Using Other Methods 90
Using AutoFill 34
Charting Depreciation Expense 96
Using Relative and Absolute References 36
End Note 100
Inserting and Deleting Columns and Rows 38
Chapter 4 Practice 101
Working with Multiple Worksheets 40
Questions 101
Using Headers and Footers 44
Assignments 101
Printing 45
Case Problem 1: Kelly’s Boutique 103
Using Print Preview and Page Setup 46
Case Problem 2: Wine Depot 104
Printing a Worksheet 48
Case Problem 3: Snick’s Board Shop 105
Printing and Viewing Formulas 49
Case Problem 4: Rosey’s Roses 105
End Note 50
Chapter 2 Practice 51 Chapter 5 Loan and Bond
Questions 51
Amortization 107
Assignments 51
Case Problem 1: Kelly’s Boutique 53 Case: What SUP, Inc. 107
Case Problem 2: Wine Depot 54 Loan Calculations 107
iv
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Contents v
Loan Amortization Schedule 108 End Note 177
What-If Analysis and the Payment Function 110 Chapter 7 Practice 178
Names in a Worksheet 111 Questions 178
Present Value and Bonds 113 Assignments 178
Bond Amortization Schedule 115 Case Problem 1: Kelly’s Boutique 180
What-If Analysis and Goal Seeking 117 Case Problem 2: Wine Depot 181
Scenario Manager 119 Case Problem 3: Snick’s Board Shop 182
Monthly Periods 122 Case Problem 4: Rosey’s Roses 183
End Note 124
Chapter 5 Practice 125
Part 2 Access for Accounting 185
Questions 125
Assignments 125 Chapter 8 Access Tour 187
Case Problem 1: Kelly’s Boutique 126
Case Problem 2: Wine Depot 127 Case: What SUP, Inc. 187
Case Problem 3: Snick’s Board Shop 128 Understanding Access’s Capabilities
Case Problem 4: Rosey’s Roses 129 and New Features 187
Starting, Navigating, and Working
Chapter 6 Cash Budgeting 131 with Access Files 188
Getting Access Help 193
Case: What SUP, Inc. 131
Examples of How Access Is Used in Accounting 194
Operating Activities Budget 131
End Note 197
Sales Budget 132
Chapter 8 Practice 198
Operating Cash Receipts Budget 134
Questions 198
Purchases Budget 136
Operating Cash Payments for Purchases 138 Chapter 9 Access Basics 199
Sales and Administrative Expenses Budget 140
Operating Cash Payments Budget 141 Case: What SUP, Inc. 199
Investing and Financing Activities Budgets 143 Tables 199
Finalizing and Formatting the Cash Budget 144 Creating the Product Table 200
Using What-If Analysis and Goal Seeking Creating the Supplier Table 202
with the Cash Budget 150 Creating a Category Table 203
End Note 151 Entering Data into Tables 204
Chapter 6 Practice 152 Establishing Table Relationships 205
Questions 152 Printing Tables 208
Assignments 152 Queries 209
Case Problem 1: Kelly’s Boutique 154 Creating a Query 209
Case Problem 2: Wine Depot 155 Printing a Query 211
Case Problem 3: Snick’s Board Shop 157 Forms 211
Case Problem 4: Rosey’s Roses 158 Creating a Form 212
Using a Form to View, Edit, or Add Information 213
Chapter 7 OtherTopics:Present/ Printing a Form 214
FutureValues,Predicting Reports 214
Costs,andAllowancefor Creating a Report 215
Editing a Report 217
UncollectibleAccounts 160
Printing a Report 220
Case: What SUP, Inc. 160 End Note 221
Present/Future Value Analysis 160 Chapter 9 Practice 222
Predicting Future Costs 169 Questions 222
Calculating an Allowance for Uncollectible Assignments 222
Accounts 174 Case Problem 1: Kelly’s Boutique 224
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
vi Contents
Case Problem 2: Wine Depot 226 End Note 296
Case Problem 3: Snick’s Board Shop 227 Chapter 11 Practice 297
Case Problem 4: Rosey’s Roses 229 Questions 297
Assignments 297
Chapter 10 Tables 232 Case Problem 1: Kelly’s Boutique 299
Case Problem 2: Wine Depot 302
Case: What SUP, Inc. 232
Case Problem 3: Snick’s Board Shop 303
Add, Change, and Delete Records 232
Case Problem 4: Rosey’s Roses 304
Add Records 233
Change Records 234 Chapter 12 Forms 308
Delete Records 234
Case: What SUP, Inc. 308
Add a Picture with an OLE Field 235
Labels and Text Box controls 308
Add an OLE Field to a Table 236
List Box and Combo Box Controls 311
Add Pictures to the Table 237
List Box Control 312
Change the Structure of a Database 238
Combo box Control 314
Change a Field Size, Row Height, Column
Calculated Controls 317
Width, and Data Type 239
Check Box Control 321
Create Validation Rules 242
Special Combo Box Control 324
Create Default Values 245
Subforms 329
Create Formats and Input Masks 246
Printing a Form 332
Referential Integrity 250
Documenting a Form 333
Documenting a Database 253
End Note 334
End Note 256
Chapter 12 Practice 335
Chapter 10 Practice 257
Questions 335
Questions 257
Assignments 335
Assignments 257
Case Problem 1: Kelly’s Boutique 336
Case Problem 1: Kelly’s Boutique 258
Case Problem 2: Wine Depot 338
Case Problem 2: Wine Depot 259
Case Problem 3: Snick’s Board Shop 339
Case Problem 3: Snick’s Board Shop 261
Case Problem 4: Rosey’s Roses 341
Case Problem 4: Rosey’s Roses 262
Chapter 13 Reports 343
Chapter 11 Queries 264
Case: What SUP, Inc. 343
Case: What SUP, Inc. 264
Use a Query to Create a Report 343
Querying Selected Records 264
Use Grouping and Summarizing in a Report 347
Using Character Data in a Select Query 265
Grouping 347
Using Wildcards in a Select Query 267
Summarizing 350
Editing a Select Query 269
Modify an Existing Report 356
Using Comparison Operators and Sorting
Counting and Summing in Report Sections 356
in a Select Query 272
Lines,Borders,andFormattinginReportSections 360
Using Compound Criteria and Limiting
End Note 362
Output in a Select Query 276
Chapter 13 Practice 363
Performing Calculations 279
Questions 363
DisplayingtheResultsofCalculationsinaField 279
Assignments 363
Computing Statistics 281
Case Problem 1: Kelly’s Boutique 364
Action Queries (Update, Parameter, and Delete) 286
Case Problem 2: Wine Depot 365
Update Queries 287
Case Problem 3: Snick’s Board Shop 367
Parameter Queries 292
Case Problem 4: Rosey’s Roses 368
Delete Queries 294
Print a Query 296 Index 370
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
Preface
What if you could integrate two critical business software programs into your
accounting classroom without using confusing and complicated manuals? What
if your students could use these programs to reinforce basic accounting concepts
in an interactive case setting? What if you could do both without spending a
fortune and a vast amount of time preparing examples, cases, and illustrations?
Excel and Access for Accounting is a textbook that fulfills and expands upon all
three of these ‘‘what ifs.’’
Why Is This Textbook Needed?
Many accounting educators are looking for ways to incorporate more business
software into their accounting curriculum without displacing basic accounting
instruction. They have tried to accomplish this by creating a stand-alone com-
puter-based course, a lab component course, or by adding business computer
software to their regular accounting curriculum. Current texts in this field are
very generic in nature, spending little if any time on accounting-specific issues.
Those that do address accounting issues address only worksheet or database
issues but not both. Some texts that have a worksheet focus deliver a wide array
of financial and managerial topics but lack a natural case flow. Some with a
database focus emphasize the creation of accounting systems but do not address
how databases are used to support the accounting function.
Moreover, employers expect today’s college student to be computer literate
in commercial accounting, worksheet, and database software. The demand for
this type of training is growing daily as more and more businesses employ busi-
ness software to solve real-world problems.
Instructors often want to incorporate business software into the first course
but are reluctant to invest the time and effort necessary to accomplish this goal.
Existing materials are often ‘‘preparer’’ driven in that they focus on the creation
of worksheets and databases without addressing the effective use of these tools.
Students are often discouraged in their use of computers because of the compli-
cated and confusing manuals that concentrate on using the software without any
business or accounting context.
This text responds to all of those needs. It provides a self-paced, step-by-step
environment in which the students use a worksheet (Excel(cid:2)) and a database
(Access(cid:2)) to solve real accounting and business problems. The text is designed
to reinforce the concepts students learn in their first accounting courses and to
show how worksheets and databases can help users make better and more
informed business decisions.
vii
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
viii Preface
What Are the Goals of This Textbook?
This textbook takes a user perspective by illustrating how worksheets and data-
bases are used and created. Both Excel and Access are user friendly, with
extensive help features and helpful toolbars to aid in accessing commonly used
functions. The textbook uses a proven and successful pedagogy to demonstrate
the features of both software programs and to elicit student interaction.
The textbook’s first goal is to help students apply the accounting concepts
they’ve learned to real-world problems, aided by the use of a worksheet and/or
database. The content complements the first course in accounting and therefore
should be used either as a supplement to that course or as the primary textbook
in a stand-alone course that follows the first course in accounting. Some instruc-
tors have found this textbook and the QuickBooks for Accounting textbook or
the Peachtree for Accounting textbook ideal matches for a stand-alone ‘‘Com-
puters in Accounting’’ course.
The second goal is to motivate students to become more familiar with and
more at ease using a worksheet and/or database to solve accounting and busi-
ness problems. Using this software application in an accounting context main-
tains student interest and provides additional incentive for pursuing an
accounting degree or emphasis.
The third goal of this text is to reduce the administrative burdens of account-
ing faculty by providing a self-paced environment for their students to learn
how important software applications are used in business. Accounting faculty
must manage different learning styles of students as well as teach accounting
concepts and practice techniques. The additional task of integrating computer
applications into the classroom will be made simpler by using this text.
What’s New in Excel 2013 and Access 2013?
Excel 2013
The first thing you’ll see when you open Excel 2013 is a brand new look. It’s
cleaner, but it’s also designed to help you get professional-looking results
quickly. You’ll find many new features that let you get away from walls of
numbers and draw more persuasive pictures of your data, guiding you to better,
more informed decisions.
Templates do most of the set-up and design work for you, so you can focus
on your data. When you open Excel 2013, you’ll see templates for budgets, cal-
endars, forms, and reports, and more.
The new Quick Analysis tool lets you convert your data into a chart or table
in two steps or less. Preview your data with conditional formatting, sparklines,
or charts, and make your choice stick in just one click. To use this new feature,
see Analyze your data instantly.
Flash Fill is like a data assistant that finishes your work for you. As soon as
it detects what you want to do, Flash Fill enters the rest of your data in one fell
swoop, following the pattern it recognizes in your data. To see when this fea-
ture comes in handy, see Split a column of data based on what you type.
Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.