Excel 2019 Project Book: Spreadsheets • Databases • Case Studies

Book Description

Designed as a project and case-oriented approach to learning Excel, the emphasis of this book is on learning by doing. The book presents a series of progressively reinforcing problem sets, which allow the exploration of real-life business problems. First, the background, theory, formulas, and calculations arediscussed, followed by the design of Excel spreadsheets, which support the creation of effective spreadsheets for these applications. Finally, the proper solution and other related aspects are discussed, forming a cohesive set of practical application problems. Some of the topics explored include amortization tables, weighted averages, cash flows, payroll calculations, break even analysis, and spreadsheet databases. Excel techniques include formulas and functions, cell addressing, conditional and lookup functions, graphs, sorting, and filtering.

FEATURES:

• Provides 30 projects, several How-to Guides, and Application Types to learn Excel skills using problems, applications, and case studies featuring practical business problems
• Explores formulas and functions, financial functions, cell addressing, conditional functions, lookup functions, graphs, sorting, and filtering, amortization tables, future values of an investment, weighted averages, cash flows, payroll calculations, break even analysis, economic order quantity, spreadsheet databases, and more
• Instructor resources available with four Excel video tutorials and images from the text.

Table of contents

PROJECT SET 1 SIMPLE SPREADSHEETS USING BASIC EXCEL CAPABILITIES
Project 1 Calculating a Grade Average
Project 2 Calculating Net Pay
Project 3 Calculating Charges for Service
Project 4 An Amortization Schedule – Part I
Project 5 The Future Value of Invested Funds
Project 6 Useful Spreadsheet Functions
PROJECT SET 2 ABSOLUTE ADDRESSING
Project 7 Using an Absolute Address
Project 8 An Amortization Schedule – Part II
Project 9 Calculating Weighted-Average Grades
Project 10 Calculating Miles per Gallon (mpg)
PROJECT SET 3 CASH FLOW PROJECTS
Project 11 Net Cash Inflows
Project 12 A Cash Balance Spreadsheet
Project 13 A Cash Flow Budget
Project 14 A Cash Flow Budget with Loan Repayment
Project 15 A Second Cash Flow Budget
PROJECT SET 4 SIMPLE DECISIONS USING THE IF( ) FUNCTION
Project 16 Computing Pass/Fail Grades
Project 17 Calculating Pay with Overtime
Project 18 Payroll After Deductions
Project 19 Cash Flow Budget with Conditional Loan Repayment
PROJECT SET 5 MAKING MULTIPLE CHOICES USING VLOOKUP( )
Project 20 Determining a Letter Grade
Project 21 Determining a Discount
Project 22 Using Multiple Lookup Tables
Project 23 Using One Lookup Table Multiple Times
PROJECT SET 6 APPLICATIONS AND GRAPHING
Project 24 Break-Even Analysis and Line Graphs
Project 25 Economic Order Quantity and Line Graphs
PROJECT SET 7 DATABASE PROJECTS
Project 26 Sorting a Database Spreadsheet
Project 27 Searching a Database Spreadsheet
Project 28 Database Functions
Project 29 Creating Subtotal Groups
Project 30 Creating Nested Subtotal Groups
HOW-TO GUIDES
Guide 1 Creating a Professional Spreadsheet Layout
Guide 2 Printing in Landscape and Fit-to-Page Modes
Guide 3 Highlighting Cells
Guide 4 Copying Cell Contents
Guide 5 Creating Line Graphs
Guide 6 Creating Scatter Diagrams and Trend Lines
Guide 7 Transferring Data Between Spreadsheets
Guide 8 Restricting Cell Access
BASIC APPLICATION TYPES
Appendix A Cash-Flow and Cash-Balance Spreadsheets
Appendix B Break-Even Analysis
Appendix C Economic Order Quantity (EOQ)
Appendix D Basic Database Concepts

About The Author

Gary Bronson PhD

Jeffrey Hsu PhD