Commercial Loan Analysis Spreadsheets and Reporting

The Pros and Cons of Commercial Loan Analysis Worksheets

Posted by Fiscal on January 10, 2023

Helping a business grow or a new business open its doors is one of the most satisfying parts of working in commercial lending. However, the process of analyzing and approving small business loans can also be one of the most complicated aspects of the industry.

Community banks and credit unions often rely on commercial loan analysis worksheets to ensure they are granting sound loans to qualified businesses that will generate income for the institution.

Excel has served community banks for years for spreading because it is simple to implement and offers much-needed flexibility. Yet for many banks today, the limitations and challenges that come with Excel call for a better method.

In this article, we’ll cover the pros and cons of commercial loan analysis worksheets and how you can overcome the most troublesome worksheet challenges.

Complexities of Commercial Lending for Small Business

Commercial lending for small businesses requires a special set of skills and an ability to thoroughly understand the financial landscape–not only the borrower and personal guarantors but also any related business entities. This requires multiple spreadsheets with diverse and disparate sources of financial data rolled into a larger formula used to calculate global cash flow.

Once the borrowers’ financials are understood, banks also have the task of determining the deal’s risk rating based on the loan type (whether interest-only, hybrid, P&I, or another product), borrower’s character, and other factors.

It’s a skill that takes time, patience, and a keen eye for detail to be done right. It also requires a toolset that is flexible enough to factor in the many exceptions and adjustments that comes with lending to a small business.

The Pros and Cons of Commercial Loan Analysis Worksheets

Worksheets help make sense of the chaos, yet have some common pitfalls that can reduce their usefulness if not employed thoughtfully. To better understand what practices work best, and which cause problems, let’s dive into the pros and cons.

What are the benefits of commercial loan analysis spreadsheets?

  1. They’re Flexible – With its built-in formulas and customization options, Excel offers countless ways to organize, format and calculate data. This flexibility can make it a good choice for commercial lending, especially when underwriting for small businesses. Whether it's changing a formula, adjusting a template, or entering tax return information from one year to the next, the commercial loan analysis worksheet options are endless.
  2. They’re Free – Most banks today own Microsoft Office, the suite of programs that includes Excel, Word, and PowerPoint. It’s hard to beat the price of a tool your organization already owns.
  3. They’re Familiar – Because Excel is ubiquitous in the banking and finance industry, it is also familiar to loan officers and credit analysts everywhere. It requires almost no special training for basic tracking and spreading tasks.
  4. It’s Easy to Make Quick Changes – Not only is Excel flexible, but that flexibility can come with the click of a mouse. Worksheets can be adjusted to fit specific needs depending on the type of loan and type of customer.
  5. They’re Powerful – When it comes to commercial lending, Excel is only limited by your knowledge and the amount of time you’re willing to invest. If you know how to implement advanced formulas, it will not only save time, but also help effectively determine global cash flow and track exceptions and adjustments for easy review.

What are the cons of commercial loan analysis spreadsheets?

  1. They’re Too Flexible – While the flexibility of Excel makes it an appealing choice for small business lenders, this flexibility can create unintended consequences. A lack of version control even in well-designed templates means they can easily get ruined. When a loan officer makes changes or breaks a formula and then inadvertently saves those changes to the template, that mistake carries through to all future users. It leaves loan officers and analysts on guard, never quite certain that the template works as it should, which requires additional review and time.
  2. It’s Easy to Make Big Mistakes – Just as formulas are easy to set up, they are also easy to break. Too often loan officers will make changes to a worksheet that may impact a referenced cell elsewhere, setting up an error that wastes valuable time finding and fixing.
  3. Files Are Easy to Lose – Many commercial loan analysis worksheets are saved in only one place, often locally on the analyst’s or loan officer’s own work computer. Unless they’ve created a backup or a great digital filing system, that worksheet can easily get misplaced, accidentally deleted, or overwritten.
  4. Lack of Support – When you’re stuck trying to fix an error or set up formulas in a new worksheet, there’s no direct support to call. Excel offers free resources, and Google and YouTube can direct you to tutorials and how-to's, but this requires you to spend time hunting for answers that could have been better spent elsewhere.
  5. No QA/QC – Whether you use a template or build your own worksheet from scratch, that spreadsheet likely does not go through a third-party vetting process. Quality assurance or quality control review is nice to have, but few community banks or credit unions have the resources to regularly review every worksheet they use to ensure proper function and conformity to best practices. If examiners question something, you stand on your own as the one who created, validated, and regularly uses the spreadsheet.

If you’re feeling frustrated about the time spent on the limitations of commercial loan analysis worksheets and spreadsheets, or worried about formula errors and regulator scrutiny, then a commercial loan analysis software solution might be right for you.

The Benefits of Commercial Loan Analysis Software

While some software for commercial loan analysis maintains rigid rules that limit the ability of community banks and credit unions to make the adjustments they need, software solutions like FISCAL were created to offer a strong middle ground between the need for flexibility and consistency.

By having a tool that provides flexibility within consistent, thoroughly tested templates, community banks and credit unions can address the challenges they face when using Excel spreadsheets for commercial loan analysis, as well as meet their tracking and spreading needs.

The top features of commercial loan analysis software:

1. Vendor Expertise

Instead of creating worksheets on your own, FISCAL’s software solution was created by seasoned professionals trained in software development and financial analysis. The templates have been tested thoroughly before release and have since been vetted by thousands of end users.

In addition, FISCAL has a support team with financial expertise you can call for help with or questions about all kinds of issues, not only when problems arise.

2. Version Control

No more broken formulas and accidental overwrites. FISCAL’s built-in templates can be modified for a specific customer and still remain a clean, uncompromised template for the next user.

Here's a quick look at just a few of the available Business Financials templates:

Business Financials templates

And here's a sample of a General Business template:

General Business template


3. Time-Saving Features

Nearly every challenge banks have with Excel can be distilled into one single problem: wasted time. Whether it’s from a lack of support, broken formulas, or losing an important file, using a software solution like FISCAL eliminates that time wasted.

With a centralized database and routine internal server backup, you always know there’s a copy of important files, and there’s no double checking formulas on each worksheet, since templates are tested and protected.

Why Choose FISCAL as Your Commercial Loan Analysis Software

Saving time on commercial loan analysis is about more than speed. It ensures loan officers’ time is well spent on the tasks that matter, understanding the qualifications of the borrower and income potential of the loan. Less time spent searching for files and answers online or fixing broken formulas also reduces needless frustration and gives them more time for business development.

To learn more about how FISCAL Forward can meet your bank’s needs contact us today.

Request a Demo of FISCAL