Lessons Archive

VLOOKUP explained in simple terms

Free Preview

Length: 5 minutesComplexity: Easy

Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP

Partial vlookup match- contains part of the value

Free Preview

Length: 15 minutesComplexity: Standard

How to do a partial vlookup match so that for example the vlookup finds something like Anitha Bath when only searching for the word Bath or finding the correct phone number when you only know 8 or the 11 characters.

Bands in excel exercise 2

Free Preview

Length: 10 minutesComplexity: Standard

In this example you are calculating the tax liability based on various salaries. The tax table is provided and a series of salaries are provided. You need to calculate what the tax liability will be on these amounts.

Excel ageing formula

Free Preview

Length: 10 minutesComplexity: Standard

How to create an excel ageing formula to age accounts receivables, stock, invoices, products or any other item that needs to be aged in useful bands for further analysis in Pivot Tables or other reporting tool.

Vlookup #n/a

Free Preview

Length: 15 minutesComplexity: Standard

Giving the seven reasons why a vlookup formula may end up with a #n/a error message.

Separation- Dealing with the Calculations of a spreadsheet

Free Preview

Length: 11 minutesComplexity: Easy

The crux of most budget/ forecasts is the calculations. If your calculations are correct, robust and flexible the reports should easily fall into place. With this in mind some tips and tricks with regards calculations of a budgets and forecasts:

Consistently display negative numbers clearly

Free Preview

Length: 5 minutesComplexity: Easy

Why you should display negative numbers in Excel spreadsheets as clear negative numbers and why this will help the person looking at the final report (which is likely to be on a piece of paper) <img class="aligncenter size-full wp-image-5765" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2016/05/Display-negative-numbers-1.png" alt="Display negative numbers" width="1079" height="545" />

Separation- Dealing with the inputs of a spreadsheet

Free Preview

Length: 11 minutesComplexity: Easy

The Inputs section of any spreadsheet is as important as any other section. In fact, no matter how well built the model, if the inputs are wrong, the model will be wrong. Remember GIGO- Garbage In Garbage Out.

Consistency of headers and flags

Free Preview

Length: 5 minutesComplexity: Easy

A very useful concept in spreadsheet best practice is the use of common and consistent headers in a budgets and forecasts. This is not the headers in your print settings but rather items that you always want to be able to see (and use) at the top of each sheet in your spreadsheet. It is sometimes referred to flags such as timing flags. As shown below, at the top of each sheet we typically have the following: <img class="aligncenter size-full wp-image-5768" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2016/05/Headers-and-flags-1.png" alt="Headers and flags" width="867" height="174" />

Consistently use simple Excel formulas (even if you need to use more cells)

Free Preview

Length: 5 minutesComplexity: Easy

ou may think it is impressive building complex, long Excel formulas in a single cell, but from a spreadsheet best practice perspective this just introduces more risk. The longer the formula, the more chance of errors and the more difficult it is to audit/ understand/ use. Even the developer may struggle to understand the complex formula a day or two later. Which would you prefer to have to review (these formulas below do the same thing, but one is built in a single cell and the other is over 3 cells) <h3>Single Cell</h3> =MID($A12,FIND("#",$A12)+1,FIND("#",$A12,FIND("#",$A12)+1)-FIND("#",$A12)-1) <h3>Three Cells</h3> =FIND("#",$A12) =FIND("#",$A12,E12+1) =MID($A12,E12+1,G12-E12-1) Although they achieve exactly the same thing, we promise you it is easier to understand and fix the second option.

Consistency of Formula across rows/ columns

Free Preview

Length: 5 minutesComplexity: Easy

When you build your formula, you should try as much as possible to build only one formula that you can copy across or down. Try and avoid building inconsistent formula within a row/ column. As noted in the picture below, row 12 seems to have 2 different formula where a number of cells use the 5% inflation rate, but one cell (I20) uses the 7%. This is inconsistent and likely to create a misunderstanding or is already an error. <img class="aligncenter size-full wp-image-5756" src="https://online-excel-training.auditexcel.co.za/wp-content/uploads/2016/05/Consistency-of-Formula-across-rows-columns-1.png" alt="Consistency of Formula across rows columns" width="1085" height="323" />

Consistently use the same formats and units throughout

Free Preview

Length: 5 minutesComplexity: Easy

Why you should consistently use the same formats and units throughout a spreadsheet model and the risks of continually converting your numbers from one metric or unit to another

Consistently using inbuilt Excel Formulas

Free Preview

Length: 5 minutesComplexity: Easy

Why should you use the inbuilt Excel Formulas and Functions rather then trying to create your own or copying them in from some maths textbook

Consistency of Row and Column structures

Free Preview

Length: 5 minutesComplexity: Easy

Why you should build spreadsheets with consistency of row and column structures and the extra Excel features that this switches on.

Consistency – no hard coding numbers in excel

Free Preview

Length: 5 minutesComplexity: Easy

One of the most common spreadsheet errors is as a result of hard coding numbers in excel cells. Hard coding of values means that with a formula like = A1*B1 you also include a value, so the formula becomes = A1*B1 + 500. The +500 is a hard coded number which will result in errors no matter how careful you are.

Other best practice suggestions

Free Preview

Length: 11 minutesComplexity: Easy

Some other suggestions for building spreadsheet models that are robust, flexible and have a reduced risk of errors. <h2>Avoid multiple workbooks</h2> Using multiple workbooks adds risk. Where at all possible combine workbooks into one workbook (the CUT AND PASTE function is useful for this). If this is not practical it is vital that all linked workbooks are open as they are being used and everyone should know about the risk shown in the video clip below. To see the full course contents click <a href="https://online-excel-training.auditexcel.co.za/course/budgeting-and-forecasting-with-excel-course/">here</a>.

Why should you follow best practice guidelines for spreadsheets

Free Preview

Length: 5 minutesComplexity: Easy

As spreadsheets have become more widespread and are increasingly used to make important decisions, it has become critical to make sure that they are built in the safest and most efficient way. As a result, based on years of experience, a Spreadsheet Best Practice of sorts has evolved to help address the common issues associated with spreadsheet use and building. This applies equally to budgeting and forecasting (which we are most familiar with) and all other decision type models. Before we go into what spreadsheet best practice is, some useful information on why it is important (perhaps your FD should see this).

Spreadsheet Integrity

Free Preview

Length: 11 minutesComplexity: Easy

The concept of integrity is to ensure that where at all possible, the model is correct, AND indicates to the user that it is correct. Error checks are therefore critical.