Microsoft Office: Tips for Excel-based financial reports
Q. I am new to a company that produces Excel–based formatted financial reports and shares them in a PDF with the executive team. My preference would be to receive these reports in an Excel–based format. Before I request this change, are there valid reasons we should be sharing our Excel–based reports in a PDF?
A. Some companies prefer PDF–based reports because they open automatically with the Adobe Acrobat Reader application, which does not allow users to accidentally delete or compromise the PDF–based reports. In addition, not every employee has Excel loaded on his or her computer, and some who do may be intimidated by Excel because they do not know the application well.
However, I agree with you that Excel should be the preferred file format for sharing financial data produced in Excel. The problem with PDF–based reports is that you can’t easily copy, paste, chart, pivot, or manipulate the data in those reports, making it more difficult to further analyze the data. Reports provided in an Excel–based format allow recipients more flexibility to prepare what–if analysis computations, create charts, and build PivotTables, among myriad other possibilities.
Excel provides the ability to save and share workbooks as either an Excel or a PDF file, so perhaps those who produce those reports would consider sharing those reports with you in both formats. As a reminder, you can share reports as either an Excel file or a PDF file from Excel from the File tab by selecting Share and then selecting the desired file format (Send a copy (for the Excel format) or Send a PDF (for a PDF)), as pictured below.
Once you’ve selected a format option, you enter the email addresses of those recipients (or better yet, an email group name consisting of the email addresses of those recipients), select the desired level of security (View or Edit), include a message (optional), and select Share.
When it comes to producing financial statements and reports in Excel, here are a few formatting rules that may help the reports appear more professional.
1. Include PivotTables. In today’s digital age, most reports are delivered electronically; therefore, you should consider including Excel–based PivotTable reports that allow recipients to dig deeper for additional insights into those financial reports. In addition to including PivotTables, I also recommend you include Slicers to enable the recipient to slice and dice the data (so to speak) to perform in–depth analyses more easily. An example of a PivotTable report with three PivotTable Slicers (Customer Type, Item Type, and Sales Rep) is pictured below.
2. Use the correct alignment. You should keep row labels left aligned to make them easier to read. Typically, you should apply left alignment to all text and right alignment to all numeric data. Dates, percentage calculations, and column headings should usually be centered for better readability.
3. Wrap text for column headings. Wrap text should be enabled for longer column headings, to keep the column widths as narrow as possible, as illustrated in the wrapped versus nonwrapped column heading examples shown below.
4. Align report titles. Each report should have a report title and title heading, consisting of the entity or organization, the title of the report, and the date range(s) for that report. Report titles may be centered atop the width of the report or left aligned. I prefer to leave the titles left aligned for wider reports, so the title is sure to be visible on the recipient’s screen without having to scroll to the right to read the full title.
5. Use bold judiciously. Bold your titles and column headings to make them stand out, and consider bolding subtotals and grand totals as well. However, do not bold your detailed data, because if everything is bold, then nothing is bold. See the comparison in the screenshot below.
6. Avoid color. You should avoid using color in your financial reports for several reasons. For starters, too many colors can make your financial statements look less than professional, except for charts and graphs. Further, if you use a red font to denote negative numbers, it won’t show up when printed in black and white, or copied on a black–and–white copier. In addition, the color of the text may be impossible to read by the approximately 4% of the population who are colorblind. Instead, I recommend using parentheses to denote negative numbers and credit balances. See the comparison in the screenshot below.
7. Use minimal borders. Borders applied to the entire financial report often make the report more difficult to read. In most cases, simple single or double underlines separating the column titles from the data and framing subtotals and grand totals are all the borders you need. See the comparison in the screenshot below.
8. Employ green bar formatting. If you have a wide report with numerous columns, which can be more difficult to read across the width of the page, you might consider applying a green bar accounting paper format to make it more readable. The best way to do this in Excel is to convert your financial report to a Table by selecting the data area to be formatted and then, from the Home tab, select Format as Table, Olive Green, Table Style Medium 4, as pictured below.
9. Use the Calibri font. Introduced by Microsoft in 2007, the Calibri font is basically a skinnier version of the Arial font engineered to make both numbers and text more legible, and the thinner Calibri font makes the numbers easier to read on today’s smaller handheld devices. While the popular Times New Roman font makes text more readable, it is commonly considered more difficult to read when applied to numbers.
10. Charts and graphs. Because charts and graphs are an effective way to present complicated data in an easier–to–read fashion, you should consider adding charts and graphs to your reports. While some CPAs prefer 2D charts because 3D charts can slightly distort the data presented, I would recommend your goal be to add charts that make the report bundle more interesting, even if it involves a few colorful 3D charts. See the examples in the screenshot below.
About the author
J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
Research & References of Microsoft Office: Tips for Excel-based financial reports|A&C Accounting And Tax Services
Source
0 Comments