The Excel Conditional Cell Formatting tool is a powerful, yet often underutilised tool in financial modelling. The following blog and two videos will outline its value in a financial model. Conditional Cell Formatting can realise flexible financial reporting by currency, which is often a necessity for broad corporations with multiple business units, that need to report in differing source currencies due to country of origin.
It can be applied where there is need to change units of measurement, from Custom Cell Format in English (#,##0 “customer(s)”) to the French equivalent measure (#,##0 “client(s)”). Finally Conditional Cell Formatting greatly improves key input assumption cells, such as the actual unit of volume i.e. “aircraft(s)”, “warehouse(s)” or “office(s)” in an activity-based costing or contract pricing template; which companies use across their three cleaning revenue streams.
The following financial model and videos illustrate the below, practical approaches of applying Conditional Cell Formatting in Financial Modelling.
Strategize FM – Conditional Cell Formatting.xlsm
Reporting Currency of Financials
In some instances, companies might edict a financial model template be used across all business units or subsidiary companies. Certain entities might report their financial statements in other currencies. Hence the need to create a financial model template that can cater to various currencies, which will seamlessly model the entity’s source financials for instance in Japanese Yen or Euros is crucial.
Excel’s Conditional Cell Formatting will deliver a flexible solution, whereby a dependent cell will drive the cell format of the financial outputs. This is demonstrated in the above, first video.
Language of Units of Measurement
Other situations will require a model for financial planning & analysis (FP&A) to translate between languages, whereby the entire workbook, or specific sheets or outputs will change output language for differing stakeholders.
There are some metrics that will be presented in a dashboard summary, contingent on that company or industry, which if presented via cell formatting, will require the adoption of Conditional Cell Formatting.
Input Assumption Cells
The implementation of a financial planning template can result in a very generic, cookie-cutter approach that may contain elements, which are relevant to some business units but not to others. To improve the model’s flexibility and ease of use, implementing Conditional Cell Formatting with certain input cells could be invaluable.
The above video illustrates the value in an activity-based costing or contract pricing tool, through the adoption of Conditional Cell Formatting in the “Volume per Month” cell (G58). This cell helps to communicate the specific contract type, which the model user is working with; whether it is the pricing of an “Aircraft Cleaning” contract, a “Warehouse Cleaning” contract or an “Office Cleaning” contract.
A further example (in another financial model) could be where US subsidiary entity will only commence gold production from 2018. Hence clearly the forecast input cells of gold produced (in ounces) will not only be zero, but it will be irrelevant for the years prior – for forecasting purposes. Whereas, all cells from 2018 and beyond must appear not just as zero, but in ounces so as to alert the model user this information for forecasting the gold production volumes. These volumes must be inputted in ounces and not tonnes or kilograms.