Newsletter - November '08
|
|
Spreadsheet Tips
|
|
Meet the Client
|
|
Year End inventory calculations using SUMIF
.
|
|
Lesley Long, of unique event management company, Women Go Wild Outdoors.
|
|
|
 |
Meet the Client -
Lesley Long, Women Go Wild Outdoors.
|
|
|
This month's featured client is Lesley Long, founder and director
of event management firm,
Women Go Wild Outdoors. Recently launched, Lesley tells us
what it's all about.
|
|
Women Go Wild Outdoors. Discuss...
|
|
|
I set up
Women Go Wild Outdoors to offer event management with a difference.
It offers women a unique combination;
a great way to have fun and enjoy the best of relaxation.
The programme of achievable adventures and adventure breaks
we have developed, enables women of all ages, shapes, sizes
and levels of fitness to have
outdoor fun, and quality time in their increasingly busy lives.
|
|
Who are you aiming this service at?
|
|
|
I see our programmes as a stimulating alternative to the usual format
of women's networking events, and by extension of that, as team building
events for work colleagues. Of course, outside of the business arena,
it is perfect for groups of all kinds celebrating special occasions.
|
|
How have Working Data helped?
|
|
|
I asked Working Data to
build a bespoke database to help the company with its marketing
and event planning after it became clear that there was no
suitable off the shelf software available. The fact that
Working Data are developing the database
in phases is a huge advantage. It gives us time to input
and verify the data and also check that things are working
just as we want before moving on to the next stage. As a
young, growing company we value such flexibility on the part of
our suppliers and really feel that we are working in
partnership with Working Data on this project.
|
|
How do I find out more?
|
|
|
There are some photos from our recent Press Day on the
News page, along with lots of other stuff on the
site. Please feel free to contact me directly by
email or
by telephone on 0845 094 8486.
|
|
|
|
Spreadsheet Tips - Year End
Inventory Calculations using SUMIF
|
Keeping check on inventory; how much has gone out, how much has come in, what value goes
down on my tax return? At this time of year, it is a question that your accountant will
want the answer to. In this month's spreadsheet tip, we show you how to get this figure
quickly by combining arithmetics with the SUMIF function.
|
|
Calculating an inventory value using SUMIF
|
|
|
Now the big day has passed, the stock controller at Bewitchin' Data wants to
get an idea of inventory levels. She has last year' results along with this
year's sales and receipts, as in Fig 1.
|
|
Fig 1 |
|
 |
|
|
The total stock value last year was £5.40, the figure in bold. In cell C28, we
use the SUMIF function to determine the total number of Hats sold. SUMIF requires three
pieces of information. The first is RANGE, which lets it know where we are looking. The second
is CRITERIA, which tells it what we are looking for. The third is SUM_RANGE, which tells it
what it should be adding up. In our example, the RANGE is A13:A24, the list of products in the
sales table. The CRITERIA is A28, which is the product. The SUM_RANGE is B13:B24 which is the quantity
sold each time. In cell C28 we write (See Fig 2):
|
|
=SUMIF($A$13:$A$24,A28,$B$13:$B$24)
|
|
|
We put dollar signs next to each part of the cell references in the RANGE and SUM_RANGE
as this locks the positions of these ranges within the formula. It allows us to drag the
formula down into the cells below without affecting these ranges.
|
|
Fig 2 |
|
 |
|
|
Hitting return will calculate the function for us, giving 23. We can now drag this
formula down into the two cells below and it will automatically calculate the total
sales for Brooms and Wands, respectively 23 and 21. See Fig 3.
|
|
Fig 3 |
|
 |
|
|
We repeat this process to calculate the total receipts. We enter the formula
|
|
=SUMIF($D$13:$D$23,A28,$E$13:$E$23)
|
|
|
into cell D28 and copy it down into rows 29 and 30, giving 65 Hats, 60 Brooms and 45 Wands received.
See Fig 4.
|
|
Fig 4 |
|
 |
|
|
To calculate the finishing stock level for each product we take the starting inventory,
subtract sales and add receipts, see Fig 5.
|
|
Fig 5 |
|
 |
|
|
And finally, to calculate the stock value for each product, we multiply the cost
with the finishing stock, and then sum these for the grand total as in Fig 6.
|
|
Fig 6 |
|
 |
|
|
You would have to agree that this is a very quick way to calculate, and substantiate,
your final stock levels and values. It will also give you a concrete starting point if
you want to do a stock check.
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 be glad to talk it over with you.
|
|
|
|
|
|
back to top |
|
|
|
|
