: Publication

Accounting Spreadsheet Applications offers a fresh approach to achieving Excel mastery.  The curriculum emphasizes the importance of quality spreadsheet design features written from a practical application approach. This contributes to improved efficiency and time management skills that can be applied in any industry. Spreadsheet mastery has become what is thought to be the most important and sought-after skill in this profession. Introductory chapters begin at a basic level with thorough step-by-step instructions. Subsequent chapters build on prior knowledge as students are taught through encouragement how to problem solve using Excel. More importantly, this leads to higher-level thinking for data organization and analysis. The popular craft brewing industry theme was chosen to maintain student interest throughout, but also brings real-world examples of skills accounting graduates can apply in any industry. Team projects have been designed in the final chapter for the practical application of Excel Workbook collaboration, accessibility, file protection, and security features.  

 

Full Accounting Cycle Review

Payroll Accounting Review 

Permits and Excise Taxes in the Craft Brewing Industry

Merchandise Inventory Costing Methods:  LIFO, FIFO, Weighted-Average 

Sales and Use Tax Collection and Reporting 

Accounting for Receivables:  Bad Debt 

Excel Depreciation Tools 

Excel Amortization Tools 

Excel Time Value of Money Tools 

Data Analytics:  PivotTables

Excel Lookup Functions

Basic and Compound Logical Functions 

Job Order Costing: Custom-Built Kegerator Projects

Process Costing: Tracking Costs in the Brewing Process

Cost-Volume-Profit Analysis: Cost Behavior Analysis 

Master Budgets in the Craft Brewing Industry 

Introduction to Hops Futures Contracts

Flexible Budgeting for Cost Control

Capital Expenditures Budgeting in the Craft Brewing Industry 

Excel Capital Budgeting Analysis Tools

Financial Accounting Spreadsheet Control Features 

Cost Accounting Spreadsheet Control Features 

Financial Ratio Analysis Projects

Individual and Team Projects

Worksheet Collaboration, Compatibility and Accessibility

Workbook File Protection Capabilities

Presentation Features:  Tables, Charts, Conditional Formatting

Utilizing Spreadsheets as a Problem-Solving Tool

Spreadsheet Mastery through Worksheet Linking, Cell Referencing, and Formula Builds

INTRODUCTION                                                                       xiii

ABOUT THE AUTHOR                                                               xv

Section I

Basic Excel Skills Application

Chapter 1     Excel Basic Review                                                  3

CHAPTER EXERCISES: 

EX1-1 Y       Chart of Accounts Template                         24

EX1-2 Y       General Journal Template                            24

EX1-3 Y        General Ledger Template                              25

EX1-4 Y        Worksheet Template                                      28

EX1-5 Y        Financial Statement Templates                  29 

Chapter 2     Accounting Cycle Review                                   33

BUSINESS CREATION ACCOUNTING TRANSACTIONS     29

CHAPTER EXERCISES AND PROBLEMS: 

EX2-1 Y        Worksheet, Adjustments                               56

EX2-2 Y        Worksheet, Financials, Closing                   58

EX2-3 Y        Worksheet, Adjustments                               58

EX2-4 Y        Worksheet, Financials, Closing                   60

EX2-5 Y        Reversals                                                             61

EX2-6 Y        Reversals                                                             62

PR2-A Y       Accounting Cycle Perpetual (long)            64

PR2-B Y        Accounting Cycle Periodic (long)               69

Chapter 3    
Accounting for Legal Requirements: Payroll and
Associated Taxes                                                                               77

Payroll Accounting WITH REPORTING REQUIREMENTS                                                                       78                                                                                                                                                                                      

CONCEPTS REVIEW: PAYROLL ACCOUNTING                   80

WORKING WITH TABLES                                                       92

CHAPTER EXERCISES AND PROBLEMS: 

EX3-1 Y        Employee Earnings Record (template) 101

EX3-2 Y        Payroll Register (template)                       102

EX3-3 Y        Payroll Journal Entries                                103

EX3-4 Y        Payroll Journal Entries                                104

EX3-5 Y        Payroll Journal Entries                                104

PR3-A Y       Employee Earnings Record (creation)  105

PR3-B Y        Payroll Register (creation)                        106

PR3-C Y        Employee Earnings Record                        106

PR3-D Y       Payroll Register                                              107

PR3-E Y        Payroll Register                                              107

PR3-F Y        Cumulative Payroll Register; Table Function    107

APPENDIX 3    Taxes and Permits in the Craft Brewing Industry  111

CONCEPTS REVIEW: LEGAL REQUIREMENTS IN THE BREWING INDUSTRY                                                            111

Chapter 4    Cash Conversion Cycle: Inventory and Receivables    121

ACCOUNTING FOR INVENTORY AND RECEIVABLES    122

Concepts Review: Merchandise Inventory and Accounts Receivable                                                     126

CHAPTER EXERCISES AND PROBLEMS:   

EX4-1 Y        Merchandise Purchases (Perpetual & Periodic)   155

EX4-2 Y        Merchandise Sales with Sales Tax Collection                                                                                156

EX4-3 Y        LIFO Comparison (Perpetual & Periodic)   158

EX4-4 Y        Average Cost Comparison (Perpetual & Periodic)   159

EX4-5 Y        Bad Debt: Comparison of Methods (text box tool)   160

EX4-6 Y        Bad Debt: Comparison of Methods (text box tool)   161

EX4-7 Y        Bad Debt: Aging Method (screenshot tool)            162

EX4-8 Y        Financial Ratio Analysis (screenshot tool)            164

EX4-9 Y        Financial Ratio Analysis (screenshot tool)            166

PR4-A Y       Merchandising Journal Entries Perpetual 167

PR4-B Y        Merchandising Journal Entries Periodic    169

PR4-C Y        Merchandising Journal Entries with Sales Tax Perpetual                                                                       169

PR4-D Y       Merchandising Journal Entries with Sales Tax Periodic                                                                          170

PR4-E Y        Inventory Costing Comparison (long)   170

PR4-F Y        Bad Debt Methods Comparison (long)  172

PR4-G Y        Inventory and Receivables Ratio Analysis Research Project                                                                  173

APPENDIX 4    Sales and Use Tax Collection and Reporting             177

CONCEPTS REVIEW: LEGAL REQUIREMENTS FOR A MERCHANDISER                                                                     177

Section II

Intermediate Excel Skills Application

Chapter 5     Accounting for Fixed Assets                           189

ACCOUNTING FOR BUSINESS EXPANSION: THE TAP ROOM                                                                                         190

COST ALLOCATION CONCERNS FOR THE FINANCIAL ACCOUNTANT                                                                         194

Concepts Review: Section A—Fixed AssetS        196

CONCEPTS REVIEW: Section B—INSTALLMENT NOTES AND
AMORTIZATION TABLES                                                219

What-if SCENARIO: Excel tools                                 227

CHAPTER EXERCISES AND PROBLEMS:

EX5-1 Y        Straight-Line Depreciation (SLN)             235

EX5-2 Y        Declining Balance Methods (DB, DDB, VDB)                      236

EX5-3 Y        Communication: Business Memorandum                                                                       238

EX5-4 Y        Sum-of-the-Years Digits (SYD)                  238

EX5-5 Y        Units of Production (SLN, basic math formula)   239

EX5-6 Y        Loan Amortization Table (PMT, PPMT, IPMT)         241

EX5-7 Y        Loan Amortization Table (PMT, PPMT, IPMT)         242

EX5-8 Y        What-if Analysis Excel Tools                     243

EX5-9 Y        What-if Analysis Excel Tools                     243

PR5-A Y       Depreciation Excel Formula Comparison                                                                           244

PR5-B Y        Loan Amortization Table (PMT, PPMT, IPMT)         246

PR5-C Y        Communication: Business Memorandum                                                                       247

APPENDIX 5    Excel Tools for Investments: Time Value of Money                    249

SPREADSHEET INVESTMENT FUNCTIONS                     250

INVESTMENT FUNCTIONS PRACTICE PROBLEMS:

5A-1 Y          Investments (FV)                                            264

5A-2 Y          Communication:  Business Memorandum                                                                       264

5A-3 Y          Investments (PPMT, FV, PV)                      264

5A-4 Y          Communication:  Business Memorandum                                                                       266

5A-5 Y          Investments (PDURATION)                        266

5A-6 Y          Investments (PDURATION)                        266

5A-7 Y          Investments (YIELD, YIELDDISC)            267

5A-8 Y          Investments (ACCRINT, PV, PVA)            268

Chapter 6    
Data Analytic Tools: PivotTables and Other Data
Analysis Features                                                                           271

SPREADSHEET ANALYTIC TOOLS FOR ACCOUNTING272

Concepts Review: PIVOTTABLES                                  274

popular Lookup Functions                                         295

popular logical function: if statement           300

SPREADSHEET PRESENTATION DESIGN—CHARTS    303

CHAPTER EXERCISES AND PROBLEMS: 

EX6-1 Y        PivotTable: Inventory                                  315

EX6-2 Y        PivotTable: Payroll                                       316

EX6-3 Y        Charts                                                                 316

EX6-4 Y        Charts                                                                 318

EX6-5 Y        Charts                                                                 319

EX6-6 Y        Lookup Function                                            320

EX6-7 Y        Logical Function                                             322

EX6-8 Y        PivotTable: Customer Information Request     322

EX6-9 Y        Logical Function                                             323

PR6-A Y       PivotTable:  Sales Data                                323

PR6-B Y        PivotTable:  Sales Data                                325

PR6-C Y        PivotTable:  Sales Data                                326

PR6-D Y       PivotTable:  Payroll Data                           328

PR6-E Y        PivotTable and Logical Functions           330

PR6-F Y        PivotTable and Logical Functions           335

Chapter 7     Managerial Accounting Cost Concepts       343

MANAGERIAL ACCOUNTING CONCEPTS                         344

CONCEPTS REVIEW: Section A—Manufacturing costs in the
beer industry                                                                345

CONCEPTS REVIEW: Section B—cost-volume-profit analysis                                                                 354

Excel Rounding Features                                            363

applying specialized Conditional Formatting                                                                          365

CHAPTER EXERCISES AND PROBLEMS: 

EX7-1 Y        Job Costing Documents Template           371

EX7-2 Y        Production Cost Report Template          372

EX7-3 Y        Inventory Aging Analysis Template        376

PR7-A Y       Raw Materials Inventory Aging Analysis     378

PR7-B Y        Finished Beer Inventory Aging Analysis     379

PR7-C Y        Process Costing                                               380

PR7-D Y       Job Order Costing                                           382

PR7-E Y        Process Costing                                               383

PR7-F Y        Job Order Costing                                           385

PR7-G Y        CVP Analysis                                                    387

PR7-H Y       CVP Analysis                                                    388

PR7-I Y         CVP Analysis; Product Sales Mix              389

Section III

Excel Skills Application Mastery

Chapter 8     Budgeting for the Future                                395

the budgeting process                                                  396

concepts review: Section A—The Master Budget                                                                                    397

concepts review: Section B—budgetary control and
spreadsheet efficiency features                     413

chapter exercises and problems:

EX8-1 Y        Brewery Tap Room Sales Budget            422

EX8-2 Y        Grains, Yeast, and Hops Raw Materials Budget       424

EX8-3 Y        Beer Production Labor Budget                 426

EX8-4 Y        Beer Production Overhead Budget         428

EX8-5 Y        Finished Goods/Cost of Beer Sold Budget       429

EX8-6 Y        Merchandising Sales Budget                      431

EX8-7 Y        Merchandising Purchasing Budget          432

EX8-8 Y        Selling and Administrative Expenses Budgets (complex)                                                              434

EX8-9 Y        Cash Budget for a Manufacturer               437

EX8-10 Y      Cash Budget for a Merchandiser              439

PR8-A Y       Full Data-Entry Working Budget (Compound

                      Logic Functions)                                             442

PR8-B Y        Full Data-Entry Working Budget (Compound

                      Logic Functions)                                             446

PR8-C Y        Flexible Budget with Performance Report

                      (Logic Functions)                                            448

PR8-D Y       Hops Futures Contracts Budget                453

Chapter 9    
Capital Expenditures Budgeting Tools: Excel
Budgeting Functions                                                                     455

Budgeting for Capital Expenditures                   456

Concepts Review: excel formulas for captial expenditures
decisions                                                                          457

time value of money analysis techniques       465

chapter problems:

PR9-A Y       Keg Packaging Equipment Capital Budgeting Analysis                                                              476

PR9-B Y        Bottle Packaging Equipment Capital Budgeting Analysis                                                              479

PR9-C Y        Wort Boil Equipment Capital Budgeting Analysis     482

PR9-D Y       Fermentation Tanks Capital Budgeting Analysis     485

PR9-E Y        Refrigeration Systems Capital Budgeting Analysis     487

Chapter 10   Financial and Managerial Control Features          491

financial and managerial control features                                                                                492

concepts review: Section A—Special Journals in
Financial Accounting                                              493

concepts review: Section B—subsidiary ledgers in
managerial accounting                                          501

chapter problems and team projects:

PR10-A Y     Special Journals Project – Perpetual (shorter version)                                                                 514

PR10-B Y     Special Journals Worksheet Collaboration Project

                      (full version)                                                    518

PR10-C Y      Special Journals Project—Periodic (shorter version)                                                                 519

PR10-D Y     Special Journals Worksheet Collaboration Project

                      (full version)                                                    523

PR10-E Y     Standard Costs Accounting System         525

PR10-F Y      Standard Costs Accounting System         526

APPENDIX 10 File Protection, Collaboration Features, Compatibility, and Accessibility                                                                      529

concepts review: section c—Workbook Collaboration, protecting
network spreadsheet files, compatibility, and accessibility                                                                       529

INDEX                                                                                        543

Katheryn A. Reynolds, CPA, MSA—Katheryn began her undergraduate degree in accounting at Aims Community College, later transferring to complete a Bachelor of Science in Accounting with a Minor in Business Administration from Regis University. She earned her CPA license soon after, later returning to graduate school to earn a Master of Science in Accountancy from the University of Phoenix. At the time of the initial publication, Katheryn is working on a second graduate degree through the Regis University MBA program. In addition to teaching a variety of undergraduate accounting courses, Katheryn also owns and operates a seasonal tax business.

Professional and Faculty Affiliations:
Regis University, Denver, Colorado
Colorado Community College System, CCCOnline, Denver, Colorado
Aims Community College, Greeley, Colorado
Adams State University, Extended Studies Program, Alamosa, Colorado

Contributors

This book could not have been written without the help of Katheryn’s son Brian Reynolds, a graduate of the University of Northern Colorado with an earned ACS Chemistry degree. His knowledge and expertise in fermentation science and the craft brewing industry helped to ensure content accuracy in many chapters. A special thank you to the author’s former accounting instructor and good friend Lori Hatchell, Accounting and Business Professor from Aims Community College. Lori’s accounting and teaching experience, and especially her superior attention to detail with the final discipline review, proved to be invaluable. Another special thank you to the author’s good friend Lori Ford, Graphics Technology Professor Emeritus from Aims Community College, who helped with design and layout ideas for the chapters. A thank you to the Kinnek team, a national supplier of equipment specific to the brewing industry. Upon learning the inquiries were for this project, their staff was extremely helpful in providing realistic prices for designing the various capital budgeting projects. Without asking for any type of advertisement in return, their staff took the time to assemble the many cost estimates needed.

A thank you to another accounting professor from Aims Community College, Gina Jones, who served as a sounding board for content ideas when this project first began, bouncing ideas back and forth about how to best relate accounting and spreadsheet concepts to this industry.