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.
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:
- 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!