Financial Model Benefits of Excel Custom Format Cells

WP ThumbThis is an introductory examination into the use of Excel Custom Format Cells, in order to improve the cosmetic appeal of a best practice financial model.

Given the flexibility of Excel, both for good and bad, there are other potential ways to achieve the same aesthetic appearance – Excel Conditional Formatting comes to mind.
There is a multitude of techniques that financial modellers can use via Excel Custom Format Cells. 

For the scope of this illustration, this presentation will focus on what is perceived to be the more common and useful techniques, which deliver added value to a best practice financial model:

  • Mixed Cell Formatting,
  • Mixed Cell Formatting – Advanced,
  • Text Cell Formatting, and
  • Colour Cell Formats

Future studies will examine further techniques via Excel Custom Format Cells, which can deliver additional optics to a financial model’s presentation of information.

Excel AGGREGATEStrategize Financial Modelling – Excel Custom Format Cells

 

1. Mixed Cell Formats

This can help to improve the communication and clarity of a financial model’s input and output cells. In the first example – Oil Revenue; we will quickly key in some numbers to compute this revenue figure. The use of custom formatting with these three input cells will greatly improve the clarity, in terms of the unit parameters behind the calculation of Oil Revenue:

  • Average Sales Price of Oil is based on price per barrel.
  • Volumes of Barrels of Oil Sold is by barrel.
  • Number of Vendors is based on quantity of vendors.

Although these input cells appear as mixed cell formats, in actual fact, they remain integer cell formats. As we can see here if we multiply these cells.

In the Spot Gold Revenue schedule, we again key in some inputs to arrive at a total revenue figure.

  • Average Gold Price will be formatted to represent dollars per ounce.
  • The US Dollar to Stirling exchange rate is clear and doesn’t require formatting.
  • Our Total Gold Production is in unit ounces of gold.

Again the great value of the mixed cell format; is the fact the cells remain as integers for number crunching purposes.

Mixed Cells 1

 

2. Mixed Cell Formats – Advanced

Next I want to improve the optics on this Income Statement. In this approach, we want to format the Net Income integer cells based on whether the figure is positive (“Profit”), negative (“Loss”) or zero (“Break-Even”).

Take your time creating this custom format, it needs to be exact and accurate – otherwise we may not get the correct outcome.For the negative part, we want to color format these cells in Red – [Color3]

Finally, if we have cells to the value of zero, I want to color format these cells in Orange – [Color46].

Mixed Cells 2

 

Once completed, these custom formatted cells are presenting Net Income numbers in a way, which improves their communication or aesthetics to the model user. We can immediately see the company is making a profit only in 2014, 2017 and 2020 – which is a problem.

Let’s repeat this exercise with the Net Cash line item in the Cash Flow Statement. We are happy with the format version from the Income Statement, thus we will use it to format Net Cash cells for “Increase” and “Decrease”.

Once again, we can see the aesthetic added value of the custom cell format on these bottom line numbers. Fortunately this company is cash accretive for every year except 2013 and 2015.

 

3. Text Cell Formats

In this simple forecast worksheet, we have a list of business units that we will type. Next we will type in the corresponding actual revenue numbers for 2014. We want to reference the Business Unit names in the B19:B24 range.

Using Custom Format Cells, we can present those blank cells with this format – “[blank]”.

Text

Let’s test it out by deleting the Theta business unit name. The cell changes to [blank].

 

4. Colour Cell Formats

In this final illustration, we have a schedule of cash receipts from a list of clients. 

Excel Custom Cell Formats can enhance the visual appeal of this table, based on different colour formatting by value range – in a similar way to Conditional Formatting.

  • We want to format cells greater than or equal to 100,000 in blue – [Color5],
  • Cells in orange, [Color45], for cells greater than or equal to 50,000, and
  • Cell values below 50,000 in red – [Color3].

On completion, we have a table that is colour formatted based on the specific value ranges.

Color

Clearly June 2014 was the company’s worst month for cash receipts – given the majority of these numbers are in red.

 

Under Ctrl – Excel Ctrl shortcut keys

Here are some great Excel keyboard shortcuts, which all financial modelling professionals should know about. All of these Excel Ctrl shortcuts can greatly improve the speed, efficiency and accuracy when building or editing financial models

Microsoft Excel has a vast array of Excel Ctrl shortcuts. This video and blog will feature, in my opinion, the top 10 most used or important Excel Ctrl shortcuts for financial modelling. Hence, some model users may have a greater personal preference for other Excel Ctrl shortcuts.

Excel AGGREGATEStrategize Financial Modelling – Ctrl combination shortcut keys.xlsm

 

1. Format Cell. Using Ctrl + 1

Clearly this shortcut allows model users a variety of format options, with the selected cell or array of cells. Whether it is the number format in this example. The special formatting of cells, the font or border. Or if it is applying the Fill & Border format to cells.

The Ctrl + 1 is a must shortcut in financial modelling.

 

2. Save Workbook. Via Ctrl + S

Crucially Excel spreadsheet users should periodically save their work – say every 5 to 10 minutes. This example illustrates how, changes to the workbook must be readily saved via the Ctrl + S keyboard shortcut.

As we can see this shortcut will update the workbook based on its file location. For illustrative reasons, this workbook is saved to the Desktop.

 

3. Undo. Courtesy of Ctrl + Z

This shortcut enables a fast and effective way to undo any erroneous changes or inputs. As we can see, this shortcut will instantly revert back to a previous version of our work.

 

4. Copy. Thanks to Ctrl + C

We will be able to copy a cell or array, courtesy of this keyboards shortcut. Evidently it will save us time from having to recreate this data array.

 

5. Paste. Using Crtl + V

Based on the copied cells in the previous worksheet, this shortcut will facilitate an effortless reproduction of the data – somewhere else in the spreadsheet.

We can witness the use of these copy and paste keyboard shortcuts again.

 

6. Cut. Applying Ctrl + X

This illustration of telephone data by country can be readily edited, thanks to the cut shortcut, where we want to move some data to the right. In order to facilitate the addition of an extra column of information – Region.

Or perhaps we want to move the Vietnam data to the row below. One approach is to cut this array out – courtesy of Ctrl + X.

 

7. Outline Border format to selected cells. Employing Ctrl + &

This example summarises Stock Indices market data.

Below we have a summary of prices for CAC, KOSPI and Nikkei. We want to format these cells by applying the outline border, which the Ctrl + & shortcut will readily do – instead of the more onerous avenue via Format Cells

 

8. Bold Formatting. Thanks to Ctrl + B

In certain instances, it’s wise to format some cell text in bold format – especially model assumptions cells. Obviously it helps to improve the clarity of the spreadsheet to users. 

This keyboard shortcut will achieve this in a faster way.

Please note Ctrl + 1 is an alternative shortcut.

 

9. Italic Formatting. Via Ctrl + I

Similarly, italic formatting in some instances can improve a financial model’s aesthetic appeal. Changes in working capital elements in a cash flow statement demonstrates the shortcut’s value.

Please note Ctrl + 2 is an alternative shortcut.

 

10. Find and Replace. Applying Ctrl + H

Finally this shortcut is a great way to readily edit a cell. A great example is building out the titles in a Balance Sheet, where we have copied and pasted the Current Assets cell, down to cell that is designated for Non-Current Assets.

By using Find and Replace, we can seamlessly effect the changes of the cell.

 

Conclusion

As you can see, the use of Ctrl keyboard shortcuts improves the ability to build and manage financial models.

Excel Traffic Lights

There are many ways to verify or analyse financial outputs. Clearly all financial outputs must be correct, but the financial analyst must be able to deliver added value via summaries, dashboard and Excel charts

Excel Conditional Formatting is one tool, which enables modelling professionals to improve the financial analysis and presentation of important financial and operational data. This article and video will focus primarily on Excel Traffic Lights, via Excel Conditional Formatting, in order to improve the analytical demonstration of high-level financial performance.

Excel AGGREGATEStrategize Financial Modelling – Excel Traffic Lights.xlsm

 

The Excel Dashboard

As the following dashboard of monthly cash receipts demonstrates, there are many ways to summarise and present high-level financial performance. It is always advisable in a best practice financial model, to integrate error checks in the dashboard, which will safeguard the integrity and accuracy of our output, summary table.

There are many ways a best practice financial model can implement Excel Conditional Formatting, which this video will present a high-level précis of.

 

1. Highlight Cells Rules

This option is highly flexible in its analytical use. In this demonstration, we are going to specify “greater than” $80,000 to highlight all monthly cash receipts above this figure. Like with all of these examples, we are going to undo these conditional format options, because we will focus on Excel Traffic Lights at the end.

 

2. Top/Bottom Rules

Here users are able to produce a statistical presentation of say the top 10 monthly cash receipt amounts, across the entire table of monthly amounts by debtors. 

Clearly, this is a fast and effective approach to better understand a certain aspect of financial performance, such as the best or worst monthly cash receipts by debtor.

 

3. Data Bars

This conditional formatting alternative will format a cell with a horizontal bar line, which is based on the cell’s value compared to the values in the other cell range.

It is a great tool because we can, for example in this summary table, visually identify smaller or large monthly cash receipt amounts by debtor. As always, we have the ability to adjust the color or the gradient of the data bar.

 

4. Color Scales

This option can color code a data array based on the respective cell’s output value. By applying conditional cell formatting to fill a cell’s color, we can compare and analyse the cell’s value, in comparison to the cells in the data range

This option, like the previous options, can be edited and adjusted for personal preference and analytical needs.

 

5. Icon Sets – Shapes – Excel Traffic Lights

Spreadsheet users can apply Excel Conditional Formatting, through the “Icon Sets” to produce complimentary Traffic Light objects beside key financial outputs. 

Firstly we are going to format the monthly cash receipts, which will produce either a red, amber or green traffic light beside each. This will be based on that cell’s value compared to the value in the other cells.

We will undo these and format the Year to Date Variance % by each debtor. Next, we will edit the rule by adjusting the percentile for green and amber for the data range; the red traffic light will format cells below this percentile range.

We then format the Year to Date values by debtor.

I am not happy with the distribution, which the Excel Traffic Lights is producing, thus I am going to edit the distribution parameters. Again, we will allocate 50% to the green, 20% to amber and the balance to red.

This will clearly adjust our output of Excel Traffic Lights – which we can see.

 

Final Remarks

This article and video illustrate the immense value of the Excel Traffic Lights in financial modelling, via its application in Excel Conditional Formatting. It will help to improve the aesthetic presentation and communication of key financial metrics to users of financial models. 

5 Potential Problems using Excel VLOOKUP

This financial model illustrates the potential problems or pitfalls with the Excel VLOOKUP function in financial analysis. These five potential issues surrounding the Excel VLOOKUP, can be a source of error and frustration in financial or other spreadsheets. 

In this example, we are using a VLOOKUP to reference the Last price for the following Dow Jones Industrial Average stocks.

Excel AGGREGATEStrategize FM – 5 Potential Problems using Excel VLOOKUP.xlsx

 

1st Potential Problem – First Value

In some instances, Excel VLOOKUP can return the wrong value.

Our simple table of DJ30 stocks worked fine for the previous 4 days, however today’s extraction of stock data contains some lines of stock prices from the previous day.

Hence the Excel VLOOKUP is erroneously referencing the previous day’s Last price for: Boeing, Cisco Systems, General Electric, Microsoft and Verizon.

This highlights one major pitfall, which is often overlooked, by financial analysts with the Excel VLOOKUP function – it will always reference the 1st value.

 

2nd Potential Problem – Exact Value

A powerful feature of the Excel VLOOKUP function, is its capacity to identify only an exact lookup value, which can be also problematic when referencing values from a large data array – as in the following.

This can be true if we are sourcing data, like these closing Dow Jones stock prices, from an external database or information source, which we are dumping into our financial model periodically.

Although the previous 4 days were fine, today’s example illustrates how the bulk of our Excel VLOOKUP formulas are returning an error – #N/A. DuPont and Intel are the exceptions.

On the surface, it appears strange why our Excel VLOOKUP formulas are not working for these other stocks. But it is when we examine these referenced cells in the table array, that we realise these cells contain spaces after the text. Hence Excel VLOOKUP will treat them as not an exact match, and therefore not return the corresponding “Last” price.

To correct this in a simple and seamless way, we apply Excel TRIM function in column K and reference the corresponding cell in column B. Then copy and paste values, and paste these values over this range in column B.

 

3rd Potential Problem – Absolute Table Array Range

The Table Array parameter in the Excel VLOOKUP function can sometimes pose problems. The previous 4 days of days were all uniform, however in the following data dump stock prices; there are both DJ30 and NASDAQ 100 stocks listed. 

When we copy over the legacy Excel VLOOKUP formula from the previous day, the absolute reference range doesn’t facilitate all the data in this extended range. The Table Array parameter should be able to facilitate variable data arrays, either via the creation of a defined name range or an expanded range i.e. $B$2:$G$500 or $B:$G.

Please remember a large Table Array of referenced cells will increase the file size of the financial model.

 

4th Potential Problem – Absolute Column Number Referencing

This can be a major cause of erroneous Excel VLOOKUP referencing in financial modelling. Under this scenario the data extract appears uniform, however there is an additional column called “Executing Broker”, which our data source is now presenting in the data array.

It may seem innocuous, but our existing Excel VLOOKUP formula is now incorrectly referencing the “Low” price; instead of the required “Last” price for each of the stocks. Even if we cut out the “Executing Broker” column, paste over the right-hand columns; the Column Index Number will still wrongly reference the 6th column – column G.

Instead of using the hard-coded 6 integer, replace it with the Excel COLUMNS function; which will reference the entire data array. Therefore, when we insert the “Executing Broker” column into the data extract of stock prices, our Excel VLOOKUP formula will update and flex to facilitate the additional column.

We will continue to reference the “Last” stock price for our Excel VLOOKUP formula.

 

5th Potential Problem – Range Lookup

This is probably the most obscure pitfall of the Excel VLOOKUP in financial analysis. It is advisable in best practice financial modelling to default to the “FALSE” option – which references an exact match. In the following example, the data extract of the Dow Jones stock prices is not clean.

Hence if we are certain there is only one reference for each stock, then it is still advisable to maintain the “FALSE” option in our Excel VLOOKUP formula and not change to the “TRUE” option – closest match.

As this example illustrates, applying the closest match Range_Lookup parameter, will incorrectly reference adjacent values. The exact “FALSE” option would prevent these errors occurring; after we have cleaned up the source data array.