How to do Financial Reporting in Excel?
Are you dependent on IT to build your very own Financial Statements? Have you ever manually built a Financial Statement in Excel? For a few, it is a long easy manual process, for many others is not only difficult but a long process to build and maintain. The lengthy manual process is prone to errors. Sometimes, finding the error is painful enough to find better ways to deal with the process.
Explaining your template to an Auditor or Manager can be challenging. IT, Auditors, and Managers may question the reliability, security, and if not you, who else can maintain these templates? Transitioning your templates to others and continue the process could be challenging to maintain and support. Others may not have the same skills in “Excel” if you ever leave the enterprise. All the above is concerning.
What is Financial Reporting?
Financial Reporting involves disclosing financial information to the various stakeholders about the organization’s financial performance and financial position over a specified period.
These stakeholders include – investors, creditors, the public, debt providers, governments, and government agencies.
There are two types of Financial Reporting –
1. Financial Reporting for various stakeholders
2. Management Reporting for Internal Management of an Organization.
This is the true story of a Certified Public Accountant (CPA) we met from a County of Public Schools who builds Financial Statements manually in Excel. The CPA mentions that he can develop his Financial Statements in hours. So, how complex are his financials? We took a deep look into his process. He has a compilation of serious function formulas, Pivot Tables, and many other Excel features that could be considered advance for many Excel users.
There is a solution that can help CPA or anyone in FP&A, Finance & Accounting to build Financial Statements secured in Excel. If you would like to skip some of the content, jump into the Solution section below, otherwise read The Process below and see what CPA must go through and check if you can relate through the “pain”:
First, the CPA runs a Trial Balance from the ERP System and opens it in Excel. Once it is in Excel, the manual process begins; the CPA would build every possible Financial Statement Template out of the Trial Balance.
- Building reference tables. For example: TEXT to COLUMNS on your GL Segment String and description for each segment…manually!
- Add Rollup values or Parent Values manually in Excel to create a summary of your Financial Statements.
- Flip the Amounts from negative to positive and vice versa (Revenue from -$1,000 to $1,000)
- Change Transaction GL Posted Date (MM-DD-YYYY or 09/03/2020) to Fiscal Year (YYYY or 2021) using multiple IF function formulas in Excel. If it is September 2020, then it is the Fiscal Year 2021.
- The “famous” VLOOKUP to pull a Month, Description of Segments from your reference tables.
- Grouping a SUM of values between rows by using a great function known as SUMIF.
- The combination of VLOOKUP and SUMIFS in the same formula to lookup and sum values between rows.
- The use of LEFT & RIGHT functions to pull specific relevant information from your Segment values.
- The combination of more than three functions like IF, AND, VLOOKUP & SUMIFS on the same function to extract a result.
To convince the CIO or CFO to invest in a solution, the CPA needs a breakthrough solution that could fit into the enterprise’s culture and get the best ROI. However, researching through vendors is not an easy task. The CPA and the IT department had rigorous top 10 lists of requirements.
The software must:
1. Be Secured.
2. Build Online or Real-Time reporting in Excel.
3. Refresh with one click.
4. Be agnostic or connect to a cloud source.
5. Be easy to maintain and allow System Admin or IT control access.
6. Definitely be Easy to Use. Any functional user should build financial statements.
7. Have Drilldown to all details.
8. Help with Tools to build financial statements in Excel.
9. Have different ways to Share securely.
10. Be able to sign on via SSO or Active Directory.
The CPA and IT went through many vendors, and only one software was able to meet the top 10 requirements, SplashGL, a financial reporting software by SplashBI.
SplashGL is a Plug-in that can build Financial Statements secured in Excel. It can eliminate many of the manual processes that the CPA is going through via Excel functions. During the call, the CPA explained The Process (above), and SplashGL presented these solutions:
|Excel – CPA Process
|It can connect to Oracle EBS & Oracle Cloud in Real-Time* or using a Cube
|Trial Balance data dump and multiple function formulas. Numbers are static and must manually refresh the numbers
|Connects via SSO, Active Directory and others. Leverages Oracle Security automatically.
|The security is manually handled by the user (CPA).
|It can drill down from a general ledger balance into all details. Making reconciliation an easy step for month-end reporting.
|There are no journal details and sub ledger details in a Trial Balance. The CPA must run multiple reports from general ledger and subledger level to reconcile balances. This is one of the most complex and manual process by any department.
|It can build a Balance Sheet, Income Statement, Cash Flows, and Retain Earning in minutes. One-click refresh capability for all reports.
|The CPA must update the Trial Balance and re-do the manual process. Updating the financial statements multiple times manually in the same period becomes a cumbersome task.
|Tools to Burst and Distribute systematically. Create the Distribution template and re-use it.
|The CPA must build each Financial Statement, sort, split, and distribute it manually.
*Real-Time only for Oracle EBS. Real-Time for Oracle Cloud coming soon.
After a 90-minute DEMO, the CPA and IT concluded that SplashGL met all requirements and was enough to talk to their leaders and move forward with a solution. Their reporting future could reshape all departments in hope of getting the best ROI.
Fast-forwarding six (6 ) months, on a follow-up conversation, the CPA confirmed that month-end and financial reporting week was able to cut down two (2) days. This is two days cut across all departments because of SplashGL. Maintenance and user adoption was a lot better than anticipated. The CPA was cheerful and expressed that they almost gave up through vendors’ research because they thought that their requirements were too ambitious and the technology did not exist. Thankfully, they found us and shared that our support system was fast and responsive.
The SplashGL Difference!
SplashGL provides you access to live, real-time, drillable data right in Excel! Refresh complete financial packages with the click of a button, with the capability to drill into balances on your report. With SplashGL, you no longer have to export and format your accounts every month.
SplashGL has a built-in Journal and Budget Loader. Quickly load your Journals, Forecasts, and Budgets right in Excel!
SplashGL empowers users to access popular reports like FSG, Trial Balance reports, Account Inquiries, and more! You do not need to stumble through your ERP.