e-skills UK Guide

Tracking financial data

Financial data

Whether you are selling online poetry in Norfolk or cuddly toys in Somerset having the skills and means to manage the day to day numbers and financial data is vital. You do not necessarily need to invest in expensive software packages to run your accounts, and many of the techniques and ideas in this guide can be completed using a spreadsheet.

By efficiently tracking your financial data you will be able to save money and avoid legal and financial problems. If you already have a PC loaded with a spreadsheet application then you are fully equipped to manage most small business finances, so there is no other cost involved.

Please note that this guide has been written from the perspective of small business IT. We have incorporated a discussion on the key financial data you will need to pay attention to, but we strongly suggest that you take qualified, professional advice on the detail of what you need to track and when. This is especially important when considering tax, VAT and other legally binding obligations all small businesses face.

What data is important?

Any data that will prevent your business from going to the wall or acting inappropriately needs to be collected and analysed. Running a small business is all about making informed decisions quickly, based on real and up to date data. If your sales data is wrong then you will not be able to project your cash flow very accurately. The old adage of “rubbish in, rubbish out” is just as applicable in these hi-tech days as it has ever been.

Most businesses need to track:

• Profitability of the business
• Value of sales made
• Value of outstanding debts
• How much the business has spent
• How much the business owes to its suppliers
• How much the business is borrowing
• How much is in the bank (and in each account if you use current and high interest accounts)
• Major cash commitments; for example, you might need to service or renew equipment which will result in a large, unavoidable invoice some time in the future.

Cash is still king

We cannot overstate the importance of tracking cash coming into and going out from your business (known as cash flow). This is for several reasons:

1. If you run out of cash, you can no longer pay staff or your suppliers (unless you can raise a loan). Your business might be successful and have excellent prospects of success but if the money runs out you might have to close it down. Keeping track of cash flow is particularly important in the early stages of a business when you are likely to spend money far more quickly than cash is coming in.

2. You might have overcome the initial hurdle of getting a business going, but will you be able to react quickly to changes in circumstances? A good understanding of cash flow will help you manage critical situations like:

o Loss of a major customer
o Winning an exceptional new chunk of business
o Failure of a customer who owes money 

If your business is generating cash you will want to pay off loans and use bank accounts effectively. Knowing when to move cash between accounts can contribute significant amounts of money to your business. It can also help you avoid penalties you might incur for having to take cash out of high interest accounts without sufficient notice.

We talk more about online banking and cash management in this guide.

Your business and financial data

This section requires a little more thought. This is because you are in the best position to decide which key numbers you need to track to understand how your business is performing.

Having said that, this project will also provide some ideas relevant to different types of business that might help you get you started.

You might also ask opinions from people that deal with business like yours; ideally, both successful and failed ones (failure can be a great education in business). For example, consider taking advice from:

• Banks
• Accountants
• Trade organisations
• Your local chamber of commerce
• Relevant books and magazines
• Consultancies
In some industries, you might even be able to get good advice from competitors.

If you want to know, don’t be afraid to ask. It might be your livelihood on the line.

Typically a people business (one that earns money from charging for people’s time) will want to monitor the value of work in progress (often abbreviated to WIP) in addition to the basics. WIP is the value of work done that you haven’t invoiced yet. Often the first thing you know about a problem with a customer or client is when there’s a problem getting an invoice paid. If you are building a large amount of WIP you might be in difficulty without knowing it. Good cash flow management means keeping WIP to a minimum.

Typically a sales and distribution business (one that buys products and sells them on – possibly in an aggregated or repackaged form – for a profit) will want to monitor the following in addition to the basics:

• Stock value. The value of stock held by the business that hasn’t been sold yet. High stock levels might be necessary in order to respond quickly to customer needs (especially if you stock a wide range of products that need to be provided to customers quickly or they will go somewhere else). However, high stock levels can also represent a major investment in cash with an impact on cash flow and profitability (if you cannot sell it for a decent profit). Monitoring stock levels for different products can also reveal overstocking of that product that might lead you to conduct a promotion while there is still a market for it.

• Ratio of price to cost. The amount you charge for a product divided by the cost of its components. This is a rough and ready indicator of profitability. This figure might also show up an error in pricing.

• Value of returns. A high level might indicate a flaw in the products or that you are mis-selling in some way.

• Credit limits. If you deal with a large number of customers it can be difficult to keep track of how much each one owes you. Most companies therefore assign credit limits for each customer and investigate any new sale that breaches the credit limit to decide whether or not to accept the order.

Typically a manufacturing business will track the same numbers as a sales and distribution business with the added complication of tracking:

• Manufacturing costs
• Sub-contract costs
• Manufacturing consumable costs.

How often should you review the numbers?

You will need to decide how often you need to review the numbers in your business. Initially you should review some of the figures on a frequent basis – maybe daily but certainly weekly. As your business develops and stabilises, you will not need to review them as often (while avoiding complacency).

Profits, profits, profits

Simple businesses can review profitability by comparing the amount they spent in a period to the amount of income they generated. However, most businesses need to do more work to come up with an accurate profitability figure. This is because:

• There can be many factors that influence a profitability calculation. You might need to bring several numbers together to calculate profitability.

• Some figures might need to be spread. For example, if you buy an expensive piece of equipment, it is reasonable to spread the cost over the period you might reasonably expect to use it. This is called depreciation. For example, if you buy a laptop computer for £1,200 and you expect to replace it in two years time, it is reasonable to show a cost of £50 per month. An accountant will let you know how to depreciate various pieces of equipment.

• If you are working on a project that is costing you, say, £2,000 per month and you expect to recover £16,000 after 4 month’s work, it might be reasonable to record £4,000 per month as revenue each month. This is known as an accrual.

• Accruals are also used for recurring payments. For example, a quarterly rent paid in arrears could be spread back into three equal month’s rent.

• You might need to forecast some income. For example, if you receive a quarterly electricity bill you might want to forecast the value of the next bill and spread that figure back in three equal amounts.

• You need to determine profitability over a given period of time. If you calculate the profitability of your business since you first started, you could miss a sharp downturn until it’s too late to do anything about it. Typically (but not always), the period of review and the frequency of review are the same.

Determining profitability can therefore be a time-consuming exercise even if you use sophisticated software.

The statutory requirement is for you to monitor profitability once a year. But that’s far too long a period for most businesses. You will need to decide what period works best for you. As a rule of thumb, most businesses monitor profitability monthly for the previous month.

Aged figures

This is a useful technique for reviewing figures that change in importance over time. For example, a debt that is 5 months old is far less likely to be recoverable than one that is just a few days old.
Stock is another example. An item that has remained unsold for 5 months is likely to be more difficult to sell than one that you only just bought. That’s particularly true if the stock has a shelf life. Batteries degrade over time, for example, so there will come a point when they are impossible to sell.

Measuring success

When you start a business you will probably have to produce a business plan and monitor progress against it as a condition of getting a bank loan.

Even if you don’t, producing a business plan and monitoring progress against it is usually good practice since it provides another way to measure how well your business is doing.

When planning, typical businesses produce:

• Budgets – forecasts made at the beginning of a budget period (usually a year) that don’t change throughout the period.
• Forecasts – forecasts that can change as the budget period develops.
For example, when constructing a business plan, you might set a budget of, say, £200,000 for sales in the year. You might then construct a sales pipeline that shows:
• Prospective sales and estimated sales values
• Actual sales and actual/estimated sales values
• Completed deliveries and actual sales values

This will show you how well you are doing against budget and whether it looks like the budget is achievable, or not.

Using a spreadsheet to track financial data

Spreadsheets are probably one of the most important tools available to you to help manage your business finances. The good news is that you do not have to build a complicated spreadsheet to run a small business. In fact the more simple the spreadsheet the less chances there will be errors and the easier it will be to maintain.

To track sales and billing you could create a spreadsheet with the following column names;

● Column 1 = Reference. This refers to your invoice number
● Column 2 = Paid?. This can have a simple red/green colour code to indicate if an invoice has been paid. When an invoice is paid you simply turn the cell green and enter the date of payment
● Column 3 = Date. This is the date the invoice was raised
● Column 4 = Customer name
● Column 5 = Item sold. A brief description of the product or service supplied
● Column 6 = Goods. This is the total value of the goods including VAT
● Column 7 = Goods VAT. This is the VAT you are charging (if applicable). To calculate the VAT amount from a gross value divide by 117.5 and then multiply by 17.5
● Column 8 = Goods ex-VAT.
● Column 9 = A running total of invoices for the month 

To track expenses or purchases you could create a spreadsheet with the following columns;

● Column 1 = Date. The date of the purchase
● Column 2 = Supplier details
● Column 3 = Notes. These explains in more detail, where needed, about the purchase
● Column 4 = Cheque number if paying by cheque
● Column 5 onwards = Listing of typical expenditure items such as heating, lighting, general expenses, travel, marketing

Note that some expenses attract VAT and therefore can be used to off set your quarterly VAT bill, if applicable. Take advice from an accountant on what expenses are in this category. You can then use this spreadsheet to help calculate your quarterly bill.

To track cash flow the spreadsheet will probably have the following columns;

● Column 1 = a list of the following: income, cash sales, capital or loans, other income. These would then be totalled going across the page in monthly columns. It will then list the expenditure such as materials, wages, insurances, postage, rent and rates. These would then be totalled in a similar monthly way to the income finance data.

● Column 2 onwards = Monthly forecast and actual cash status. Each month will have a forecast and actual cash amount with the end of month actual being carried across to the next month. This way you can track month by month the status of your business cash flow.

Profit and loss accounts can be created from these two spreadsheets that will give you a month by month picture of the performance of your business and, hopefully, a final monthly profit number for you to sit back and enjoy.

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.