Posts Tagged ‘Excel’

2012 One Page Excel Calendar

Wednesday, December 22nd, 2010

At the time I write this 2010 is nearly over, which means that 2012 is just over a year away. If you are planning far in advance then you might appreciate this free downloadable 2012 one page Excel calendar. If 2012 is too far ahead then I have a 2011 one page excel calendar available for download as well.

2012 excel calendar

Just like in previous years this calendar features the whole year on a single page. It should fit on your computer screen, but you might need to adjust the zoom depending on your default settings.

There are two versions, one is plain, and the other has the UK bank holidays marked on it.

2012 one page Excel calendar – blank
2012 one page Excel calendar – with UK bank holidays marked

Both version of the calendar are free to download, and should work on Excel 97 and above, or on OpenOffice (which you can download for free).

2011 staff holiday planner

Friday, November 12th, 2010

I had a request to create an Excel staff holiday planner in addition to my usual one page calendars that you’ll find on this site. The Excel staff holiday calendar for 2011 can be used to note down holidays, training days, sick days, and home/off-site time at your company. Here’s an example of what it looks like with some information filled in.

2011 staff holiday planner example

Is has the whole of 2011 going left to right, and staff members can be listed from top to bottom. The weekends and months are coloured, and the days are labelled to make it easy to see where you are in the calendar. Week numbers are listed as well – I’m using the ISO 8601 week numbering convention if you are interested in such things.

I’ve frozen the top and left parts of the spreadsheet so they are always on screen. The staff role column has an auto-filter box in case you want to filter by job type. And on the top left (highlighted in the red box below) are a number of colour coded categories that you can easily copy and paste into the relevant cells. Change the category names, and colours to suit your taste.

2011 staff holiday planner categories

To delete the colours in any cells just copy and paste an empty cell over it (Ctrl-C, Ctrl-V is the fastest way).

Here’s a final picture of the blank staff planner which you can download for free from the link underneath it.

2011 staff holiday planner blank

Download the 2011 staff holiday planner calendar – Excel XLS (20kb zipped)

Staff holiday planner with remaining holiday formulas

By popular demand I’ve created a second version of this spreadsheet. This one has built in formulas to calculate the remaining holiday. It has extra slots at columns C and D for allocated holiday, and remaining holiday. Column D counts cells containing the work ‘Holiday’ and ‘Half’ which subtract one day and half a day respectively from column C. If you look in the comments of this page you’ll see some other formulas as well for counting holidays only, and also for counting holidays and sick days as well.

2011 staff holiday planner formulas

I’ve set the XLS to count to the end of 2011 only, even though the holiday planner extends into the first few weeks of 2012. If you want different behaviour you’ll need to make some minor modifications.

Download the 2011 staff holiday planner calendar with formulas – Excel XLS (21kb zipped)

If you have any suggestions for the 2012 version let me know in the comments below.

Note: You’ll need at least Excel 2007 (or OpenOffice which is free!) to view the whole year, as earlier versions of Excel limit the number of columns to 256. And we need 365+ columns to fit the whole year in!

Estimate the value of your house or flat with a graph

Thursday, November 4th, 2010

If you want to know what the current value of your house, flat or property is (useful if you are buying, selling, or extending the leasehold for example) you can go for the easy option of asking an estate agent to do a valuation, or use a tool such as Nationwide’s house price calculator. Both have their disadvantages; the estate agent will almost certainly try to sell you an overly inflated figure to get your business, and an online tool such as the one from nationwide bases the prices on a very wide region (e.g. Greater London).

If you live in an area with lots of similar properties (e.g. a block of flats, or a street with very similar houses) and are good at Excel you can produce a localised graph of property prices over time. The scatter graph below is of the prices of flats for one specific street in London. The red line is the Nationwide regional data for flats in Greater London. Based on the sale prices of other similar properties you can make a guess as to the value of yours.

house price estimation graph 10

I’m going to assume you have a reasonable amount of Excel knowledge – this is *not* a step by step guide. To follow this you need to know how to import data into Excel, produce graphs, add new data to existing graphs, and format/sort data.

To start with you’ll need historical property sale prices. There are lots of websites that you can get them from, but www.houseprices.co.uk gives you them in a form that is easy to import into Excel.

house price estimation graph 1

Try searching by postcode and by street name to get the relevant prices for your block of flats or street. I’d suggest setting the number of results to 100 so the data isn’t too broken up by their adverts.

Copy and paste the prices tables (there will be multiple tables split by ads) into your Excel sale prices spreadsheet one after the other.

house price estimation graph 2

This produces a basic date vs price graph. If you select from the first date / house price to the last then Excel seems to correctly guess which way round the X and Y axis should be. If you include the column names in the selection Excel gets it wrong.

house price estimation graph 4

If you want to compare against regional data like I did, go to Nationwide’s house price data download page. I chose the ‘Regional Series’ / ‘Flats (Post 91)’ as the best match for the actual property price data I had.

house price estimation graph 5

The Nationwide spreadsheet data looks like this with prices by quarter and region.

house price estimation graph 6

Unfortunately Excel doesn’t understand the date formats in this spreadsheet (e.g. Q2 2009) so I used Find and Replace to change the Q1/Q2/Q3/Q4 values into dates. E.g. I changed ‘Q1 ‘ into ‘01/01’. Note that I used the find and replace to remove the extra space.

house price estimation graph 7

Then I marked the column data format as being in ‘date’ format.

house price estimation graph 8

I then copied the Nationwide data into a separate area of my original property sale prices spreadsheet. The data from the Nationwide spreadsheet is in chronological order, whereas the data from houseprices.co.uk is in reverse chronological order. I therefore sorted the Nationwide data to be in reverse chronological order as well so that it could be easily added to the same graph.

Finally I added a new data series to my original graph by selecting the Nationwide dates for the X axis and the prices for the Y axis.

house price estimation graph 9

And this is of course the result (I did a bit of tidying / formatting of the graph to make it look better), which will look familiar as being the graph at the start of this post.

house price estimation graph 10

It can’t predict the future, but you can make some useful assumptions based on past property prices. And it can give you a good indication if your estate agent is over inflating his/her estimate.

Graphing the Post Office Over 50’s Life Cover plan

Saturday, October 16th, 2010

Two years ago I produced some graphs showing how much you might pay vs how much you’d get back with two over 50 plans from AXA Sun Life. Today (Saturday 16th October 2010) The Times has included one of these graphs in its paper, so I thought I’d produce another set of graphs for a different company. This time it is The Post Office.

As before I’ll mention that I’m not a financial advisor, I have no personal connection to these kinds of plans, and I’m not making any kind of recommendation. All I am doing is turning the numbers for the Post Office Over 50’s Life Cover plan into some graphs. I’ve done my best to make these graphs accurate, but if you spot any problems leave me a comment.

I got a quote for a 65 year male using their website based on paying in £7 per month. Using these figures will get you a fixed £892 lump sum (or rather your family will), payable on death. You get this lump sum if you die after the first 12 months. If you die in the first year you get your premiums back. Your payments stop after 20 years when you are 85, and there is no cash in value. So if you stop making payments you get nothing back.

There are some other terms which affect the plan: if you die in an accident within the first 12 months you get your full premium back, and if you die of an accident after the first 12 months you get double your premiums back. I am not taking these additional terms into account for my graphs, but of course you might take them into account if considering a policy like this.

That is a rough summary in words – but what does it look like when turned into a graph?

post office over 50s life cover 1

The pink line is the lump sum payable which remains fixed, and the blue line is what you pay in. This remains fixed until you have paid in for 20 years. This person starts paying more in than they’d get out of the plan once they reach 76 years old.

It can be useful to compare the figures against what you’d get if you put the money into a bank account. This graph has two additional lines. One for saving this amount monthly at 2%, and the other at 4%.

post office over 50s life cover 2

These plans are all to do with when you die. Obviously the companies selling these plans have to make a profit (you don’t think anyone would give you free money do you?), so they use the average life expectance to work out how much life cover to give you.

Some people will get more than they paid in, but as with all other kinds of insurance the company has to make more money back. If the companies who make these policies paid out more than you gave them (on average) then they’d go out of business.

The National Statistics Office produce tables of life expectancy information. By looking at the ‘Great Britain, Interim Life Tables, 1980-82 to 2007-09’ I can see that a 65 year old male has a life expectancy of another 17.61 years. This means a man who is now 65 years old will die when he is 82.61 years old (on average of course).

Here are the above two graphs again, but this time with a smaller scale. Instead of being from 65 to 100 years, they are from 65 to 85 years.

post office over 50s life cover 3

post office over 50s life cover 4

Again as I mentioned above these kind of plans do offer other benefits such as accident insurance, so you have to make up your mind using all the available information.

But I hope you can see that a little bit of work in Excel can make the numbers a lot easier to understand.

The Post Office Over 50 Life Cover page is here, and on this site you can find similar but old graphing information for the AXA Sun Life Guaranteed Over 50 plan.

2011 Excel week numbers calendar

Tuesday, September 14th, 2010

If you work in a company then you may find yourself dealing in week numbers rather than dates. E.g. the deadline for this project is week 38!

Here is a free downloadable Excel calendar for 2011 that has the days of the week across the top, and then the week numbers for the whole of 2011 down the side.

2011 week number calendar

Each day has five boxes for you to write in, and the days of the week are frozen at the top, making it easy to see which box is for which day.

I’ve saved two versions. One standard version for modern versions of Excel (2007 and above), and an older version which should work in Excel 97.

2011 Excel week number calendar (for modern versions of Excel – 2007+)
2011 Excel week number calendar (for old versions of Excel – 97+)

If you prefer a month by month view of the year then I also have a one page 2011 Excel calendar on this site available for free download as well as a calendar for 2010.

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).

2011 One Page Excel Calendar

Thursday, November 19th, 2009

2010 is still over a month away and yet I’ve already had several requests for a 2011 one page excel calendar. The format of my 2010 excel calendar seems to be a good one so I’m repeating it for the 2011 version.

2011 Excel one page calendar

As in previous years I’ve made a blank calendar with just the days / months marked out, and a calendar with the 2011 UK bank holidays highlighted.

2011 Excel calendar – blank (5kb)
2011 Excel calendar – with UK Bank Holidays highlighted (5kb)

Like before you should be able to get it to fit on your computer screen without any scrolling – you may need to adjust the box sizes, or zoom level depending on the resolution of your monitor.

2010 One Page Excel Calendar

Monday, March 9th, 2009

We’re well into 2009 so to follow on from my 2009 Excel one page calendar it seems time to put up the yearly calendar for 2010.

2010 Excel one page calendar

As before I’ve done a blank calendar with just the days / months marked out, and a calendar with the 2010 UK bank holidays highlighted.

2010 Excel calendar – blank (7kb)
2010 Excel calendar – with UK Bank Holidays highlighted (7kb)

Like before you should be able to get it to fit on your computer screen without any scrolling – you may need to adjust the box sizes, or zoom level depending on the resolution of your monitor.

For anyone who is planning well ahead I have now added my 2011 one page excel calendar to this site.

2009 One Page Excel Calendar

Friday, August 29th, 2008

Following on from my 2008 Excel one page calendar I’ve produced a new yearly calendar for 2009.

2009 Excel one page calendar

This time I’ve done a blank one with just the days / months marked out, and one with the UK bank holidays highlighted.

2009 Excel calendar – blank (7kb)
2009 Excel calendar – with UK Bank Holidays highlighted (7kb)

Like before you should be able to get it to fit on your computer screen without any scrolling – you may need to adjust the box sizes, or zoom level depending on the resolution of your monitor.