How to easily Automate Your Customized Excel Financial Statements

Although Excel provides a great deal of flexibility in creating financial statements, it has several limitations:

1. It is not integrated with accounting data.
2. It requires a substantial amount of data manipulation.
3. It is difficult to ensure completeness of the financial statements without excessive review.
4. Excel cannot easily and automatically update the financial statements.

If you use Excel to create financial statements, you will want to read on.

In this article, we’ll show how the patented FSM technology allows you to easily produce exactly the custom financial reports you need in Excel. They are automatically updated which saves you hours of work, increases accuracy, and reduces errors.

Importing the Trial Balance data

The first step in creating the financials is to export trial balance data from ANY accounting system. Assuming your accounting system can export to a file type that Excel can read, such as csv, xlsx, txt, and so on, you will be set.

After installing the ExcelFSM add-in, you’ll be ready to import the file as shown in this short video.

Note: there is an optional QuickBooks integration described below.

In summary, you:

1. Start Excel, open a blank workbook and click the FSM tab.
2. Click the Read Trial Balance Data button.
3. Select the Excel Trial Balance source file. A new temporary worksheet called “Data” is created for your convenience, while you specify additional information about the Trial Balance.
4. Specify the name of the enterprise.
5. Specify the as-of date for the Trial Balance.
6. Specify the letters of the worksheet columns.

This renames the workbook, opens the Chart of Accounts and generates a “ReadTBData printout” worksheet for your review.

Using QuickBooks?

If you are using QuickBooks (Desktop or Online), you can save the manual export/import step by connecting ExcelFSM directly.

The trial balance data can be retrieved directly from the QuickBooks company file, for both QuickBooks Desktop and QuickBooks Online.

Also, if you use classes … a Trial Balance AND a General Ledger WITH classes can be produced!

Once the trial balance has been imported, you are ready to build the financial statements.

Building financial statements using FSM buttons

The ExcelFMS add-in creates a tab in Excel called FSM. You can use the icons on that tab to easily insert specific values ANYWHERE in your worksheet. You are not limited to the report format created by your accounting system. You are free to use the flexibility of Excel to have total control over the structure, layout, and design of your financials.

To build the financial statements

The FSM add-in enables you to create and manage financial statement line items groups based on the underlying accounts. For example, grouping multiple bank accounts into the Cash and Cash Equivalents line item. Once the initial report has been created and the account groupings defined, future updates are simple.

This short video demonstrates how easy it is to use the FSM tab to group accounts and insert ANY value into ANY cell. This flexibility allows you to create the financials in the precise format desired.

1. Name: Initialize cell with Name
2. Date: Insert the as-of date of the accounting period into the active cell.
3. Item balance: Group accounts into an item and insert the item’s balance into your financial report.
4. Total: Group items into a total and insert the total’s balance into your financial report.

Note: balances comprised in any financial statements are of two types: financial statement Items and Totals.

Automatically updating the financial statements

Once the financial statement has been created, it is VERY easy to import updated data.

1. From the FSM ribbon, click the “Read Trial Balance Data” button.
2. Select the Excel Trial Balance source file
3. Specify the as-of date for the Trial Balance
4. Click OK

This creates a new FSM workbook, generates a “ReadTBData printout” worksheet, and automatically updates the financial statements. Video.

This is beautiful because now that all of the accounts have been mapped to the line items, the update process is easy.

Useful FSM features

FSM includes helpful features that address and solve many common issues, including ROUNDING!

Rounding

The FSM “Rounded” button displays rounded values and uses them to accurately compute account balances.

Item Sense

FSM provides an easy way to present accumulated depreciation between parentheses by simply changing the sense of the item.

NewID

When a balance sheet account must be classified based on its balance (e.g., a bank account that must be classified as a current asset (cash) when it has a debit balance and as a current liability (overdraft) when it has a credit balance), FSM provides an easy solution. The “NewId” button replaces the item’s unique identifier with a new one in the FSMITEMBALANCE formula. This classifies balance sheet accounts based on their balances.

Details

Item balance details can be displayed by toggling the “Details” button.

Select Period

With the “Select Period” button, the user can select a range of cells and the period or period range for item balances.

Period Range

Select the period range to prepare Periodic Financial Reports (Monthly, Quarterly, etc.), and the Statement of Cash Flows.

Adjusting Entries

Adjusting entries can be recorded to change account balances. The user can toggle the effects of adjusting entries with the “Adjusted Balances” button.

Chart of Accounts

The Chart of Accounts, built from Read Trial Balance Data, can be viewed, edited, re-ordered, and printed. Accounts, such as presentation accounts, can be inserted into the Chart of Accounts. New accounts are added to the chart of accounts through the Read Trial Balance Data process. The user can select the Chart of Accounts printout and display the account groupings.

Amended Trial Balances

Amended trial balances can be processed and automatically update the financial reports.

Additional Optional Features

The add-in offers several optional features if needed.

Consolidated Financial Statements

Consolidated Financial Statements: The Consolidation list can combine files of various types, e.g. Excel files, QuickBooks Desktop Company files, QuickBooks Online Company files. The reading of selected amended trial balances to update a Consolidated FSM workbook with amended trial balances is also allowed.

Dynamic Working Papers

Dynamic Working Papers: Trial Balance, Leadsheets, Grouped Account Sheets

Drill Down

General Ledger Drill Down: the trial balance and the entire General Ledger of any accounting software can be read, organized and manipulated, allowing you to “drill down” and obtain the detail of an account balance and a transaction from the custom financial statements, as well as from the dynamic working papers.

QuickBooks Integration

Direct connection to QuickBooks Desktop or QuickBooks Online data to eliminate the manual export/import process.

WordFSM

Also note that if you need to create financial statements in Microsoft Word, there is a separate add-in for that!

Conclusion

Excel offers everything needed to prepare custom financial statements except a way to automate the process. It needs only the integration of the functionalities of the FSM patented technology to automate the process of preparing and updating custom financial statements with Excel.

For a demonstration, feel free to check out this free webinar (no login required):

• Free FSM Webinar (YouTube)

With the FSM functionalities, Excel then becomes a universal financial statement generator (or Financial Statement Writer) for any accounting software, and a powerful Trial Balance program.

These patented FSM functionalities are available thru the ExcelFSM add-in, which is only $49.95 per year. Once installed on a system, it can be used to import data from unlimited client files.

We also offer a free trial so you can check it out risk-free:

• Free 30-Day Trial

Recent reviews:

• “Saved HOURS of work producing our financial statements.”
• “Finally, an add in that releases the power of Excel in the preparation of financial statements.”
• “The flexibility is as unending as Excel’s because you’re in Excel.”
• “The presentation of our reports since adding the tool is phenomenal.”
• “It allows me to produce exactly the financial reports our board of directors need.”

Special Excel University Offer

If you are interested in buying ExcelFSM, we are offering a FREE $24.95 optional feature of your choice as an Excel University promotion.

Pick from the Consolidated FS option, the QuickBooks Desktop connector, or the QuickBooks Online connector to add to the base subscription of $49.95 per-year per-seat and use code XLU during checkout.

Note: for any questions, please contact FSM directly or email them: [email protected].

Disclosures and Notes

• This is a sponsored post for FSM. FSM is not affiliated with nor endorses any other products or services mentioned on this site.
• 100% of sponsor proceeds fund the Excel University scholarship and financial aid programs … thank you!
• If you’d like information on becoming a sponsor, please check out our sponsorship opportunities page.