PART 7 PEMBUATAN APLIKASI EXCEL AKUNTANSI 2023 | LABA RUGI

Summary

In this tutorial, Sarjayadi AW guides viewers through the process of creating a simple accounting application using Excel. The video covers the steps to build a Profit and Loss (Laba Rugi) report, from setting up a new sheet, formatting, and inputting account codes to using formulas for revenue, costs, and profit calculations. With detailed instructions and clear examples, the tutorial helps users automate financial data entry, linking accounts dynamically and calculating key financial metrics like gross profit, operating profit, and net profit. The goal is to help users create a functional and customizable accounting report in Excel.

Takeaways

• 😀 Create a new Excel sheet and name it 'LR' for the income statement (Laba Rugi).
• 😀 Set up a header for your report, including the business name, report title, and the reporting period.
• 😀 Ensure the income statement includes columns for 'Code', 'Account Name', and 'Balance'.
• 😀 Enter revenue data and link it to the ledger for automatic updates.
• 😀 Calculate total revenue by summing the relevant accounts and display it clearly in the report.
• 😀 Add 'Cost of Revenue' (Biaya Atas Pendapatan) and sum the related costs from the ledger.
• 😀 Compute Gross Profit by subtracting the total cost of revenue from total revenue.
• 😀 Input operating expenses and link them to the ledger for accurate reporting.
• 😀 Calculate Operating Profit by subtracting operating expenses from Gross Profit.
• 😀 Include 'Other Income' and 'Other Expenses', linking to the appropriate data in the ledger.
• 😀 Use Excel functions like INDEX, MATCH, and IFERROR to dynamically pull account names and balances, ensuring the report updates automatically.

Q & A

What is the main purpose of the tutorial in this video?

The main purpose of the tutorial is to guide viewers through the process of creating a simple accounting application in Excel, specifically focusing on building an income statement and incorporating various formulas for automatic data processing.

Why was there a gap of 14 days between the previous and current video?

The gap of 14 days was due to other activities that prevented the tutorial creator from continuing the video series. However, the tutorial has now resumed.

What is the first step in creating the income statement in Excel as shown in the tutorial?

The first step is to create a new sheet in Excel and rename it to 'LR' (short for 'Laba Rugi', which means Income Statement in Bahasa Indonesia).

How should the income statement be formatted in Excel?

The income statement should include the company name, the report title ('Laba Rugi'), and the period for the report (e.g., 'January 1 to January 31, 2024'). The format should also include columns for account code, account name, and balance.

What formula should be used to pull account names and balances from another sheet?

The formula used is a combination of `INDEX` and `MATCH`. These formulas allow the income statement to dynamically pull account names and balances based on the account code from a separate 'Chart of Accounts' sheet.

How can errors be handled in the formula when account data is missing or incorrect?

To prevent errors in the formulas, you can use the `IFERROR` function. This will replace any errors with a blank value (empty cell), ensuring the spreadsheet remains clean and functional.

What is the purpose of the 'Total Income' and 'Cost of Goods Sold' sections?

The 'Total Income' section calculates the total revenue from sales, while the 'Cost of Goods Sold' section calculates the costs directly related to producing the goods or services sold. These two sections are crucial for determining the company's gross profit.

How do you calculate 'Gross Profit' in the income statement?

Gross Profit is calculated by subtracting the 'Cost of Goods Sold' from the 'Total Income' (Revenue). It represents the profit a company makes after covering the costs associated with producing its products or services.

What is 'Operating Profit' and how is it calculated?

Operating Profit (also called operating income) is calculated by subtracting administrative and general expenses (or operating expenses) from the Gross Profit. It reflects the profit a company makes from its core operations.

What is the next step after completing the income statement in this tutorial?

After completing the income statement, the next step is to create a Balance Sheet, which will be covered in the next video. The Balance Sheet will also require similar data organization and formulas.