This example shows how to use variable values in a model and also introduces the percentage numeric type and the use of names for ranges of cells. Calculating pay from hourly rates and hours worked is fairly straightforward but setting the amount of tax paid requires assumptions about the resulting annual wage. This model shows a simple approach to this, multiplying a fixed weekly amount by 52. In practice, if hours vary markedly, tax may be rebated to the employee if in excess or the employee may be required to pay tax at the end of the year according to the amount he has earned (the American system and now being introduced to the UK).

We begin by setting up some variables for use in the model. Employees in the UK suffer various deductions from their pay, including income tax at a standard rate and a higher rate and National Insurance. There are thresholds at which the tax rates are introduced, one for the onset of basic rate tax and the other for the onset of higher rate tax. National Insurance is paid on all income up to a certain level. We set up the variables for these items so that we can use them in calculations for individual earnings.

The formatting in Row 2 was achieved as follows:

1. Select cells A2:F2
2. Select Format/Cells
3. Click on the Alignment tab
4. Select the Wrap text option

The formatting of the percentage items in Row 3 was achieved as follows:

1. Select cells A3, B3 and E3 in turn
2. Click on the Percent button (%) in the Formatting toolbar
3. Enter the value required, e.g. 10

Note that if you first enter 10 and then click on the Percent button you will get 1000%. The percent format multiplies the cell contents by 100.

The next step is to define some names which we can use in place of clumsy cell references like ‘$A$3’. To define a name for cell A3 proceed as follows:

1. Select cell A3
2. Select Name/Define from the Insert menu
3. Click OK to accept the name which is taken from the label in cell B2 immediately above

Repeat this for cells B3:F3.

You can inspect the names which are available and move quickly to them by clicking on the Name List which is in the area to the left of the Formula Bar - click on the triangle and the list drops down. You can move to a cell defined by a name by selecting it from the list.

We now add some column headers for the individual data - Name, Hourly Rate, Hours Worked, Gross Weekly, Gross Annual, Annual Tax, National Insurance, Total Tax and Net Income. We also add some appropriate data.

We now define names for the new fields as follows:

1. Select the range B6:B11 (or the range of your data)
2. Select Name/Define from the Insert menu
3. Click OK - the name is defined from the label in B5.

Repeat this for columns C to I.

We now use the names we have created in formulae to calculate the rest of the data in the sheet. We can do this as follows:

1. Select cell D6
2. Enter either:
3. =Rate*Hours or:
4. select Insert/Name/Paste and choose from the list of names

Copy the formula in D6 to D7:D11.

In E6 you enter:

=Gross_Weekly*52.

In F6 you enter:

=IF(Gross_Annual<No_Tax_Threshold, 0,

(IF(Gross_Annual<High_Tax_Threshold, (Gross_Annual-No_Tax_Threshold) * Base_Tax_Rate,

(High_Tax_Threshold - No_Tax_Threshold) * Base_Tax_Rate+ (Gross_Annual -High_Tax_Threshold) * Higher_Tax_Rate) ) )

(No line breaks, these have been added here for clarity.)

This is one of those complicated IF functions you met earlier. The Annual Tax paid is dependent on the gross earnings. There are three possible situations:

1. If the amount earned is less than the No Tax Threshold then no income tax is paid;
2. If the amount earned is between the No Tax Threshold then income tax is paid on the amount over the No Tax Threshold;
3. If the amount earned is over the High Tax Threshold then income tax is paid on the amount between the Low and High Thresholds plus the amount between the High Tax Threshold and the total earnings.

Thus:

If I earn £3,000 I pay no tax

If I earn £10,000 I pay tax on £10,000-£5,000 (my earnings minus the Base Tax Threshold)

If I earn £40,000 I pay tax on £25,000-£5000 (the High Tax Threshold minus the Lower Tax Threshold) at the basic rate plus tax on £40,000 minus £25,000 (my earnings minus the High Tax Threshold) at the higher rate

The first line of the IF function checks to see if earnings are less than the No Tax Threshold; if so then the tax is set to 0.

The second line checks to see if earnings are less than the Higher tax threshold; if so then the tax is calculated from:

(Gross_Annual<High_Tax_Threshold, (Gross_Annual-No_Tax_Threshold) * Base_Tax_Rate

The third line is the ELSE part of the second line, following the comma; if this part of the function is executed then earnings must be above the High Tax Threshold and the calculation is made from:

(High_Tax_Threshold - No_Tax_Threshold) * Base_Tax_Rate + (Gross_Annual -High_Tax_Threshold) * Higher_Tax_Rate)

In G6 you enter:

=IF(Gross_Annual> NI_Threshold, NI_Threshold * National_Insurance, Gross_Annual * National_Insurance)

This compares the Gross Annual earnings with the threshold for National Insurance contributions. If the earnings are less than the threshold then the contribution is the earnings times the rate; if the earnings are greater than the threshold then the contribution is the threshold times the rate.

In H6 you enter

=Annual Tax + National_Insurance

In I6 you enter

=Gross_Annual - TotalTax

You can now complete the model by adding three further columns for Weekly Tax, Weekly National Insurance and Net Weekly Pay. You should be able to work out the contents of these columns from the names available to you.

Each year the values in Row 2 may be changed by the Chancellor of the Exchequer and all you have to do to adjust the tax and National Insurance paid is to change the values in A3:F3.

This model may not implement the exact methods used in an Accounts department but if you understand this version you should have little trouble implementing whatever amendments you have to make to conform with legal requirements.

Use the Back Button to Return to the previous page