How to automate Accounting Ledger, Trial Balance, Income Statement, Balance Sheet in Excel | English

EXCEL DOERS・2 minutes read

The text provides a detailed video tutorial on the accounting cycle, covering topics such as creating a chart of accounts, making general entries, preparing a trial balance, income statement, and balance sheet, with a focus on specific transactions like sales, purchases, and expenses. It emphasizes the importance of accurate recording, categorization, and organization of financial transactions to generate essential financial statements for businesses.

Insights

  • Entries in accounting cycles involve creating a chart of accounts, recording transactions like investments, purchases, and sales, and preparing financial statements like income statements and balance sheets.
  • Proper organization of accounts into assets, liabilities, equity, expenses, and revenue categories, along with meticulous recording of transactions, ensures accurate financial reporting and analysis.

Get key ideas from YouTube videos. It’s free

Recent questions

  • How can I create a chart of accounts?

    By listing all accounts for general entries.

Related videos

Summary

00:00

Accounting Cycle Tutorial: Chart of Accounts to Balance Sheet

  • Video tutorial on complete accounting cycles, covering chart of accounts, general entries, dynamic ledger, trial balance, income statement, and balance sheet.
  • Start with creating a chart of accounts in a spreadsheet, listing all accounts for general entries.
  • General entries include date, description, accounts, debit, credit, and comments, converted into a table for easy entry.
  • Chart of accounts divided into assets, liabilities, equity, expenses, and revenue, with revenue further divided into revenue and contra revenue.
  • Individual accounts opened for transactions, like establishing a business with a 1 million investment in cash on Jan 1, 2022.
  • Entries made for depositing 5 lakhs in the bank on Jan 2, 2022, and purchasing inventory of 6 lakhs on credit from Mr. Zubair on Jan 3, 2022.
  • Further transactions include purchasing inventory of 2 lakhs through a bank check from Mr. Ahmed on Jan 4, 2022.
  • Sale of inventory for 4 lakhs in cash on Jan 10, 2022, with entries for sales and cost of goods sold recorded.
  • Sale of inventory for 3 lakhs on credit to Mr. Kazim on Jan 15, 2022, with corresponding entries for sales and cost of goods sold.
  • Payment of electricity bill of 15,000 through the bank and payment of salaries of 100,000.

24:03

Financial Transactions and Ledger Preparation Techniques

  • Business establishment and depositing funds in the bank on 30th Jan 2022.
  • Purchase of inventory and subsequent sale, with COGS involved.
  • Payment of electricity bills and salaries.
  • Payment of 100,000 to Mr. Zubair for inventory on 30th Jan 2022.
  • Sale of inventory to Mr. Kazim and receiving 150,000 on 15th Feb 2020.
  • Preparation of a ledger using journal entries by creating a pivot table.
  • Setting up the pivot table to display accounts, debit, credit, and balance.
  • Creating a calculated field for balance as debit minus credit.
  • Using slicers to filter accounts and view specific details like cash ledger.
  • Utilizing the trial balance to prepare an income statement, including revenue and expenses calculations.

46:02

Creating Income Statement and Balance Sheet

  • To create an income statement, add a dark border on the top and left side for a polished look.
  • Ensure all entries made in the income statement are also recorded in the ledger.
  • Expenses like taxes, payable to the government, are part of the income statement under current liabilities.
  • After completing the income statement, move on to preparing the balance sheet.
  • The balance sheet should be dated, not for a period, and follows the equation: assets = liabilities + equity.
  • Divide the balance sheet into two sections: assets and liabilities plus equity.
  • List fixed assets first, followed by current assets, ensuring proper formatting and totaling for accuracy.
Channel avatarChannel avatarChannel avatarChannel avatarChannel avatar

Try it yourself — It’s free.