Excel Jurnal umum, buku besar, neraca saldo dengan rumus VLOOKUP, SUMIF, IF, hanya 9 menit

Summary

In this video, Mari provides a step-by-step guide on preparing a general journal and pre-adjustment trial balance using Excel formulas. The tutorial begins with downloading a provided file containing multiple sheets, including previous balances and account formats. Key techniques include utilizing the VLOOKUP and SUMIF functions to accurately input transactions and compute balances. Mari emphasizes the importance of structuring data properly, calculating debits and credits, and formatting tables for clarity. This comprehensive approach ensures viewers can effectively manage their accounting tasks with Excel.

Takeaways

• 📅 Start by opening the provided Excel file, which contains four sheets for tracking journal entries, balance sheets, and more.
• 📖 The first transaction recorded is the additional capital deposit made on December 1st, which is entered into the general journal using the VLOOKUP function.
• 🔍 To find account names in the balance sheet, use VLOOKUP to easily fill in the journal entries.
• 💰 Input the cash account for the additional cash, and also record data for accounts receivable, supplies, and equipment, crediting the capital account.
• 🔢 Ensure that all transactions from the earliest to the latest are inputted into the general journal.
• 📊 After completing the general journal, the next step is to create a ledger and pre-adjustment trial balance by copying data from the previous balance sheet.
• 📑 Use the SUMIF function to compute totals for each account in the ledger based on the journal entries.
• ⚖️ For the pre-adjustment trial balance, calculate the balances by adding debits and credits from both the balance sheet and the ledger.
• 📉 Apply logic tests with the IF function to determine account balances, comparing debit and credit totals to ensure accuracy.
• 🗂️ Finally, format the table neatly, applying accounting number formats and borders for better clarity and presentation.

Q & A

What is the main topic discussed in the video?

The video discusses how to create a general journal and a trial balance before adjustments using Excel formulas.

What resources are provided to the viewers at the beginning of the video?

Viewers are encouraged to download a provided file that contains four sheets, including problems, previous month's trial balance, general journal, and format for the ledger.

What is the first transaction mentioned in the video?

The first transaction mentioned is an additional capital deposit into the business on December 1.

How does the presenter suggest using the VLOOKUP function?

The presenter suggests using the VLOOKUP function to input account names and numbers in the general journal based on data from the previous month's trial balance.

What formula is recommended for calculating totals in the journal?

The presenter recommends using the SUM function to calculate totals in the general journal.

How are the ledger and trial balance prepared?

The ledger and trial balance are prepared by copying the initial trial balance data and then using the SUMIF function to calculate debits and credits based on journal entries.

What logic is used to calculate the trial balance before adjustments?

The logic involves comparing the debit and credit balances from the initial trial balance and ledger, and performing calculations to ensure the balances match.

What formatting techniques are suggested for the final document?

The presenter suggests using accounting number formats and applying borders to tidy up the table.

What is the significance of completing the journal and ledger accurately?

Accurate completion of the journal and ledger is crucial for producing reliable financial statements and ensuring the integrity of financial reporting.

How does the presenter conclude the video?

The presenter concludes by thanking the viewers and expressing hope that the information was helpful.