• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

How to Create End-of-Month Charts in Excel for Accountants

0 Comments

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.

How to Create End of Month Charts in Excel for Accountants2Excel 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

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.

Rate this post
27
233 Views
Revealing 10 Excel Tricks Every Accountant Should KnowPrevRevealing 10 Excel Tricks Every Accountant Should KnowJuly 27, 2023
Top 10 Most Common Excel Shortcuts for AccountantsJuly 30, 2023Top 10 Most Common Excel Shortcuts for AccountantsNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Kaspersky Internet Security 2021 1 year 5 devices key Global Kaspersky Internet Security 2021 1 year 5 devices key Global
    Rated 5.00 out of 5
    $45.00
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
Products
  • Buy Windows 11 Professional MS Products CD Key Buy Windows 11 Professional MS Products CD Key
    Rated 5.00 out of 5
    $6.00
  • Microsoft Office Professional Plus 2010 retail Microsoft Office Professional Plus 2010 retail CD Key Global
    Rated 4.90 out of 5
    $11.00
  • Windows Server 2012 R2 Standard Key Global Windows Server 2012 R2 Standard Key Global
    Rated 4.78 out of 5
    $15.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Windows Server 2019 Standard Key Global Windows Server 2019 Standard Key Global
    Rated 4.68 out of 5
    $7.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.71 out of 5
    $99.00
  • Microsoft Visio Professional 2021 Key 1PC Microsoft Visio Professional 2021 Key 1PC $13.00
  • Microsoft Visio Standard 2021 Key 1PC Microsoft Visio Standard 2021 Key 1PC $13.00
  • Microsoft Office Professional Plus 2013 retail CD Key Global Microsoft Office Professional Plus 2013 retail CD Key Global
    Rated 4.97 out of 5
    $11.00
  • Windows Server 2016 Remote Desktop Services 50 USER Connections Key Global Windows Server 2016 Remote Desktop Services 50 USER Connections Key Global
    Rated 4.74 out of 5
    $15.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.