Cruisers Forum
 


Join CruisersForum Today

Reply
 
Thread Tools Rate Thread Display Modes
Old 19-12-2009, 18:59   #1
Long Range Cruiser
 
MarkJ's Avatar

Cruisers Forum Supporter

Join Date: Dec 2007
Location: Australian living on "Sea Life" currently in England.
Boat: Beneteau 393 "Sea Life"
Posts: 12,828
Images: 25
Smile Expenses: Excel Spreadsheet, Please

Hi Folks of the Brainy, Financial types


Before I ask this question can we get one thing straight: I DID NOT COME TOP OF MY MATH CLASS!!!! In fact the teacher is still laughing 30 years later

I am trying to make up an Excel spreadsheet for my cruising expenses.

I just need a SIMPLE one but I can't do one calculation:

I can't make an input field where I put in what I have spent today and it adds it to the previous total. I seem to have to add it to the catagory total myself and then it adds it to the monthly or yearly total.

When I come back to the boat with a wallet full of receipts I just want to type the figures in to the correct catagories and it adds it to the running total.


For example I want:

Maintenance: <Todays amount> <Total>
Food: <Todays amount> <Total>

Yearly Total <total>

The attached XLS file (I would prefer XLSX but can't upload here) tries to show what I am trying to do.

I havent been able to find any ready made spreadsheet for what I want, and when I try to do a Sum of C + D = D I get a circle error.

Woe is idiot me.


I really want a nicer fat spreadsheet with months and years and all the rest but can't find one with the cumulative input

Thanks everly for the help!


Mark
Attached Files
File Type: xls Book3.xls (17.5 KB, 453 views)
__________________

__________________
Notes on a Circumnavigation.
OurLifeAtSea.com

Somalia Pirates and our Convoy
MarkJ is offline   Reply With Quote
Old 19-12-2009, 19:11   #2
Registered User

Join Date: Jan 2009
Location: Lynnwood Washington
Boat: 16' Catalina
Posts: 18
I see two solutions, to maintain the format you have now would likely require a macro that copies and pastes todays data over into a table. The monthly total could then sum up the figures from that ever growing table. The second solution would be to abandon the data entry format you have and go straight to the tables. You could have a summary sheet that then reports the sum of the monthly expenses. Excel is fun...
__________________

__________________
Grasshopper is offline   Reply With Quote
Old 19-12-2009, 20:20   #3
Moderator Emeritus
 
Ex-Calif's Avatar

Cruisers Forum Supporter

Join Date: May 2007
Location: Singapore
Boat: Maxi 77 - Relax Lah!
Posts: 11,514
Images: 4
Hi Mark - Creating a spreadsheet for what you want is completely doable but requires considerable time effort.

I would recommend you download and use one of the thousands of free software that exist for tracking expenses.

I use Quickbooks to track the expenses on our boat. Quickbooks, of course is not free. However I recently bought a PC magazine that had a free software disc attached. One of the free programs is called Express Accounts - I read the reviews and it seems to be able to do full accounting.

Cnet.com is a great source of free software.

Here is a link to a free downloadable version of Express Accounts.

Express Accounts - Free software downloads and software reviews - CNET Download.com

I haven't used it and it may have more features than you want.
__________________
Relax Lah! is For Sale <--- Click
Click--> Custom CF Google Search or CF Rules
You're gonna need a bigger boat... - Martin Brody
Ex-Calif is offline   Reply With Quote
Old 19-12-2009, 21:12   #4
Registered User
 
sabray's Avatar

Join Date: Aug 2008
Location: Wash DC
Boat: PETERSON 44
Posts: 3,169
If I understand you have columns with labels like mntc grocery etc...
Start with mntc. First cell below mntc is blank that is a,2. now put your entries below a2 after you have some entry's click on a2 click the formula box select some and highliight the entry's not a2 though. That will give you the total. You could highlight down 40 rows. That's the easiest way in excel maybe not the best.
__________________
sabray is offline   Reply With Quote
Old 19-12-2009, 21:17   #5
Registered User
 
ozmike's Avatar

Join Date: Jan 2008
Location: Mackay Qld, Australia
Boat: 38' Chieftain Centre Cockpit
Posts: 71
Hi Mark,
Is this what your after, you can have daily spend totals in right hand side and each expence total at bottom, with grand total bottom right corner, hope the formulas paste as well.
Attached Files
File Type: xls expences.xls (20.5 KB, 348 views)
__________________
ozmike is offline   Reply With Quote
Old 19-12-2009, 23:37   #6
Moderator
 
Boracay's Avatar

Cruisers Forum Supporter

Join Date: Feb 2004
Location: Pelican Bay, Great Sandy National Park
Boat: Steel Roberts Offshore 44
Posts: 5,175
Images: 18
I did a search...

Express Accounts looks to be for small business.

I did a search for home finance software and came up with :-
Personal Finances.

I had a quick look at the program and it looks like it might do what you're after. You'd need to create new expense categories for different boat expenses. Looks simple and straightforward.

Please keep in mind that I have no knowledge of the company or the website behind this software so use with caution.

There should be heaps of personal finance software out there.
__________________
Rust never sleeps
Boracay Blog.
Boracay is offline   Reply With Quote
Old 20-12-2009, 00:45   #7
Registered User
 
Morganministry's Avatar

Join Date: Nov 2009
Location: Klamath River, California
Boat: Buccaneer 240
Posts: 160
Send a message via Skype™ to Morganministry
Quote:
Originally Posted by MarkJ View Post
I havent been able to find any ready made spreadsheet for what I want, and when I try to do a Sum of C + D = D I get a circle error.

Woe is idiot me.


Mark

Since you have been up front about your math abilities I will not pick on you to much.

First of all the example that you made can not be calculated. Unless c=0.
otherwise anything plus D will be somthing other than D.

Secondly, I use Microsoft Works Database. When I want a total or some other amount to appear in a field, I enter the formula starting with =.
So to get the total price on some thing I would write a formula like this
=price*quantity

The total price for that Item would appear.

I hope that helps,
Scott
__________________
Morganministry is offline   Reply With Quote
Old 20-12-2009, 01:16   #8
Registered User

Join Date: Aug 2008
Boat: Trident marine Voyager 30
Posts: 98
Quote:
Originally Posted by MarkJ View Post
I havent been able to find any ready made spreadsheet for what I want, and when I try to do a Sum of C + D = D I get a circle error.
Mark
To add upp all the marina expenses write 0 in the c3 field, write =c3+d3 in the d3 field. You get an circular error. Ignore it and go to tools->options->calculation. Click the Iteration check box and change maximum number of iterations to 1.
Anytime you write a number in the c3 field it will be added to what is already in the d3 field.

EDIT:
Not actually the best idea since you would have to put a 0 in the c3 field before you can put a number in the c4 field or it would add the number in the c3 field again.
__________________
Anders is offline   Reply With Quote
Old 20-12-2009, 03:56   #9
Long Range Cruiser
 
MarkJ's Avatar

Cruisers Forum Supporter

Join Date: Dec 2007
Location: Australian living on "Sea Life" currently in England.
Boat: Beneteau 393 "Sea Life"
Posts: 12,828
Images: 25
Quote:
Originally Posted by Anders View Post
You get an circular error. Ignore it and go to tools->options->calculation. Click the Iteration check box and change maximum number of iterations to 1.
.
Winner!!!!!!!!!!!! Thats just what I was after!

I love the cruisers methods... don't take "error" just ignore it

Thanks for everyones help and particularly to OxMikes spreadsheet!


Mark
__________________
Notes on a Circumnavigation.
OurLifeAtSea.com

Somalia Pirates and our Convoy
MarkJ is offline   Reply With Quote
Old 20-12-2009, 04:45   #10
Long Range Cruiser
 
MarkJ's Avatar

Cruisers Forum Supporter

Join Date: Dec 2007
Location: Australian living on "Sea Life" currently in England.
Boat: Beneteau 393 "Sea Life"
Posts: 12,828
Images: 25
Quote:
Originally Posted by Anders View Post

EDIT:
Not actually the best idea since you would have to put a 0 in the c3 field before you can put a number in the c4 field or it would add the number in the c3 field again.
OK just found a problem with it.

LOL
__________________
Notes on a Circumnavigation.
OurLifeAtSea.com

Somalia Pirates and our Convoy
MarkJ is offline   Reply With Quote
Old 20-12-2009, 08:38   #11
Registered User
 
Extemporaneous's Avatar

Join Date: Dec 2007
Location: Canada
Boat: Corbin 39 Special Edition
Posts: 909
GnuCash Portable | PortableApps.com - Portable software for USB drives

I'm not sure if this program would do a good job of what you want, but because it's a PortableApp (and it's free) it's where I try to go first for my software requirements. I can also tell you that any of the PortableApp's that I have are FULL featured and work Great.

Good Luck,
Extemp.
__________________
Extemporaneous is offline   Reply With Quote
Old 20-12-2009, 09:38   #12
Senior Cruiser

Cruisers Forum Supporter

Join Date: Mar 2007
Location: Santa Cruz
Boat: Boatless Again
Posts: 4,324
Excel is much more flexible than the accounting programs, and there are several ways to do what you want. Without going into macro's, the best way to enter you data is like a checkbook log--one line for each entry, with date, $$, category, and description fields. Just enter each expense as it occurs.

Then use Excel's database tools to mine the raw data for what you want. First use the edit function to copy the sheet with the raw data to a new sheet, so you don't mess up your data. Then in the new sheet, highlight all your data (including the column headings) and use the data/sort function to rearrange your data. For example, if you want to see how much you spent on each category, you would sort first by the category and then by the date. Then you highlight the data again and use the data/subtotal to get a subtotal for each category (the subtotal function gives you a subtotal each time the data in the category column changes).

Getting monthly subtotals is a bit more complicated, but I would use the Month and Year function to extract the month from the dates you entered. Say you put the date in column A. Make a column E called "Month" and make cell E2 =MONTH(A2). Copy that formula all the way down your data and do the same with F2 = YEAR(A2). Now select your data and sort it by first year, then month, then use the subtotal function for year and month to get annual and monthly expenses.

You can do other fun things like putting in a country column, entering local currency and converting, etc. The type ahead function in Excel makes entering data a lot easier--for instance, if your category is "Fuel",
once you have entered it once, the next time you type "F", it will suggest "Fuel".

OTOH, why would you want to waste your valuable cruising time tracking your expenses???
__________________
donradcliffe is offline   Reply With Quote
Old 20-12-2009, 20:12   #13
Moderator Emeritus
 
Ex-Calif's Avatar

Cruisers Forum Supporter

Join Date: May 2007
Location: Singapore
Boat: Maxi 77 - Relax Lah!
Posts: 11,514
Images: 4
I don't disagree with the fact that excel can do anything you might need. The advantage of a freeware program is that the heavy lifting is already done for you.

Entering the data in excel is pretty easy. Setting it up correctly in the beginning is critical to the real purpose - getting the data out again for meaningful use.

An accounting program is not for everyone but I wouldn't do it any other way. The advantage is that you can easily get useful output like the attached...
Attached Files
File Type: pdf 2009 December - Relax Lah! - Profit & Loss.pdf (4.2 KB, 180 views)
__________________
Relax Lah! is For Sale <--- Click
Click--> Custom CF Google Search or CF Rules
You're gonna need a bigger boat... - Martin Brody
Ex-Calif is offline   Reply With Quote
Old 21-12-2009, 05:15   #14
Long Range Cruiser
 
MarkJ's Avatar

Cruisers Forum Supporter

Join Date: Dec 2007
Location: Australian living on "Sea Life" currently in England.
Boat: Beneteau 393 "Sea Life"
Posts: 12,828
Images: 25
Quote:
Originally Posted by donradcliffe View Post
OTOH, why would you want to waste your valuable cruising time tracking your expenses???
Because someone on board this boat Excels at shopping!!!!


__________________
Notes on a Circumnavigation.
OurLifeAtSea.com

Somalia Pirates and our Convoy
MarkJ is offline   Reply With Quote
Old 21-12-2009, 13:15   #15
Moderator Emeritus
 
FrankZ's Avatar

Cruisers Forum Supporter

Join Date: Sep 2006
Location: Chesapeake Bay
Boat: Bristol 35 Bellesa
Posts: 13,565
Images: 1
Quote:
Originally Posted by MarkJ View Post
Because someone on board this boat Excels at shopping!!!!



It seems we hear about $1 dresses and then expensive solar panels. Seems you do a great job shopping Mark.
__________________

__________________
Sing to a sailor's courage, Sing while the elbows bend,
A ruby port your harbor, Raise three sheets to the wind.
......................-=Krynnish drinking song=-
FrankZ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sharing Marina Expenses Jace General Sailing Forum 13 13-09-2009 10:37
Excel Provisioning Spreadsheet MV Provisioning: Food & Drink 52 27-07-2009 16:14
Spreadsheet for Boat-Buying Comparison? outthere09 The Sailor's Confessional 7 25-03-2009 15:03
How much for shared expenses - Poll seagypsywoman Crew Archives 6 02-12-2008 16:35
Homework using a spreadsheet Redbull addict Monohull Sailboats 6 15-06-2007 11:03



Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -7. The time now is 03:55.


Google+
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Social Knowledge Networks
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.

ShowCase vBulletin Plugins by Drive Thru Online, Inc.