Spreadsheet Risks in Banks

No other industry perhaps handles such large volumes of critical financial data more than the banking industry. For decades now, spreadsheets have become permanent fixtures in the front-line reporting tool sets of banks, providing organised information when and where needed.

But as banks enter into a period of heightened credit risks, elevated levels of fraud, and greater regulatory scrutiny, many are wondering if continued reliance on spreadsheets is a wise decision for banks today.

The downfall of Lehman Brothers which eventually led to its filing for Chapter 11 bankruptcy protection on September 15, 2008, served as a wake up call for many institutions across the globe to make a serious examination of their own risk management practices. But would these reforms include evaluating the security of user developed applications (UDAs), the most common of which are spreadsheets, and putting specific guidelines as to when they can – or cannot be – used?

Banks and Spreadsheet Use

Banks have been known to utilise spreadsheets systems for many critical functions because most personnel are well-acquainted with them, and the freedom of being able to develop customised reports without needing to consult with the IT department offers flexibility and convenience. In fact, more than having a way to do financial budgeting and analysing customer profitability, even loan officers and trade managers have become reliant on spreadsheets for risk management reporting and for making underwriting decisions.

But there are more than a few drawbacks to using spreadsheets for these tasks, and the sooner bank executives realise these, the sooner they can adopt better solutions.

General Limitations

Spreadsheets are far from being data base systems and yet more often than not, they are expected to act as such, with figures constantly added and formulas edited to produce the presumably right set of reports.

In addition, data integrity is always a cause for concern as most values in spreadsheets are entered as manual inputs. Even the mere misplacement of a comma or a negative sign, or an inadvertent ?edit? to a formula can also be a source of significant changes in the outcome.

Confidentiality risk is also another drawback of the use of spreadsheets in banks as these tools do not have adequate?access controls to limit access to only authorised individuals. Pertinent financial information that fall into the wrong hands can lead to a whole new set of problems including the possibility of fraud.

Risks in Trading

For trading transactions, spreadsheets can prove to be of immense use – but only for small market volumes. As trade volumes increase and the types vary, spreadsheets are no longer a viable solution and may likely become more of a hindrance, with calculations taking longer in the face of bigger transaction amounts and growing transaction data.

And in trading, there is always the need for rigorous computational functions. Computing for the Value at Risk (VaR) for large portfolios for instance, is simply way beyond the capabilities of spreadsheets. Banks that persist in using them are increasing the risk of loss on those portfolios. Or, they can be opening up?opportunities for fraud?as Allied Irish Bank (in the case of John Rusnak – $690 million) learned the hard way.

Risks in Underwriting

Bankers who use spreadsheets as their main source of information for underwriting procedures also face certain limitations. Loan transactions require that borrowers? financial data be centralised and easily accessible to risk officers and lending officers involved in making decisions. With spreadsheets, there is no simple and secure way of doing that. Information can be pulled from different sources – individual tax returns, corporate tax documents, partnership documents, audited financial statements – hence there is difficulty in verifying that these reports adhere to underwriting policies.

Spreadsheet control and monitoring

Financial institutions which are having difficulty weaning themselves from the convenience and simplicity that spreadsheets offer are looking for possible control solutions. Essentially, they want to find ways that allow them to continue using these UDAs and yet somehow eliminate the?spreadsheet risks?and limitations involved.

Still, the debate goes back and forth on whether adequate control measures can be implemented on spreadsheets so that that the risks are mitigated. Many services have come forward to herald innovative solutions for better spreadsheet management. But at the end of the day, there really is no guarantee that such solutions would suffice.

More Spreadsheet Blogs


Spreadsheet Risks in Banks


Top 10 Disadvantages of Spreadsheets


Disadvantages of Spreadsheets – obstacles to compliance in the Healthcare Industry


How Internal Auditors can win the War against Spreadsheet Fraud


Spreadsheet Reporting – No Room in your company in an age of Business Intelligence


Still looking for a Way to Consolidate Excel Spreadsheets?


Disadvantages of Spreadsheets


Spreadsheet woes – ill equipped for an Agile Business Environment


Spreadsheet Fraud


Spreadsheet Woes – Limited features for easy adoption of a control framework


Spreadsheet woes – Burden in SOX Compliance and other Regulations


Spreadsheet Risk Issues


Server Application Solutions – Don’t let Spreadsheets hold your Business back


Why Spreadsheets can send the pillars of Solvency II crashing down

?

Advert-Book-UK

amazon.co.uk

?

Advert-Book-USA

amazon.com

Contact Us

  • (+353)(0)1-443-3807 – IRL
  • (+44)(0)20-7193-9751 – UK

Check our similar posts

ESOS Facts on a Page

The UK?s ESOS energy saving program stands for ?Energy Savings Opportunity Scheme?. Its purpose is to reduce demand – and hence fossil-based pollution at both ends of the supply chain. It currently applies to large UK companies only. However its guidelines are also valuable input to smaller firms voluntarily going greener.

The program threshold is 250 employees and / or turnover or at least ?UK50 million. This affects approximately 9,000 UK firms, with others below the threshold wondering whether the government plans to lower it. In essence, ESOS requires that qualifying businesses complete comprehensive audits of energy use and opportunities at least every fourth year.

The plan is carrot and stick. Compliant companies will probably uncover significant savings when they stop and measure. They may even unearth carbon credits they can sometime exchange for cash. Reactionary firms who try to duck the issue will feel Her Majesty?s wrath through stiff penalties. In time, they may find it harder to attract investors. If ESOS affects your company, then the wise thing could be complying by the first deadline of 5 December 2015.

To do so, you must conduct an energy audit and report it to the UK Environment Agency. This comprises

  1. Measuring total energy use across processes, transport and facilities
  2. Pie charting 90% of this to identify areas that are energy intensive
  3. Singling out cost-effective energy-saving projects in high use areas
  4. Submitting your report to the Environment Agency ahead of the deadline

ecoVaro recommends affected companies do not leave this to the last minute. While having ISO 50001 may exempt some from ESOS, the regulations are far from straightforward and it will take months to reach complete clarification. We would like to suggest a more balanced approach.

ESOS is a wonderful incentive to save energy costs while contributing to a better future for the kids. The Energy Savings Opportunity Scheme is precisely that. The cost of energy has crept up on us to the extent that we have to do something, government or no government.

Measuring energy consumption is as simple as installing meters at critical points in the flow, and you probably have many of them anyway. Once you have your data you no longer have to crunch the numbers. ecoVaro can do this for you and return the result in the form of handy graphs and spreadsheets.

Eck Industries Sheds Fresh Light

William Eck began his business in 1948 in a 650m2 garage building. The aluminium foundry prospered, and now has an 18,500m2 factory in Manitowoc, Wisconsin employing 250 people casting a variety of casings. Like high-tech industries around the globe it needs effective illumination. After it measured its carbon footprint, it realised it needed energy efficient lighting too.

When Eck Industries began its review it had around 360 high-pressure sodium lights throughout the plant. Their operating cost was substantial. After taking independent advice from an independent agency they realised they needed to replace these with more energy-efficient fluorescent lights that consume half as much energy.

The feasibility team conducted performance tests to determine the optimum solution. After selecting enclosed, gasketed and waterproof T8 fluorescents (available in G13 bipin, single pin and recessed double contacts) they collaborated with the supplier to calculate the best combination of 4 and 6 bulb fixtures.

The fittings they chose cost $60,000 plus $10,000 installation. However a $33,000 energy rebate wrote down 47% of this immediately. They achieved further energy savings by attaching motion sensors to lights over low-traffic walkways.

The retrofit was a huge success, with an 8 month payback via a direct operating saving of $55,000 a year. Over and above enhanced illumination Eck Industries slashed 674,000 kilowatt hours off its annual lighting bill. During the 20 year design life, this equates to a total 13.5 million kilowatt hours. Other quantifiable benefits include 443 tons less carbon, 2 tons less sulphur dioxide, and 1 ton less nitrogen oxide per year.

Many companies face similar opportunities but fail to capitalise on them for a number of reasons. These may include not being aware of what is available, lacking technical insight, being short of working capital and simply being too busy to focus on them.

Eck Industries got several things right. Firstly, they consulted an independent specialist; secondly they trusted their supplier to provide honest advice, and thirdly they accepted that any significant saving is worth chasing down. Other spin-offs were safer, more attractive working conditions and an opportunity to take their foot off the carbon pedal. This is an excellent example of what is possible when you try.

If you have measured your illumination cost and are concerned about it (but are unsure what the metric means within the bigger picture) then Ecovaro offers online reports comparing it with your industry average, and highlights the cost-benefits of alternative lighting. 

Job & Staff Scheduling with FieldElite Mobile Service Management Software

Field Service Management (FSM) software systems are designed to enable you to manage your mobile workforce from a central point- and do away with the paperwork involved with the process. They connect your technicians on the ground (via app on their phones), to the staff at the head office- who have an interactive dashboard accessed through their browsers. The office team will have access to all the jobs that are to be handled by the company, simplifying the management process and taking away the risks that come with manual data entry. Here, we will walk you through a quick process of scheduling a job for your personnel with FieldElite.

Say you are a HVAC contractor, licensed, bonded and insured. You’ve made quite a name for yourself in the industry, and have a wide range of clients- in both residential and commercial establishments. Consequently, you also have a large workforce to attend to the different situations- from installing to repair and maintenance. One of your clients- let’s call them ABC Computer Supplies, has an issue with their HVAC unit- perhaps a pipe is leaking. It needs to be fixed, and ABC have booked an appointment.? Your goal here is to get one of your personnel to handle the task as soon as possible, and this field service scheduling software comes in handy.

There are two approaches that you can take:

1. Job Scheduling

From your Dashboard, on the left-hand side you will see the menu option. Clicking on Jobs, will take you to all jobs carried out by your company.

FieldElite

The filters will allow you to view different categories of jobs:

  • Complaint– This means that there was an issue with on ground during the task delivery, and the client lodged a complaint.
  • On hold– Here, different aspects can cause a job to be paused- like when spare parts or equipment required for repair jobs have been ordered, and one needs to wait for them to be shipped in from a different location.
  • Pending– This is basically your in-tray, a list of jobs that are to be carried out.
  • In Progress– The technicians are on the ground, attending to the client’s needs, and you’re getting routine updates from them.
  • Incomplete– Though the job had been assigned to the required technician, it was not completed in the set amount of time, thus requiring an additional visit to the site. Given that the FSM solution increases the first-time fix rate, cases of ?incomplete tasks? are reduced.
  • Complete– The task is successfully done and the customer has appended their e-signature, and now it can be invoiced.
  • Cancelled Invoice– The head office determines that a particular invoice shouldn’t be paid, and thus cancels it.

Our focus here is the pending tasks, so use this filter. ABC’s HVAC job will be among these. Clicking on its Job ID will open up the details of the task, with such an Update Job window:

FieldElite Job

This section contains all the information of the job- both past and present, which you can update in real-time. Any changes will be recorded by the system and can be viewed on the “Audit” tab.

As you can see here, the HVAC repair job is both “pending” and “urgent”. No one really likes sitting in an office that feels like an oven. Being the headquarters, it’s likely handles lots of foot traffic, and the damaged HVAC unit will make the working conditions really difficult. It’s best not to keep the client waiting, right?

So, head on over to the Supervisor and Workers section (on the same “Details” tab), and select the personnel suited for the task.

FieldElite Job Details

Set the time that the task will take for your technician, and once satisfied with the details of the job, click on Update. Voila! You’re done.

FieldElite Job Update

Immediately this happens, the worker received a notification on their app, telling them that they have been assigned the job.

From the app, the technician will be able to view the specifics of the HVAC job, including notes and attachments that you can add directly from your own dashboard, such as schematics of the building and reports from other technicians who installed the air conditioning system for the facility. You also get to add products that will be required for the task- like the pipe and panel mounted socket shown here. As the system also includes an inventory of the products used, their quantity and costs, you will be able to keep an accurate record of the supplies as they as are used.

As such, the field workers will not have to keep coming back to the central office to get documents and reports of new tasks, or walk around with bulky files. When they are carrying out the job, they will also be able to keep the staff at the office updated about its progress, through the chat feature on the mobile app, taking photos and adding notes as required.

2. Staff Scheduling

With this approach, the perspective is basically: ?So I have a couple of jobs- which of my employees has time to handle them?? The FSM allows you to optimise your productivity- by ensuring that you get the most out of the staff work hours, and avoid cases of jobs going into overtime.

Follow these steps:

  1. Select ?Scheduler? from the left-hand side of the window. You will have a view of the workers of your company and how their day is planned out, and a summary of the unassigned jobs.

Here, you can tell whose busy, and who can have a new task assigned to them at the click of a button- which is far more effective than keeping on jotting down points in your diary or going through files of documents.

If the job has yet to be added to the system- like for the cases of new clients, simply click on the ?Add Job? button and key in its details.

2. Scroll down, you will see a list of unassigned jobs.

unassigned jobs

3. Next, click on the edit button under ?Actions?. This will take you to the same ?Update Job? window described in the first approach, in order to assign the preferred worker to the role.

This real-time dispatching avoids cases of your desk getting cluttered with paper sheets, and prevents duplicate entries as each job has its own ID and task details- from the scheduling to the invoicing. In this case, your HVAC technician will have access to the information needed right at the palm of their hand, to ensure that the task at ABC?s head office goes seamlessly. The optimised schedule will enable the task to be carried out faster- restoring normalcy to your client’s facility.? In case the client’s location is on the route that one of your technicians takes while heading home, you can take advantage of this by giving them the task towards the end of their working day- thus clearing more of your backlog, sorting out your client, and easing your technician?s worries about getting home late.

As you can see, the field service scheduling software enables you to easily and efficiently handle your workflow, avoid the mess that is associated with manual documentation and cases of your employees getting conflicting schedules and overlaps- which would strain them and dampen their morale. Streamlining your workflow and standardising operations ultimately results in increased customer satisfaction.

Ready to work with Denizon?