Financial Statements in Excel

[primuspaul]

I have data that comes in as individual accounts (could be 100-200 accounts) along with an amount next to it (two columns).

Account name......Amount
Account name......Amount
Account name......Amount
Account name......Amount

I need an automated way of generating financial statements using that data. They should look something like this:

https://www.aicpa.org/InterestAreas...forSMEs_SampleFinancialStatements_FSUsers.xls

Unfortunately, the statements aren't really "logical" as far as a computer is concerned. Each account on the account chart could belong to one type of statement and not to another, or perhaps to multiple statements. In each statement, the accounts would NOT appear using the account name found in the chart, but would be allocated to aggregate accounts. For example, TD Bank $10,000, Bank of America $11,000, and Petty Cash $1,000 on the account chart could appear on the balance sheet under the "ASSETS:" category as Current Assets $22,000.

I thought about using pivot tables and adding columns to the chart of accounts that had markers indicating the financial statement and aggregate category in said statement to which each account belongs, but it never comes out right. Many of the financial statements simply have illogical formats. I'm sure you can see that yourselves. Has anybody tackled this problem? Is there a working template available? What approach would I take to doing this the right way?

 

[Scott T]

Maybe something like this

Chart of accounts

	A	B
1	account	category
2	TD bank	cash
3	Bank of America	cash
4	Petty cash	cash
5	Comp eq	PPE

<tbody>
</tbody>

Data

	A	B	C
1	TD bank	10,000	cash
2	Bank of America	11,000	cash
3	Petty cash	1,000	cash
4	Comp eq	3,000	PPE
5	Etc.

<tbody>
</tbody>

in C1 a vlookup to get the category

=VLOOKUP(A1,coa!A2:B5,2,0)

Then on your FS use a sumifs

	A	B	C	D
1	CURRENT ASSETS:
2	Cash		22,000
3	Trade Accounts Receivable
4	Inventory
5	Prepaid Income Taxes
6	Total Current Assets	XXXX

<tbody>
</tbody>

In C2 and copy down

=SUMIFS(data!$B$1:$B$4,data!$C$1:$C$4,A2)

 

[primuspaul]

I use something like this now. The problem I have is that I need to add accounts to the financial statement manually instead of having them generated automatically based on additions to the chart of accounts. Formatting is very important for financial statements and it turns into hell when I have to add a category and then reformat everything. Then there is the issue with changes to category names on the financial statement, which would also need to be updated manually in the chart of accounts. The closest thing I've seen that does this automatically is QuickBooks statement writer, but I need to do this in excel, or at the very least have a quick and easy way of importing it into QuickBooks, which does not appear to be possible.