Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Excel Accounting Problem

Name: Excel Accounting Problem
Rating: 4.7 (674 reviews)

Learn How to Create an Accounting Worksheet Using Microsoft Excel from a Certified Public Accountant (CPA)

Created byRobert (Bob) Steele

Last updated 12/2021

English

English [Auto],

What you'll learn

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

Course content

25 sections • 93 lectures • 28h 45m total length

1025 Create Accounting Worksheet Part 119:13
Build a comprehensive Excel accounting worksheet from data input to financial statements, including the trial balance, general ledger, and subsidiary ledgers, using color coding and step by step formatting.
1028 Create Accounting Worksheet Part 211:04
Create an accounting worksheet in Excel, build a trial balance, and compute net income using debits as positives and credits as negatives, with practice and example tabs.
1031 Create Accounting Worksheet Part 313:20
Build an Excel accounting worksheet from scratch, using the example tab to enter transactions and post to the trial balance and general ledger to verify debits equal credits.
1034 Create Accounting Worksheet Part 47:50
Learn to create and post transactions in an Excel workbook, building a trial balance and general ledger from assets, liabilities, and equity using debit/credit logic, with example and practice tabs.
1037 Create Accounting Worksheet Part 517:14
Create an Excel accounting worksheet that guides debits and credits, builds a trial balance and general ledger across assets, liabilities, equity, revenue, and expenses, and uses a running balance formula.
1040 Create Accounting Worksheet Part 615:58
Build an Excel accounting worksheet from data input to trial balance, general ledger, and subsidiary ledgers for accounts receivable and accounts payable.
1043 Create Accounting Worksheet Part 78:28
Build the accounts payable subsidiary ledger in Excel, linking journal entries to the trial balance and general ledger, organized by vendor with beginning balances.
1046 Create Accounting Worksheet Part 815:58
Create an inventory subsidiary ledger in Excel, linked to the general ledger and trial balance, using the weighted average method to track purchases, cost of goods sold, and ending inventory.
1049 Create Accounting Worksheet Part 927:54
Create a balance sheet in Excel from a trial balance, detailing assets, liabilities, and equity, with current assets, property, plant and equipment, and accumulated depreciation.
1052 Create Accounting Worksheet Part 1018:39
Build an Excel-based accounting worksheet from a blank sheet, linking transactions through journal entries to trial balance, general ledger, and subsidiary ledgers, then construct balance sheet, income statement, and equity.
1055 Create Accounting Worksheet Part 1119:31
Create and link an Excel accounting worksheet by posting to the trial balance and ledgers, build balance sheet, income statement, and statement of equity, including beginning equity and net income.

1180 Bill Form27:09
Demonstrates an Excel bill form, posting to accounts payable, utilities expense, assets, inventory, with journal entries, general ledger, and subsidiary ledgers, revealing effects of debits and credits on financial statements.
1200 Pay Bills Form Check21:38
Learn the pay bills process in Excel by entering a bill, posting to accounts payable, and paying it with a check form, linking to the general ledger and trial balance.
1260 Check Form22:14
Learn how to record check form transactions in Excel that decrease the checking account, including utilities expenses, equipment assets, and inventory, and post to the general ledger.
1280 Void Check Prior Period Adjustment25:57
Explore how voiding a check and prior period adjustments affect the ledger, preserving an audit trail with void and journal entries across current and prior years, and why reconciliation matters.

1340 Invoice Form28:09
Explore invoice forms in Excel by recording accounts receivable, sales, tax payable, and inventory transactions, and distinguish service items from inventory items under accrual accounting.
1360 Receive Payment Form27:59
Learn how to record accounts receivable, issue invoices, and post customer payments in Excel, using direct deposits or deposited funds, with emphasis on bank reconciliation and accrual accounting.
1380 Deposit Form33:18
Learn how to record deposits in Excel using deposit form and deposited funds, post accounts receivable to the general ledger, and reconcile with the bank statement within the revenue cycle.
1400 Sales Receipts Form26:30
Record sales receipts in Excel, posting service and inventory transactions to checking or deposited funds. Track revenue, sales tax payable, cost of goods sold, and inventory via subsidiary ledgers.
1420 Credit Memo Refund Form & Bad Debt Expense Service Item29:55
Explore credit memos and refunds, handle bad debt expense with a service item, and reverse invoices in Excel using journal entries and accounts receivable.
1488 Credit Memo with Inventory33:13
Record a credit memo involving inventory in Excel, reversing the invoice and restoring inventory, updating accounts receivable, sales, sales tax payable, and cost of goods sold through journal entries.

1520 Pay Employees Form18:24
Explore payroll processing in Excel, computing gross pay, withholdings (federal income tax, Social Security, Medicare, benefits), and net pay, and learn to record payroll as journal entries.
1522 Pay Employees Form Part 226:18
Record payroll transactions for two employees, capturing gross pay, withholdings, and net pay, via journal entries, payroll expenses, and payroll liabilities, linked to the trial balance and subsidiary ledger.

6010 Set Up Excel Worksheet10:15
Set up an Excel worksheet for accounting practice, establishing beginning balances, journal entries, and a trial balance, color coding journals and ledgers, and previewing general ledger and subledgers.
6012 Set Up Excel Worksheet #215:17
Construct a start-to-finish Excel worksheet for a trial balance, entering beginning balances, posting journal entries, and preparing a general ledger with subsidiary ledgers, formatting, and balance checks.
6014 Set Up Excel Worksheet #310:15
Builds the general ledger in an Excel workbook, links journal entries to the trial balance, and demonstrates formatting, date fields, and starting balances for a basic accounting ledger.
6016 Set Up Excel Worksheet #412:57
Set up Excel worksheet for double-entry accounting by creating a journal entry area with debits and credits, a trial balance, and a general ledger with liability accounts and running balances.

6340 Accounts Receivable & Subsidiary Ledger12:11
Practice an excel-based accounting problem that builds accounts receivable and a subsidiary ledger by customer, starting from beginning balances and posting journal entries to balance the trial balance.
6360 Accounts Payable & Subsidiary Ledger9:02
Explore how to set up and post beginning balances for accounts payable, build a vendor subsidiary ledger, and link accounts payable to the general ledger and trial balance.
6362 Financial Statements19:06
Learn to build a balance sheet in Excel from a trial balance, categorizing cash, accounts receivable, inventory, property, plant and equipment, and current liabilities into assets, liabilities, and equity.

6380 Add New Accounts & Opening Balances20:42
Add new accounts and opening balances in Excel. Post transactions to the general ledger and reconcile the trial balance across assets, liabilities, and equity.
6383 Adjust Beginning Balances in Worksheet for Current Period Data Input10:28
Adjust beginning balances in the worksheet for current period data input by moving ending balances to the start and refreshing the general ledger and trial balance.

7020 Record Deposits from Owner and Loan11:20
Record deposits from the owner and a loan in Excel, post to the general ledger, and set up an owner investments account to reconcile the trial balance.
7040 Purchase of Furniture & Investment Transactions16:28
This lecture guides an Excel accounting practice problem on purchasing furniture and making short-term investments, recording journal entries, and posting to the trial balance and general ledger.

Requirements

None

Description

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.

Who this course is for:

Anyone who wants to learn Excel, real world practice problems being the best tool
Anybody learning accounting or bookkeeping
Anybody who uses accounting software like QuickBooks and wants to understand what the software is doing
Accounting students and professionals

Excel Accounting Problem

What you'll learn

Explore related topics

Course content

Create Excel Worksheet11 lectures • 2hr 55min

Vendor Cycle, Purchases Cycle, or Accounts Payable Cycle Transactions4 lectures • 1hr 37min

Customer Cycle, Sales Cycle, or Accounts Receivable Cycle Transactions6 lectures • 2hr 59min

Employee Cycle - Payroll Cycle Transactions2 lectures • 45min

Create Worksheet for New Company Accounting Transactions4 lectures • 49min

Adding Inventory Items & Related Inventory Account Balances1 lecture • 25min

Add Customers, Add Vendors, & Accounts Receivable & Accounts Payable Balances3 lectures • 40min

Add Opening Account Balances2 lectures • 31min

Month 1-Record Owner Investment, Purchase of Fixed Assets & Inventory2 lectures • 28min

Month 1-Enter Transactions for Purchase of Inventory1 lecture • 23min

Requirements

Description

Who this course is for: