Financial Model Formatting: Numbers

What Are Financial Model Formatting Numbers?

Numeric data displayed in different colors and formats in financial models to make the models clear and understandable is called financial model formatting numbers. 

Well-structured financial model formatting numbers can help model reviewers efficiently obtain financial information and make wise decisions.

Financial professionals typically use Excel to create financial models. Generally, the data in a model is classified into four categories: 

• Historical numbers
• Assumption numbers
• Financial formulas
• Formulas linked to other Excel worksheets.

How to highlight key points and data categories in the model to make it easier for reviewers to access all information is also a consideration for professionals. To address this issue, model builders often use different colors to distinguish key points and data categories.

Finally, the numbers in a financial model need to maintain a consistent formatting structure. This means that data of the same type should have the same number of decimal places, alignment, scaling, expression of negative values, and currency symbols.

Proper categorization, coloring, and formatting of numeric data can increase the likelihood that financial models accurately and efficiently convey financial information. Mastering number formatting is an essential skill for success in entry-level finance roles.

Data Categories

If reviewers misinterpret information due to unclear data categorization, it reflects an oversight in the model's design.

To become an outstanding financial model designer, being familiar with the following data categories is essential.

Let’s extract a portion from a complete income statement to help you better understand:

(Figure 1: For details, see WSO Income Statement Template)

Historical Numbers

Historical numbers are defined as confirmed facts that will not change. As shown in Figure 1, the numbers in cells C6 to G6 are historical numbers.

We determine that the numbers in C6 to G6 belong to the historical numbers category because the column headers for these five columns all include "A." 

In financial models, the "A" following a time period is an abbreviation for "Actual," indicating that the corresponding data is confirmed and unchangeable.

Historical numbers are a type of hard-coded input. In financial modeling, hard coding refers to directly inputting data into a model rather than deriving it from calculations or external sources. (Hard Coding, 2023). 

In financial models, the input is not derived from calculations based on other financial data.

Assumption Numbers

Assumption numbers are estimates of potential future scenarios. If professionals set assumption numbers based on comprehensive analysis and are fortunate, these numbers might represent what will actually happen in the future. 

However, in most cases, assumption numbers will differ from the actual data, which can only be confirmed in the future. In Figure 1, the numbers in cells H6 to K6 are assumption numbers.

We determine that the numbers in cells H6 to K6 are assumption numbers because the column headers for these four columns all include "E." 

In financial models, the "E" following a time period is an abbreviation for "Estimated," indicating that the corresponding data is an estimate of future scenarios and may differ from actual values.

Some professionals prefer to use "P" or "F" instead of "E". These are abbreviations for "Projected" and "Forecasted," respectively. Both indicate predictions of future actual scenarios.

Assumption numbers are often a type of hard-coded input. The model builder typically enters them directly into the model after completing some analysis, rather than using Excel formulas to calculate them.

Financial Formulas

Financial formulas in a financial model can range from simple arithmetic operations, like addition, subtraction, and multiplication between cells, to complex Excel built-in financial functions.

It is important to note that even if a cell’s input only references the value of another cell, we still categorize the data in that cell as a financial formula. In other words, as long as the input in a cell is not a static number but rather a reference or calculation, the data in that cell is classified as a financial formula.

(Figure 2: For details, see WSO Income Statement Template)

In Figure 1, the percentages in cells D7 to K7 are financial formulas. As shown in Figure 2, these percentages are calculated by referencing the values in several cells within the Excel workbook.

Let’s extract a portion of the cash flow statement corresponding to the previously mentioned income statement to introduce the final data category.

(Figure 3: For details, see WSO Cash Flow Statement Template)

Formulas Linked to Other Excel Worksheets 

Due to the interrelated nature of many financial data points, professionals often use Excel formulas to calculate new values by referencing data from other worksheets. 

When a cell's input references data from another Excel worksheet, we categorize the data in that cell as "formulas linked to other Excel worksheets."

In Figure 3, the numbers in cells C6 to K6 are formulas linked to other Excel worksheets. Those familiar with the 3 financial statements know that, in our example, the net income value in the 2007 income statement should equal the net profit value in the 2007 cash flow statement.

Therefore, in Figure 3, we can see that the model builder used Excel formulas in cells C6 to K6 to reference the net income values from the corresponding years’ income statements, ensuring net income equals net profit in each year’s 3-statement model.

Financial Model Formatting Numbers

Financial experts assign various colors to different data categories, enhancing clarity and helping reviewers interpret the model efficiently. By emphasizing important aspects, stakeholders can extract more information from the model more effectively.

It is worth noting that there is no universal standard for color usage. Different professionals may assign different colors to the same data categories. Therefore, we will introduce some of the most common color usage practices.

Blue Color

Generally, financial specialists color hard-coded inputs, which include the historical numbers and assumption numbers in a financial model, in blue. As shown in Figure 1, the historical numbers in cells C6 to G6 and the assumption numbers in cells H6 to K6 are colored blue.

Sometimes, professionals differentiate assumption numbers from historical numbers by adding a background color. For example, in Figure 1, we can see that cells H6 to K6 have a light blue background.

Green Color

The data category "Formulas linked to other Excel worksheets" is often colored green, symbolizing data flow from external sources within the file.

This is because some model builders aim to use green and black (as explained below) to differentiate which cells reference content outside the current worksheet and which cells reference content within the current worksheet.

Red Color

The use of red is relatively specific, as it is not associated with the previously mentioned common data categories. Generally, values in the model that reference other Excel files or any external links, such as Bloomberg data, are colored red.

The distinction between an Excel worksheet and an Excel file is worth noting. In most cases, a single Excel file contains multiple Excel worksheets. Therefore, using red and green highlights the difference in data sources.

In many cases, red is also used to highlight errors in a financial model. Due to its high visibility, red helps model builders and reviewers quickly locate errors, making it easier for them to correct the results.

As shown in Figure 4, the percentages in cells D21 to G21 are extracted from an external website. Therefore, they are marked in red.

(Figure 4: For details, see WSO Income Statement Template)

Black Color

Black is the most frequently used color for numbers in financial models. If the data category of a financial number is a financial formula, it is often colored black.

Some model builders choose not to use red and green to differentiate data categories. Instead, they prefer to use black to color all non-hard-coded input data uniformly. As a result, you will often see Excel financial models that only contain blue and black colors.

As shown in Figures 1 and 3, the financial formulas in cells D7 to K7 in Figure 1 and the formulas linked to other Excel worksheets in cells C6 to K6 in Figure 3 are both colored black.

For more information on the use of colors in financial models, please refer to: Financial Model Formatting: Colors.

Formatting Structure

Adhering to rigorous formatting guidelines is essential when creating a professional financial model. This means that the model's numerical data must follow the relevant layout and formatting specifications.

Model builders should use any formatting methods that make financial models clearer. Therefore, covering all formatting standards in a single article is impossible. The following sections will introduce the 5 most common and important formatting requirements.

Decimal Places

Depending on precision requirements, model builders should decide and adhere consistently to a standard for the number of decimal places retained for each type of financial data. 

However, they must adhere to the initially established decimal place retention standard and not change it arbitrarily during the model-building process.

The distinction between "type" and "data category" shows that even within the same data category, different types of data may follow varying decimal place conventions.

As shown in Figure 4, the value in cell D17 is derived by dividing the value in cell D13 by the value in cell D20, making it belong to the "financial formulas" data category. 

However, since this value is not a percentage, it is not rounded to an integer like the data in row 7, which belongs to the "financial formulas" category but is in the form of percentages.

Figure 4 shows that the formatting standard initially established by the model builder—retaining one decimal place for non-percentages and rounding percentages to integers—has been consistently followed throughout the entire income statement.

Alignment

In a well-structured financial model, the different elements within each cell must be aligned according to a specific alignment style. Professionals typically follow the standard of aligning numeric data to the right and text to the left.

As shown in Figure 4, all text in the table, such as titles and row names, is left-aligned. Meanwhile, all numeric data, specifically all inputs within the subtable from C4 to G26, is right-aligned.

The reason financial specialists follow this alignment standard is that left-aligned text is easier to read, while right-aligned numeric data creates a formatting distinction from the text, making comparisons between numbers more convenient.

Scaling

The numbers analyzed in financial models are often extremely large, with tens of millions or even hundreds of millions being common. Therefore, properly using scaling to express large numbers concisely is very important for professionals.

The most commonly used scaling units are "K" and "M," which stand for "thousand" and "million," respectively. 

Scaling units like "K" and "M" make large numbers more concise, aiding readability and helping reviewers gauge values quickly. For example, "1.2M" is clearly more intuitive than "1,200,000" as you don’t need to count the digits in the former to understand its value.

To further save space within each cell, the scaling used in the model is often specified at the beginning to inform reviewers that all subsequent data will be presented using this scaling abbreviation.

As shown in Figure 4, the table's header corner has the label "USDm." USDm indicates that all data in the table is expressed in millions and denominated in US dollars. For example, the actual value of 212.6 in cell C6 is "$212,600,000."

Expression of Negative Values

Model builders often need to include negative numbers in their financial models. For example, the interest expense must be subtracted as an expenditure when calculating cash flow from operating activities in a cash flow statement. Therefore, all interest expense data in the cash flow statement is presented as negative numbers.

There are two common ways to represent negative numbers. One method is to directly add a minus sign before the financial data that needs to be subtracted, reminding reviewers that the value should be subtracted rather than added. 

For example, if the interest expense that needs to be subtracted in a cash flow statement is $1,000, it can be written as "-$1,000" in the cell.

Using brackets to represent negative values is a more common method than directly adding a minus sign. To avoid confusion, financial specialists consistently use a single currency symbol throughout the financial model.

Figure 5 shows how brackets are used. They are added to the "Less: Interest Expense" row to remind reviewers that this column represents expenditures and should be subtracted when calculating cash flow from operating activities.

(Figure 5: For details, see WSO Cash Flow Statement Template)

Currency Symbols

The final formatting standard worth noting is the selection of currency symbols. Since different currency symbols assign different actual values to the same numbers, financial specialists use only one unified currency to price all data in a complete financial model.

Let’s revisit Figure 4. The "USDm" in the header corner of this income statement informs readers that all data in the table is denominated in US dollars. 

If not all data in the model requires a currency symbol to reflect its true value, the model builder can choose to add the currency symbol to the row or column name to indicate that specific rows or columns are denominated in a certain currency. This is illustrated in row 20 of Figure 4.

For more recommended formatting methods related to designing financial models, please refer to: Financial Model Formatting: Advanced Numbers.

Conclusion

Properly formatting your financial data in Excel can help you design clearer, more concise, and professional financial models. This skill enables those reviewing your financial model to extract meaningful information more efficiently and accurately.

Please remember the work process for formatting numbers outlined in the second Key Takeaway. Learning to properly format numbers will help you perform better in entry-level roles in the finance industry, such as financial analyst positions.

If you want to learn more about financial model formatting beyond just numbers, please refer to: Financial Model Formatting.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Financial Model Skills
• Financial Model: What is it?
• Financial Modeling Code
• Financial Modeling for Beginners
• Financial Modeling Tools