Newsletter, Feb. '08

Spreadsheet Tips		Meet the Client

Compiling data from a long list using the SUMIF function. How many of each product did I sell and for how much?		Meet Working Data Client, Phil Keane, Independent Financial Adviser. As an appointed representative of the Lighthouse Temple Ltd, Phil dispenses invaluable financial advice across Warwickshire and the West Midlands.


Meet the Client - Phil Keane, Lighthouse Temple

		Phil Keane is an Independent Financial Adviser based in Coventry covering Warwickshire and The West Midlands. He is an appointed representative of Lighthouse Temple Ltd, a National Independent Financial Advisory Firm.

"The majority of my work is focussed on pensions and investments however I do also advise on all aspects of what are termed protection contracts i.e. life insurance, critical illness cover, income protection, private medical insurance etc. if it is appropriate for my clients. I feel very strongly that it is important to offer independent advice. I work for my clients, not any Product Providers, to source contracts which are most suitable for their needs and objectives"

Working Data designed and built a database for Phil which enabled him to track his clients' policies and schedule review meetings at appropriate anniversaries. "Servicing is a very important aspect of my work. Clients have told me that they value the regular contact as it gives them the peace of mind that someone they trust is looking after their finances - it gives them one less thing to worry about. It is vital that contracts are reviewed on a regular basis in order to ensure they remain appropriate. The database developed by Working Data affords me the confidence that each and every one of my clients receives a very high level of ongoing service"

What's new?

Recently, Phil introduced a fully independent Pension Review Service and has now expanded this to include an Investment Review Service - the aim being to inform and educate people about their existing arrangements. "It's a sad fact that the majority of people have no idea where their pension funds are invested and how well or how badly those investments are performing. In general people have no point of reference and therefore no means of comparison to determine whether their particular arrangement is actually any good! Additionally, pension statements tend to be complicated and in today's busy world, people don't have the time to fully understand them. Essentially what I'm trying to do is raise peoples awareness and ensure that their pensions and investment contracts provide good value and are appropriate for their needs."

Part of the review involves a detailed, though quick and simple to complete, psychometric test which provides an analysis of each client's individual attitude to risk. "For example, people accumulate their pension fund over a period of years, often in several different contracts as they change jobs. In combination these can amount to a significant sum of money. However because people often have little idea as to where their money is invested, they have even less idea as to what level of risk they are taking. Whilst it may be appropriate to be more adventurous at a younger age, as you near retirement it is equally important to err on the side of caution in order to avoid the downside risk of adverse markets. The Psychometric analysis allows me to give each client a clear understanding of their own attitude to risk and then compare this with the risk they are taking with their pensions and investments. People are generally surprised and occasionally shocked when they realise how much risk they are taking and often this means making significant modifications to existing contracts. Frequently it can be beneficial to consolidate existing contracts in order to benefit from a lower charging structure and generate a fully diversified investment portfolio. However, the overall objective always remains to ensure the client is better positioned financially for life after work."

The Service is available free of charge. "The only cost is an hour or so of your time - not much for the peace of mind it provides and the insight it offers!"

Contact Phil by email or telephone 02476 333 588.

back to top


Spreadsheet Tips - Compiling data with the SUMIF function

Faced with a long sales ledger with a month's worth of sales, it can be daunting to find a way to compile the data and get some meaningful feedback. In this month's spreadsheet tips we look at the SUMIF function which is used to add things together depending on the criteria we present.

In our flower shop, Blooming Data, the manager is looking over the sales ledger. He wants to see how many roses he's sold over the period so that he can have a good idea of what to re-order from the supplier. He is also interested in finding out the total sales for roses and how much he sells them for on average as he has different price levels by quantity, see Figure 1.

Fig 1

In Figure 1, we see the entries in the sales ledger for February. There is also some price break information for roses which explains the different unit price rates in the ledger. How many roses have we sold in total, for how much? What was the average price? And between which two dates?

First, we start by determining the date range we will be looking at. To do this we use the min() and max() functions to pull out the oldest and newest dates from the sales ledger. The dates are in column B, and more precisely in cells B13 to B19, written B13:B19. In cell H12 we write the equation; =min(B13:B19) and in cell H13, we write =max(B13:B19). See Figure 2.

Fig 2.


Now we know the date range of the entries in the sales ledger. What was the total number of roses sold between the two dates? We could go through the ledger one line at a time, check if it's for roses, make a note of the quantity on a piece of paper then add it all together at the end. Or, we could save a bunch of time and use the SUMIF function.

The SUMIF function has three elements. Where we are searching, what we are searching for and the value we want. In this instance, we are searching in cells A13:A19, for 'Rose', and we want the values in C13:C19 (the quantity sold). The formula takes this form, =SUMIF(A13:A19,"Rose",C13:C19) This tells Excel to search through cells A13 to A19 for the word 'Rose'. It takes the relevant values from column C and adds them together to give you a single figure for all the rose sales in the ledger. See Fig 3.

Fig 3.


We can also find the total sales value for all the rose sales using the SUMIF function. In cell H16 we write =SUMIF(A13:A19, "Rose", E13:E19). It is almost the same as the previous formula, except this time we want the formula to add up the values in column E, the Total sales price for each transaction. See figure 4.

Fig 4.


Finally, it would probably be interesting to know what the average sales price for roses is. For this we divide the total value of all the sales, cell H16, by the total number of roses sold, H15. We write in cell H17, =H16/H15 See Fig 5.

Fig 5.


With the SUMIF function we can get totals and results from long lists of information, such as a sales ledger, in seconds. If you have any questions about SUMIF functions and when to use them, or you have a topic you wish us to cover in a future newsletter, please contact us.

back to top