Posted by CaliNotary on 12/16/04 5:27pm
Msg #13828
Rate List Excel Formulas
If anybody is interested, here are the Excel formulas that I used to create my rate sheet. It made it MUCH easier than calculating each figure by hand. Imagine the info below is an Excel spreadsheet - column headings are A - F, Rows are 1, 3 and 5.
City County Miles Rate
Rate Per Mile: $1.25
Acton LA 39 $97.50 $100
The $97.50 figure in cell D5 is $1.25x39x2. $1.25 represents the rate per mile, 39 represents the mileage one way to this city, 2 represents doubling the mileage for a round trip. In the cell D5 on the spreadsheet I haven't actually typed $97.50, I have a formula in there. The formula is =PRODUCT($B$3,2, C5). What this does is take the figure shown in cell B3 ($1.25), muliply it by 2, then muliply it by the figure shown in cell C5 (39). But it is displayed on the spreadsheet as the answer $97.50, not the actual formula itself.
Since all of my rates are rounded up to the nearest $5, the forumula in cell F5 is
=CEILING(D5, 5). This means that it's rounded up the figure in D5 to the nearest $5. If I wanted to round it to the nearest $10 the formula would read =CEILING(D5, 10). I then used the "hide column" action to hide row D, so the only fees that were showing were the rounded figures.
If you copy and paste these formulas down the entire column it should adjust the rows accordingly.
Hopefully this will be useful to some people.
| Reply by Becca/FL on 12/16/04 6:48pm
Msg #13843
Thank you very much!
So nice of you to post this info. for those of us who are not real "hip" with excel. Now, if someone could just help me with setting up Quickbooks, I might be ready for 4/15/05.
| Reply by BrendaTX on 12/16/04 10:22pm
Msg #13857
Re: Thank you very much!
Becca and others:
What "edition" of Quickbooks are you all using?
The Basic edition, or one of the better models?
Thanks.
| Reply by Becca/FL on 12/16/04 11:37pm
Msg #13872
Re: Thank you very much!
I just installed QB Pro 2004.
I spent the past weekend inputting info. I already found some kind of error I made but, don't know how to fix it. I keep telling myself, this will help me AND I keep asking myself HOW!?
I'd love to know how others use QuickBooks to manage their business.
Don't bash me, please. I've never used QB before only Quicken and that was quite awhile ago.
| Reply by BrendaTX on 12/16/04 10:10pm
Msg #13854
Ok, Cali...you've just lifted the veil, and you have revealed a couple of things I could have done without...
(1) My 12 pages of rate sheets look really complicated and important, and as if it took three days for me to finish. Now, everyone knows that they were not all that hard to generate once you know a few bits of Excel code.
(2) That you are really a very nice guy; insightful, and helpful, as well. Now, you will have so many new friends that you won't have time for me any more.
...this was all about me, wasn't it???
| Reply by Charm_AL on 12/17/04 1:40pm
Msg #13933
Thanks Calinotary...I find this info very helpful. BTW: At Home Signings called me this am to see if I was still avail for Tues,. 9am 45 miles away for $85. Next time I know better, but at the same time my fee is $35. higher than they pay. She said she had not gotten approved by the lender yet for the 'extra'. Don't they get at least 2x as much for us as they pay us???
-- ok in the middle of my post the phone rang...it was AHS...they met my fee and are sending an e-mail conf. amd overnighting the docs!
| Reply by Nicole_NCali on 12/17/04 6:03pm
Msg #13961
Thanks Cali..I just set-up a spreadsheet with your formulas and I am really shorting myself by 15 dollars for some of my zipcodes.
|
|
