CASE STUDY 1

Transaction Calculator & Brokerage Fee

An easy way to visualize data in a table

This case study is answering a request for a broker to help in getting an easy way of calculating the transaction costs and the brokerage fees. If the first part is easy as you only need to apply a few formulas and pay attention to the cell reference types you are using, the Sensitivity Analysis table was more complex.

In the example below, we have a calculation of the total costs based on the possible multiples applied to the deal. As you can see, the company is applying 2 different multiples: one for Accounting and one for the Financial Planning of the deal. 

The challenge now, after fixing the initial formulas, is how to make the data found at the intersection of the selected multiples stand out. And how to make this automatically, without a need of manually adjusting the highlight colors in the row and columns headers, nor for the cell showing the total deal when taking the specific multiples into consideration.

For STATIC TABLES, we can always use the normal formatting tools, as the table's data is not changing. Sometimes even using the Alternating Colors found on the Format menu in Google Sheets.
 
For the DYNAMIC DATA TABLES tables, we are normally using the Conditional Formatting feature in Google Sheets. This allows us to easily highlight empty or non-empty cells, cells containing values higher or lower than a specified value, cells containing entirely or partially a specified text, etc.
 
The next level is using formulas in Conditional Formatting.
 

Registered members will see more and get free access to the files.

A simple example of using a formula in the Conditional Formating would be the one used for Column A on the Sensitivity Analysis table:

=A22:A47=$C$13

  • The formula will check the data in the range A22:A47 – the range with our Accounting Multiples for example.
  • Every cell from this range is cheched and if  it is equal to the cell C13, then it will apply the formatting defined by you.
  • Note the use of Abolute Cell References $C$13

This content is locked

Login To Unlock The Content!

Access the file FOR FREE: