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.
There is a multitude of techniques that financial modellers can use via Excel Custom Format 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.
Whilst the 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.
Finally, 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.
As we can see in the following.
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].
0:04:14.066,0:04:14.000 For the negative part, we want to color format these cells in Red –
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, 0:04:34.066,0:04:34.000
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, 0:05:05.033,0:05:05.000
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.
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.
However, I want to improve the optics of the referenced cells that are blank and return zero values.
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].
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 – 0:07:44.066,0:07:44.000
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. 0:09:19.033,0:09:19.000
Clearly June 2014 was the company’s worst month for cash receipts –
given the majority of these numbers are in red.
This concludes the video on Financial Model Benefits of Excel Custom Format Cells.