Using Excel in Finance: The Love/Hate Relationship

The Enduring Appeal of Microsoft Excel

Since its release in 1985, Microsoft Excel has dominated the business world. Intuitive and easy to learn basic functions, even beginners can create a basic spreadsheet with a formula in minutes.

Though Excel adds new features and functions regularly, long-time users appreciate the consistency of the application, with basic functions remaining much the same over the years.

Today, Excel remains the most popular spreadsheet application on the market, with approximately 750 million users worldwide using the application for everything from financial modeling to data analysis.

But why?

Why does Excel continue to be the choice of finance professionals to manage their financial data?

What makes Excel’s popularity so enduring?

One of the major reasons why Excel usage remains so high is a simple one. It’s included in a Microsoft Office license, which just about every business has, making it a simple choice based on proximity alone. Compatibility is also important, and Excel certainly shines in that area, with versions available for Windows, macOS, iOS, and Android operating systems. It’s also robust, with users able to create more than a million rows and 16,000 columns on a single spreadsheet.

But let’s be honest: if Excel didn’t perform, it wouldn’t matter if everyone had easy access to it. The truth is that Excel’s structure makes it easy to get started, even if you’ve never created an Excel spreadsheet.

Comfort and familiarity also play a role in Excel’s continuing popularity. For many of us, the first spreadsheet software we used right out of college was Excel. Over the years, as new employees were trained to use Excel, the impetus to change spreadsheet software applications remained nonexistent.

In most cases, there is no need to look for an alternative spreadsheet application. When used properly and for the right reasons, Excel allows users to manage data, identify trends and perform complete financial analysis on their business data.  It’s only when Excel is used for reasons it’s designed for (as an accounting software application for instance), that you’ll run up against Excel’s limitations.

Advantages of Microsoft Excel

Like any financial application, using Microsoft Excel has both advantages and disadvantages, which we’ll go through next.

Advantages

• Easy to get started: While complex calculations, macros, and pivot tables take a significant amount of time to master, even Excel beginners can easily master most Excel functions in a short amount of time. Yes, it’s true that not everyone can become an Excel master, but in most cases, the majority of users will never need to be.
• Built-in formulas: One of the reasons finance teams use Excel is that the formulas are built into the application. While there are instances where creating a custom formula is necessary, for those creating simple spreadsheets, clicking on a formula to include with your data takes mere seconds.
• Tables: The Tables option in Excel is second to none, with even new users able to easily create a table quickly. With more than 50 predefined table styles, custom data sets available, and automatic column calculation, if you need to create data tables, Excel is your best option.
• Scalability: You can create a small worksheet, or enter more than a million lines of data in Excel, making it a great option for small businesses as well as global organizations.
• Password protection: If you need to share your spreadsheet with colleagues, you can password protect the data. This way, colleagues can view spreadsheet data, but cannot make any changes; whether accidentally or purposefully.
• Collaboration: Excel is a good collaborative tool. It’s easy to share Excel files and worksheets, with multiple users able to edit the spreadsheet as needed.

Disadvantages

• Can become slow: One of Excel’s advantages is its ability to store large amounts of data. However, large files can impact the speed of the application, causing it to become sluggish.
• Takes time to master: Again, new users can easily create basic spreadsheets and ever produce charts and graphs. But if you need to use macros, pivot tables or complicated formulas, expect a very steep learning curve.
• Excessive data entry: While it’s easy to import data from other applications into Excel, that data will still need to be formatted properly. And if you’re starting from scratch, you’ll be spending a lot of time inputting your financial data.
• Error potential: Manual data entry increases the likelihood of errors; errors that can be difficult to spot and can easily perpetuate over time. This is particularly true for those using Excel to create financial statements.

Excel would not have endured this long if it wasn’t a valuable tool for finance and accounting professionals. But for all its strengths, Excel is not always the best choice when it comes to accounting.

Common uses for Excel

Using Excel, business owners, CFOs, accountants, and clerks can create everything from a simple spreadsheet to a complex analysis of your business’ performance.

The following includes some of the most common ways that you can use Excel for your business.

Creating a budget

Businesses small and large should always prepare a budget. Unfortunately, many entry-level accounting software applications offer limited budgeting capability, and even high-end applications may not allow you to create a custom budget.

You can download a variety of budget templates to use for your business.

That’s where Excel shines in. Whether you export your numbers directly into Excel or enter them manually, you can easily create a simple budget for your business, using one of the included budget templates available in Excel, or by creating one from scratch. In larger organizations, departments are usually tasked with creating a departmental budget in Excel, then providing that information to the accounting or finance department, where they will combine the budget totals received from each department to create a complete organizational budget.

The only drawback to creating an Excel budget is that you’ll need to manually update budget numbers regularly.

Financial analysis

Excel is an excellent tool for financial analysts; providing numerous functions and formulas that are particularly useful for financial analysis. The following are a few of the most frequently used Excel financial functions and formulas.

1. XNPV – This formula calculates a company’s net present value or NPV for a particular period.
2. XIRR – This formula calculates a company’s internal rate of return, looking at different cashflows over a specific period of time.
3. MIRR – This formula calculates the internal rate of return for an investment, looking at the success or failure of an investment made into a second investment.
4. PMT – This formula is one of the most frequently used formulas in Excel and is used to calculate the present value of an investment by looking at current interest rates.
5. IPMT – Slightly different from PMT, IPMT calculates interest on a specific debt payment rather than investments across the board.
6. IRR – IRR calculates the internal rate of return for cash flows, using the annual interest rate and regularly occurring income.

Altogether there are more than 450 functions available in Excel for financial professionals to take advantage of, all designed to help with the decision-making process.

Creating charts and graphs

One of the most frequently used features in Excel is the ability to create charts and graphs. Using data in Excel, you can easily create pie, bar, and line charts.

You can create pie, bar, and line charts in Excel.

You can also create column charts and area charts, as well as more complex charts such as scatter charts and bubble charts.

Database management

While Excel is not designed as a database management application, using it as such can be beneficial, particularly if your database is numbers-driven. If your database is mainly informational and will be used by multiple parties, Excel may not be your best choice. However, if your database contains information that requires calculations or comparisons, it can be an excellent choice.

You can create multiple databases in Excel to help manage everything from employees to customer orders.

For more advanced database management capability, consider using Excel with Microsoft Access.

Forecasting

Excel is a good resource for preparing a financial forecast for your business. You can create a simple forecast based on historic data or use some of the more advanced functions to create a more in-depth forecast.

The forecast above shows historic sales as well as forecasted sales for this company.

Along with a simple forecast, you can choose from various options including a confidence interval which can help you determine how accurate the prepared forecast is.

Is Excel a good choice for finance?

Excel would not have endured this long if it wasn’t a valuable tool for finance and accounting professionals. Excel can create custom charts and graphs in minutes, forecast short and long-term income and expenses, and provide valuable ‘what-if’ scenarios using sensitivity analysis, for companies looking to make more informed financial decisions.

But for all its strengths, Excel is not always the best choice when it comes to accounting. Transposition errors, incorrect or inadvertently removed formulas, or even a simple typo can carry serious repercussions. Without the safeguards found in other financial and accounting programs, Excel data should always be closely reviewed for potential errors.

The difference between Excel and other financial software

There are plenty of proponents who advocate for using Excel as a financial software application. While certainly possible, the risks outweigh the rewards. Consider the following.

• Using Excel, you have to create all accounting processes and enter all transactions manually. Paid your rent? You’ll have to enter the payee manually. Received a check from a customer? The same thing goes. While you’ll also have to enter customer and vendor information in your accounting software, you’ll only do that once. In Excel, you’re doing it for every single transaction.
• If you’re not an experienced accountant, you may find working in Excel both confusing and time-consuming. Using financial software, much of the accounting work is done behind the scenes. Not so in Excel, where you’ll need to enter your data and create all of your reports manually, which can be a struggle for those not schooled in accounting.
• Mistakes can happen no matter what kind of software application you use. But in Excel, a simple transposition of numbers can have catastrophic results. While you can also make a data entry error in an accounting software application, it’s much easier to locate that mistake and correct it than to locate that same mistake in a spreadsheet containing hundreds of rows of data.

But for all its advantages, accounting software has limitations as well, particularly in vital areas such as forecasting and budgeting. That’s why using Excel remains an important part of the complete financial management process for your business.

Why Excel will remain popular

Excel is one of the most popular tools used by corporate finance departments worldwide, and is likely to remain the go-to resource for businesses large and small. When used in conjunction with other important resources such as accounting software and database applications, Excel can help with data management, financial planning, and budgeting, while easily handling small tasks as well. Creating a basic Excel workbook only takes minutes and can be used for everything from managing depreciation expenses to tracking financial performance.

Included with Microsoft Office, easy to get started, and flexible enough for small and large businesses, the advantages of using Microsoft Excel clearly outweigh the disadvantages.