EXCEL : Jurnal Umum, Buku Besar & Neraca Saldo hanya dengan 3 rumus (VLOOKUP, SUMIF, IF)

Summary

This tutorial walks users through the process of preparing a general journal, general ledger, and unadjusted trial balance in Microsoft Excel. Using an example from a case study, the video details the step-by-step procedure, starting from setting up the Excel sheets, inputting data for November 2020, and applying formulas like VLOOKUP, SUMIF, and IF. It also explains how to process transactions, calculate account balances, and format data. The guide emphasizes creating a well-organized ledger with clear debits and credits, culminating in a finalized trial balance before adjustments. Ideal for those learning Excel-based accounting tasks.

Takeaways

• 😀 Set up four sheets in Excel: Trial Balance (Neraca Saldo), General Journal (Jurnal Umum), General Ledger (Buku Besar), and Unadjusted Trial Balance (Neraca Saldo Sebelum Penyesuaian).
• 😀 Name the first sheet as 'Neraca Saldo' for the November 2020 trial balance and input all account data as provided.
• 😀 Use the 'VLOOKUP' formula to automatically fetch account names from the trial balance in the General Journal sheet.
• 😀 In the General Journal, input the transaction details, including account numbers, dates, amounts, and reference proof numbers (e.g., BKK 01).
• 😀 After entering all journal transactions, copy the data into the General Ledger and remove unnecessary columns like proof numbers and amounts.
• 😀 For the General Ledger, copy initial balances from the November trial balance and apply sorting and subtotaling to clearly display debit and credit totals.
• 😀 For the Unadjusted Trial Balance sheet, copy the General Ledger data and use the 'SUMIF' formula to sum debits and credits per account.
• 😀 Apply the 'IF' formula in the Unadjusted Trial Balance to check for discrepancies and calculate final balances for each account.
• 😀 Format the table for better readability by adjusting date formats, highlighting sections for debit and credit, and applying colored formatting to differentiate starting balances from new transactions.
• 😀 Use Excel's 'Subtotal' feature to aggregate and clearly display debit and credit totals for each account in the General Ledger.
• 😀 Ensure the final trial balance correctly balances by comparing the debit and credit totals and making adjustments as necessary.

Q & A

What is the first step when starting to create a general journal in Microsoft Excel?

The first step is to open Microsoft Excel and create four sheets. Rename the sheets accordingly: 'Neraca Saldo', 'Jurnal Umum', 'Buku Besar', and 'Neraca Saldo Sebelum Penyesuaian'.

How do you name the range for the 'Neraca Saldo' sheet?

Select the data from the account number to the profit and loss summary, and then name the range as 'NSD Awal' in the named box at the top of Excel.

What formula is used to retrieve account names in the 'Jurnal Umum' sheet?

The VLOOKUP formula is used to retrieve account names. It looks up the account number and fetches the corresponding account name from the 'NS Awal' table.

How do you handle transactions in the 'Jurnal Umum' sheet?

For each transaction, input the account numbers, use the VLOOKUP formula to fill in the account names, and enter the debit and credit amounts. Ensure the dates and transaction reference numbers are also included.

What is the purpose of the 'Buku Besar' sheet?

The 'Buku Besar' sheet is used to record the detailed movements of each account, showing the debit and credit transactions and their corresponding balances.

How do you enter the opening balances in the 'Buku Besar' sheet?

To enter the opening balances, copy the account names, account numbers, and their respective balances from the 'Neraca Saldo' sheet and paste them into the 'Buku Besar' sheet under the corresponding headings.

What does the 'Subtotal' feature in Excel do in the 'Buku Besar' sheet?

The 'Subtotal' feature in Excel is used to sum the debit and credit amounts for each account. It helps to easily calculate the total debits and credits for each account and the overall balance.

How do you calculate the trial balance in the 'Neraca Saldo Sebelum Penyesuaian' sheet?

In the 'Neraca Saldo Sebelum Penyesuaian' sheet, use the SUMIF formula to sum the debits and credits for each account from the 'Buku Besar'. Afterward, use IF formulas to calculate whether the debit or credit is higher and adjust the balances accordingly.

What steps should be followed to format the date in the 'Jurnal Umum' sheet?

To format the date, select the date column, click on the 'Format Cells' menu, choose 'Date', and select the format that includes the month name, such as 'MMMM dd, yyyy'.

How do you remove the word 'total' from account names in the 'Neraca Saldo Sebelum Penyesuaian' sheet?

To remove the word 'total', use the 'Find and Replace' function in Excel. Search for the word 'total' and replace it with nothing, effectively removing it from the account names.