VLOOKUP is one of Excel’s best functions. Its uses are seemingly endless.
With Financial Models it is particularly useful when trying to model fixed or semi fixed costs e.g. costs that stay the same until a certain level of production, then it needs to step up to a new level.
As an example, if we have a factory, whether we produce 0 or 1 000 widgets we need to hire people and pay the rent, say 100 000. But when we go over 1 000 widget we need to hire another person so perhaps the monthly cost is now 125 000. Get to producing over 1500 widgets and now we also need to hire more storage space which may total 150 000.
A good financial model should allow you to play with the level of production and see the impact including what will happen to ‘fixed’ costs. Most people use a nested IF function but this is a bad way to do it.
Below a video clip on VLOOKUP (both TRUE and FALSE options). In the text below this, we focus on the TRUE option only which is applicable for this course. If you want to know more about VLOOKUP, look at the VLOOKUP mini course.
To see the full course contents click here.