Excel Accounting Problem

Excel Accounting Problem

Excel Accounting Problem

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

Language: english

Note: 4.5/5 (69 notes) 15,245 students

Instructor(s): Robert (Bob) Steele

Last update: 2021-12-26

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

 

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

 

Course content

  • Create Excel Worksheet
    • 1025 Create Accounting Worksheet Part 1
    • 1028 Create Accounting Worksheet Part 2
    • 1031 Create Accounting Worksheet Part 3
    • 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
    • 9141 Bank Reconciliation Month Two Part 2
    • 9143 Bank Reconciliation Month Two Part 3
  • Adjusting Entries
    • 10175 Adjust Worksheet for Adjusting Entries
    • 10180 Adjusting Entry Accrued Interest Part 1
    • 10182 Adjusting Entry Accrued Interest Part 2
    • 10220 Reversing Entry Accrued Interest
    • 10260 Adjusting Entry Accounts Receivable Sales
    • 10300 Reversing Entry Accounts Receivable Sales
    • 10340 Adjusting Entry Prepaid Insurance
    • 10380 Adjusting Entry Depreciation
    • 10400 Adjusting Entry Unearned Revenue Customer Deposit
    • 10460 Reversing Entry Unearned Revenue Customer Deposit
    • 10465 Loan Payable Short Term and Long-Term Portion
    • 10467 Reversing Entry Loan Payable Short Term & Long-Term Portion
    • 10480 Financial Statements with Every Account
    • 10485 Balance Sheet Condensed Format
    • 10490 Income Statement Condensed Format

 

Excel Accounting ProblemExcel Accounting Problem

Time remaining or 987 enrolls left

 

Don’t miss any coupons by joining our Telegram group 

Udemy Coupon Code 100% off | Udemy Free Course | Udemy offer | Course with certificate