By Robert J. Swetz, CPA (Inactive)

Does your accounting software limit your ability to create financial statements that are both nice looking and useful? Believe it or not, it’s easy to create beautiful financial statements in Excel with a little bit of setup and a few simple steps each month.

Here are five steps to get the job done.

Step 1: Create a Financial Statement Structure

Create three tabs in an Excel worksheet for the three 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 under each tab, create the structure for each statement, including a header and line item captions. Use a font that you think makes the statement look professional and engaging. Make sure to include subtotal and total lines as needed based on your desired structure for each statement.

Step 2: Create a Trial Balance Layout

Create another 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 it into this tab. Depending on your export, your setup could be a little different than the one shown below, but this example should give you an idea of what it will 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.

Rounding – If your balances are not in a single column, do that now with a formula like this “=B6-C6” in the DR (CR) column. (In the example above it would be Column D.) In the rounded column (E), your formula would look like this “=ROUND(D6,2).” The 2 would be rounding to 2 decimal places; 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.

Mapping – 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 on the BS Mapping column in the first cell next to your first Trial Balance 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: =’Balance Sheet’!$A$9.

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.

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 Profit & Loss 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: Pull TB into Financial Statements

This is where the magic happens on the financial statements.

In Column B on your Balance Sheet tab 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. 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 tab, with the only difference being you are pulling from the IS Mapping column on the Trial Balance tab.

Step 5: Wrap It Up

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 other two financial statements. Of course, you would need to have comparative statements to do this.

Once you have completed this setup, you can create additional columns on your financial statements to do whatever analysis you deem necessary in the circumstances. You can also copy and paste new trial balance numbers into the template each month to have updated statements in minutes.

Robert J. Swetz, CPA (Inactive), is controller consultant, Tier One Services LLC, and a member of PICPA’s Corporate Finance Cabinet.

Sign up for weekly professional and technical updates in PICPA's blogs, podcasts, and discussion board topics by completing the form here.