Is your accounting software limiting your ability to create nice looking financial statements that are actually useful?
Don’t worry, it’s easy to create beautiful financial statements in Excel with a bit of setup and a few simple steps each month.
Step 1: Creating the Financial Statement Structure
Create 3 tabs in your Excel worksheet for the 3 core financial statements. If you do not need a statement of cash flows you can omit that, otherwise your tabs should be named as follows:
- Balance Sheet
- Profit & Loss
- Cash Flows
In Column A create the structure for each statement including a header and line item captions. Use a font that you like to make the statement look professional. Make sure to include subtotals and total lines as needed based on your desired structure for each statement.
Step 2: Creating the Trial Balance Layout
Create a tab in your worksheet called Trial Balances, or something along those lines and set it up as follows:
- Column A = Account
- Column B = Debits
- Column C = Credits
- Column D = DR (CR)
- Column E = Rounded DR (CR)
- Column F = BS Mapping
- Column G = IS Mapping
Next, export the trial balance from your accounting software and copy and paste into this tab. Note that depending on your export, your setup could be a little different than the one shown below, but this should give you an idea of what it should look like…
You can also add columns between E and F for things like budget or prior years.
Step 3: Rounding & Mapping
Once your trial balance is imported you need to round your balances and map each line item to the financial statements.
If your balances are not in a single column, do that now with a formula like this “=B6-C6” in the DR (CR) column. Then in the rounded column your formula would look like this “=ROUND(D6,2)” The 2 would be rounding to 2 decimal places, so you can choose whatever rounding you would like. For example, 0 would be no decimals, while -2 would round to the nearest hundreds.
Make sure to add a line at the bottom of your rounded trial balance column that sums the trial balance line items. This will be used to accommodate rounding differences in the next step.
This may be the most critical step to make your financial statements dynamic. You will also see how this step makes it possible to easily assign line items in Step 4 without having to add each cell to the financial statements.
Click in the BS Mapping column in the first cell next to your first TB line item and hit the “=” sign. Now go to the Balance Sheet tab in your worksheet and click on the caption for “Cash & Cash Equivalents.” (I’m assuming your first line item is cash, which it usually is.) Hit the F4 Key to create an Absolute Reference, then hit enter. Note that the resulting formula in the BS Mapping column will look like this…
The “$” before the A and 9 represent an absolute reference. You can now copy and paste this formula to all line items on your trial balance that map to the balance sheet line “Cash & Cash Equivalents”
Follow this same procedure until you map all of the line items in the BS Mapping and IS Mapping columns.
Also, remember in the previous step I suggested you SUM the Rounded DR (CR) column. Map that column to Accounts Payable in the BS Mapping column. This will take care of any rounding differences on your financial statements.
Note that all P&L accounts should be mapped to “Retained Earnings” on the Balance Sheet in the BS Mapping column. The balance sheet accounts will be blank in the IS Mapping column. Here is an example of how this should look…
Step 4: Pulling the TB Into the Financial Statements
This is where the magic happens on the financial statements.
In column B on your Balance Sheet in the cell next to “Cash & Cash Equivalents” we will use the SUMIFS formula to pull the numbers from the Trial Balance tab. Your formula should look like this…
=SUMIFS(‘Trial Balance’!E:E,’Trial Balance’!$F:$F,$A9)
So what does that all mean?
The first part of the formula is the sum range, so the formula is looking to column F on the trial balance tab which is the column where we have our Rounded DR (CR) balances. The next segment is looking at the mapping on the trial balance tab, and the last segment is looking at the line item caption on the balance sheet.
So what we have is the summation of all values from the trial balance tab where the mapping (Cash & Cash Equivalents) matches the financial statement caption Cash & Cash Equivalents.
Note the absolute referencing in the formula. This is intentional so that you can copy and paste that formula to all of the line items on the balance sheet.
Follow this same process on the Profit & Loss statement, with the only difference being you are pulling from the IS Mapping column on the trial balance tab.
Complete your Balance Sheet and Profit & Loss statements by adding the appropriate formulas for subtotals and totals.
Preparing the Statement of Cash Flows is as simple as pulling amounts from the the other 2 financial statements. Of course, you would need to have comparative statements to do this.
Once you have this setup completed, you can create additional columns on your financial statements to do whatever analysis you deem necessary in the circumstances. You can also copy any paste new trial balance numbers into the template each month to have updated statements in minutes.
To Your Success,
P.S. If you would like to dig deeper into this topic you have a few options:
- Leave a comment on this post
- Find me on social media by using the icons at the top of this page
- Set up a 15 minute video call by using this link https://bobswetz.link/meeting15
- How to Write a Blog Post Visitors Want to Read
- Email Marketing Best Practices