Excel Models - Everything you need to know
There is more to Excel models than just financial modelling. Here you can find all types of Excel models, classified based on scientific methods their deploy, industries they serve and the problem they solve.
You can also learn how to create an Excel model and what defines a good model.
1. What are Excel models?
Spreadsheet models are Excel-based systems that help understand and manage processes by describing and decomposing them. They are usually workbooks or file systems that allow applying scientific methods and industry knowledge to put real-life data into perspective. Then, it can be used to understand the relations between different factors, draw conclusions and act on them.
Models are simplified representations of what happens in the real world. They allow professionals to play around with different model variables & assumptions to simulate how the outcomes of complex processes would change. They may also use models to simulate scenarios that may happen in real life.
From financial performance to setting optimal pricing or accurately predicting the financial risk exposure, models enable companies and people to limit the guesswork in decision making and actively approach uncertainty.
2. What an excel model is used for?
Most companies use spreadsheet models to understand complex events without breaking a bank or using black-box systems. Most popular are the financial models but there are many others as well.
Excel is the most universal modelling tool allowing for quite an in-depth representation of reality with the use of mathematical, financial and statistical formulae. Also, econometric models and regression analysis are easily available and VBA programming can help increase modelling granularity or scale it.
Although there are many types of modelling software, the main advantage their offer is their industry-specific orientation and availability of benchmarking data. Such modelling systems often serve large firms with tailor-made solutions like this one allowing retailers to model how their product distribution across store shelves impacts sales and profits.
Others help optimise engineering challenges regarding energy consumption or the use of materials in production. Nevertheless,
Excel is also a great testing ground for advanced models before increasing the complexity of the modelling environment.
Excel models offer a very attractive combination of flexibility, simplicity and control. They allow to break down calculations and prediction systems into components that can be easily changed and tested.
3. What are the types of excel models?
Excel models can be divided into categories based on what scientific methods they deploy, what industries benefit from deploying them and what challenges they solve.
3.1 Excel Models by scientific methods used
These models play important role in describing problems and validating claims with the use of mathematics. They are often a part of a greater set of planning and forecasting tools, reassuring their users about the correctness of used methods.
Based on the methods used, Excel models can be classified as:
3.1.1 Quantitative Models which use mathematical equations, computations and may also deploy elements of statistics or econometrics
3.1.2 Statistical Models used to validate claims about the patterns in empirical data
3.1.3 Econometrical Models that deploy regression models and uncover their dependencies and limitations to render the causality observed in real live data
3.1.4 Probabilistic models that use probability calculus to calculate the expected value and probability distributions of random events, such as in random games or insurance claims
3.2 Spreadsheet models by industry & function
These models use data specific to chosen segments of the market or company departments. They may leverage business intelligence to predict sales or market insights to maximise investment ROI or minimise the risk exposure. The most popular types of industry-specific Excel models are:
Excel models by industry & function
Financial Models
Investment Modelling
Behavioural
Models
Engineering Models
Manufacturing
Models
Banking & Financial Risk Models
Insurance Models
Supply Chain &
Logistics Models
Compensation &
Reward Model
Pharmaceutical models
3.2.1 Financial Models
Financial Models help to understand, predict and dissect different elements of financial performance. Usually, they regard the performance of a company and help describe its revenue capabilities and cost structures as well as cashflows.
3.2.2 Investment Modelling Spreadsheets
Sometimes, the investment models are also referred to as financial. They usually refer to portfolio modelling where investors try to achieve their goals under the risk-related limitations and assumptions set for themselves.
Quite often they deploy VBA, especially for complex derivative pricing models. They can model the price of stocks, derivatives, commodities but also the investments in properties and various, less popular asset classes.
3.2.3 Behavioural Models
Investors and economists alike also use behavioural Models where they try to understand the motivations behind people’s decisions even when they seem irrational. They can measure satisfaction, confidence in actions, the likelihood of purchasing the product or even the happiness perceived by people.
They become increasingly important as different sciences recognise the importance of behavioural aspects in how we make decisions. Such models are also used in marketing modelling as well as in politics and can include in-depth profiling of personality traits.
3.2.4 Engineering Models
Although often used in environments outside of Excel, many engineering models deploy Excel to simulate the conditions of closed-circuit systems. They may model energy efficiency or consumption as well as lay out computational problems related to mechanics or physics.
3.2.5 Production/Manufacturing Models
Optimising the production of any product can be complex. The variable cost of materials and energy may change in a non-linear fashion so can the product demand. If to add the limited flexibility of the production pipeline bandwidth and specific properties of the manufactured goods, production planning models may be needed to coordinate manufacturing efforts. They also often deploy catalogues of parts that may be stored in Excel or external database.
3.2.6 Banking & Financial Risk Models
Banks run on models. In the business of connecting money with people, planning and understanding the environment is crucial. Apart from demand modelling, banking risk is increasingly important. Accurate risk modelling systems allow traditional banking services to actively adjust their risk exposure and manage their capital requirements under the regulatory scrutiny defined by Basel frameworks.
At the same time, investment and stock market risk can also be better understood with models. Although banks have the capacity to create their own internal systems, not always it's the most viable solution. Also, the chances are that the inputs and outputs of such systems are processed in Excel or Google Sheets.
3.2.7 Insurance Models
Accurate pricing of the insurance contributions or premiums wouldn't be possible without modelling, sometimes done in spreadsheets. Leveraging probabilistic models by actuaries and quantitative analysts allows to accurately price the risk and create high-resolution risk profiles. Using appropriate probability distribution types and statistical data is fundamental for this and at a smaller scale, Excel is well-equipped to support it.
3.2.8 Supply Chain & Logistics Models
Planning in the logistics & warehouses is immensely important. Depending on the scale of such operations, Excel may either be used as the main engine or a tool to process data and analyse outputs. Single warehouses or a fleet of a medium-size production or distribution company can run their resourcing in Excel.
3.2.9 Compensation & Reward Model (HR)
Many companies and People Directors prefer to create tailor-made reward and benefit systems. That's especially important in industries that employ many commission-oriented professionals and rely on sales performance. Accurately setting the commission and reward structure to incentivise the most profitable behaviour is often worth a bespoke system, which could be automated with VBA for Excel. Quite often it's combined with salary band modelling and optimisation of the company structure.
3.2.10 Pharmaceutical models
Big Pharma uses models in their research and development as well as to inform their sales and promotion. It's a very interesting industry for modellers as medicines are usually high-margin products that display low pricing elasticity and their testing requires a high level of statistical and econometrical scrutiny.
3.3 Challenge-oriented Excel models
There are a plethora of models which solve various interdisciplinary problems or serve multiple business areas. They usually act as tools that allow finding a solution to an open-end problem by assuming a certain (usually simplified) set of goals.
Excel Models can solve a multitude of challenges, such as:
3.3.1 Resource allocation - determining how to use the resources in a most productive way which could mean maximising the value of produced goods, minimising travel distance (travelling salesman model), saving energy or even maximising the happiness
3.3.2 Behavioural Models attempt to understand the logic behind people's emotions, preferences and choices. Such models are used in marketing but also in political campaigns.
3.3.3 Various optimisation models help to maximise or minimise chosen target values. From maximising the profit, production capacity, through minimising the cost, to optimising the nutritional values of a meal plan or optimising the packaging shape.
3.3.4 Grading & KPI models allow measuring performance and are a unique category as this type of modelling creates unified measures and means of comparison. From schools and teachers that won't settle for simple grading systems to certification centres and employee performance reviews, measuring performance can be approach with models.
Besides that, models have many more functions. Excel models...
-
Can help answer business and life questions to inform decisions
-
Can allow attributing effects to causes and establishing causality
-
Can give a chance to understand complex problems and distil the most crucial result drivers
-
Can help to forecast and predict future
-
Can be treated as proof of prudent approach (e.g. when convincing investors or Financial Regulators)
-
Can broaden the horizons of a company by highlighting things otherwise unnoticed
Need help with your Excel project?
Book a free consultation call today.
4. Excel Model attributes
Creating a model is an open task. Although there is no clear definition of what a perfect model is, there are significantly better and worse ones. Here are the attributes of good Excel models:
-
Well-suited for challenges they solve
-
Easy to use
-
Separating inputs from outputs and file mechanics
-
Flexible
-
Having optimal complexity to usability ratio
-
Are compatible with systems used
Models should be neither too simple nor too detailed. Their level of complexity should reflect the required precision which is often determined by the benefits from accurate decision making. That stresses the importance of commercial justification of the model use. Except when learning, the models have to present a utility for the business.
Then, they have to be optimal tools for the environment they are used in. When relying on benchmarking, industry-specific solutions are best. When in need of flexibility, spreadsheets are most versatile.
When heavy-duty simulations are required, a database might be needed or at least an automated VBA modelling solution in Excel. Otherwise, when ongoing collaboration is suggested, the Google Sheets can come in handy, especially with Apps Script.
5. Who uses Excel models?
All professionals can benefit from understanding the reality and determinants of the processes that concern them. Excel models are used by scientists, engineers, sales professionals, marketing managers, finance teams, accounting people, banking, investments & insurance professionals. Also, NGOs and government agencies use models to make better-informed decisions.
6. What are the elements of an Excel model?
There are three main parts of Excel models:
-
Input data which can be historical data gathered by the business or obtained from external databases
-
Model assumptions and the background knowledge justifying the selection of used modelling methods
-
Model outputs may involve the results of scenario simulations, dashboards, analysis of generated solutions and stress testing.
Models are also put to a perspective with a commentary that can describe the model background, purpose, limitations and refer to specific industry practices, additional information resources. Models can be created with the use of multiple software programmes and technologies. An Excel model can be paired with analysis or research obtained outside of spreadsheets or programmed externally.
7. What are the steps for building a spreadsheet model?
All models are different and many simple yet practical Excel models wouldn't require such scrutiny when created.
They may even be suited for a purpose relying solely on the expertise of the creator.
Model building steps may vary depending mainly on its purpose and complexity.
Usually, such a process would involve a gradual formation of the model based on the scientific and industry standards, coordinated with the investigation of the data availability, limitations and constant validation of the assumptions.
When acquiring data, it's important to assure its high quality and correct capture of the information that underpins the model. For more complex or dispersed data such as text communications over social media, data mining techniques may come in handy.
8. What are the features of a good model?
Good is the model that serves its purpose. Even if the purpose is safe to sleep at night or avoiding the risk, the model may be deemed successful. Some models may describe such complex phenomenons that understanding them may take months or even years.
Good models can be characterised as being:
-
Simple
-
Manageable
-
Accurate
-
Statistically valid
-
Displaying causality
-
Following best industry practices
When you can improve any of the above parameters without worsening any other - go for it.
And if you are not sure how to create a model from your dreams, contact our Excel Experts who contributed to this article and specialise in creating the majority of the models listed here.
