Definition: A financial modeling consultant will provide expertise in generating forecasts, planning budgets, creating valuations, and providing equity research. A good consultant should be well versed in programming and statistics to help with various financial projects.
As technology continues to progress, financial modeling will have more overlap with programming and statistics. This article will include a list of skills to pick the right consultant for your job. First, modeling types will go over standard models and methods. We’ll then touch on some tools that can be used before summarizing the approach SurveyKing Consulting Services undertakes.
Skills a Financial Modeling Consulting Should Have
This list of skills will ensure a financial modeling consultant can complete all the requirements for your project. Many projects require pulling and organizing data from different places, so data skills are of utmost importance.
SQL
You would use a relational database when working with large amounts of data. SQL is used to access, modify, and upload data into a database. For example, many financial modeling projects will use data from accounting, payroll, or even research platforms to build models.
A consultant well versed in SQL will be able to build queries, views, and stored procedures, making it easy to access source data. Consider an example where an executive team wants to compare budgeted product margin with actual project margin in an online dashboard. SQL could be used to join multiple tables together and make comparisons and segment by product type.
Excel & VBA
Microsoft Excel is one of the world’s most widely used software programs. A good consultant should not only be familiar with the use of advanced formulas but also VBA; the programming language used in Excel used to extend the native functionality.
VBA can do everything from sending emails, uploading files to a SharePoint site or server, taking data from other files, and used to create custom formulas. A lot of modeling projects have a tendency to use nested IF formals that can get messy to handle the analysis of different scenarios. A custom formula could instead be used to simplify the process. This makes managing large of amount logic easier and, in turn, makes the models easier to update.
Business Intelligence Tools
Business intelligence tools like Tableau are commonly used to create charts and graphs quickly and have them accessible by anyone in a company. These tools make publishing charts, graphs, and data tables easy without building a custom website.
One of the most common things we include when building interactive Tableau reports is the use of actions. Actions enable you to share a filter within a single dashboard. For example, if you create a revenue report, a user could click a particular month and bring up the revenue detail for that month. Here are two resources for those actions.
General Programming
Scripting languages like Python are commonly used to perform statistical analysis and build models. For example, there is a variety of Python financial packages to help develop parts of your model.
Things like connecting to an API to pull external data, cleaning data with the use of Regex, or even creating dynamic Excel files, are all things that will ensure a successful project. The more well-rounded a consultant’s programming skills, the better they have of coming up with solutions to solve problems.
Basic Financial Statements
One of the most common modeling types is simply creating financial statements. These statements include an income statement, balance sheet, cash flow, and statement of equity, commonly produced in Excel. High-quality statements will compare the current period to prior periods, the budget, and forecasts. Variances and out-of-balance errors should use conditional formatting to highlight differences.
The financial statements should highlight these metrics:
- Revenue
- Gross Margin
- Operating Margin
- EBITDA Value
- Net Income
Forecasting
A forecasting consultant is crucial to planning the demand for a product, developing staffing and financial projections, and determining material requirements. In addition, forecasting is necessary to estimate product profitability and lead times and help mitigate supply chain issues.
Consider an example where a company is looking to complete a new joint venture for producing widgets. As a result, new employees must be hired, and inventory purchased. There could also be lead times for production. A forecasted income statement will be built from those assumptions, giving stakeholders insights into how the deal will perform.
A typical forecasting project for SaaS companies is predicting and accounting for attrition, the rate at which people cancel their recurring subscriptions. Evaluating past trends, machine learning, and market research build a model to predict attrition rates.
For manufacturing companies, a forecast needs to pull data from various sports, such as the ERP for time bills and material purchases, marketing tools for sales data, and vendor websites for inventory information. Again, an excellent financial forecasting consultant will recommend and help build process improvement to make additional forecasts easier.
Budgeting
Building a budget is crucial to a company’s success. The budget is usually formatted the same as the income statement, except the numbers are based on expectations. The main driving factors of the budget are planning sales, cost of goods sold, and sell/administrative expenses.
This planning step is where advanced formulas and VBA come into play. A consulting firm with a wide range of programming skills can build custom tools to help streamline the budget and make it more accurate.
To help budget revenue, SurveyKing offers the ability for you to conduct product and pricing research. For example, Conjoint Analysis and MaxDiff are two great ways to help plan for new products if your company sells a new product.
Mergers and Acquisitions
When a company wants to acquire another company, assessing the impact of the deal is essential. When it comes to mergers and acquisitions, there are two main models used.
Leveraged Buy Out
Most commonly used by equity firms when they want to accrue another company using debt. Most often, this model is used when a firm has the intention of later selling the acquisition. This type of model has a few basic steps:
- Understand the purchase price assumptions – This includes getting the correct acquisition price and getting the interest rates and terms of the debt.
- Plan how debt will be used – The debt structure could include additional funds to buy capital expenditures or invest in new technologies.
- Create financial projections – This is the model’s most complex step, and expert Excel and VBA skills will make the process easier.
- Exit planning – If a company wishes to exit after a certain period, the projected valuations will be used here. A lot of the time, IRR is used to determine if an exit makes sense.
Accretion/Dilution
This model is used when a publicly traded company wishes to acquire another company. The acquisition can use debt or equity (issuing new shares). This model aims to determine the effect of earnings per share once the purchase is completed. This model includes building debt and amortization schedules for goodwill.
Valuations
Getting the value of a company can be helpful when needing to take out a loan, go public (IPO), buy out a partner, or a whole list of reasons. There are main models used when valuing a company:
Discounted Cash Flow
The discounted cash flow model gets the present-day value of all cash flows discounted at a specific rate, usually the weighted average cost of capital (WACC). This model is generally used in equity research when determining if a company is under or overvalued.
Because cash flows are not always consistent, a discounted cash flow model might consist of an Excel file with multiple tabs to build each year of cash flow. A leveraged cash flow could also be created, making adjustments to exclude debt and other equity claims.
Comparability Analysis (IPOs)
When a company wants to go public, the value needs to be determined to set the initial share price properly. This research commonly involves comparing similar companies, called “Comparative Company Analysis”. An excel model can easily map out revenue, gross margin, profit margin, and other relevant industry specifics to understand how to value the company.
Sum-of-Parts
Sometimes called a SOTP evaluation, the sum-of-parts model simply breaks out the company into different business units to get a company value. Most commonly, this model is used when a company wants to restructure or identify underpromoting business units. The discounted cash flow method is commonly used to calculate each business unit’s value. Once you sum the value of the business units, you subtract net debt.
This valuation method is sometimes referred to as consolidations.
Options Pricing
Options are an investment type that offers an individual the right (but not obligation) to buy the underlying assets at a stated price during a specific period. Because the underlying assets can have a wide range of values, different models are used to determine a selling price. These methods require statistical tools such as R or a statistical package in languages such as Python or C++.
- The binomial Options Pricing Model (BOPM) – Uses a price tree (like a flow chart) to calculate the option price. You set the price for each tree level, then start the end of the tree and work backward to compute a final price.
- Black-Scholes Model – A differential equation that takes five inputs: the strike price of an option, the current stock price, the time to expiration, the risk-free rate (minimum return), and the volatility.
- Copula Function – A probability model that examines the association or dependence between many variables.
Tools to Build Financial Models
Depending on your project, these tools are great options for building financial models. The SurveyKing consulting team will guide you on what tool to use during the project.
Excel
Excel is the most common way financial models are built. Excel makes it easy to build scenarios, format data, and carry out standard financial equations like NPV and PV. VBA can be used to create custom formulas, clean data, or read data from other files.
Data Visualization Tools
Tools like Tableau and Power BI help build dashboards and track key performance indicators. These tools are perfect for combining the final metrics for a model. Company leadership might want to see an income statement with discounted cash flow all in one dashboard as an example.
Custom Built Web Apps
A custom-built web application or website can give you the flexibility to anything your firm requires. Front-end frameworks like React make it easy to build and then share components, perfect for modeling projects where Excel won’t work and where other data tools lack features.
Consider a company wanting to create a new budget process. Excel can be clunky and hard to distribute multiple files and combine them. A custom app could have one budget section built and reused for each region. Historical accounting data can easily be pulled using SQL, and inputs can be summarized with one click to create the master budget.
Our Approach to Consulting
SurveyKing consulting services have a flat rate of $50 per hour. There are no minimum hours for a consulting project. We bill travel expenses at cost for projects requiring travel or in-person meetings.
- Get an understanding of the problem. All of the inputs and desired outputs will be discussed.
- Get an understanding of your business or industry. This can help different areas that your current financial model may lack. This step usually includes an overview of the IT infrastructure and where input data is located.
- Model creation. Rough draft copies are created along the way and are shaped by the feedback you provide.
- Model delivery. The final product will be delivered. Additional adjustments can be made to ensure the project is fully complete and functional.
- Documentation. We write documentation describing the logical flow of data in the model, any Excel formula notes etc.
ABOUT THE AUTOR
Allen is the founder of SurveyKing. A former CPA and government auditor, he understands how important quality data is in decision making. He continues to help SurveyKing accomplish their main goal: providing organizations around the world with low-cost high-quality feedback tools.
Ready To Start?
Create your own survey now. Get started for free and collect actionable data.
