Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    I've questioned about this before but don't remember if I attached files. Our company uses a contract template created in Excel. Every year we send out over 800 contracts which, hopefully for the last time, I enter ALL the information manually for each. I have attached the contract and the data file. The information to merge from the 2010 GL Customer List are as follows: Last Name, First Name, Service Address, City, State, Zip, Phone (goes into cells A8, A9, A10, A11 of Contract); BT Last Name, BT First Name, BT Address, BT City, BT State, BT Zip, BT Phone (goes into cells H8, H9, H10, H11 of Contract); Service, Aer, Lime (goes into cells M17, M18, M19 of contract). I realize it's probably not possible to merge more than one cell from the Customer List into one cell of the Contract, so I am prepared to recreate the Contract in a Word document if that is what you suggest. I'm just giving you the files that have been in use for several years. Also, since there are about 15 - 20 scenarios for the contracts, I need to have a way to chose (filter?) the data that goes into each contract. For instance, you will notice there are formulas in the contract file; these corresponds with the different sales taxes in the counties we service. I'm not sure how to accomplish that unless I have a separate column to use as an "if"? I'm also open to using an Access database, but would hate to have to re-enter all of my customer data. I'm open to any and all suggestions. Thanks so much!
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    The attached file can get you started. I copied your customer info into a new tab in the Contract file and I added a column for a customer number.

    Try this on the Contract tab....
    Type a customer number in Cell B7.
    The info will fill in the contract automatically.

    One possibility to do the Sales Tax calcs ....
    To do the Tax calcs, will need a list of counties and their tax rate. The county name will have to be part of Customer Data.
    Attached Files Attached Files

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Certain files in the first two posts to this thread had attached files censored of personal customer information.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I have revised my attachment to remove personal info.

    Try typing a Customer number in cell B7 to see how it works.

    Various scenarios for each contract as well as Senior pricing can also be automatically computed.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Thanks for your help, Tim! I'm in the process of working on it now. I did, however, re-post with dummy information in the contract and customer information files since the original ones had to be hidden. My bad!. I had someone message me and said they wanted to reply and noticed I had quite a number of views. I hope that's okay. I will let you know how this works out for me. I've got until January to get it set up so I've got a good amount of time to work on it. Thanks again!

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Okay, Tim, this works great but I have a couple of questions. 1) the service, aeration and liming information references are different on the contract than the name and address fields so the information doesn't change when I put a new customer number in field B7 2) is there a way to not have zeroes show up when there is no information being transferred to the address and phone number fields? Thanks! You just can't imagine how much work this is going to save me!!

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello k32 - I have attached a revised file to answer questions.

    1) On the "Customers" tab for "service, aeration and liming" I put in some sample pricing in Columns AA, AB, AC for each customer. Use whatever pricing is appropriate for each customer.

    2) Check this revision to see if this is what is needed for the Contract's Service info.

    If this works, we can move on to automating the Sales Tax info or whatever is the next priority.

    Tim
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Tim - This looks wonderful! It's hard to believe all I have to do is type a number and all the information pops in without having to worry about mistakes made by typing it all manually. This will save countless hours! I knew the capability was there but didn't know how to get there. Re Sales Tax: We use four different sales tax rates. We also do properties that are tax exempt. And we have businesses that I don't include the options for senior pricing. Some of these are tax exempt, also. I am attaching examples of these (one business and one exempt) Thanks!
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello k32 - Sorry for the delayed response, I was away on vacation last week.

    It looks like one contract template can be used for all customers, depending on answers to the following.

    Questions for Sales Tax calculations ....
    1) What are the 4 different tax rates?
    2) How is it determined which tax rate applies to each customer?
    3) Can zip code determine sales tax rate?
    4) Do sales taxes get charged only to businesses?
    5) How is tax-exempt status determined?

    Question for Senior pricing ....
    A) What factor determines Senior pricing?

    How/where are these columns from Customer List used?
    Program,Base App,Paid,ES,LS,SS,FS,Grub,Lime,Aeration


    Tim

  10. #10
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Tim Sullivan View Post
    Hello k32 - Sorry for the delayed response, I was away on vacation last week.

    It looks like one contract template can be used for all customers, depending on answers to the following.

    Questions for Sales Tax calculations ....
    1) What are the 4 different tax rates? 6%, 8%, 8.5%, 7.75%
    2) How is it determined which tax rate applies to each customer? Depends on the county; I group some by county and others by towns within county if large towns
    3) Can zip code determine sales tax rate? Yes
    4) Do sales taxes get charged only to businesses? No
    5) How is tax-exempt status determined? Churches; Native American; schools

    Question for Senior pricing ....
    A) What factor determines Senior pricing? 60 and over get 5% discount (I have that built in the formula)

    How/where are these columns from Customer List used? ES,LS,SS,FS - they are not set up in a format that would be used; I have added 4 more columns on the end that would be used for inserting the "Anticipated Dates of Application". Program - N/A. Base App - N/A. Paid - N/A. Grub - N/A (auto calculation based on Cost/Application), Service, Lime, Aeration - these are the costs that pop into the "Price No Tax" fields when the customer number is entered.
    Program,Base App,Paid,ES,LS,SS,FS,Grub,Lime,Aeration

    Hope I've answered all your questions clearly -- and I hope you had a great vacation!


    Tim

  11. #11
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Whoops! Forgot the file! Sorry, here it is
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello k32 - I made some modifications to the contract and customer database.
    See file attached.

    On Contract:
    Anticipated dates of application is now automatic.
    Cell M12 shows "County" and Tax Rate automatically
    Cells D29, D38, D47 show "Tax Exempt" or "Tax Included"
    For example: type Cust# 1004 and it will show as tax exempt

    Will need to work on some Senior ideas:
    Senior Discount - Can a line be added to Sections A. C, and E
    to show the amount of discount? That could eliminate Sections B and D
    Can also make contract so Senior Discount only shows on Seniors contracts.
    Would add a column to Cust List for an "S' when it is a senior.
    That way there would only be one contract form.

    Added a Sales Tax tab in case you wanted to automate County and Sales Tax by Zip Code.

    Please, do some tests on the Contract and make any comments.

    Tim
    Attached Files Attached Files

  13. #13
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Wow! This is way more than I anticipated. Sorry for the late reply. I'm only working part time now so don't always get to everything I need to in a day. I will reply after I check things out. Thanks!

  14. #14
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Tim Sullivan View Post
    Hello k32 - I made some modifications to the contract and customer database.
    See file attached.

    On Contract:
    Anticipated dates of application is now automatic.
    Cell M12 shows "County" and Tax Rate automatically
    Cells D29, D38, D47 show "Tax Exempt" or "Tax Included"
    For example: type Cust# 1004 and it will show as tax exempt

    Will need to work on some Senior ideas:
    Senior Discount - Can a line be added to Sections A. C, and E No, the contract has to stay as formatted, except to eliminate B & D for businesses/exempt contracts that do not qualify for the Senior Discount. So we would need two contract forms.
    to show the amount of discount? That could eliminate Sections B and D
    Can also make contract so Senior Discount only shows on Seniors contracts.
    Would add a column to Cust List for an "S' when it is a senior.
    That way there would only be one contract form.

    Added a Sales Tax tab in case you wanted to automate County and Sales Tax by Zip Code. Yes, this can be done if you think it would be the easiest way to do the Sales Tax

    Please, do some tests on the Contract and make any comments.

    Tim
    Everything else looks great! More than I expected could be done!

  15. #15
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello k32 - It looks like Sales Tax cannot be determined by ZipCode. There are several Zip Codes that are in 2 or more counties in New York.
    For Example: Zip Code 14030 City of Chaffee is in 3 counties - Erie, Wyoming, and CATTARAUGUS.

    Here is a link to ZIP Codes by County that shows what percent of a certain Zip Code lies within a particular county.

    http://www.melissadata.com/lookups/C...e=NY36New+York

    After you Pick the County, Click on the City Name and it will show some useful info including what Counties that City is in. You might want to verify this with your tax advisor.

    Tim

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •