Share this blog
The Financial Modeler- Conditionnal Format Controls – Part Deux
- Spotting Errors
- How to Model Depreciation
- Improve Table Visualization using OFFSET and Conditional Formatting
- OFFSET Function Tutorial
- Getting More From Your Pivot Tables
- Conditional Formats Control – The Warm-Up
- Improve your model now! #REF Editions
- Back to School – The Excel Model
- R1C1 Enlightenment
Author Archives: Sebastien Labonne
Spotting Errors
In a workbook, I like to be consistent in the use of columns across all worksheets. If the first month of my model is in column L in one worksheet, then it will be the same in all sheets.
I have found that this method is really useful in reading formulas because you can promptly see [...]
How to Model Depreciation
Completing the OFFSET series, let’s see a real-life example on how to use the OFFSET function to model depreciation.
Basically, the depreciation model needs the following inputs:
- Monthly capital expenditures forecast
- Useful life of the assets
With those inputs we’ll be able to calculate:
- Monthly depreciation
- Accumulated CAPEX
- Accumulated depreciation
Improve Table Visualization using OFFSET and Conditional Formatting
Strong of our knowledge of the OFFSET function, here's a tip on how to use that function in combination with Conditional Formatting to make a table more legible by reducing clutter.
This tip can be applied on a table where the items of the main field are layed out on multiple lines, just like in the next picture.
Posted in Tips Tagged Conditional Format, Excel Function, Format, OFFSET, Tables, Visualization 1 Comment
OFFSET Function Tutorial
OFFSET is a useful function that adds lots of flexibility to your Excel models.
Using it can help you transform what would have been formula modifications into simple inputs changes. A common example is to use it to automatically compute monthly figures into year-to-date results by changing one input instead of changing formulas each month.
Download the [download id="4"] file, to learn how to use this function.
Getting More From Your Pivot Tables
In A Few More PivotTable Improvements in Excel 2010, Diego Oppenheimer, Program Manager on the Excel team at Microsoft, discusses new pivot table features being implemented for the next release of Excel.
One of the improvement mentioned is the ability to repeat down labels to facilitate using formulas with the pivot table as a source. If you don't want to wait for the next Excel version, here's a trick to get to the same result.
Conditional Formats Control – The Warm-Up
Conditional formats is a wonderful feature that incrementally adds format to a cell based on some criterias. You could use it to highlight cells by adding a color fill, changing borders or modifying any other format element.
In it’s basic form, you can use it with a simple condition depending on the cell value. However, a [...]
Posted in Excel Functionality, Model Techniques Tagged Conditional Format, Controls, Error Handling Leave a comment
Improve your model now! #REF Editions
Are you looking to:
Quickly find the sources of all your #REF errors found in some cells?
Audit your model for potential undetected problems?
Simply use Excel’s Find functionality to find all of the #REF errors.
Press CTRL-F to launch the “Find” dialog
Type #REF in the “Find what:” section
Click the “Options” button to expand the dialog, if it’s not [...]
Back to School – The Excel Model
I often find myself having a hard time explaining what a financial model is because to me, it could be almost anything.
That said, I'll set aside the financial part today and show you a very simple Excel model, useful for those of us who have kids in elementary school. It's my personal version of the random cards you might have at home to help the kids learn their alphabet or addition tables.
Conditionnal Format Controls – Part Deux
In this post, I'll show you how I like to use Conditional Formatting in my Excel models to add a disabled look to some cells based on a criteria. With this technique, you will get clear visual indications that will simplify inputs and help you avoid errors.