OpenCourser brand icon
Sign in
We may earn an affiliate commission when you visit our partners.

Excel Accounting Problem

Robert (Bob) Steele

This course will walk through a comprehensive accounting problem using Microsoft Excel in a step-by-step process. You may also use another spreadsheet software like Google Sheets.

Learners will learn how to navigate Microsoft Excel as well as how to create a well-designed accounting worksheet, complete with a general journal, trial balance, general ledger, subsidiary ledgers for accounts receivable, accounts payable, & inventory, financial statements and much more.

Read more

This course will walk through a comprehensive accounting problem using Microsoft Excel in a step-by-step process. You may also use another spreadsheet software like Google Sheets.

Learners will learn how to navigate Microsoft Excel as well as how to create a well-designed accounting worksheet, complete with a general journal, trial balance, general ledger, subsidiary ledgers for accounts receivable, accounts payable, & inventory, financial statements and much more.

Excel is a very good tool to learn accounting because it is much more transparent than a database program, like accounting software, QuickBooks being a common example of accounting software.

As we enter transactions into our Microsoft Excel worksheet, we will discuss how the data input would commonly be entered into accounting software like QuickBooks and what data input forms would normally drive the transactions.

The course will start out from a blank Excel Worksheet. We will build the outline for our accounting system from the blank spreadsheet.

For each new step in the process, you will have access to a downloadable Excel Workbook, containing at least two tabs, one with the answer, the new steps being completed, the other starting out where the prior presentation left off.

Therefore, you can complete the entire problem starting from one blank spreadsheet, or you can jump forward in the problem and rework any component of the problem by downloading the related worksheet at that point in the process.

After constructing the outline of our accounting worksheet, the first section will discuss the most common accounting transactions and the data input form often used in accounting software, like QuickBooks, to enter them, common data input forms including invoice, bill, deposit. . .

We will then start our comprehensive problem, constructing a new Excel worksheet and entering beginning balances into it, imagining that we had a prior accounting system which we are now converting to our new accounting system in Excel.

We will then enter two-months worth of data input. When entering the accounting data we will create the journal entry, post it to the trial balance, giving us a good look at which accounts are impacted, post to the general ledger, and make adjustments to any subsidiary ledgers needed.

We will also update the financial statements periodically.

For the second month of operations, we create a bit more complex trial balance, allowing us to calculate both the year-to-date numbers, including both months of data input, and the current period numbers, showing income statement accounts for just the second month of operations.

After entering two months of data input, we will process two bank reconciliations, using mock bank statements. As we construct our bank reconciliations, we will discuss how accounting software often accomplishes this task.

Next, we will adjust our worksheet to enter period end adjusting journal entries as well as reversing entries. For each adjusting entry we will consider the reasons for it and how to set up the accounting system so that our adjusting entries do not mess up the data input process in the accounting department.

After entering the adjusting entries we will construct our financial statements.

Enroll now

What's inside

Learning objectives

  • How to navigate microsoft excel or other computerized spreadsheet like google sheets
  • How to create an accounting system in excel from a blank worksheet including a general journal, trial balance, general ledger, subsidiary ledgers and more
  • How to enter journal entries related to common transactions and list the data input forms often used to enter them into accounting software like quickbooks
  • How to enter two months worth of data input into our accounting system
  • How to do two month of bank reconciliations, comparing the process to how accounting software like quickbooks would accomplish the task
  • How to enter period end adjusting journal entries
  • Create financial statements

Syllabus

Create Excel Worksheet
1025 Create Accounting Worksheet Part 1
1028 Create Accounting Worksheet Part 2
1031 Create Accounting Worksheet Part 3
Read more
1034 Create Accounting Worksheet Part 4
1037 Create Accounting Worksheet Part 5
1040 Create Accounting Worksheet Part 6
1043 Create Accounting Worksheet Part 7
1046 Create Accounting Worksheet Part 8
1049 Create Accounting Worksheet Part 9
1052 Create Accounting Worksheet Part 10
1055 Create Accounting Worksheet Part 11
Vendor Cycle, Purchases Cycle, or Accounts Payable Cycle Transactions
1180 Bill Form
1200 Pay Bills Form Check
1260 Check Form
1280 Void Check Prior Period Adjustment
Customer Cycle, Sales Cycle, or Accounts Receivable Cycle Transactions
1340 Invoice Form
1360 Receive Payment Form
1380 Deposit Form
1400 Sales Receipts Form
1420 Credit Memo Refund Form & Bad Debt Expense Service Item
1488 Credit Memo with Inventory
Employee Cycle - Payroll Cycle Transactions
1520 Pay Employees Form
1522 Pay Employees Form Part 2
Create Worksheet for New Company Accounting Transactions
6010 Set Up Excel Worksheet
6012 Set Up Excel Worksheet #2
6014 Set Up Excel Worksheet #3
6016 Set Up Excel Worksheet #4
Adding Inventory Items & Related Inventory Account Balances
6300 Inventory & Inventory Subsidiary Ledger
Add Customers, Add Vendors, & Accounts Receivable & Accounts Payable Balances
6340 Accounts Receivable & Subsidiary Ledger
6360 Accounts Payable & Subsidiary Ledger
6362 Financial Statements
Add Opening Account Balances
6380 Add New Accounts & Opening Balances
6383 Adjust Beginning Balances in Worksheet for Current Period Data Input
Month 1-Record Owner Investment, Purchase of Fixed Assets & Inventory
7020 Record Deposits from Owner and Loan
7040 Purchase of Furniture & Investment Transactions
Month 1-Enter Transactions for Purchase of Inventory
7080 Cash Payment for Inventory Linked to Purchase Order P.O.
Month 1-Enter Transactions for Sale of Inventory and Deposits
7100 Invoice Selling Inventory
7120 Receive Payment From Previous Invoice
7140 Sales Receipt Payment Received at Point of Sale
7160 Deposits From Undeposited Funds
Month 1-Enter Transaction for Sale of Inventory, Receive Payment, & Bill
7180 Invoice Created From Check Created From Purchase Order
7200 Receive Payment & Make Deposit
7220 Write Checks for Expenses & Prepaid Assets
7240 Bill Pay
Month 1-Transactions for Payroll & Create Sales Receipt
7280 Pay Employees
7281 Pay Employees Part 2
7300 Enter Billable Time & Add to Invoice
7301 Enter Billable Expenses to Invoice
Month 1-Create Financial Statements
7320 Month One Financial Statements
Month 2-Enter Loan Transaction, Create Amortization Table, & Investment
8005 Format Worksheet for New Month
8020 Make Amortization Table
8022 Make Loan Payments
8040 Short Term Investment Sales & Gains
Month 2-Enter Sale, Cash Received at Same Time & Bill for Inventory
8120 Sales Receipt & Deposit
8122 Record Receipt of Inventory with Bill Linked to P.O.
Month 2-Invcoices for Service Sales & Advanced Customer Deposit
8130 Populate Invoice Using Billable Item That was Created From P.O.
8140 Advanced Customer Payment or Unearned Revenue Method 1
8160 Apply Customer Deposit (Credit) to Invoice
8200 Advanced Customer Payment or Unearned Revenue Method 2
Months 2-Transaction for Billable Time
8320 Bill for Hourly Services of Staff Set up Items & Enter Billable Time
8322 Create Invoices Using Billable Time
Month 2-Rental Income Transaction & Advanced Customer Deposit
8342 Rental Income Customer Deposit
8344 Rental Income Estimate & Invoice
Month 2-Purchase & Finance Equipment
8360 Purchase & Finance Equipment
8362 Subaccounts Categories For Fixed Assets
Month 2-Enter & Pay Month End Bills & Pay Sales Tax
8365 Bills - Enter, Sort, & Pay
8370 Pay Sales Tax
Month 2-Enter Payroll & Pay Payroll Tax for Prior Period
8380 Enter Payroll For Second Month Part 1
8381 Enter Payroll For Second Month Part 2
8385 Pay Payroll Taxes
Month 2 Financial Statements
8420 Create Reports After Second Month of Data Input YTD
8421 Create Reports After Second Month of Data Month of February Only
Bank Reconciliations
9040 Bank Reconciliation Month One Part 1
9041 Bank Reconciliation Month One Part 2
9042 Bank Reconciliation Month One Part 3
9140 Bank Reconciliation Month Two Part 1

Save this course

Save Excel Accounting Problem to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Excel Accounting Problem with these activities:
Review 'Accounting for Dummies'
Solidify your understanding of accounting fundamentals by reviewing a beginner-friendly accounting book. This will provide a strong foundation for the Excel-based problem-solving in the course.
View Accounting For Dummies on Amazon
Show steps
  • Read the chapters covering basic accounting principles.
  • Complete the practice questions at the end of each chapter.
  • Take notes on key concepts and definitions.
Review Basic Accounting Principles
Reinforce your understanding of fundamental accounting principles before diving into Excel-based problem-solving. This will help you better understand the logic behind the formulas and processes.
Browse courses on Accounting Principles
Show steps
  • Review the accounting equation (Assets = Liabilities + Equity).
  • Study the definitions of debits and credits.
  • Familiarize yourself with the chart of accounts.
Brush Up on Excel Fundamentals
Practice basic Excel skills to ensure you can efficiently navigate the software and create formulas. This will save time and reduce frustration during the course.
Browse courses on Excel Formulas
Show steps
  • Practice creating basic formulas (SUM, AVERAGE, etc.).
  • Learn how to use cell references (relative, absolute, mixed).
  • Familiarize yourself with common formatting options.
Four other activities
Expand to see all activities and additional details
Show all seven activities
Review 'Excel Formulas and Functions'
Enhance your Excel skills by studying a dedicated guide to Excel formulas and functions. This will enable you to create more sophisticated and efficient accounting worksheets.
View Microsoft Excel Formulas and Functions (Office... on Amazon
Show steps
  • Read the chapters covering formulas relevant to accounting (e.g., financial functions, lookup functions).
  • Practice using the formulas in Excel with sample data.
  • Experiment with different formula combinations to solve accounting problems.
Practice Excel Accounting Problems with Peers
Collaborate with other students to solve practice accounting problems in Excel. This will help you learn from different approaches and identify areas where you need more help.
Show steps
  • Form a study group with 2-3 other students.
  • Choose practice problems from accounting textbooks or online resources.
  • Work through the problems together, explaining your reasoning.
Create a Video Explaining a Specific Excel Accounting Function
Solidify your understanding of a specific Excel accounting function by creating a short video tutorial. Teaching others is a great way to reinforce your own knowledge.
Show steps
  • Choose an Excel accounting function (e.g., VLOOKUP, SUMIF).
  • Research the function and its applications in accounting.
  • Record a video explaining the function and demonstrating its use.
  • Share your video with other students for feedback.
Develop a Personal Budgeting System in Excel
Apply the skills learned in the course to create a personal budgeting system in Excel. This will provide practical experience and help you manage your own finances.
Show steps
  • Create a spreadsheet with categories for income and expenses.
  • Track your income and expenses for a month.
  • Analyze your spending patterns and identify areas for improvement.

Career center

Learners who complete Excel Accounting Problem will develop knowledge and skills that may be useful to these careers:
Bookkeeper
A bookkeeper maintains accurate financial records for organizations, which is directly related to topics covered in this course. This course helps one become proficient in creating accounting systems in Excel, including setting up a general journal, trial balance, general ledger, and subsidiary ledgers. These are all essential components of the bookkeeping process. The course also demonstrates how to enter common accounting transactions and reconcile bank statements, valuable skills for any bookkeeper. One who wishes to become a bookkeeper should appreciate that this course starts with a blank Excel worksheet, and then builds up to final financial statements. It may aid in their own development of similar financial statements.
See salaries and explore the career path for Bookkeeper
Accounting Clerk
An accounting clerk is responsible for recording financial transactions, updating statements, and verifying records. This course provides a comprehensive walkthrough of accounting processes in Microsoft Excel, creating a well-designed accounting worksheet. The course demonstrates the creation of a general journal, trial balance, general ledger, subsidiary ledgers, and financial statements. Mastering these skills in Excel helps the accounting clerk ensure accuracy and efficiency in their work, as well as an understanding of how accounting software works. Anyone aspiring to be an accounting clerk will appreciate that this course will demonstrate bank reconciliations, comparing the process to how accounting software accomplishes the task.
See salaries and explore the career path for Accounting Clerk
Junior Accountant
A junior accountant supports the accounting department with tasks such as data entry, account reconciliation, and financial reporting. This Excel course provides the learner with hands-on experience in creating an accounting system within Excel, which translates directly to the responsibilities of a junior accountant. The course covers essential tasks such as creating a general journal, trial balance, and general ledger, along with subsidiary ledgers for accounts receivable and payable. The course demonstrates how to enter two months of data into an accounting system, all of which helps a junior accountant better understand the full accounting cycle. The Excel skills acquired in this course are invaluable for performing daily tasks efficiently.
See salaries and explore the career path for Junior Accountant
Staff Accountant
A staff accountant is responsible for preparing journal entries, maintaining balance sheet reconciliations, and assisting with month-end closings. This course helps build a strong foundation in accounting principles and Excel skills, both critical for a staff accountant's success. By learning how to create an accounting system in Excel from scratch, including journal entries, trial balance, general ledger, and financial statements, the learner gains practical experience in the full accounting cycle. Furthermore, the discussion of data input forms commonly used in accounting software like QuickBooks allows the staff accountant to see how data entry works. A future staff accountant might find the coverage of adjusting journal entries and reversing entries particularly helpful.
See salaries and explore the career path for Staff Accountant
Accounts Payable Specialist
An accounts payable specialist manages the process of paying invoices and tracking vendor payments. This course is correlated, because it covers the creation of subsidiary ledgers for accounts payable, along with instructions for paying bills. This course helps the specialist to ensure efficiency in their payments. One aspiring to be an accounts payable specialist will appreciate that the course also includes a discussion of how to enter common accounting transactions and reconcile bank statements. This adds to the specialist's confidence in the reliability of the financial data.
See salaries and explore the career path for Accounts Payable Specialist
Accounts Receivable Specialist
An accounts receivable specialist manages the process of invoicing customers and tracking payments. This course is correlated, because it covers the creation of subsidiary ledgers for accounts receivable, along with instructions for creating bills. This helps the specialist to ensure efficiency in their collections. The aspiring accounts receivable specialist will appreciate that the course also includes a discussion of how to enter common accounting transactions and reconcile bank statements. This adds to the specialists confidence in the reliability of the financial data.
See salaries and explore the career path for Accounts Receivable Specialist
Auditor
An auditor examines financial records to ensure accuracy and compliance. This course may be useful for gaining stronger accounting skills, which will be foundational to the skills of an auditor. This course enhances one's understanding of how financial data is recorded and summarized in Excel. The auditor may be involved in assessing financial statements and the underlying data that constitutes those financial statements. The course takes one through a comprehensive accounting problem using Microsoft Excel in a step-by-step process, from a blank worksheet. This is also useful for understanding common accounting transactions and the data input forms often used in accounting software.
See salaries and explore the career path for Auditor
Financial Analyst
A financial analyst analyzes financial data to provide insights and recommendations for business decisions. This course may be useful, as it helps the learner gain a solid understanding of accounting principles and Excel functionality. The course provides a step-by-step guide to creating financial statements and performing bank reconciliations. These accounting and Excel skills are relevant to the financial analyst's responsibilities of interpreting financial data and developing reports. Financial analysts often work with spreadsheets, so knowing how to manipulate them is helpful. A financial analyst will note that the Excel workbooks in this course can be downloaded and used for future reference.
See salaries and explore the career path for Financial Analyst
Budget Analyst
A budget analyst helps organizations develop and manage their budgets. This course may be useful for a budget analyst, because it reinforces the importance of financial data input and report preparation. The course walks through a comprehensive accounting problem using Microsoft Excel, teaching learners how to create a well-designed accounting worksheet. This includes creating a general journal, trial balance, general ledger, subsidiary ledgers, and financial statements. These are all components that will be reviewed, understood, and analyzed by a budget analyst. The course simulates the creation of an accounting system in Excel from a blank worksheet.
See salaries and explore the career path for Budget Analyst
Tax Accountant
A tax accountant prepares tax returns and ensures compliance with tax laws, often with the help of accounting software. This course may be useful, because it discusses the creation of financial statements and the entry of adjusting journal entries, which are relevant for tax accounting. This knowledge of accounting systems helps the tax accountant better analyze and interpret the information needed for tax filings. By understanding how financial data flows through an accounting system, the tax accountant can better analyze and interpret the information needed for tax filings. The tax accountant may appreciate the discussion of adjusting journal entries and reversing entries.
See salaries and explore the career path for Tax Accountant
Tax Preparer
A tax preparer prepares and files tax returns for individuals or businesses. This course may be useful, because it can help the learner become more familiar with financial data and how it is organized in an accounting system. The course covers the creation of financial statements and the entry of adjusting journal entries, which are relevant for tax preparation. By understanding how financial data flows through an accounting system, the tax preparer can better analyze and interpret the information needed for tax filings. The tax preparer may appreciate the discussion of bank reconciliations and how these accounting processes are used in computerized systems.
See salaries and explore the career path for Tax Preparer
Payroll Specialist
A payroll specialist manages the processing of employee payroll, including calculating wages, taxes, and deductions. This Excel course may be useful, as it provides a foundational understanding of accounting principles and Excel skills. The payroll specialist can better understand how payroll data integrates with the general ledger and financial statements. The course also demonstrates how to enter common accounting transactions and perform bank reconciliations. A payroll specialist would most likely find the discussion of data input forms commonly used in accounting software like QuickBooks most useful.
See salaries and explore the career path for Payroll Specialist
Treasurer
A treasurer manages an organization's finances, including investments and cash flow. This course may be useful in understanding accounting data, which is core to the treasurer's role. This course provides the treasurer with information on the creation of financial statements. The course will also cover common accounting transactions and the data input forms often used in accounting software. A treasurer requires an understanding of accounting software packages. The course demonstrates examples from Quickbooks, a popular accounting software. This will make the treasurer better equipped to oversee the accounting department.
See salaries and explore the career path for Treasurer
Cost Accountant
A cost accountant analyzes production costs and develops cost accounting systems. This course may be useful, as it teaches the learner how to track financial transactions in Excel. Cost accountants should recognize that the creation of Excel-based accounting systems may assist in understanding data management. This is valuable when assessing the integrity of data from other software systems. This course starts with a blank Excel Worksheet and builds the outline for an accounting system from the blank spreadsheet. This may assist in creating one's own cost accounting models.
See salaries and explore the career path for Cost Accountant
Accounting Manager
An accounting manager oversees the accounting department and ensures the accuracy of financial reporting. An accounting manager usually has an advanced degree. While this course may be useful, an accounting manager will typically need to oversee individuals who perform routine duties, and not perform these tasks alone. Nevertheless, the course demonstrates creation of a general journal, trial balance, general ledger, subsidiary ledgers, and financial statements. These skills are important for the accounting manager to have visibility as they oversee those underneath them. The accounting manager may wish to use this course to familiarize new employees with the flow of accounting transactions.
See salaries and explore the career path for Accounting Manager

Reading list

We've selected two books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in Excel Accounting Problem.
Cover image
Microsoft Excel Formulas and Functions (Office 2021...
Save
Comprehensive guide to Excel formulas and functions, essential for mastering Excel-based accounting. It covers a wide range of functions, from basic arithmetic to advanced financial calculations. This book serves as a useful reference tool for understanding and applying Excel formulas in accounting scenarios. It adds depth to the course by providing detailed explanations and examples of Excel's capabilities.
Microsoft Excel Formulas and Functions (Office 2021...
Paperback
$$
Microsoft Excel Formulas and Functions (Office 2021...
Kindle Edition
$$
Cover image
Accounting For Dummies
Save
Provides a solid foundation in accounting principles, making it an excellent resource for those new to the field. It explains complex concepts in a clear and accessible manner, which is helpful for understanding the underlying logic behind Excel accounting. While not specifically focused on Excel, it provides the necessary accounting knowledge to make the most of the course. It is most valuable as additional reading to supplement the course materials.
Accounting For Dummies
Paperback
Check
Accounting For Dummies
Kindle Edition
Check

Share

Help others find this course page by sharing it with your friends and followers:
Facebook
LinkedIn
Reddit
X
Link
Email

Similar courses

Similar courses are unavailable at this time. Please try again later.
Effort
29 total hours
Via
Udemy
Instructor
Robert (Bob) Steele
Language
English
Share and help others discover this course.
Facebook LinkedIn X Reddit Link Email
Don't miss out
Enroll today to gain access to this course
Enroll in this course
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2025 OpenCourser