|
Candle Business Training.com
|
|||||
|
Candle Business Tracker Tips and Tricks Many of you liked the idea of my sending emails once a week that help you get the most out of your Tracker. So I've decided to archive them all here, so you can get back to them at any time, and for those starting with the Tracker during the year, here's where you can catch up on emails you might have missed. Browse the topics below that were issued to all registered Tracker users by email: 01/05/09: Tips & Tricks 01-
Transferring Data from 2008 to 2009 -------------------------------------------------
Transferring Inventory from
Year to Year
If you used the Tracker in 2008, and you just
downloaded the new 2009 version, then you'll need to get your Ending
Inventory of the 2008 version, into your Beginning Inventory column
of the 2009.
Generally speaking for tax purposes, Beginning
Inventory of one year, should always be equal to the Ending
Inventory from the year before.
Unfortunately, Excel wouldn't allow me automatic
transferring of data, so at this point it's still a manual process,
but here's the easiest way to do it.
------------------------------------------------- Jan 12, 2009: Tips & Tricks #2
Entering Starter Packages,
Purchases, COTM and their Tax & Shipping, into
the Income & Expenses Tab of the Tracker
When you signed up with Scent-Sations, you
chose a starter package right? Well, whether you just started
your business, or purchased an Sample or Fast Start Pack after
you began, there's a good way to enter them into the Tracker.
As with regular purchases, items contained
within the various Starter Kits need to be broken down, to
keep the Cost of Goods Sold item on your Schedule C more
accurate. This is due to the fact that part of the Starter
Kits consists of product, part is advertising, and part is our
"Membership Fee" (to be a distributor). So let's break them
down...
A lot of this stuff here, can also apply to
regular purchases you make throughout the year.
First, How to Claim the Shipping you
paid on your order
If I have an order that contains both
product & distributor materials, I split the cost of shipping
between them, based on their respective percentage in the
order...
Here's an example.
I have a $100 order; $75 is product (6 jars,
12 votives, a Hand Wash), and $25 is distributor materials
(Display Boxes & Pads). So, 75% product, 25% Advertising.
I paid $10 in shipping, so that means I'll
put 75% of that shipping (or $7.50) into Purchases
with the products, and the remaining 25% of the shipping (or
$2.50) into Advertising with my Display Pads.
Second, How to Claim the Tax Paid on
Your Order
Tax, on the other hand, has to be handled a
little differently than shipping. Because we get charged on the
retail price of products, we have to
take that into account when we figure out the tax. It's
actually pretty easy to do. Let's use the order above to
continue our example:
So that's how to handle tax and shipping on split orders... simple enough.
-----------------------------------------------
Here's How to Separate Starter
Packages or Candle of the Month
The Basic Starter Package: $49.95
Purchases = $26.00 + (tax on
$51.85) + 66% of the shipping
Advertising= $ 9.95 + (tax on
$9.95) + 34% of the shipping
Website = $14.00 (Goes
under Other Expenses: Membership Fees)
The Sampler
Pack: $179.85
Purchases = $169.90 + (tax on
$314.80) + 94% of the shipping
Advertising= $ 9.95 + (tax on
$9.95) + 6% of the shipping
Website = Free
The Fast Start Pack: $399.90
Purchases = $334.20 + (tax on
$612.50) + 84% of the shipping
Advertising= $242.05 + (tax on
$242.05) + 16% of the shipping
Website = Free
-----------------------------------------------
For COTM
(after initial Starter Package), you have two options:
One
Jar/12 Votive Option:
Purchases= $25.50 +
(tax on $60.90) & 100% of the shipping
Website = $14.45 (Goes
under Other Expenses: Membership Fees)
Two Jar
Option:
Purchases= $23.00 +
(tax on $43.85) & 100% of the shipping
Website = $16.95
(Goes under Other Expenses: Membership Fees)
------------------------------------------------- Jan 19, 2009: Tips & Tricks #3
Hiding and Un-hiding Rows and
Columns
There are definite benefits to hiding rows
and columns, especially because the Tracker is such a big
document. If you only have a few select scents, hiding the rows
you don't need can help you work more quickly, since you'll only
have to scroll through rows that are pertinent to you.
Here are a few examples of why you'd want to
Hide Rows or Columns;
Here are two examples of why you would want
to Un-Hide Rows or Columns
It's important to know that by hiding, you
are not deleting those rows or their content... if you hide a
row and then un-hide it later, it looks exactly the same as it
did before.
I also formatted the Tracker to not even
allow you to delete rows, except in certain select tabs, such as
I & E and CUSTOMERS,
I think.
-----------------------------
Now, let's say we want to hide Black
Cherry in the JAN tab,
because we have none, have no plans to get any, and we don't
want to have to scroll past it all the time. Here's how to do
it...
That's it! You can also hide or unhide rows or columns in multiple months, by "control-clicking" on the tabs you want to apply the hiding/unhiding to (hold down the CTRL key and click the tabs that apply). The same process can be done to hide and unhide columns too, but that would normally only need to be done in the I & E tab, to hide expense columns that you don't need.
So hopefully this will help a little!
------------------------------------------------- Jan 26, 2009: Tips & Tricks #4
Working with the RETIRED
and SCENTS tabs
The RETIRED
tab is exactly that, a collection of fragrances and products
that the company has retired from the current product line.
Obviously, though, it's possible that many distributors
will carry some of these retired scents & products for
awhile before they're all gone.
I included as many retired scents as I
could, when the Tracker was first released, but others may
be discovered along the way. If that happens, you can
simply add any missing scent names to the list yourself,
because the Tracker is programmed to allow that.
In fact, the RETIRED
tab (and CUSTOMERS too) has the
least
amount of protection,
with regard to cells being locked. The reason is, that if
you need to add a new scent, and then want to re-sort them
to appear in order, Excel won't allow the sorting if the
Ending Inventory
columns (which contain formulas) are locked. So be very
careful not to
type anything in, or delete out, any of the gray
Ending Inventory
column data, or those formulas could be deleted.
In addition, managing inventory is done
a little differently in the
RETIRED
tab, because there are no columns for individual days of
the year, as in
JAN-DEC.
Since the products are retired, it's reasonable to assume
that Retired inventory will only decrease, until it's
eventually gone.
So, the
Beginning Inventory column will stay the same all
year, and you would just update the
Added,
Sold,
Promo, and
Personal columns as
they change.
For example, if I start with 10
Apple Bette jars in
January, then I sell 3 in March, I enter the 3 Sold
in March. If I sell 4 more in July, I would update the
Sold column, from 3
to 7. I can do that either by simply entering "7"
in the Sold column,
or by entering "=3+4",
so that the formula will show the separate times I sold them
(the equals sign must
be present for that to work). If I choose the "=3+4"
method, I can modify it directly inside the formula (fx)
bar, as necessary, and continue adding each change (e.g.
"=3+4", then "=3+4+2" etc).
One final note on the
RETIRED
tab, is that if you don't have some of these scents, you can
either hide the rows which you don't need to see all the
time, or delete the scents (I recommend hiding rows so the
scents will still be in there). Refer to Tips & Tricks #2
on the website for details on hiding rows.
--------------------------------------------------------
SCENTS is
a very straightforward tab, which is essentially locked,
with the exception that scent descriptions for COTM can be
added as they're released. You can get the description from
the newsletters as they come out.
SCENTS is
automatically filled in with the current inventory of
virtually all products, including the Retired scents. It
also displays the COTM scent names once they're entered in
the month they're released.
When it's time to transfer your data
from one Tracker version to another, such as from going to
2009 to 2010, that's when it helps to print out the SCENTS tab,
to use when transferring your data. You can then check
things off your SCENTS tab
printout as you enter them into the new version.
------------------------------------------------
In both the
RETIRED and
SCENTS
tabs, you can Hide the rows you don't need, if you only have
a few select scents. Hiding unneeded rows helps you work
more quickly, and makes the tab more manageable.
That's it for now... hopefully this
helped!
-------------------------------------------------
Sorting Rows in the Tracker
Sorting is a useful tool, if you want to view data in a certain
order. On the Tracker, there are only a few tabs that would really
need any sorting:
* The I & E tab * The RETIRED tab * The CUSTOMERS tab There's really no need to sort columns in the Tracker; at least I can't think of any... so this is more about sorting the rows. You may never even need to sort (or want to), but if you do, here are a few tips.
------------------------
When
you want to Sort the things into a certain order (usually
alphabetically or by date), you first need to select the cells you
want sorted. There are two primary ways of doing that.
First, there's the click & drag method, which is basically clicking and dragging your mouse from where you want the sort to start, down (or up) to where you want the sort to end. This highlights the area to be sorted. The second way is to type in the range of cells to be sorted, into the Name box (which is a white box, to the left of the Formula (fx) bar, in the upper left portion of the Excel window), and then hitting Enter. This highlights the cells as well. For example, in the RETIRED tab, let's say I added 3 retired scents to the current list, and now I want to re-sort them, so they're all in alphabetical order. I can either click and drag my mouse from cell B7, down and across to cell T63, to highlight them; or, just type in "B7:T63" into the Name Box, and hit enter. Then, once the range of cells to sort is highlighted, choose the Data drop-down menu, and select Sort. You would then choose how you wish to sort (by a specific column, such as Date or Amount). Finally, you choose to sort either in ascending order (meaning forward, e.g. A-Z, 1-100), or descending order (meaning reverse, e.g. Z>A, 100>1).
------------------------
For
the RETIRED tab, sorting
the cells has to be done as described above...
The CUSTOMERS tab is sorted in the same manner, but you cannot sort the last three columns on the right. So for this tab, you want to select the cell range "B5:AB124" when you prepare to sort. For the I & E tab, it's a little easier, as you can do a "click-and-drag" the rows themselves. Click right on the row number of your first line (which highlights that row, then drag your mouse down to the last expense entry row, to highlight them all, then go your sorting.
------------------------
One
final note: You can also sort using multiple criteria. For example,
if I'm sorting my CUSTOMERS,
and I have a bunch of them in Florida, I can sort them by name
first, then by City, or by ZIP code... this can focus your
searching, so you can see things how you want to see them.
So play around and try it! If you're afraid you messed it up (which even the best of us do from time to time), you can always use the Undo function (or the Ctrl+Z key combo), which is a blessing!! That's it for now! -------------------------------------------------
The Form8829 Tab
When claiming expenses for Business Use of the Home, the expenses are taken on Form 8829, and the Form 8829 total then gets transferred to Line 30 of Schedule C. Here are some things that will help you get started using the Form8829 tab... ---------------------------------------
Item 1: Establish the Business-Use
Percentage of the Home
First, at the top of the Form8829 tab, enter the business-use square footage, and the home's total square footage. The Tracker calculates the business-use percentage for you. It's a simple division, really... dividing the number of square feet used for business, by the total square footage. With regard to square footage, IRS is gentle on us... by allowing us to measure just the main rooms in your house, rather than making us include things like bathrooms, hallways, stairwells, foyers, etc. EXAMPLE: John lives in a 4000 square foot home. His entire finished basement is used as an office, and it's 800 square feet. So, dividing 800 by 4000, he has a 20% Business Use Percentage.
---------------------------------------
Item 2: Rent
Using the Rent row in the Form8829 tab, enter the amount you pay each month for rent. Based on the Business-Use Percentage at the top, you'll see how much of your rent is an allowable deduction. (If you own your home, see #7 below)
---------------------------------------
Item 3: Utility Bills
The same rules apply to utility bills, but be careful here. We don't want to take what we're not entitled to, so it stands to reason that only gas and electric utility bills, security systems and garbage collection fees are acceptable here. Water is a questionable utility, and Landscaping & Cable TV are very doubtful, but you can certainly track all these expenses, and your accountant will advise you if they're allowable or not.
---------------------------------------
Item 4: Association Dues
If you pay any Homeowner's Association Dues as part of (or in addition to) your Rent or Mortgage, the business-use percentage of those Dues is generally allowable. Use the Association Fees row for this. ---------------------------------------
Item 5: Phone Expenses (Second Phone
Line)
There's a row for Phones on the Form8829 tab, but phones should really be claimed in the Utilities column of Schedule C. Remember, because IRS assumes everyone has at least one primary phone in their home, almost none of that primary phone bill is deductible. All you can typically deduct on a primary phone line, really, is: a) long distance charges
attributable to your business, Internet access charges are normally claimed as an Office Expense on the I & E tab (for Schedule C), and any phone expenses & (business use of) cell phones are claimed as Utilities on the I & E tab, not on Form8829. If you have a second line in your home exclusively for business (which we recommend), it is generally fully deductible in the Utilities column of the I & E tab as well.
---------------------------------------
Item 6: Insurance
Homeowner's or Renter's Insurance is allowable here, but generally not business liability insurance, nor your medical, dental, auto or life insurance. Those (if deductible) would be claimed elsewhere. ---------------------------------------
Item 7: Mortgage, Mortgage Interest,
Real Estate Taxes & Depreciation
Generally, the same business use percentage that applies to Rent, would also apply to Mortgage as well. But this is an area I strongly recommend you speak to your accountant about The Form 8829 itself is a bit complex, so if you can to identify the portion of your mortgage that's principal, interest, and property tax, and separate them each month on the Form8829 tab, that will help a lot!
--------------------------------------------------------
Item 8: What If I Move During the
Year?
If you move during the year, the Tracker allows you to enter a second set of square footage numbers just below the first. The fields are shaded in dark gray, but if you enter anything into them, they'll be highlighted in yellow. Take a look at Row 34 of the Form8829 tab. It's the Business-Use Percentage row. The numbers on this row match the percentage you started with at the beginning of the year, so you'll see that same percentage (going left-to-right on Row 34) for all 12 months. By moving, your business-use percentage will probably change, when you put the new square footage numbers in... so you'll have to adjust Row 34, so it knows you moved. The numbers on Row 34 are currently equal to cell "O4" (that's the letter O (column) and 4 (row). That's why you'll see "=O4" in the Formula (fx) bar for each month's cell on that row. If you move, the new business use percentage will now be in cell "O6". So in Row 34, in the months that apply to the new home, enter "=O6" (without the quotes), and you'll see the percentage change. It's actually very easy, but not so easy (for me, anyway) to program Excel to do it automatically. ------------------------------------------------- Feb 16, 2009: Tips & Tricks #7
Entering Fundraisers into the
Tracker
--------------------------------------
1) Where to Claim the
Fundraiser
To completely enter a fundraiser into the Tracker, three tabs will be used; the tab of the month in which the order is placed (JAN-DEC), CUSTOMERS, and I & E. Use the very bottom rows of the CUSTOMERS tab to enter your fundraisers. The reason I say this, is that it keeps them separated from individual customers, and at the same time, allows me to make the fundraiser tax-exempt, while keeping the sorting capability alive for the individual customers.
--------------------------------------
2) Entering Your Fundraiser's Order
In CUSTOMERS, enter the quantity of each product sold in the fundraiser, the same way you would for a retail customer. If the organization is tax-exempt, you can change the Sales Tax Added column for that row, to "0", which will eliminate the Sales Tax Added calculation. Note: The ability to change Sales Tax Added to "0", only exists in Version 0301 and later. After you enter all the products, let's say it's 500 jar candles, you can change the price of the jars for that specific fundraiser, if different than the company-set MSRP. This example fundraiser sold jars at $25 each. Here's how to change the price: 1) Say this is my first fundraiser... so I use the last row (Row 124)... I enter their order of 500 jars into column J. 2) Then, in the gray-shaded column K (under Cost), I enter a new formula to change the price. a) I select that exact cell (K124), where I see $9475, then b) Type this in exactly... =J124*25 (the equals (=) sign MUST be present for it to work, and the asterisk represents "times", or the multiply command). So J124, (the 500 jars) *(times) 25 will equal $5 per jar. c) Hit Enter. The jars should now cost $25 each, bringing the Cost column up to $12,500. 3) I then do the same thing for any other product requiring a price change.
--------------------------------------
3) Applying the Organization's share of the
Fundraiser's Profits
Let's say out of the $25 price, the organization is getting $9 per jar out of the profit. This means they will get $4,500 (or $9 times 500 jars). I then enter their share of the profits in the Discount column, as a negative number (e.g. -4500). You'll see the Total Invoice Amount adjust automatically, which will now more accurately represent the actual amount of money you take in for both the order, and your share of the profits.
--------------------------------------
4) Entering Fundraiser Expenses and Income
In the I & E tab, you would enter; * The net profit (which should equal the Total Invoice Amount from the CUSTOMERS tab), into the Income column. * The total cost of the candle order (including tax and shipping), under Purchases. (Note: If you incur any additional shipping cost to get the product to the organization, that would be claimed in Other Expenses: Postage & Shipping.)
-----------------------------------------------
5) Track the Inventory Movement in JAN-DEC
In whichever month of JAN-DEC the order is received by you (or the organization if you had it drop-shipped directly to them), enter the 500 jars as Added and Sold in the same day. This will keep your SUMMARY accurate in terms of how much product you move. Note: The SUMMARY will still reflect the jars at $18.95, but that's okay... the SUMMARY is just for the MSRP-based value of your inventory. The real deal is in the Income and Purchases columns of I & E. ------------------------------------------------- Feb 23, 2009: Tips & Tricks #8
RESERVED
INSERT TEXT HERE
-------------------------------------------------
RESERVED
INSERT TEXT HERE
------------------------------------------------- Mar 9, 2009: Tips & Tricks #10
RESERVED
INSERT TEXT HERE
------------------------------------------------- Mar 16, 2009: Tips & Tricks #11
RESERVED
INSERT TEXT HERE
------------------------------------------------- Mar 23, 2009: Tips & Tricks #12
RESERVED
INSERT TEXT HERE
------------------------------------------------- Mar 30, 2009: Tips & Tricks #13
RESERVED
INSERT TEXT HERE
------------------------------------------------- Apr 6, 2009: Tips & Tricks #14
RESERVED
INSERT TEXT HERE
------------------------------------------------- Apr 13, 2009: Tips & Tricks #15
RESERVED
INSERT TEXT HERE
------------------------------------------------- Apr 20, 2009: Tips & Tricks #16
RESERVED
INSERT TEXT HERE
-------------------------------------------------
How to Format the Tracker to Print
First, there are 4 things that dictate your printing:
These are each tab's settings used in the Page Setup
screen,
to print 1-page wide. These are all set up, so you should have to
do anything... but in case your Print Preview doesn't look like it'll
fit one-page-wide, here are the settings: How to: Fix Cells That Are Locked, But Shouldn't Be There should never be a need to unlock cells, because enough time has gone by since the Tracker's first release, that most issues related to locked cells have been resolved. I've decided to remove the instructions for unlocking the Tracker, because there are many steps, and margin for error is high. If cells are locked accidentally, they could be hard to find, and if cells are unlocked accidentally, critical formulas for calculating things could be lost. So if you ever feel the Tracker needs to be unlocked for any reason, feel free to call me, or shoot me an email at RoyJuers@mac.com, and I'll be happy to help! ---------------------------------------------------
How to: Sort Rows to Appear
You can sort rows in Excel so they appear in the
order you want them. However, there are only so many places you
can really do this. One location is in the INCOME & EXPENSES
tab, where sorting is helpful to get the expenses you entered into
chronological order. This is ideal because you then don't have to
worry about when or in what order to enter the information. You
can simply go through one receipt at a time, and then sort the whole
thing later. With respect to the RETIRED tab, allowing sorting required that I leave the Ending Inventory columns un-protected. Excel wouldn't allow sorting with the cells protected, so it was the only way I could get it to work. And since this tab will probably be the one people would most likely want to sort on, I decided to un-protect those columns. So here is what you have to do:
Caution: Since the columns I mentioned are not protected, it is possible to erase or corrupt the formulas. It is critical that you not modify the Ending Inventory columns, which are all highlighted in gray.
[Next Topic]
|
|
|||
|
Main Page
~
Contact Us ~
Donate
~
Get Started
~
How-To
Roy Juers & Stephanie Sterling, Senior Director Spreadsheet Author, The Flame and Fortune Team Founders PO Box 4012, Woodbridge, VA 22194 (703) 497-4727 E-Mail: RoyJuers@mac.com
© All Rights Reserved, MMIII-MMX
Candle Business Tracker,
Tax Deduction Guide,
|
