e-skills UK Guide

Using a spreadsheet

Spreadsheet software

A spreadsheet is one of the most useful pieces of software for a small business. It comprises an electronic grid that can be populated with mainly numerical data, which in turn can be manipulated and worked with to produce useful insights into a small business. 

The term spreadsheet is thought to come from bookkeeping ledgers which had columns of data used to calculate sales and expenditure. These were in turn spread out and then analysed – hence spreadsheet.

Microsoft Excel is a popular spreadsheet application, part of the Microsoft Office suite which includes, amongst other tools, Word, PowerPoint and Access the database product.

Another popular spreadsheet is Lotus 123.

Google, the famous search engine, also has an application suite that contains a spreadsheet.

For a small business managing the numbers is a vital part of staying in business. In reality most if not all of your basic book keeping could be done on a spreadsheet if you are only dealing with a small number of clients. As business grows you could then migrate to an accounts system.

Other guides that you may wish to review include:

Tracking financial data

Small business accounts systems

Payroll management

Online tax payment

Online banking

Common uses of a spreadsheet in a small business could be: 

• Building a cash flow model.
• Expenses tracking.
• Incoming sales recording.
• Quarterly VAT calculations (if appropriate).
• Product pricing.
• Business planning.
• Client time recording or expenditure.

The flexible nature of a spreadsheet enables you to build complex “what if” analysis of your business, a key asset in your planning armoury.

The cost of building a spreadsheet is really down to your time. For many businesses it is a very worthwhile investment as it provides you with a useful tool and can be used by professionals such as accountants to help you with your business – again saving time and money.

Using a spreadsheet to manage the data

A good way to think about a spreadsheet for your business is to think of it as a great way of organising your numerical data. Data sheets that contain product information are a useful way of organising the written word, and a spreadsheet can do more or less the same for your numerical data.

One of the key abilities of spreadsheets is to link a lot of numerical data together to produce outputs. For example imagine that your small business was manufacturing very specialist designer shoes. To effectively cost your final product you will need to think through that basic elements of the final product.

The calculation may look something like this:

• Cost of raw materials + cost of design + cost of manufacturing + cost of marketing + cost of overheads.
• Divided by the number of shoes you make.
• Equals the cost per unit.

Ok, we accept this is a very simple example and we are not experts in shoe manufacture but I am sure you understand the principles!

At this point if you enter values for each cost above into a calculator you end up with a total cost that can be divided by the number of shoes.

Now imagine if your raw materials suddenly increased by 27% due to a shortage of quality leather. To recalculate your cost per unit you would need to undertake the entire calculation again.

With a spreadsheet you would create a simple sheet that may look something like this:

Cost of raw materials	£1,000.00
Cost of design	£   250.00
Cost of manufacturing	£   300.00
Cost of marketing	£   900.00
Cost of overheads	£   700.00
Total Costs	£3,150.00
Number of shoes	10,000
Cost per Shoe	£      0.32

If you needed to increase the cost of your raw materials simple increase that value and as the other figures are part of a calculation you will immediately see the new costs. This is the wonderful thing about spreadsheets – the ability to very quickly plan and explore your numbers.

Limitations of using spreadsheets

Whilst you may agree that spreadsheets are very useful in a small business, they are not perfect.

The upfront time in building a complex spreadsheet can be significant. Your reward will be the saving of time later – something only you can decide how valuable this is.

It is very easy to get carried away and build huge interrelated spreadsheets that calculate lots of figures to come up with your answer. The problem is, if something goes wrong with the spreadsheet or you enter a wrong calculation, this bad result could cascade through your spreadsheet and give you a wrong answer. Worse still, you may not realise the answer is wrong until it is too late, so do be careful.

Small businesses have been known to fail as the plans they submitted were wrong and no one realised it.

A good suggestion is to document or annotate your spreadsheet so you understand how the figures are derived and you can pass the spreadsheet to someone else for checking.

Commercial suppliers

We do not recommend specific products or suppliers; instead we provide you with a representative sample which covers the range of suppliers/products available. You may choose to look at these suppliers or products but this is entirely at your discretion.