Posts Tagged ‘spreadsheet’

2013 Staff holiday planner spreadsheet

Friday, November 18th, 2011

At the time of writing it nearing the end of 2011 so some of you may be searching for my previous staff holiday planner for 2012. If you are planning very far ahead (or if you are reading this in 2012/2013) you might want my 2013 staff planner. This is my latest free spreadsheet that can help you track staff holidays, training, sick days and offsite days.

2013 staff holiday planner spreadsheet

It comes configured with formulas that can automatically track the total number of holiday days remaining (columns C&D) for each employee. If you don’t want to use this simply delete those columns.

The default formula subtracts 1 or 0.5 from the remaining holiday if the employee cells contain the words ‘holiday’ or ‘half. You can copy and paste (ctrl-c, ctrl-v) the coloured tags (holiday, half, training, etc) from the top left into the staff planning cells and the totals are automatically updated.

It is easy to customise the formulas if you want to factor in sick days for example. See the 2012 planner for more example formulas. Note that the 2013 calendar formulas count the entire row (which includes the last bit of 2012 and the first bit of 2014), whereas the 2012 calendar formulas just count the 2012 year. You can adjust the formulas to suit your requirements.

As always the week numbers are based on ISO 8601, and the top and left parts of the staff planner are frozen so you can always see them – this makes navigation easy. Here is the download link:

Download 2013 staff holiday planner spreadsheet – 23kb

As the staff holiday planner has over 365 columns you won’t see the whole year if you open it with an ancient spreadsheet package such as Excel 97. You’ll need to use a more modern spreadsheet package such as Excel 2007 or OpenOffice (which is free). One workaround if you are forced to use Excel 97 at work is for you to use OpenOffice at home to split the spreadsheet into 2, one part for 2013 H1, and one for 2013 H2.

Three point estimation spreadsheet

Saturday, February 12th, 2011

If you are trying to make a three point estimation spreadsheet for Excel but are struggling with the formulas, then this free Excel download that I’ve made may help you.

three point estimation person spreadsheet

It has fields set up for entering data such as the task name, person responsible, and then the best, worst, and most likely estimates. You can also enter a contingency value as a letter. One of the spreadsheet tabs lets you set what percentage of contingency applies for each letter.

three point estimation contingency

If you like your estimates to be based on the most likely plus the contingency, you’ll see this automatically calculated for you in the first yellow columns (you may have to copy the formulas down as far as you need them).

If you prefer your estimate to be based on E/SD/Confidence values you’ll find them to the right of the sheet. Also available are the average and standard deviation (of the best/worst/most likely). The second yellow column is the 98% confidence plus contingency value.

three point estimation confidence values

Finally if you want to see a quick summary of how much work is allocated to each person there is a tab which gives you this information.

three point estimation person view

This spreadsheet is by no means ready for immediate use, you’ll probably want to customise it, remove the columns that you don’t need, and verify that the formulas do what you want and are correct. But at least it may help you produce the three point estimation spreadsheet you want more quickly.

Download the three point estimation spreadsheet – Excel XLS (16kb zipped)

It has been tested on Microsoft Excel 2007 and is a free 16kb download.

Present and gift logging spreadsheet

Monday, March 15th, 2010

Sometime it can be hard to think of what presents to give for birthday, Christmas, Valentines or anniversaries. Having a record of what presents you have given before can help to provide inspiration, and can help you avoid giving the same present twice.

present or gift logging spreadsheet

I’ve made a simple spreadsheet for logging gifts and presents. I’ve put some example data in there so you can see how to use it.

The rows can easily be filtered by the person’s name, or the event, by using the auto filter options in the header row. I’ve added some example auto-colouring which colours certain events (birthday, Christmas, and Valentines) to make them stand out.

The gift logging spreadsheet can be downloaded with this link – it is only 5kb zipped up.

Managing holiday money with an Excel burn down chart

Tuesday, March 2nd, 2010

If you go on a short holiday then it should be pretty easy to manage your holiday money. It isn’t too hard to track how quickly your foreign currency is running out when you are only abroad for a week or two.

If you are going on holiday for a number of months then it becomes a bit harder. When I recently went on holiday for three months I thought being able to record my available currency in the form of a burn down chart would be a useful thing to do.

I ended up creating an excel spreadsheet which when filled in gave me three lines on a graph.

holiday money burndown chart

  1. The first straight line (pink) goes down from the total amount of foreign currency on the first day of my holiday to 0 on the last day of my currency. This line shows where my currency reserves should be on average each day in order to spend all the money I took with me.
  2. The second line (blue) is the actual amount of foreign currency that I have left. A few times a week I would count up how much money I had left, and enter the data into the Excel spreadsheet. This would then update the second line. If this line is above the straight line then I’m under spending (on average), if the line is above the straight line then I’m over spending. The concept of under or over spending is of course ‘on average’. There may be good reasons why your spending during your holiday might be unevenly spread.
  3. The third line (yellow) is plotted against the axis on the right of the chart. This shows how much currency I have left per day on average.

Using the holiday currency burn down spread sheet

To make it easier to see how to use the spread sheet I have put a load of example data in the yellow cells. When you understand it you will have to remove the example data and enter your real data. Don’t delete anything in the red cells as these cells contain the formulas which will make it all work.

holiday money burn down spreadsheet

I have put comments in cells A2, B2, H2, I2 and I3 explaining what you need to put into these cells to get the spread sheet setup for your own holiday.

Then all you need to do is update it every few days and you’ll be able to see how fast your holiday money is burning down.

Download the holiday money spreadsheet from here (12kb).

Telephone call log spreadsheet

Monday, July 6th, 2009

It can be well worth keeping a log of phone calls to companies such as banks, utility companies and anyone else who you have an account with. You never know when it could be useful to have this information.

For example this week I received some unsolicited credit card cheques from Natwest. I have previously been sent these by Natwest and I remember that I phoned them up to ask them not to send them to me anymore. I know I have called them at least once and I suspect that I have called them twice. As they are clearly not honouring my preferences I am writing a letter of complaint to them.

Unfortunately I don’t know the dates (or even whether I made one or two) of my calls to them. I can still write the letter, but if I had recorded my calls to them I could have been very specific about details of when I told them not to send me any more of their credit card cheques.

Here is the call logging XLS spreadsheet that I’ve put together to record future calls.

Telephone call log spreadsheet – 17kb