How to Create End-of-Month Charts in Excel for Accountants
How to Create End-of-Month Charts in Excel for Accountants: At the end of the month, accounting professionals often feel exhausted and under pressure due to the large volume of data, charts, and reports they need to handle. Therefore, the following article aims to share simple steps to create end-of-month charts using Excel software to reduce stress and enhance accounting efficiency in businesses.
For the Inventory Summary Table in Excel
Purpose: The Inventory Summary Table is used to track and control various items in stock, including materials, tools, finished goods, or merchandise.
Excel Functions Used:
Columns “Quantity” and “Total Cost of Purchases in the Period”: Use the SUMIF function to gather data from the purchase invoices. (The SUMIF function calculates the total of cells in a specified range that meet specific criteria.)
Column “Quantity Sold in the Period”: Use the SUMIF function to gather data from the sales invoices.
Column “Unit Price of Sales”: Calculate using the weighted average unit price at the end of the period.
Columns “Item Code,” “Item Name,” “Unit of Measurement”: Use the VLOOKUP function to retrieve data from the previous month’s closing balance. (The VLOOKUP function searches for a value in the first column of a table range and returns a value in the same row from a specified column.)
For the Allocation of Short-term, Long-term Prepaid Expenses, and Depreciation of Fixed Assets in Excel
Purpose: The Prepaid Expense Allocation Table is used to reflect actual expenses related to the business’s operations.
Excel Functions Used:
Step 1: Copy the tables from the previous month and paste them below.
Step 2: Delete the data in the column “Accumulated Depreciation (Allocated) from the Previous Period.”
Step 3: Use the VLOOKUP function for this column to retrieve the corresponding value from the previous month.
Additionally, you can use the “Number of Months Allocated” and “Monthly Allocation” to calculate.
If new fixed assets are acquired, the accounting staff should declare them in the appropriate corresponding tables.
For the Month-end Trial Balance Table in Excel
Purpose: The Month-end Trial Balance Table is used to monitor the movement of accounts during the period. It serves as an intermediary step between the General Ledger and Financial Reports, providing essential data for generating the Financial Statements.
Excel Functions Used:
Columns “Account Code,” “Account Name”:
Method 1: Use the VLOOKUP function.
Method 2: Copy from the chart of accounts (note that the chart of accounts must always be regularly updated and complete).
Column “Beginning Balance”: Use the VLOOKUP function to retrieve the previous month’s closing balance.
Columns “Debit and Credit Transactions in the Period”: Use the SUMIF function to sum data from the General Journal for each month.
Note: The Total Debit must always equal the Total Credit.
How to Create End-of-Month Charts in Excel for Accountants
End-of-Period Balance:
Debit Column = Max(Beginning Balance Debit + Debit Transactions in the Period – Beginning Balance Credit – Credit Transactions in the Period, 0)
Credit Column = Max(Beginning Balance Credit + Credit Transactions in the Period – Beginning Balance Debit – Debit Transactions in the Period, 0)
Calculate the total for each Level 1 account (only for accounts with transaction details): Use the SUBTOTAL function with the formula: =SUBTOTAL (9, range of cells to calculate the total).
These are the steps to create three commonly encountered end-of-month charts using Excel software for accountants. As you can see, functions such as SUMIF and VLOOKUP are frequently used. Therefore, it is essential to understand how to use and the purpose of these two functions to apply them correctly when creating charts.
