Newsletter - September '08
|
|
Spreadsheet Tips
|
|
Meet the Client
|
|
CountIF - Sales by Supplier.
|
|
Richard Fitzpatrick, of independent mortgage advisers Chase de Vere.
|
|
|
 |
Meet the Client - Richard Fitzpatrick, Chase de Vere.
|
|
|
This month's featured client is Richard Fitzpatrick of
mortgage brokers, Chase de Vere. Based in Leamington Spa,
Richard runs a team of mortgage advisers, and has some advice
for us all in these interesting times.
|
|
|
|
It has been a remarkable few weeks in the
finance world. Lehman Brothers went into administration, AIG
(the world's biggest insurers) were nationalised, Merrill
Lynch were forced into the arms of a safer bank and HBOS was
narrowly saved from a Northern Rock-like run. A year ago,
nobody would have believed it. But these four major events all
happened within a week.
|
|
|
|
The banks are really closing ranks. LIBOR, the rate at which banks lend to
each other, has started to rise. Mortgages are being repriced daily
and it is not unreasonable to expect rates to rise accordingly.
|
|
|
|
The same as ever‚ to provide my clients with unbiased expert
independent advice. We receive many of our enquiries from the
publicity we receive in the national press because we are
constantly quoted in the country's leading personal finance
sections as a source of expertise and advice on the mortgage and
property markets. However, our biggest source of business - and best
measure of our success - is the wealth of referrals from existing,
satisfied customers who recommend our service to their friends,
family and business associates. For every one customer that comes
to us we typically receive a further four referrals as they recommend
our services to others in need of mortgage advice.
|
|
|
|
In this ever changing financial climate it has never been more important
to keep in touch with my clients. That is why the work that Working
Data have done to organise and structure my client data has been
invaluable. It allows me to know exactly what my clients needs
are and contact them at the perfect time to ensure they get the
very best mortgage scheme when they need it.
|
|
|
Contact Richard by email or
by telephone: 07941 190220
|
|
|
Spreadsheet Tips - Sales by Supplier using Countif
|
There is no doubt that having up to date, accurate and relevant information
on your customers is a very useful business tool. In this month's
Spreadsheet Tips, we look at how we can quickly pull out some key
figures from a sales list, using the CountIF function. Knowing where you are
always helps you make decisions about what to do next, to get where you
want to go.
|
|
|
|
In our perfume boutique, Scented D'ata, the manager is compiling the
monthly sales information. It is also time to re-order, but how many,
exactly, sold last month? With this figure, will he be able to secure
a discount from his supplier? The sales list is shown in Fig 1.
|
|
|
|
 |
|
|
In cell F5, we write the CountIF formula. CountIF requires two pieces of
information; where to look and what it is looking for. Where to look is
called the 'Range' and what to look for is the 'Criteria'. The Range is the
cells in the spreadsheet that you want to look in, in our example it is
cells E10 to E14, which we write E10:E14. You can click and select on the cells
instead of typing in the cell addresses. Excel also brings up a little help box when
you type in the CountIF function to remind you what information it requires. See Fig 2.
|
|
|
|
 |
|
|
We now put dollar signs next to each part of the cell references. E10:E14 becomes
$E$10:$E$14. This locks the position of the range we want to look up. If we now drag
the formula down the column, the 'Range' will not alter.
Next we type a comma. This tells the function that we have finished giving it the
'Range' and want to move on to the 'Criteria'. The criteria is the name of the supplier.
We could type in the supplier's name here, or put in the cell reference of a cell which
already contains the supplier's name, i.e. E5. See Fig 3.
|
|
|
|
 |
|
|
This gives the answer 4. We can do a quick visual check on the sales list in our
example and check that this is correct. In a real environment, you have just saved yourself
an hour or more. See Fig 4.
|
|
|
|
 |
|
|
Because we used the cell reference, E5, rather than typing in the supplier's name into
the CountIF function, we can now drag cell F5 down to F6 and it will count the number
of sales against products supplied by our other supplier, Feline Memories.
The Range will remain the same because we used the dollar signs to lock it, and the
criteria will move down one row, from E5 (Wagon Trail Perfumery) to E6 (Feline Memories).
See Fig 5.
|
|
|
|
 |
|
|
We drag down and the formula calculates one sale. You have just saved yourself another hour,
at least. See Fig 6.
|
|
|
|
 |
|
|
There are many time saving, business enhancing tools available in the average PC
nowadays. Working Data can help you unlock them. Call us on 01926 335 772 and
we will talk it over with you.
|
|
|
|
|
|
back to top |
|
|
|
|
