Times tables, calculators and Excel

You really ought to learn your basic times tables and be sure you can do quick calculations in your head. Of course, you can rely on a calculator or software such as Excel for something trickier, but you might need to be able to think on your feet and do some simple calculations quickly.

This guide is not a calculator manual or an Excel tutorial. You need to be comfortable with your own calculator and be sure you can do the basic functions. When it comes to Excel, remember that Excel only does what you tell it to. If things aren’t working out, you’ve made a mistake. It can be something as simple as an annoying error in the syntax of what you are writing, or you just can’t find the right option. If you can’t fix it quickly, go online and use your favourite search engine. It is almost certain that someone has had the same problem as you before and the solution is there.

Scenario

In this ebook I will place you in a scenario. The scenario is that you have been appointed to a small company – naturally your future working life will vary from this, but we will try to cover the main concepts. I will introduce you to various aspects of the company as we go through this article, and hopefully make you realise how much you have to take into account.

This is a fairly simplistic scenario, but should give you an idea. The company concerned is a small UK-based business that produces hand-crafted ceremonial plates and mugs. You need to get these crafted on-site and then sell them. You also have an online presence and distributors in several countries, and can ship your items worldwide.

Before we start, what sort of issues do we need to consider when it comes to making a profit?

Hopefully you came up with quite a few things that you need to consider. We won’t consider all of these here, but there are aspects such as supply costs, the price you set on your products, the cost of manufacture, the cost of your physical building space, the likelihood of customers returning (linked to customer satisfaction), the wages you pay your employees, and many more.

Your list should be quite extensive. But look back later and you will probably realise that you missed out some factors. It is vital in business to consider absolutely everything – the different factors all contribute to your overall profit or loss, and so will all be part of the calculations you need to do!

Basic arithmetic

You should be able to do simple calculations quickly, without making mistakes. Let’s say that you have decided to sell plates for £8 and mugs for £5. A customer buys three plates and two mugs. How much does this cost them? How much change do you give them from £40? You should be able to quickly work out that this costs 3 × £8 = £24 for the plates, and 2 × £5 = £10 for the mugs, so in total the cost is £24 + £10 = £34. Hence the change you give them is £40 – £34 which gives £6.

Note that you can write the total price in one go as 3 × 8 + 2 × 5. Multiplication takes priority over addition when doing calculations, so this can be seen as (3 × 8) + (2 × 5). Remember that you do not just work from left to right in calculations! Do brackets first, then any multiplications and divisions, before any additions and subtractions. This is often referred to as the BODMAS law (the letters are an acronym for Brackets, Orders, Divisions/Multiplications, Addition/Subtractions) but you probably just remember it as something like ‘multiplication before addition’.

You are likely to have to make a large number of calculations in your business life. As another example, say you create the plates at a cost of £6 and the packaging for each is 50p. That means you make a profit of £8 – £6 – £0.50 = £1.50 on each plate.

Simple calculations you ought to be able to do in your head, but don’t be afraid to resort to your calculator if needed – it’s better than getting it wrong!

Algebra

The word ‘algebra’ puts fear into many people, but in reality it is simply a shorthand way of writing things down so that you can easily do calculations and work out solutions to problems.

Above, we gave an example of a customer buying a certain number of plates and a certain number of mugs, and we calculated the total cost. Ideally, your till would just require you to give it the number of plates and the number of mugs, and do the calculation for you. Let us denote the number of plates by p and the number of mugs by m. I’ve chosen these letters to make some sort of sense (p for ‘plates’ and m for ‘mugs’) but you can choose any letters you like. Then we would say that the total cost is 8p + 5m. Note that we don’t include the multiplication signs (they tend to get confused with x, which is a common letter in algebra).

The advantage of doing this is that if you program this formula into your till, then each time someone comes to be served, you just type in the values of p and m (i.e. how many plates and mugs they are buying) and the correct cost is worked out. This is called evaluating the formula for particular values of the letters, which are often called variables.

A lot of what you do will be taking something in words and reducing it to a mathematical expression so you can work with it and evaluate it. And this is all algebra is – taking a phrase like ‘multiply the number of plates by 8, and multiply the number of mugs by 5, and then add these values together’, and writing it far more concisely mathematically as 8p + 5m. We’ll see more algebraic expressions later on!

Converting between units

You are likely to have to have to do some conversions in your daily business life. For example, you may need to work in different currencies as you deal with your financial transactions, or you might need something measured in centimetres, but the price quoted is per inch.

In our scenario, the business is international, so you have to deal with a wide range of countries. Be sure you know how to effortlessly convert between different currencies! The rates between different currencies is known as the exchange rate and are normally given as a decimal. These are volatile and can change at any time (literally within seconds) as the political and economic landscapes of the countries change, so there are financial considerations as to whether to accept the current rate or hold on for a while in the hope of getting a better rate.

You might well be aware that if you change, say, holiday money, that different companies set slightly different rates, so shopping around can save you some money. Some include commission, but of course they are competing in a market to offer the best competitive rate! You can find the actual exchange rates at any time on the internet, for example at www.xe.com which will give you the official live exchange rate between any two currencies.

As an example, at the time of writing this, the exchange rate from UK pounds to US dollars was 1.46124. This means that £1 = $1.46124, which would obviously be rounded in practice to the nearest cent, so £1 = $1.46. This exchange rate means that to convert from a certain number of pounds, you just multiply by 1.46124. You’ll probably need a calculator! It then makes sense to round it, so for example if you need to process an order of £75 from the USA, this is equivalent to 75 × 1.46124 which gives $109.59 (to the nearest cent) to quote to the customer. To go the other way (so in this example, from dollars to pounds) you are doing the opposite, so instead of multiplying you are going to divide. So, for example, if you receive a transaction to your business of $75 from your international branch, this is equivalent to 75/1.46124 which (using a calculator) gives £51.33 to the nearest penny. Just make sure you get this multiplication/division the right way round!

Other conversions you might need to do can involve quantities. For example, length can be measured in the common metric system or the slightly less common, but still used a lot, imperial system. For example, the imperial measurement of an inch (abbreviated to ‘in’) is equivalent to 2.54 centimetres (written as ‘cm’). Suppose you want to convert from inches to centimetres. Then just multiply by 2.54. So for example, if the height of one of your mugs is 4in, then this is 4 × 2.54 = 10.16cm. To convert the other way (so to convert from centimetres to inches), you need to do the opposite, so instead of multiplying you divide by this ‘conversion rate’ of 2.54. Hence if the mug is 12cm tall, that is 12/2.54 which is about 4.72 inches.

In imperial, there were 12 inches in a foot (‘ft’) and 3 feet in a yard (‘yd’). Hence, a yard is the same as (12 × 2.54) × 3 centimetres (converting the foot from 12 inches into centimetres, and then multiplying by 3 as there are 3 feet in a yard). This gives that a yard is 91.44cm. It is more common to write this as 1yd = 0.9144m (where ‘m’ is metres, a metre being 100cm). Another conversion that you might be familiar with (for example, if you drive), is the conversion between a kilometre (1000m) and a mile (1760 yards) which is approximately 1.609, and hence 1 mile = 1.609km.

Volumes are also often measured in metric and imperial ways too. For example, a gallon in imperial is approximately 4.54609 litres in metric. These are often used in petrol prices, which tend to be given in litres nowadays – for example, a price of £1.20 per litre is equivalent to a price of approximately £5.46 per gallon (just multiplying £1.20 by 4.54609). It might also be worth knowing that there are eight pints in a gallon, so a pint of milk is equivalent to 4.54609/8 which is approximately 0.568 litres (often written as 568ml, where ‘ml’ is the notation for millilitre – a litre is 1000ml).

Weights are another example – an imperial pound (‘lb’) is equal to approximately 0.453 kilograms (‘kg’). That means that 1kg is equivalent to approximately 1/0.453lb, which is around 2.2lb. Imperial stones (‘st’) are still used a lot in measuring people’s weight – there are 14 pounds in a stone, so a stone is approximately 14× 0.453 kilograms which is around 6.34kg.

As a final example of conversion, consider Celsius (°C) and Fahrenheit (°F) for measuring temperatures. This one is a little different, in that there isn’t a simple conversion rate to multiply by. Suppose c is the temperature in Celsius. Then the temperate f in Fahrenheit is given by the equation f = 1.8c + 32. So, for example, if it is 30°C then this is equivalent to 1.8 × 30 + 32 = 86°F.

To go the other way, we need to rearrange the equation to have c = something. This is called making c the subject of the expression. To rearrange an equation, you can ‘move’ things from one side to the other, just remembering that when you move from one side to the other, it becomes its ‘opposite’ on the other side.

So take f = 1.8c + 32. If we ‘move the + 32 to the other side’, it becomes its opposite, which instead of adding 32 is subtracting 32, and we have f – 32 = 1.8c. Now, remembering that 1.8c is shorthand for 1.8 multiplied by c, we can ‘move the multiplying by 1.8’ to the other side. The opposite of multiplying is dividing, and so we can write (f – 32)/1.8 = c. Since this is just an equality (the two sides are the same thing), swapping them round doesn’t do anything and so we have c = (f – 32)/1.8.

In the UK, it is often talked about in the blazing hot summer whether we will reach 100°F. Putting this into our formula, we get c = (100 – 32)/1.8 which rounds to 37.8°C. Research for yourself to find out if this has ever actually happened!

You probably convert more than you think, sometimes just automatically. A lot of people are equally as comfortable in Celsius or Fahrenheit, or in inches and centimetres, and use either in different circumstances!

Areas and volumes

Depending on the industry you end up working in, you may well have to deal with calculating areas and volumes. Be very careful when doing these calculations that you are using the correct units – you don’t for example want to order some new carpet to find out you mixed up your measurements in square metres and square feet!

Some of the common areas you might have to work out are:

• Area of a square = a² (the side length squared)
• Area of a rectangle = ab (multiply the two sides together)
• Area of a triangle = ½ab (half the base multiplied by the height)
• Area of a circle = πr² (pi multiplied by the radius squared)
• Area of an ellipse (oval) = πab (pi multiplied by the two ‘major and minor axes’)

π (pi) is a special number in mathematics. You can’t write it down as a decimal as it goes on for ever, but you can take it to be 3.142 – your calculator or Excel will use a more precise value.

For example, suppose you want to repaint a wall in your sales office and you have been quoted £3 per square metre by a painter. The wall is rectangular and measures 7m by 3m. Then the area of the wall is 7 × 3 = 21m². Hence you need to pay 3 × £21 which is £63 to repaint the wall. It is obviously absolutely vital here that you use the correct units – if either of you measure in square feet, say, then you will be hopelessly wrong if the other is using square metres!

Similarly, supposing your plates are circular with a 9cm radius, then the area of a plate is given by the formula πr², so you need to calculate π × 9² which works out to be around 256cm².

Volumes move us up into three dimensions. The following may be useful:

• Volume of a cube = a³ (the side length cubed)
• Volume of a cuboid = abc (the side lengths multiplied together)
• Volume of a cylinder = πr²h (area of the circle multiplied by the height)
• Volume of a sphere = (4/3)πr³ (4/3 multiplied by the radius cubed)

You can determine others too – for example the volume of a cone is πr²h/3 – note that this is one third of the area of a corresponding cylinder. Look up anything you need!

For example, suppose your mugs are essentially cylinders with height 8cm and radius 3cm. Then their volume is πr²h = π × 3² × 8 which gives approximately 226cm³.

Percentages

Percentages appear a lot in the news, and seem natural to many people. A percentage is simply a fraction where you divide by 100 – and that’s basically it. You see percentages a lot in business – shops will advertise ‘25% off’ in their stores, or say that their profits ‘have increased by 5%’, for example.

Working out percentages of is fairly straightforward. Suppose you decide to offer a discount of 20% on all of your items. 20% is the same as 20/100, which is the same as 1/5. So just multiply the current price by 1/5 to get the discount, and then subtract it. For example, if your plates retail at £8 and you offer 20% off, then work out (1/5) × 8 which is a discount of £1.60, so the customer only pays £8 – £1.60 which is £6.40.

It’s worth you noting the various discounts that stores offer. For example, suppose you are considering a sale and you decide to offer either ‘buy one get one free’ on all of your products, or 40% off everything. What is best for the customer? If they plan to buy two plates, then ‘buy one get one free’ means they get the two plates for the price of one plate, so they pay £8. The other offer is a discount of 40% on their total purchase of £16, which is a discount of (40/100) × 16 which gives a discount of £6.40, so they pay £16 – £6.40 which gives £9.60. Therefore the ‘buy one get one free’ offer is better for them.

However, what if they just want one plate and one mug? Then ‘buy one get one free’ is worthless to them as they don’t want the free items, and they would be far better off with the 40% discount offer. When deciding sales and what sort of offers to give to your customers, you need to bear in mind what they are likely to benefit from!

Interest rates, VAT rate and inflation

Some of the most common percentages that occur in business are interest rates, and the VAT rate and inflation rate. Let’s look at these – we’ll do some numerical examples as well as generalising the important formulae.

The VAT (Value Added Tax) rate is a fixed level of tax on purchases, which (at the time of writing) stands at 20%. Retailers would normally set their prices to incorporate the VAT, so that the customer does not have to work it out for themselves and they just pay what is labelled. So say, as we did before, that you set the sale price of your plates at £8 – but the customer will need to pay VAT on top of this. You can calculate that 20% of £8 is £1.60, so the actual price they pay should be £9.60. As this is a rather awkward number, the company may well opt to round it up (perhaps in justification, to account for ‘other costs’, etc.) and sell the plates for an advertised £10 – prices are easier for people when they are easy numbers to deal with!

The inflation rate is calculated by the government and the Office of National Statistics and is essentially an overall measure as to how much prices have increased over the previous year. This rate fluctuates depending on the economic circumstances at the time. The current inflation rate is usually used as the predictor for what will happen over the next year. Let us suppose that the inflation rate is 5%, and we follow the example here where the cost of creating the plates is £6. Then you can calculate the expected cost in a year by working out 6 × (1 + 5/100) – to see what this means, here the 6 is the cost of creating the plates now, and multiplying this by 1 + 5/100 gives the current cost and the extra inflation (dividing by 100 as it is a percentage). Hence this is 6 × 1.05 = 6.3 and so you expect the cost to be £6.30 this time next year. You can work out what you expect it to be in two years by multiplying this £6.30 by 1.05 again to give £6.62 (to the nearest penny). Note however that this assumes the inflation rate will stay constant at 5%, which is unlikely!

More algebraically, if the inflation rate is r% and you want to know the cost in y years of an item currently costing c, then this can be calculated by the formula c(1 +r/100)^y.

Hence in ten years’ time, our cost of £6 at an inflation rate of 5% is given by calculating 6 × (1 + 5/100)¹⁰ which is around £9.77.

Interest rates are slightly different. While there is a fixed ‘interest rate’ set by the Bank of England, different companies offering bank accounts, credit or loans will set their own rate of interest. Interest is essentially the additional payment on top of what you pay. If you are a saver, high interest rates are good – you get back significantly more money than you invested. If you are a borrower, the opposite applies – if you have a loan, say, you have to pay back significantly more than you borrowed.

The formula is identical to the formula for inflation rates – we are doing exactly the same thing, all we are calculating is the repeated multiplication of a value by a percentage. So, suppose we want to borrow £10000 at an annual interest rate of 3% for 10 years. How much do we have to repay eventually? Using the formula c(1 +r/100)^y, we have a total to repay of 10000 × (1 + 3/100)¹⁰ which comes out to be (check you get this on your calculator) £13439.16 to the nearest penny.

In practice, you wouldn’t pay this all off at the end, instead you would stagger the payments at monthly intervals. The usual way to do this is simply to take the final amount and divide it by the number of months, and then charge that amount per month. There are 120 months in 10 years (there are twelve months in a year), so our monthly payments are 13.439.16/120 which gives £111.99 to repay each month. This shows the point of a loan – you end up losing money but have a large amount at the start to use for a particular purpose, then repay at a (hopefully!) affordable cost over a long period of time.

You are likely to encounter all of these concepts in your career – for example, when setting your prices, or you may need to borrow money to start up your business, or you may invest your profits in a high interest account. Make sure you are comfortable with them and realise the consequences.

Solving equations

Algebra is used a lot when it comes to solving problems. Often you are faced with a problem in your working life and you have to work out the solution. Remember that all algebra is, fundamentally, is representing things written in words by letters.

For example, a customer has spent £50 in your stores on your plates (£8) and mugs (£5). Your file system has become corrupted – you know they bought two mugs, but not how many plates they bought. Can you work how many plates they bought to fill in the missing data?

We are trying to find out the number of plates, let us call it p. We know that they bought two mugs, which cost 2 × £5 = £10, and they spent £50 in total. Since plates are £8 each, this means that 8p + 10 = 50 (£8 for each of the p plates, together with the £10 for the mugs, must give £50).

To solve equations like this, we need to write p = something. All you really need to remember when rearranging equations like this is that if you move something to the other side, it becomes its opposite on the other side.

So we start with 8p + 10 = 50. Move the 10 to the other side, it is added on the left so when it is moved it becomes subtracted. We have 8p = 50 – 10 and so 8p = 40. Then to get rid of the multiplying by 8 we divide by 8 on the other side, to get p = 40/8 = 5 and so you have worked out that the customer bought 5 plates.

As another example, suppose that you know your profits over the last two months were £15,000 and that the profit in the second month was exactly twice that in the first month. How much profit did you make in the two months separately?

Let m be the profit in the first month. You made twice this in the second month, so you made 2m in the second month. Altogether you made £15,000, so adding the two profits together you get m + 2m = 15000. Collecting together, m + 2m is just 3m, and so you have 3m = 15000. Dividing by 3 gives m = 15000/3 = 5000, and so you made £5000 profit in the first month, and hence £10000 in the second month (since you know you made twice as much).

Picking out the mathematics from written words, and then finding the solution, is an important skill – you will develop it as you encounter it more and more!

Data presentation

One of the most common things you will encounter in business is the presentation of data. Too much data can be bewildering, but put into a graphical concept that people can quickly visualise and understand the basic message is a powerful way to explain your arguments and analysis. There are lots of different ways to present data, but make them useful!

Let’s look at some different types of graph that you might want to use.

A line graph is mainly used when you want to show values over a period of time. Here you put the relevant values onto a graph (mark with a cross or something similar) and join them together with straight lines to give a visual representation of how the values have changed over time.

For example, suppose the following gives the inflation rate over a 12-month period, where the rate is recorded each month (figures are in percentages):

A line graph would look something like the following:

You can see that there seems to be a general upward trend with a recent dip. This is a lot clearer to visualise the progress of the inflation rate rather than just looking at the collection of figures!

ASSESS YOURSELF

Draw a line graph of the following data, which shows your monthly profit (in thousands) over a 12-month period.

Then find or create your own set of data and be comfortable with drawing a relevant line graph corresponding to this data.

A bar chart is generally used when the data falls into various categories. They are particular useful when there is a lot of data, which would make little coherent sense to simply look at the raw data. For example, consider the votes cast in a general election – a list of every individual vote would be extremely tedious to look at and be relatively meaningless. Instead the data is categorised into the various political parties, and the total number of votes (or their percentage of the total vote) for each party is presented, to give a visual representation of the data.

Suppose you surveyed 200 of your customers on the quality of your products. The results were as follows:

To draw a bar chart, you simply label the horizontal axis with the categories, and then, for each category, draw a vertical bar to the appropriate height, as below:

A pie chart is used in similar circumstances. Here you have a circle and you divide the circle up into sectors, where the size of the sector represents the ‘share’ of the category overall.

I’ll show you how to calculate the sectors as it helps with the understanding to know how something works, though in practice you would probably just use software such as Excel! It’s easier to represent the numbers as percentages. There were 200 people surveyed so the fraction that fall into a particular category, say ‘Excellent’, is the value divided by 200 and then converted to a percentage – since a percentage is just a fraction out of 100, these percentages are obtained simply by halving the values (since if they are originally out of 200, halving them makes them out of 100). That means the percentages are:

A circle is made up of 360 degrees. Let’s take the 20% ‘Excellent’ category as an example. 20% corresponds to the fraction (20/100), so if we multiply this by 360 we will get the number of degrees we need for the ‘Excellent’ category This calculation is (20/100) × 360 which gives 72° (the small circle ° is the symbol for degrees). If drawing this manually you can use a protractor to do this, but fortunately Excel or other packages can do it for you if you learn how to do it! Whichever way you draw it, you should get something like the chart below:

These are some common ways to present data – of course there are many other ways that you can try.

One other thing to note is that data can sometimes be presented in such a way as to make the effect seem more or less significant depending on the viewpoint you want to get across. For example, recall our inflation rate example from before. Here I’ve plotted it with the scale on the y (vertical) axis going up to 100. As you can see, the pattern is now much less pronounced – it almost seems like a straight line, so would support an argument that the inflation rate is fairly consistent. Clever changing of axes like this can help emphasise the point you want to make!

Measures of location and dispersion

While this sounds a complicated topic, this simply refers to averages (measures of location) and spread (measures of dispersion).

When people talk about an average, they are usually referring to the mean. This is the value obtained by adding up all the data values and then dividing by the number of values. For example, consider the following data which shows the age (in years) of the ten staff employed by your company:

Then the mean (which most people would loosely refer to as the average age) is obtained by adding up the values to get 350 (check this yourself!) and dividing this by 10 (since there are 10 values here). This gives the mean age as 35 years old.

Sometimes the mean can be a little misleading, and it would make more sense to refer to the average as ‘the middle value’. For example, suppose the following data shows the annual salary of the employees (in thousands of pounds):

The mean here is 30 (check it!), which is a little misleading. The very large salary enjoyed by one person (presumably the owner/chairman) is skewing the value somewhat – apart from that person’s exceptionally high salary, everyone else has a salary less than this. If the company advertised for a new post, and said the average salary is £30k, is that really meaningful? After all, the starting salary is likely to be significantly lower than this when you consider all the other employees.

The median or ‘middle value’ would be more representative here – that means half of the employees earn more than the median, and half earn less than the median.

To calculate the median, firstly list the values in order. In our example, this is the list of values 6, 8, 9, 9, 11, 14, 15, 18, 20, and 190. If there were an odd number of values in this list, there would be a clear ‘middle value’, but when there are an even number of values there are two ‘middle values’, which are 11 and 14 here. The convention here is to take the median to be exactly halfway between these two values (you can obtain this by adding them together and dividing by 2 if you wish) which is 12.5. Hence the median salary is £12.5k which does seem more reasonable.

There are other averages you might encounter – the mode is the most commonly occurring value (which would be 9 in this example as it appears twice and everything else only appears once), and you might come across such things as the geometric mean or harmonic mean, but we won’t cover these here – if you do come across them, look them up!

As well as an average to measure the ‘central point’ of data, it is also often useful to know how ‘spread out’ the data is. For example, the sets of data 49, 49, 50, 51, 51 and 0, 25, 50, 75, 100 both have mean 50, but clearly the second set of data is much spread out than the first set of data.

You can give a very simplistic measure of the spread of a set of data by simply subtracting the smallest value from the largest, which is called the range. So in this example, the first set of data has range 2 and the second set of data has range 100. Clearly the larger the range the more spread out the data is. However, this measure does not take into account any of the values apart from the smallest and largest, so is not particularly representative of the data as a whole.

Far more commonly used are the variance and standard deviation. There are formulae that can be written for these, but I’ll just run you through what they mean and how to calculate them – in practice you would almost certainly use a calculator or software such as Excel to calculate them!

To calculate the variance, first of all work out the difference between each value and the mean, and then square each result. The squaring serves two purposes – first of all it makes everything non-negative (since squares can’t be negative), and secondly it adds greater weight the further something is away from the mean (since if the difference is large, its square is especially large).

The following table (using the same example as above with employees’ ages) shows this – remember that we calculated the mean to be 35, so Diff refers to the difference between the number and 35 (so subtract 35):

Now take the mean of these ‘differences squared’ – that is, add them up and divide by the number of values – you should get the answer 201.1, which is the variance. The standard deviation is just the square root of this, which is approximately 14.18.

Technically, these are referred to as the population variance and population standard deviation. The word ‘population’ refers to the fact that our data covers the whole set (in this case, all employees). It is actually more common for your data to be a sample of a wider population. The only difference here is that instead of dividing by n at the end of the variance calculation, where n is the number of values, we divide by n – 1 instead. These are known as the sample variance and sample standard deviation. So for example with the data above, the sum of the differences squared is still worked out the same (it’s 2011) but you divide by 9 instead (since there are n = 10 values, and we take n – 1) to give the sample variance as approximately 223.44 and then the square root gives the sample standard deviation as approximately 14.95.

Standard deviations are quite important. In data sets that follow what is called a normal distribution then you expect most values to lie within two or three standard deviations of the mean. This leads us into probability distributions which we won’t discuss here, but feel free to research and find out more!

For reference if you use Excel, the commands for the sample variance, population variance, sample standard deviation and population standard deviation are VAR( ), VARP( ), STDEV( ) and STDEVP( ) where the data is given in the brackets.

Correlation, inference and regression

Often when taking samples of data, you want to see if there is a connection, or correlation between two things. For example, suppose you conduct a survey and find out from a sample set of customers how much they spent in your shop, and their age. You can then plot these values on a graph, which might look something like this:

What you can notice from this graph is that it seems the older the person is, the more they spend in general. It’s by no means perfect, but there does appear to be some sort of correlation between them. You can actually work out a measure of how correlated they are – there are functions such as the Pearson correlation coefficient which give a number between –1 and 1; the closer to 1 it is, the more closely they are ‘positively correlated’ (i.e. as one goes up, the other does). A negative correlation means that when one thing goes up, the other goes down, and a value of 0 means there is no correlation at all.

Having identified that there is a correlation, the task is then to work out why. Are older people more likely to spend more money as they have more to spend in general than younger people? Or is it the other way round, and your products are aimed more towards the older generation? Working out which implies which is an example of inference – does one thing follow because of the other, or the other way round? Or are they connected due to some third factor?

Given a graph like above, you can calculate the ‘line of best fit’ that goes through the points – there are again theoretical ways to do this but it would be enough for you to draw it visually by approximating the best straight line, or by using a software package like Excel. For example, with the graph above, we might draw a line like below, which slopes upwards to indicate the positive correlation:

This is known as linear regression – there are other types of regression using more complicated curves to fit the data, again it is for you to find out more if you wish!

A very brief introduction to hypothesis testing

As a last topic, let me give you the basic principles of hypothesis testing. You could easily write an entire book on this, so all I’ll do is summarise the important points and leave it to you to find out more if you need to.

Quite often you want to make a claim, but you need to be confident in the claim you make. You can do some testing, but are the test results enough? Suppose you have made a decision to also offer clocks for sale, powered by batteries. You have been approached by another supplier who say that they can provide batteries that last longer, at the same cost to you. They send you a sample of batteries to test. Is there enough convincing evidence from the test results to switch to the new supplier? That is quite a bold business decision, so you need to be really sure that they are better.

There are two options here – should you stay with your current supplier as you don’t have the evidence to switch, or are you convinced enough to make the switch? The null hypothesis is that there is not enough evidence, that is, stay as you are. The alternative hypothesis is that there is enough evidence, so you should change supplier. These are usually denoted by H₀ and H₁ respectively, so we have:

H₀ : There is no difference between the current batteries and the new ones.

H₁ : The new batteries are better than the old ones.

We need strong evidence (say with 95% or 99% certainty) to reject the null hypothesis and accept the alternative hypothesis – otherwise we do not reject the null hypothesis.

Two things that could go wrong in your decision. A Type I error is when you reject the null hypothesis when in fact it was true (so in this case, you switch supplier but the new batteries are no better). A Type II error is when you do not accept the alternative hypothesis when it was in fact true (so in this case, you don’t switch when you should actually have done). A good analogy is the British legal system of ‘innocent until proven guilty’. The null hypothesis is ‘the person is innocent’ and the alternative hypothesis is ‘the person is guilty’. A Type I error would be finding them guilty when they were actually innocent – this is probably considered more serious (as you are sending an innocent person to jail) than a Type II error, where you declare them innocent when in fact they were guilty (so the criminal walks free).

Having set up your hypothesis, you can then use statistical tables to perform the test. For example, your current batteries last for 6 months on average, and the prospective new supplier claim that their batteries last for more than 6 months on average. So your null hypothesis is that the new batteries last 6 months on average (no difference) and your alternative hypothesis is that their average is greater than 6 months. There are various test statistics that you can use to test with, say, 95% confidence, or 99% confidence. These are looked up on a table – broadly, if your calculated value is greater than the looked-up value, you accept the alternative hypothesis.

That’s as far as I want to take this here, as to go into any more detail would take this beyond an ‘express’ read, but I wanted you to see the ideas– as always, if you need to know more, find the resources to help you. Any good statistics book will cover these concepts (and more!)

Conclusion

Maths is all around you in business. This guide only gives an indication of some of the things you might encounter. You will encounter more – you might come across probability and probability distributions for example, or more advanced mathematical topics such as calculus. But work on your maths and it will become second nature and you will be so much more confident in your daily life.

Finally, I cannot stress enough the need to practise the mathematics.