Clean It Up
UK Window Cleaning Forum => Window Cleaning Forum => Topic started by: mark dew on January 01, 2009, 05:24:29 pm
-
i've been using excel since i started window cleaning and few people have asked for a template before so if anyone is interested in using these they are welcome to.
This is what they look like.
This one is called april2010. I use this one for each month. may2010, june etc.
The headings can be changed, i just use these. The mileage, cost of job, expenditure, jobs that have paid, average job price and profit, are all automatically worked out.
If someone has paid i put a Y for yes. This highlights a green background. If not paid i put an N and it highlights a red background.
Also it doesn't matter how many jobs you have per month. I have the formulas setup on this one for 500 per month. But it is easy to lower the number. (Which saves the printer spewing out pages and pages of blank paper when you print them.)
-
This next worksheet i use for the yearly accounts.
Again the headings can be changed. This sheet is linked automatically to the information entered on the file called APRIL2010.
I have these files in a folder i named dai hutton window cleaning. Sorry dai. :)
In this folder i have a file for each month, a yearly excel file that summarises the year and also a yearly expenditure breakdown.
With this yearly sheet the information entered on the above file called APRIL2010 is automatically entered and added up on this this yearly worksheet sheet.
The formula used to link it is this ='C:\Documents and Settings\mark\Desktop\dai hutton window cleaning\[APRIL2010.xls]Sheet1'!J1
Whenever the information is linked from the monthly accounts to yearly accounts, this file will add up the numbers.
You just change the month name to keep repeating the information on the next months file which would be called MAY2010.
='C:\Documents and Settings\mark\Desktop\dai hutton window cleaning\[MAY2010.xls]Sheet1'!J1
TheN JUNE2010 etc.
-
then i use an excel file for a breakdown of my expenditure.
The total on the right is automatically linked to the april account file. But i don't have the formula yet for adding up the separate expenses in the smaller fields. It only adds up the final total.
-
What i wondered was if anyone used something similar and if they knew the formula for linking text from one worksheet to another?
If i spent £10 in april 2010 on fuel and it is entered as fuel in the expense type column, i want to link it to the yearly expense sheet so it shows up in the columns as well as just showing up in the final total.
This link i can't work out yet.
Can anyone help with this formula?
ps
why can't i link an xls file to the posts. It says jpeg etc only?
Is that a glitch or there for a reason?
I'm hoping someone will click on it, open it up and say, i need this and i can do that etc.
-
oh, i haven't been using the same monthly template as the one shown above. The one i have been using is divided by weekly totals which are then automatically added up.
The trouble was the formulas would work if i added more work to the sheet. But the link wouldn't work anymore.
The only way i saw around this was to do away with weekly and try it out monthly. This way i don't need to enter any data for the 2 bits of paper my accountant is interested in. It is automatically done when i update my daily work.
I will get wcp nearer the end of the tax year, cos i was impressed though i found some things harder to adapt to than my existing method.
But i love the idea of only entering data once. Then just clicking afterwards.
But i'm stuck with my excel files for this tax year and i just wanna to be able to suss out that 1 formula, until then.
-
I'm still working on mine, the idea being that they should be bomb proof. So motoring would have it's own template, as would advertising.
-
What i wondered was if anyone used something similar and if they knew the formula for linking text from one worksheet to another?
If i spent £10 in april 2010 on fuel and it is entered as fuel in the expense type column, i want to link it to the yearly expense sheet so it shows up in the columns as well as just showing up in the final total.
This link i can't work out yet.
Can anyone help with this formula?
ps
why can't i link an xls file to the posts. It says jpeg etc only?
Is that a glitch or there for a reason?
I'm hoping someone will click on it, open it up and say, i need this and i can do that etc.
It's to prevent viruses spreasing. MS Office software can use things called macros. It's possible to conceal a virus in a macro. I don't use MS Office myself (of which Excel is a part). I use MS Works. You can't link spreadsheets in Works so I don't know about that. However, there is excellent help on usenet - a very overlooked part of the internet that is based on the old bulletin boards. The newsgroups can be found in google or you can download them in a newsgroup reader such as Outlook Express (I prefer to use one called Agent).
The newsgroup called microsoft.public.excel could ne a good place to start.
Try google, find the "groiups" link and type in some relevant keywords and you may get your answer without having to even post a question. It's a great archive and I've used it many times. This is one of the reasons I prefer to use software that is widely used rather than written by individuals. Although the service is great, it does depend on the authors' well-being.
-
Mark,
I'm fluent at Exel but sorry a bit confused what you are trying to do. Best email me and will help you out. A bit too late in the night plus going away for the weekend so if you can wait will sort it out for you.
Cheers
Doug
-
cheers doug. It was you and shiner that gave me the help last time in finding out how to link excel files.
i will email you.
Cheers
-
did you solve the problem? I couldn't work out what you meant though. The easiest way to link one cell is to put an = in the cell, and then go to the place you want the link to, click that cell and press enter.
I am only guessing but is your yearly expense total just doing an =sum(firstcell:lastcell) If so, you need to link each expense separately rather than just doing an =sum on the whole range
But it seems you know enough about spreadsheets that you'd know that already, so I think I am just misunderstanding you!
One thing I'd do differently though is rather than having a new spreadsheet file for each month, and then ending up with 12 spreadsheets, just create one spreadsheet for the whole year and have 12 sheets in it, a tab for each month. Rename Sheet1 Sheet2 Sheet3 etc to April May June etc
And one last thing to The Shiner. If you want more functionality from your spreadsheets but don't want to shell out hundreds of pounds for MS Office, then try OpenOffice.org Its a fully functional office suite but it wont cost you anything (its free but legal!). Its produced by Sun Microsystems (who also make Java)
-
I am only guessing but is your yearly expense total just doing an =sum(firstcell:lastcell) If so, you need to link each expense separately rather than just doing an =sum on the whole range
I'm not sure what you mean about linking each expense separately? I do as you said use =sum(:).
One thing I'd do differently though is rather than having a new spreadsheet file for each month, and then ending up with 12 spreadsheets, just create one spreadsheet for the whole year and have 12 sheets in it, a tab for each month. Rename Sheet1 Sheet2 Sheet3 etc to April May June etc
Lol. I hdn't thought of that. It is a good idea. Which i will do.
As for the original question, it was a bit vague. BUt if on my monthly expenditure column i have an expense that is for fuel. I wanted the yearly expense sheet to link if it sees the word 'fuel' by adding the amount spent on fuel in the fuel column on the yearly expense sheet.
Thanks for the suggetions. :)
-
did you solve the problem? I couldn't work out what you meant though. The easiest way to link one cell is to put an = in the cell, and then go to the place you want the link to, click that cell and press enter.
I am only guessing but is your yearly expense total just doing an =sum(firstcell:lastcell) If so, you need to link each expense separately rather than just doing an =sum on the whole range
But it seems you know enough about spreadsheets that you'd know that already, so I think I am just misunderstanding you!
One thing I'd do differently though is rather than having a new spreadsheet file for each month, and then ending up with 12 spreadsheets, just create one spreadsheet for the whole year and have 12 sheets in it, a tab for each month. Rename Sheet1 Sheet2 Sheet3 etc to April May June etc
And one last thing to The Shiner. If you want more functionality from your spreadsheets but don't want to shell out hundreds of pounds for MS Office, then try OpenOffice.org Its a fully functional office suite but it wont cost you anything (its free but legal!). Its produced by Sun Microsystems (who also make Java)
Thanks for that. I am aware of Oo. I don't need to link spreadsheets at the moment. I do use the Oo spreadsheet facility but only when I want to send a small sheet to someone else by email who doesn't have Works. Oo can open Works spreadsheets directly.
-
on your yearly earnings template you have a column Expenditure. I am not sure its possible to make the yearly earnings template recognise the word Fuel, and then add the amount (although the 'if' command if pretty powerful)
I think you'd have to manually add a column for fuel in yearly earnings and then link back to your monthly sheets
On your expenditure breakdown you said you don't have the formula for adding up the separate expenses in the smaller fields. It only adds up the final total. The formula to go in field B16 would be =sum(B3:B15) C16 would be =sum(C3:C15) etc
The formula in I3 should be =sum(B3:H3) To make adding a range like B3:H3 easier, you can type =sum( then click B3 and drag you mouse along to H3 - that will select the row for you, press enter and it will automatically close the formula with a )
-
on your yearly earnings template you have a column Expenditure. I am not sure its possible to make the yearly earnings template recognise the word Fuel, and then add the amount (although the 'if' command if pretty powerful)
I think you'd have to manually add a column for fuel in yearly earnings and then link back to your monthly sheets
On your expenditure breakdown you said you don't have the formula for adding up the separate expenses in the smaller fields. It only adds up the final total. The formula to go in field B16 would be =sum(B3:B15) C16 would be =sum(C3:C15) etc
The formula in I3 should be =sum(B3:H3) To make adding a range like B3:H3 easier, you can type =sum( then click B3 and drag you mouse along to H3 - that will select the row for you, press enter and it will automatically close the formula with a )
What i was thinking was that by using COUNTIF i could get it done. But i haven't progressed with it yet. It is the only thing that i have to add manually.
Anyway, april isn't far away and i will probs get wc pro nearer the time.
-
I have a sheet that looks like one big mess, but i got it from PSYBT and once u spend mabie 30mins looking at it it is really good, it manages all your incommings vs outgoings and auto calculates and deducts loans etc...really usefull!!!
Want a copy let me know.
-
Yeah will do thanks.
I like tinkering with mine. And i was quite impressed with the window cleaner pro trial once i'd got used to it.
But that will be more cost for the program and for something to run it on during the day. But worth it i think. :)
-
window cleaner pro is quite an expensive program. Do you think they have worked out that window cleaners are raking it in!? Is it the same reasoning that takes place when you price up a job and there's a new porsche in the driveway?
-
But i don't have the formula yet for adding up the separate expenses in the smaller fields. It only adds up the final total.
You could create a column for the type of expense it is and then use the filter tool to sift out the kind of expense you are interested in and then use the Subtotal function to show the total of the visible cells only eg. =SUBTOTAL(9,A1:A100)
Or use this formula in a hidden column:
Let's say column G10 is where you have entered the expense type and H10 is where the amount spent is:
In another column, let's say J10, write:
=IF(G10="Fuel",H10,0)
Then use the sum function to add up column J to see the total of what you have spent on Fuel.
Do a column like this for each expense type and then use the hide columns feature to make it look tidy.