I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.

[blue_wynd]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

I think I found the problem. You're using OpenOffice and I'm using Excel. Here is an expanded copy that should work with OO.

This is v3.1 which I've added support for up to 14 companies.

Thanks. So what did you do additional to add support to non office programs? Curious.

----------

Also for the company code, can you put in a view by company code or company name so that you can drop down the list and see how much you wrote with one carrier. I assume that is what the numbers are for? Or another use for the company code?

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Thanks. So what did you do additional to add support to non office programs? Curious.

Nothing, really. I just unhid the hidden tabs and re-uploaded it. Open Office is a free clone of MS Office but I guess there are some differences. Most likely there was a way for you to unhide the tabs somehow but it was easier just to repost it.

One thing I discovered about Excel that I hate is that you can't perform a function within a cell and have the results display in another cell. It took me a week to discover this and to find a workaround.

 

[blue_wynd]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Nothing, really. I just unhid the hidden tabs and re-uploaded it. Open Office is a free clone of MS Office but I guess there are some differences. Most likely there was a way for you to unhide the tabs somehow but it was easier just to repost it.

One thing I discovered about Excel that I hate is that you can't perform a function within a cell and have the results display in another cell. It took me a week to discover this and to find a workaround.

Yea, I use opensource whenever I can. lol. So about the company codes you have listed on the sheet, what is the use of it? 1-7?

 

[Jerard]

Yea, I use opensource whenever I can. lol. So about the company codes you have listed on the sheet, what is the use of it? 1-7?

It's 1-14 now with v3.1. Each one is a company code for one of your companies. For example if you enter "2" it will automatically subtract the (non-commissionable) policy fee and calculate the advances. If you are as earned on everything then it will tell you the commission based on that plan's FYC. Just enter your companies, etc. to the right of the spreadsheet. You still have to unlick...errrr...unlock it to change the dropdown menus. Keep in mind that any changes to one sheet (Q1 for example) will not change anything on the other 3 sheets. So if you make any changes than it would be a good idea to delete Q2, Q3, and Q4...make changes then copy the sheets again. Hope that makes sense.....

 

[blue_wynd]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Believe so, need to play around with it. thanks.

 

[jwk1257]

Re: I Made a Spreadsheet for tracking Sales/Commissions/Deposits/Taxes that I'm Sharing.

Hey Jerard, this is a great tool! I have been looking and wondering how I could do this....But there are some questions I need you to answer.....you said you had upgraded so we could put in annual rates, and also clik on the right to edit companies, I can't seem to find that or maybe I just don't know how....

Awhile back I was looking for a simple Excel spreadsheet to track sales, commissions, deposits, and taxes and I couldn't find one I liked, so I made one for myself. I was thinking that maybe some of you may find it useful so I uploaded it.

You'll need to change the companies you are appointed with. Make all edits before inputting sales. Also delete the Sample data and all text in the white areas (white cells are for data/text input. Green cells are protected and for calculated results).

If you unlock the protected cells and screw things up you'll have to start over.


TO CHANGE THE DROPDOWN MENUS:
1) Select the topmost cell on the column you want to change.
2) Unlock the spreadsheet using the password. It is 1111
3) Select the Data tab (on top), then Data Validation then Data Validation again.
4) Under the Settings tab select "lists" - then make your changes in the Source box. Each dropdown entry must be separated by a comma. Be careful when doing this. I got weird characters showing up when I was typing in this box. When I say "weird characters" I don't mean insurance agents.
5) Select the box near "Apply these changes to all other cells with the same settings." You'll see the whole column become highlighted. Hit OK and that's it.

I designed it so that after taxes are paid for that particular quarter you can 'close' the month out by hiding the sheet. The tax rate used for the saving of money for taxes is 30% but this can be changed on the far right.

Hope this is useful to someone. Don't forget to unzip it.

Jeff

-------------------------------------------------------
If you are using a previous version I would recommend starting a new version on Jan. 1st (start of the next Quarter)

EDIT: Uploaded Version 3.1 on 12/27/13
- Added support for up to 14 companies

EDIT: Uploaded Version 3.0 on 12/26/13
Major Upgrade includes:
- Automated input and subtraction of non-commission policy fees from your base commission (Edit companies and policy fees on the right)
- Automated input and multiplication of your advance rate (edited on the right)
- Foolproof dropdown menus (see above on how to edit-It is only necessary to edit Column "P")
- Columns added for sales and deposit notes
- Columns added for tracking your Client contacts (Note: you can add much more text than what looks like is available. Click the down arrow to the right of the input box.
- Simplified and made more accurate the imputing of policy fees (now you can enter the annual PF instead of having to figure the monthly fee-this eliminates the rounding error)
- Cleaner look than from previous versions
Known issues:
- Inability to change your advance rate if it should change in mid-quarter as this will change all previous sales figures for that same company for the same Quarter (If you have 5-Star then wait until 1/1/14 to use as this is when their advance rate drops to 70%)

EDIT: Uploaded Version 2.1 on 03/02/13 (Fixed subtraction error on Column M. This error was giving a slightly different comm balance after advance had been paid)

EDIT: Uploaded Version 2 on 02/23/13

 

[Jerard]

Re: I Made a Spreadsheet for tracking Sales/Commissions/Deposits/Taxes that I'm Sharing.

Hey Jerard, this is a great tool! I have been looking and wondering how I could do this....But there are some questions I need you to answer.....you said you had upgraded so we could put in annual rates, and also clik on the right to edit companies, I can't seem to find that or maybe I just don't know how....

There's two things that need to be done to set it up for use.
1) Look to the right to see the area that have my company names, the annual POLICY FEE and the advance you have with each company. Change those to match your own criteria.

2) Now make the following changes:
1) Hover over column "D". You'll see a pop up yellow comment. Right click to edit that comment. Make sure it matches the order you listed your companies on the right side.
2) Next you need to edit the companies under column "P". Instructions for this is on my 1st post on how to edit dropdown menus.
NOW (important). The easiest way would be to copy the text from the "Source" area and paste it to the other sheets (Unlock each sheet first). One thing to note is that changes you make to one sheet (Q1 for example) DO NOT change in the other sheets. I learned that the hard way. Copy and Paste is your friend.

Things to remember:
- Column "H" is the annual premium from your sale less the policy fee that is NON-COMMISIONABLE. Most companies will pay you a commission on the policy fee but some do not.
- Column "M" is the amount the company still owes you. If they pay you a 75% advance then this is the 25% that is still owed to you. Assuming no chargeback, of course.
- Any cell that has a red corner contains an informative note. Maybe I'll add some more of these in the next few days.
- If you are using OpenOffice instead of MS Excel then download the file in post #20. I've found out that you can't see the hidden tabs if using OO (thanks goes to blue_wynd for help with that).

If any more help is needed I'll be around...except for tomorrow, I'll be off watching the Bucs lose their last game. Damn...I miss football already.

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Version 3.2 Uploaded 12/30/13

Added extended Getting Started help screen. See post #1 for details.
This will be the last update for awhile.

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Version 3.5 Uploaded 1/1/14 - See post #1 for details.

OK...I know I said the last version would be the last one for awhile, but I took a few days off and believe it or not, this is what I do for fun.

I wanted to have some sort of Year To Date calculations for myself but I added some other stuff while I was at it. I like to do programming, so sue me!

 

[blue_wynd]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Thanks for the effort, I will buy the beer when you come my area even though I probably won't be using this since I do mainly P & C. .