This 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.

Strategize 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.

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].

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]”.

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.

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