How three businesses benefited from better financial models

We’ve been solving financial modelling problems, as well as building complex models from scratch, since the late 1990s. As a result, we’ve seen nearly every error under the sun. This blog talks through a small sample of previous clients—projects that we’ve worked on and solutions we’ve provided.

 

If you’d like to discuss your financial modelling problems, give us a call on +44 20 3151 4799.

 

1. Rosemont Pharmaceuticals

 

Based in Leeds, Rosemont Pharmaceuticals has been at the forefront of manufacturing and supplying liquid medicine for the last 40 years, reaching out to Lazuli for help in 2013.

 

What problem did the business face?

Due to an imminent management buyout, Rosemont Pharmaceuticals approached Lazuli to assess the accuracy (and usability) of a series of spreadsheet models that Rosemont was using for its monthly financial reporting.

 

The main problem Rosemont Pharmaceuticals faced was that while the spreadsheets were accessible, they weren’t functioning properly. This problem needed to be fixed before the management buyout could move forward, meaning there was a high-pressure deadline.

 

What was Lazuli’s solution?

Lazuli recommended its ExcelCheck service, to review the usability of the existing spreadsheet models.

 

All of Rosemont’s models went through a rigorous checking process. To do this a duplicate model was created by the Lazuli team, which was tested for logical and arithmetical integrity, to see whether its performance matched the business’ overall objectives.

 

This approach enabled Rosemont Pharmaceuticals to test the accuracy of its current spreadsheet models against an objective third party model built to achieve the same outputs.

 

The Lazuli model allowed Rosemont to streamline the number of models it was using, reducing around 100MB of logic to just 7MB.

Commenting on the result of the project, CEO and founder Dominic Robertson stated: “This was one of our most successful monthly reporting models. As well as being used for the valuation and subsequent buyout of the company, this model was a foundation for monthly reporting. As opposed to having a one and done spreadsheet—created for one purpose—Rosemont Pharmaceuticals could use the model for ongoing monthly reporting.”

2. High Speed 1 (HS1)

 

In 2012, Lazuli was approached to create the financial models for the UK’s first high-speed rail service, HS1. The project required a suite of operating models to report to both shareholders AND run the company’s financials on a daily basis (until 2040).

 

What problem did the business face?

There were a few challenges specific to this project. For one, standard financial models are based on a 12-month period per year. However, the HS1 project had to be based on a 13-month financial year, meaning that the standard 12-month base couldn’t be used.

 

Alongside this, a project as detailed as high-speed rail meant that mixing a long-term time frame with a short-term operating horizon simply wasn’t going to work.

 

Having one model fit two moulds would be too convoluted and ultimately inadequate, as it would result in either a messy model that would be a minefield to operate, or a simpler model that wouldn’t be as effective as it needed to be.

 

What was Lazuli’s solution?

The solution was to build two separate models with a shared interface. The interface was a statutory financial statement fed by one model which accommodated a longer time-frame, and another with a shorter time-frame:

 

•    The short-term model would produce a three-year forecast, seeded by two years of management accounting actuals.

 

•    The long-term model used the short-term model five-year base to forecast 25 years, carrying on from where the short-term model left off.

 

Commenting on the result of the project, Dominic stated: “The benefit of having two models was that each could meet the different requirements set by company directors and each model could perform its individual tasks to the best of its ability. The shared interface ensured that no human error occurred due to a lack of interface understanding and usability, and both models could be used interchangeably when needed.”

 

“The shareholders were extremely pleased with the result and recommended Lazuli for an upcoming project with Caruna Oy”

 

3. Caruna Oy

 

Finnish power grid company, Caruna Oy, has an electricity network nearly 79,000km long and is the largest supplier of energy in Finland. In 2014, Caruna Oy changed ownership from Fortum (who had been running it since 1912) to an array of investors: Keva (12.5%) and Elo (7.5%), as well as international infrastructure investors, First State Investments (40%) and Borealis Infrastructure (40%).

 

This transition meant that the newly established Caruna Oy had to start reporting their finances to shareholders and lenders.

 

What problem did the business face?

As Caruna Oy was a new company previously under the umbrella of a much larger corporation, they had no financial model base.

 

Lazuli needed to create a strategic model that could be used by a firm that had to deal with multi-company group consolidations and adjustments—which are very specific, complex requirements.

 

What solution did Lazuli put in place?

To meet these requirements, Lazuli Solutions built a long-term strategic operating model in Excel. 

 

In order to easily and effectively run company financials, the model included a multi-company structure, extensive financing and inter-company loans and obligatory complex regulatory revenue calculations.

 

Commenting on the result of the project, Dominic stated: “Caruna Oy were particularly happy with the result. As this project required specific expertise that usually can’t be found in-house, it would have been impossible for them to build this model themselves.

 

“Also, as Caruna Oy was a new company that had to start dealing with both shareholders and company directors, our model helped build a bridge between the two groups—allowing shareholders to see company returns, while also forecasting the future of the company for the directors.”

 

Now it’s time for your solution…

Collectively, the Lazuli team has a breadth and depth of knowledge and experience in financial modelling that is rare in today’s age.

 

Our background enables us to both support the financial health of companies as a whole as well as identify improvements to the financial models of specific silos within.

 

We work closely with financial controllers or directors and offer three core services:

 

•   Enterprise reporting for firms that are looking to increase their monthly financial model’s productivity.

•   Strategic operating models for large regulated infrastructure and finance directors who wish to gain a deeper understanding of their business.

•   Strategic review for firms that want to consolidate multiple, disconnected investments.

 

If your firm is struggling with any of the above, let us find the right solution for you. Drop me an email, and we’ll arrange a time to chat through the model(s) your business needs.

4 key questions to ask about your monthly reporting model

Recent research highlighted that a staggering 88% of all spreadsheets used by companies almost every day contain mistakes. (Source: Oracle, 2017). The existence of these errors often has significant financial, reputational, operational, and legal repercussions which can go unnoticed for weeks or even months.

The spreadsheet model’s building process is the first instance where such mistakes can be avoided, and where you can limit the likelihood of errors in the future.

At Lazuli Solutions, we implement excel spreadsheet modelling best practice for all clients, and we ask several key questions of our models. Four of these are:

1. Have you defined the objectives of the model?

Without clear objectives, financial spreadsheets used for monthly reporting are a challenge to build, and even harder to navigate once completed. The main reason for this is the organisation and structure of modelling components.

Therefore, before building a spreadsheet model, set out clear objectives. Doing so will provide guidance and direction, facilitate the planning stages and help evaluate the success of the spreadsheet model once completed. In turn, this will help establish the outputs you require to meet the objectives that you set.

Remember: Defining specific outputs leads to successful monthly reporting financial spreadsheet models. Clearly state these outputs before starting the building process to ensure a more streamlined build.

For example, a monthly reporting spreadsheet model could have multiple purposes to (1) create a reporting spreadsheet model with a three to four-year timeline and (2) use the management accounting actuals to seed the forecast. Therefore, your main objective is to create a reporting spreadsheet that’s defined as an actuals plus forecast model.

Excel spreadsheet modelling best practice summary: Determine the main aims of the spreadsheet and keep those at the forefront of specification, design, build, test and deliver stages.

2. Have you formalised the model interfaces?

Before building your model’s interface(s), ensure that you establish a formalised design to ensure that they remain consistent.

What is an interface? An interface joins one system or set of calculations to another through a user-friendly system—and a formalised design will keep those systems consistent.

A lack of formalised interface will lead to a higher number of human errors due to lack consistency.

For example, you may have multiple interfaces—between the accounting system and the model, the financial statements of the model and the reporting key outputs in the model, and between the actuals and the forecast in the model. Errors will creep in when accounts are ignored and forgotten if the interfaces are not formalised through a consistent system.

Excel spreadsheet modelling best practice summary: All interfaces should have a formalised process, method, look and usability, and all team members should agree them. To further reduce the likelihood of errors occurring, the interface should be as simple, effective and transparent as possible.

3. Is the model use process holistic?

How the model is used, called the ‘use process’, is defined by five factors. Keeping in mind how users will be using the model through these factors, you’ll be able to build a model that is easy to use and understand. The factors are:

1. Events—A series of events define the use process. These include:

•  Updating actuals

•  Making adjustments

•  Making prior-period adjustments

•  Checking outputs

•  Re-forecasting

•  Publishing the draft reporting pack

•  Publishing the final reporting pack

2. People—It is essential to determine both the users of the model, the users of the results of the model and very importantly, the owner of the model.

3. Timing—Identify how often the model will be used and build a model that is appropriate for the frequency of use.

4. Method—What happens when the model is used. This includes determining the physical location of the files, the naming convention associated with the files, and the audit trail across the various outputs, with the explained reasons behind differences in the various outputs.

5. Outcome—Determine the answers the various users will be looking for and make the use process for finding those answers explicitly clear.

Excel spreadsheet modelling best practice summary: Holistic factors are key to establishing the successful use of the model.

4. Have you honestly evaluated the efficacy of the resulting model?

Once the model has been built, it’s time to evaluate its efficacy in relation to the objectives set out at the start.

We stress that this is a very important question, even if frequently overlooked. Identifying and rectifying these situations results in less risk in the future use of the model.

When assessing your model, ask the following questions:

·      How long did the project take start to finish? Who was involved?

·      What is the probability of errors in the final solution (particularly focus on the interfaces of the solution)?

·      Have you achieved the objectives defined at the start of the project?

·      Have you communicated, tabulated and archived explanations for changes in outcomes?  Are these within the actual model?

·      Is the use process user friendly and transparent? Could anyone look at the final model and understand it independently from those that built it?

Excel spreadsheet modelling best practice tip: Get an independent third party to test new models. This is best done by someone who wasn’t close to the project and hasn’t been exposed to the model before. The result is a more honest review on the efficacy of the model.

An alternative solution…

If you would like an objective, external assessment conducted on the efficacy of the models you rely on, our ExcelCheck service is a cost-effective solution.

When we receive your model, we put it through a thorough MOT and error checking service. We give you a list of errors and potential errors and advise on best practice modelling functionality.  We also deliver to you a duplicate model that is simple, transparent and reliable.

To book your Excel model check today, call 020 7450 3318 or email dominic.robertson@lazulisolutions.com.

Please note, we sign NDAs and all models will be treated confidentiality.

Sign up for our newsletter here:

Traditional model audit vs ‘ExcelCheck’

Traditional financial model audits are often a necessary evil, both for those conducting them and the finance departments having their spreadsheets scrutinised.

The rubber stamp of ‘approval’ that auditors provide when conducting a financial model audit comes with a hefty price tag—and rightly so, because the 'transmittal letter' they provide comes with significant liability.

However, while these audit documents do provide financial controllers with a feeling of security, usually a model’s ‘correctness’ is dependent on the data set it’s been audited with. In practice, financial model audits effectively say….“yes, X model, exactly as it stands now, works appropriately to achieve Y output with Z dataset.” BUT the moment you start tweaking parts of X, alter the Y you want to generate, or Z dataset gets updated, your audit effectively becomes outdated. Any approval previously given will no longer stand.

‘ExcelCheck’ offers a pragmatic alternative, but the offering is specific. Here are the differences between a traditional model audit and an ‘ExcelCheck’:

1. Pragmatic application

Traditional model audit: Model auditors typically check model integrity with a two-step process:

  1. Run spreadsheet software tools to check for model consistency and create a map
  2. Use the map to check the spreadsheet manually, ‘cell-by-cell’ to confirm logic


Today’s model auditing software tools only check for consistency and poor referencing, they do not actually analyse any logic, and ‘cell-by-cell’ reviews are as prone to human error as the building and maintenance of spreadsheets themselves. In fact, research suggests that these manual reviews capture around 70% of errors, leaving the other 30% undetected (Source: Financial Model Audit—A Fresh Approach).

ExcelCheck: The principle difference between a traditional financial model audit and ExcelCheck is the pragmatic, useful outcome and long-term benefits. ExcelCheck doesn’t just verify the models you have but takes your important financial model spreadsheets and rebuilds them to pinpoint errors and potential errors. This ensures the lowest chance of errors both in the short and long term.

For some businesses, this can be done by stripping out unnecessary logic while for others it’s a complete overhaul of content, coding, format, structure, and organisation—and anything in between. For example, many Excel models become cluttered with over-complicated, unnecessary formulae. Often nested IF statements, OFFSET, VLOOKUP and INDEX can be replaced with simpler alternatives which reduce the likelihood of errors occurring in the future. Similarly, businesses often replicate formulae to calculate the same or similar output. Large chunks of logic are harder to maintain and are more likely to return inaccuracies because they are reliant on so many elements. These aren’t optimisation opportunities that will be identified and rectified when a model audit is conducted but are long term, pragmatic benefits of ExcelCheck.

2. Liability

Traditional model audit: Traditional model audits come with indemnity insurance in the form of 'transmittal letters’ effectively providing a guarantee that your spreadsheet has no errors. Therefore, in theory, if your model subsequently returns faulty outputs that commercial decisions are based upon, costing your company money, you can claim against your model audit provider. In practice, this insurance is usually only valid for the exact data set that the transmittal letter was provided for.

ExcelCheck: Excel Check provides reassurance, rather than insurance. Its key purpose is to check the logical and arithmetical integrity of your spreadsheet model within the context of the commercial objectives you have set.  By rebuilding your model with these clear objectives we have the best chance of dealing with multiple data sets in the long-term (as opposed to the short term security that model audits provide). Therefore, Excel Check provides the same level of expert, thorough analysis without the legal guarantee. Since ExcelCheck works with the understanding that data sets can change, so you can be sure that your outputs will remain accurate whatever reasonable changes in the data being input.

3. Cost

Traditional model audit: The 'transmittal letter,’ which verifies the model’s integrity, provides companies like yours with insurance—something that can be claimed on if subsequent errors arise. However, experts in modelling have identified this as a worrying trend, saying “often the criteria for selecting an auditor then becomes a straight comparison of who will offer the highest ‘cover’ for the lowest price” (Source: Financial Model Audit—A Fresh Approach). As a result, this means that expertise and usability of audit reports come second and third to value for money quotes.

ExcelCheck: ExcelCheck is charged on an ‘hourly’ basis which depends on the size of your spreadsheet and the amount of repetition/duplication.

It can take anywhere from a few hours up to three weeks for particularly complex spreadsheets; the length of time depends on some technical factors like duplication, the number of original formulae, and the complexity of the original formulae.

For an accurate quote, simply upload your spreadsheet model and we will give you a quick call to discuss it with you. Then we will go about getting you a quote and delivery estimate:

ExcelCheck

If you’re concerned about inaccuracies in your Excel spreadsheet or other problems associated with your Excel model(s), our ExcelCheck service offers a hassle-free way of identifying any errors and improvements.

When we receive your model, it undergoes a thorough MOT and repair service where we advise on best practice usage going forward. So, what you receive back is an accurate, uncluttered, fully functional, and—most importantly—reliable version of the model you left us with.

To book your Excel model check today, call 020 7450 3318 or email dominic.robertson@lazulisolutions.com.

Please note, we sign NDAs and all models will be treated confidentiality.

Sign up for our newsletter here:

FREE Indexation Module for financial models

FREE Indexation Module for financial models

Overview

This is a FREE module that calculates macro-economic indexation for use in a financial model.

How to use

1.      Use as standalone or copy/add module sheets into existing model

2.      Write name of chosen index in cell F14 on sheet “Indexation_Module”

3.      Update with indexation actuals from government site (e.g. for Retail Price Index [RPI] in UK use https://www.ons.gov.uk/economy/inflationandpriceindices/timeseries/chaw/mm23

4.      Update forecast index and months of actuals in rows 20 and 21 respectively

5.      Customise use of the index as required – look at EXAMPLES 1,2 & 3 on sheet “Indexation_Module”

Inputs

  • Name of index
  • Annual timeline (can be used in monthly, quarterly, semi-annual and annual periodicity models)
  • Government monthly index time series (in our example this is UK RPI)
  • Forecast index
  • Months of actuals

Outputs

  • Monthly index for all years
  • Actual plus forecast index for all months and years on timeline

More information

Requires an annual timeline, but results in monthly indexation for all years

Pricing

Free

Additional Info

For more information contact Dominic Robertson at Lazuli Solutions at dominic.robertson@lazulisolutions.com or call +44 207 450 3318