The 411 on Financial Modeling (2024)

If you’ve ever built a simple Excel formula to test how changing a variable wouldaffectyour revenue, you have already created a simple financial model of sorts. Financialmodels are essentially complex calculators—typically built in spreadsheets—thatprovideinformation about likely outcomes based on assumptions from financial forecasts.Business assessments ranging from valuations to credit risk are based on financialmodels.

Video: What is Financial Modeling?

What Is Financial Modeling?

Financial modeling is a tool for determining likely financial outcomes based on acompany’s historical performance and assumptions about future revenue, expenses andother variables. Financial modeling relies on financial forecasts: It takes a forecast’sassumptions and plays them out using a company’s financial statements to showhow those statements may look in the future. Because models are created from financialstatements, they most often generate results for a month, quarter or year.

Most financial models are constructed in an Excel spreadsheet and requiremanual dataentry. One of the simplest types, known as the three-statement model, only requires anincome statement, balance sheet, cash flow statementand supporting schedules. However, the uses for models vary greatly, so some are muchmore complex. Businesses routinely customize models for their own purposes.

There is software that allows users to optimize forecasting estimates with a rigorous,pre-built statistical modeling engine. With a few clicks, predictive modeling willcollect historical data, match it to industry standard statistical models and generate adashboard, offering predictions for future financial results and enabling users to applypredicted values directly into their plan or forecast. This software can integrate withspreadsheet tools like Excel so you can slice and dice data yourself. It canalso run your historical data through pre-built models on its own, to generate financialpredictions without your input.

What Is a Financial Model Used for?

Financial models are useful for many applications. Businesses commonly use them for:

• Valuations and raising capital. If you’re aimingto go public, for example, bankers will run financial models to determine how muchthe company is worth. You might also need to provide models in order toget venture capital funding, loans or other types offinancing.
• Budgeting and forecasting. Budget and forecastingmodels help finance understand the company’s performance based on input from itsvarious components. As each program, department and business unit creates its ownbudget, they can then roll them up into a single overall financial model for theentire business to be used to allocate resources and predict financial results forthe coming year.
• Measuring possible outcomes of management decisions.You might use a financial model to predict changes in revenue if you wereto, say, raise the price of your top-selling product next year.
• Credit analysis. Investors will use financialmodels to determine the likelihood of your business repaying its debts, if they areto lend you funds.

Why Are Financial Models Important?

Financial models are the simplest way to compute performance and express projectedoutcomes for your company. Depending on the specific model, they can advise youregarding the grade of risk associated with implementing certain decisions. Financialmodels can also be used to devise an effective financial statement that reflects thefinances and operations of company. This is important for pitching investors,securing loans or calculating insurance needs. The applications are virtually limitless,but the basic idea is that they help you understand where your company stands now, howit has performed historically and what to expect in the future.

Who Uses Financial Models?

Anyone with an interest in the financial performance and outlook of a company could use afinancial model, and there are courses for developing the skill. However, professionalsin business development, accounting, financial planning and analysis (FP&A), equityresearch, private equity and investment banking frequently develop models in the courseof their usual duties. Each of these analysts use different types of models depending onthe focus of their business.

What Are Some Examples of Financial Models?

The forms of financial models vary as widely as their functions. For instance, thethree-statement model mentioned earlier is the most basic variation. It simply takesprevious financial statements and projects them into the future. It provides a completeoverview of the company’s past, present and future and has the added benefit ofallowingyou to see what would happen if you changed some assumptions. For instance, what wouldhappen if we sold 200 more units? Or, what if we reduced our labor costs by 12%?

Another example of a financial model is the discounted cash flow model. To determinevaluation for an entire company or a particular project or investment, many analystswould use this model to provide the current value of the company and predict futureperformance.

Key Takeaways

• Good financial models are easy to understand and well-suited to their purpose.
• If you have basic accounting skills and a solid understanding of Excel, you canbuild a basic financial model.
• Financial models vary in structure and purpose, but there are some key industrystandards to follow, including those around color coding and formatting.
• A financial model is only as good as the assumptions and formulas it is built from,so check your numbers thoroughly.
• Do not include assumptions in your formula, and do not enter the same date twicewithin your worksheet. Changing your assumptions should automatically affect outputsthroughout the sheet.

Financial Modeling Examples

To get a better picture of a financial model in use, imagine a bakery is acquiring acandy company. The bakery could use a complex financial model for mergers andacquisitions to add the valuation of both companies together and present the newvaluation of the combined entity.

When pitching to an investor, your company might prepare models that demonstrate thegrowth investors could expect to see based on your company’s projected sales orimprovements in overhead due to economies of scale.

Or, if your print shop is seeking to build a new store with financing from a loan, thebank will use models to determine your company’s creditworthiness and the likelihoodthat your new location will be successful.

Financial Modeling Best Practices

Even with all the variety and customization of financial models, there are some generalindustry expectations, formatting and best practices. The best models are easy to read,accurate, well-matched to the application and flexible enough to fully accommodate thecomplexity of the task at hand. Here are some best practices:

1. Develop an understanding of the problem, the users of the models and the overallgoal of the model.
2. Unless you absolutely can’t avoid it, construct the entire financial model on oneworksheet. This makes the model easier to understand and prevents user errors.
3. For increased clarity and flexibility, group your sections. Start with yourassumptions, then your balance sheet and income statement. Your sections will varydepending on which financial model you are using, but keep them in logical groupsthat are easy to differentiate.
4. Follow standard protocols for color coding. Use a blue font for hard-coded numbers(assumptions). Formulas should be black. Green is reserved for links to otherworksheets, and red indicates links to other files.
5. Be consistent with number formats throughout your model. For instance, if you chooseto identify negative dollar values with parentheses, you should always useparentheses. In Excel, you can maintain this consistency by right clicking all cellsthat represent financial values, select “format cells,” choose the numbertab andclick “accounting.” You could also choose “currency,” but thishas more options andtherefore more opportunities for accidental inconsistency.
6. To avoid errors and preserve the readability of the model, every value should have acell to itself and only appear once in the sheet. You should never embed anassumption into a formula. If you do, you are likely to forget it’s there when youadjust your model, and this could significantly affect the accuracy of your outputlater.
7. Keep your formulas as simple as possible, and break complex calculations intomultiple formulas.
8. Check your numbers and your formulas. Your model is only as good as itsconstruction, and your output is only as good as the data you use to generate it.
9. Test your model. Try to construct scenarios to make it fail so you can refine it orat least understand its limits.

12 Steps to Building a Financial Model

A basic financial model is relatively easy to build in Excel, and it’s good forpracticing the Excel skills, formula logic and conventions you’ll need to constructmorecomplex models later. It also lets you play around with your assumptions to see how theyaffect the output. Follow these steps to build a basic model:

1. Create a new spreadsheet and label the first row “Assumptions.” Label columnsB, C,and D with three future fiscal years. In these columns, you’ll make predictions aboutyour performance in each of those years, for a variety of metrics.

2. Label rows with revenue, units, price, cost of goods sold (COGS) and operating expenseslike marketing, labor or whichever expense categories are relevant to your business.

3. Fill in the values with your assumptions, based on financial forecasts and yourcurrent financial statements. Use a blue font that is easily distinguished from black inthe corresponding cells. Remember to format any dollar figures with the accountingnumber format by right clicking the cells, going to “format cells” and selecting“accounting” in the “numbers” tab.

At this stage, your worksheet should look something like this:

4. This is a good time to freeze panes. To accomplish this, highlight cell B4 or itsequivalent in your worksheet, go to “view” in the task bar, and click“freeze panes.”This keeps your labels visible as you toggle around in the sheet. If you pay attentionto the row numbers as we move on, you’ll see how this improves readability.

5. Next, move down a couple of rows to start calculating projections for your incomestatement, beginning with net revenue. The font for these values will be black. You willrun the numbers in your assumptions through a formula to calculate net revenues bymultiplying units by price (=B6*B7 in the sample above). Copy and paste the formula tothe next two cells to the right. Excel should automatically apply the formulaappropriately for the year you’re working in, but it’s a good idea to make surethat itdid by double clicking the cell to view the formula. Ensure that the letters match thecolumn for that cell. Make sure your number formatting is set to “accounting.”

6. Calculate COGS by multiplying units by unit costs (=B6*B10 in our example). Just asbefore, copy and paste that formula in the two cells to the right, checking to make sureit was applied appropriately for each column. Check your number formatting to ensurethat it’s “accounting.” Now, the format of your worksheet should resemblethis:

7. Still within the income statement area, calculate gross profit by subtracting COGSfrom net revenue (=B18-B20 in our example). Copy and paste the formula to the next twocells to the right. Check the formulas and the number formatting for the correct columnand “accounting.”

Here’s our model at this point:

8. If you would like to see margins, you can divide the gross profit by revenue(=B22/B18) and change the number format to percentage so you get this result:

9. While still within the income statement, calculate operating expenses. For our sample,this included labor and marketing in our assumptions, and we will reflect those sameexpenses in the income statement. Remember that you don’t want to enter the same valuein two cells, so fill in these cells by using a formula to pull numbers from the sourcein the assumptions. In this example, we would compute labor for 2020 by entering =B13because we got that number from the assumptions. If we change the assumption later, theincome statement will adjust along with it. Repeat this step for the remaining fivecells in the operating expenses section of the income statement. Check your numberformatting.

Remember our source for operating expenses assumptions here:

10. Calculate the total for operating expenses by adding all of the costs in this sectiontogether. In our case, we can simply add labor and marketing, but if you have more thantwo items here, you can use the sum formula. As always, check the number formatting.

11. Calculate operating income by subtracting operating expenses from gross profit(=B22-B28). Copy and paste the formula in the cells to the right. Check your formula andnumber formatting.

12. Determine operating income margins by dividing operating income by revenue(=B30/B18). Again, copy and paste the formula for the next two years, and check yourformulas and change the number format to “Percentage.”

Now you have built a simple but dynamic model using best practices that automaticallycalculates gross and operating margins as you change assumptions. This is one of manymodels that can help you set goals and make decisions for your company.