VLOOKUP in budgets- The TRUE option

Please purchase the course before starting the lesson.

With budgets and forecasts 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 budget 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.

Back to: Budgeting and Forecasting with Excel Course > VLOOKUP in budgets