Excel Budgeting on Cloud for Banks
This article will focus on Excel Budgeting on Cloud for Banks.
Nothing is easier than Excel – When it comes to budgeting for a bank, it all starts with the balance sheet. The main component of a bank’s revenue comes from the interest earned from loans and paid on deposits. To budget interest, you will need to figure out what the existing loan and deposit balances will look like over the next several months.
Loans will amortize over time while deposits will fluctuate up and down depending on interest rates the bank is willing to pay. In addition to the existing loans and deposits, banks will need to add balances for new production.
Once you have all your balances figured out, you will need to apply the individual loan and deposit rates to the amortizing portfolio and new rates to the production layers. Profit Stars and IPS Sendero are pretty sophisticated Asset/Liability Management tools that help banks in the process.
But if you had to figure out how these black boxes work, good luck! With the aid of a data warehouse for historical information and a Microsoft Excel-based report writer and budgeting tool, it is pretty easy to replicate the needed loan and deposit schedules to calculate your net interest income and expense.
Personnel Expense Planning – Personnel expense is the biggest non-interest expense item for the bank. It all starts with head count and salary planning. Namely and BambooHR are human resource tools that do a pretty good job for compensation management. They track each individual employee with all of the hours, pay rates, taxes, and benefits. However, they don’t really forecast the projected personnel expenses all that well.
The best approach will be to get a data extract from human resources for each employee by department. You will need to have pay rates and scheduled hours for each person as well as tax and benefit rates. In addition, you will need the ability to add new employees. By using Excel to arrange the data and allow for inputs, the process becomes much easier.
Capital Expenditures – Property, plant, and equipment will be the next biggest category for non-interest expense. The biggest driver of the category will be depreciation expense that comes from capital expenditures.
Sage Fixed Assets and FMIS Asset Management are pretty good tools that track assets as well as provide forecasts for depreciation. The budgeting pieces require a lot of setup and entering in of the actual assets to be considered.
That is where modern Excel-based budgeting and reporting tools can help out greatly. Excel is great at setting up pick lists to choose from for your various asset types. Along with each asset type, you can easily set up lookup tables that have the asset life in order to calculate depreciation.
Non-interest Income and Expense – Simply listing historical expense by general ledger account by department is a good start for budgeting. In the grid below, department managers simply type in the expected numbers for the next several months. A good design is to include a column that includes comments.
Expense by Vendor – An even better design for expense planning is to plan by vendor, see below. This approach provides much better information than just a comment. The reviewer of the report is able to see all the details that go into each expense item. I have seen some banks that track and forecast by vendor be able to use this information when negotiating with vendors.
For example, a bank’s various departments and branches may be using three or four different vendors for supplies. When they added up the total spend on supplies, they shopped total forecasted and historical spend to the same vendors and the one that could come up with the best overall price, won the business for the next year.
Excel Budgeting on Cloud for Banks – There are an infinite variety of Excel templates that banks can use for budgeting. The key is to find a tool that comes with a data warehouse and a good report writer that allows for publishing and collaboration to a web portal on the Cloud. Below are a few tools that meet that criteria.
The Adaptive Suite by Adaptive Insights – This tool is Cloud based only. You can build ad hoc reports, collaborate with other users, create dashboards, drill-down, and make financial reports. It runs on an OLAP cube which makes it hard for other visualization tools to access.
Host Analytics Business Analytics by Host Analytics – Along with being Cloud-based only, you can build ad hoc reports, collaborate with other users, create dashboards, drill-down, and make financial reports. Host Analytics also runs on an OLAP cube and thus limiting the use of other tools for visualization.
Domo by Domo Technologies – Domo is another Cloud only solution that offers the same ad hoc, collaboration, dashboard, drill-down, and report features. DOMO appears to be using AWS Amazon Relational Database Service (Amazon RDS) and Hadoop to store data in the cloud data warehouse models. However, Domo is not a budgeting and forecasting tool and thus would have to be purchased and deployed in conjunction with a planning tool.
BI360 by Solver, Inc. – BI360 is offered both on-premise and Cloud. If you start with on-premise, you can always migrate to their Cloud version later on. All the necessary budgeting, forecasting, ad hoc, collaboration, dashboard, drill-down, and financial reporting features are included. BI360 uses SQL Server for its database engine. This is a nice feature in that it will allow other visualization tools access. BI360 also includes already has templates for Loan and Deposit Amortization and Production, Personnel Expense, Capital Expenditures, Non-interest Income and Expense, as well as Expense by Vendor.
Wherever you are on your Excel budgeting journey, BI360 by Solver is enabling world-class decisions for banks. Solver, Inc. is happy to answer any questions and review BI360’s easy-to-use, Excel- and web-based budgeting and reporting solution for banking users. Whatever you choose, the good news is that you don’t need to leave Excel behind to create best of breed budgeting templates on the Cloud for banks. Solver, Inc. is happy to answer any questions and review BI360’s easy-to-use, Excel-based budgeting and reporting solution for banking users. If you are looking to take your love affair with Excel to the Cloud, choose the right tool that will meet your bank’s needs today.
