Money Sheet, Second Page

TreverT · Post by **TreverT** » Sat Jun 02, 2007 10:45 am

OK, I hope those who were building their versions of this thing have all gotten their files created, their individual page tabs made and named, and set up the first page, "Weekly Targets", already. Now we're on to the big page!

Open your page titled "Incoming Cash and Labor Costs" and start making yourself a replica of the picture above. You'll have one of these big blocks for each month of the year, enabling you to easily look back over your years of work and see exactly who bought which pipe, when, and even how they paid. This can help immensely in touchy situations like warranty work - if, for instance, an estate dealer gets hold of one of your pipes and reams it out, redrills the airhole, files your bit to micron thinness, etc, and then somebody else buys the thing and burns it out or bites through the stem... Well, you can easily look back and tell whether it's the original buyer or not.

I'll start with the top columns, those that have been tagged with notes in red.

Across the first line of the database, you'll see JUNE (current month), the year, and further out to the right, an exchange rate. This will only be needed for those of you who do lots of business in other currencies. I update my rate monthly, so the numbers produced will not be exactly precise, but you can correct for variance when bank statements come in.

Now...

NAME - DESTINATION Just fill in the names of your buyers here, and their location if you like. Location can help as a memory job for addreses and/or tax help.

PURCHASE - PAYMENT METHOD Enter the pipe that each buyer got, by name, number or whatever, and their method of payment if you like. Payment method is NOT necessary. But it can be handy as a historical reference. Those who like to graph this kind of data may want to make Payment Method a separate column and use some sort of code to represent the different methods, if you really have a burning need to look back over four years and see what percentages of buyers have paid you via Paypal versus checks or credit cards.

DATE The date of the SALE. Not the creation, the sale. Remember, this page is all about what you have SOLD. Unsold inventory is kept track of on the first page, "Weekly Targets"

Next up we've got five columns that everyone will probably need a different way of doing. I'll tell you what mine mean:

TTC PRICES This is the French term for "sales on which I charged sales tax" (If you think this all seems complicated and scary, just imagine doing all this in another language and business law environment). US pipemakers can title this something like "In-State Sales". If you charged sales tax on a sale, plug that TOTAL price in this column. And by total, I mean the pipe's price plus shipping and sales tax.

EUROS Call this dollars, pounds, whatever - This column is for the prices you collect for your pipes. If someone pays you WITHOUT tax, just plug that number in here (include shipping extras). If someone pays you WITH tax, you put the total price-wi-tax in the "TTC Prices" column to the left, put in a formula here to deduct the tax percentage and hand you the total, and then plug in a formula to the right to calculate exactly how much of that total price was sales tax. You will need to know how much sales tax you've collected for your income tax filing.

For convenience, my EU tax formulas are:
TTC Price = total € price billed
Euros = (TTC Price)/1.196 (France's VAT sales tax)
TVA= (TTC Price)*0.1639

Get these columns right, and you'll have a handy tally at the bottom of each which will track how much money you've actually been paid (the total of the EUROS column) and how much sales tax you've collected.

Now, the next two columns are titled DOLLARS and EST EURO-$$. You only need this if you collect a lot of income in foreign currency. I do, via my sales to Pipe & Pint, who simply deposit their payments directly into my US bank account. What these two columns do is allow me to plug in my wholesale price in euros, and then the DOLLARS column formulas will multiply that wholesale euro cost by this month's exchange rate (up top, remember?) to tell me just how much P&P needs to pay me in dollars.

Finally we come to one of the most important rolling tallies -

EST MATERIAL COST For EVERY pipe you make, you need to plug in an amount here to deduct from your available income to cover the replacement costs of the materials. It's up to you to determine what your material cost is per pipe, and it will often be different. The cost of the briar block and the stem section (easy enough to figure directly), plus an amount to cover used sandpaper, stain, shellac, tool wear, etc... basically all consumables.

Tracking this is crucial!!!!!!

What you will do is steadily build up a reserve fund, deducted from your income, that is set aside for purposes of purchasing new materials. At first, your amounts will seem piddly - You'll have a hundred bucks or so at the end of a month, and you may need $1500 to buy a new bag of briar. But, KEEP DEDUCTING THESE AMOUNTS, and over time you will build a healthy and impressive fund to cover your material costs. In one year of deducting roughly 16 € from each pipe sold, I garnered a fat 2793 € material costs fund from which I can dip for any new stock I need. It will no longer be, "Crap, I'm nearly out of rod stock and don't have any cash for new", but instead, "Ahh, I'll take the volume discount and buy $1000 of rod with this order, and just pull that from my standing $2400 materials fund". Give it a year, and you'll be happy.

I will cover the stuff in the bottom half of the screen in my next post. A lot of it is probably self-explanatory.

TreverT · Post by **TreverT** » Sat Jun 02, 2007 11:36 am

OK, Next part of the page!

I've red-circled the sections I'm about to explain. I hope this will make it easier to follow. I know that first post must have seemed fairly heavy.

In this post, we're going to find out how much you're really working, and if you're making any money!

In the upper red oval, look in the center and you'll see two entries:

OFFICE/NET MINUTE TOTAL A running record of how much time you spend doing recordkeeping and sales and all that jazz, over the course of the month. I track it by minutes because we rarely work in convenient hour blocks of time. I sit down at the computer, jot down 2:15pm, then write up a blog entry and reply to a day's email. I finish at 4:45, then add 150 minutes to the Office Minute total.

You MUST keep track of this. If you're paying your bills by pipemaking, you're working when you're doing taxes, photographing pipes for your site, or even posting ads on forums. This is a dangerous block of time, because all time here is not money-producing time. Workshop time produces actual sellable goods that enhance your purse - Office time does not, and needs to be minimized.

WORKSHOP MINUTE TOTAL How much time you've spent in the workshop this month. Just keep adding to it as you go.

Now, look left at the "Other Costs" box - in it, you enter two formulas which will divide your running total of minutes by 60, and tell you exactly how many hours you've spent this month in the workshop and the office.

The last item in that upper red oval is over at the far right, and is a simply list of how many pipes you've made this month, with breakdowns of your preference. How much variance you want to track here is up to you. I track how many Talberts, Mortas, and Ligne Bretagnes I make in a month, and from this data, over the course of a year I can graph my monthly income line and compare it directly to the percentages of Talberts produced versus LBs or mortas... Ergo, easily spotting trends such as how my monthly income goes DOWN every time the number of Ligne Bretagnes starts to rise above the Talbert Briars. What you want to track here is up to you - maybe you want to know pipes versus pens, or god forbid you may even want to compare your grades of pipes for individual profit levels over time... Whatever, you track that here.

Now, look down at the lower left red circle. There are three items here -

% SHOP HOURS is total workshop hours divided by the sum of both shop and workshop hours

% OFFICE HOURS is just the opposite.

These two will tell you just how profitably your time was divided during a typical month. The bigger the Office hour percentage, the more money you're losing. I tend to run around 71% workshop, 29% office typically, with tax month jumping to around 56% workshop, 44% office.

Just under that is Average Hourly Wage. This is figured by dividing your total income (We'll get to that next post) by the hourly totals of your workshop of office time. It will tell you exactly what you made per working hour in a month.

Don't be scared.

In order to be in decent health, an artist, artisan, craftsperson in general needs to make around $20 per hour. This may seem like a lot to the salaried, but you're covering so many other expenses yourself (insurance, for one, plus accounting, all expenses, etc) that $20/hour is really just scraping-by money. If you're running this database and find out you're only making $7 an hour, you have to do something differently. Raise your prices, cut your time per pipe, something. This is where you'll really know just how you're doing.

Now, over in the bottom-right red circle, we see "Trever Made" and "Emily Made", two entries which you can probably discard unless you have multiple pipemakers in the shop in friendly competition with each other. The same goes for the entries for "French Study Time", which I use to keep track of just how much time per month I'm devoting to my language study. You may, however, like to use this to track some random thing of your own, like exercise time, walking time, or whatever.

The important entries are-

AVERAGE WORKING HOURS/WEEK This is a formula which adds your total office and workshop working hours and divides them by 4. Note that this figure will be invalid until the end of the month, when all your time is in, unless you want to manually change the divisor by week. The result will tell you exactly how much working time you're putting in. Obviously, you don't want to find that you're working 57 hours a week for $6 an hour. But if you are, you NEED to KNOW that by using these tools to track your income, because if that's the best you are able to make in pipe income, you're better off working at Borders or somewhere, where at least you'll get insurance.

BILLS MET This is just a little note to mark what date of the month you have made enough to pay that month's expenses. Ideally you'll want to enter the month with your expenses pre-covered, but that doesn't always happen...

AVG HOURS/PIPE This takes your total workshop hours and divides them by the total pipes produced this month. It will tell you just what your typical time is. Useful for comparison tracking, if you produce multiple lines or grades, because you can graph average times versus different lines and overall income.

Right!! One more post, and we'll have the most difficult page of the database fully covered!

TreverT · Post by **TreverT** » Sat Jun 02, 2007 1:24 pm

Right, we're nearly done building the most complicated page!

I'm going to start at the top of the database items and simply explain downwards:

Carryover from Previous Month This is the "Current Status" of last month. Since your database is new, it should be set to zero, or wherever you determine your "starting point" to be. If you end your month above your expenses, bonuses get carried over to next month - and the same for deficits.

Total Production this month: This field should be set to mirror the sum of your "Total Production" field from the "Weekly Targets" previous database page. Note that this figure is simply here for comparison and graphing - What you have PRODUCED has little reflection on what you have actually sold.

Total Income, This Month: The sum of "Euros" plus "Est. Euros", if you're using dual currency adjustments. Basically, whichever columns you have that total your current income not including taxes.

Total Income minus 4% Visa/PP Charges: I am lucky, both my Paypal account and our bank credit card account each deduct exactly 4% of each bill as their cut, so the math is simple. Just yank 4% out of your total income, above, to reflect what you've really landed in your account, as opposed to pre-"handling fees". Occasionally I'll have a French buyer pay with a check, and this deduction won't apply, but that's rare. Pipemakers who take a lot of payments by deduction-free checks will have to think up some other way of recognizing Paypal and CC deductions, though.

Total VAT Collected: Non-EUers will want to change this to "Total sales tax collected" or something similar. Just a mirror of the column total of this month's sales taxes, down here for easy viewing with the other big numbers.

Total Material Costs: A mirror of the "Material Costs" column total, again down here just for convenient reference.

Current Income minus Material Costs: Take the "Total Income minus Paypal/CC deductions" sum and subtract your "Total Material Costs" number here. This effectively treats that bit of material cost as if it isn't part of your income, shunting it aside for future use. This income total is what you have really made minus tags and costs and handling fees.... for me, that is. US artisans will have a different time of things, and should read my postscript to this thread below.

This Month's Bill Total This field will be a mirror of your expenses total on the next page, "Expenses". That will be the next page we build.

Current Status Set a formula to subtract your expenses from your income and here's where you're at, in regard to this month's bills.

Up/Down from Last Month Set a formula to subtract the "Carryover from Last Month" amount from the "Current Status" above, and you'll know how much you need to make not just to meet your expenses, but also to top your income of the previous month.

And that's it! At the end of the month, copy your entire box of data and formulas to a new line down below this one, and change the month date, delete the field entries, and start a new month. Eventually you will have a huge rolling field of your financial performance, month by month. I know this seems like a lot of stuff, but I promise that if you set up something similar to fit your own needs, you will be amazed at how much better you can work, and how much more FUN you will have. And that's the silly thing - I know plenty of guys who will flee screaming from complex bookkeeping like this, saying they just want to have "fun" with their pipemaking, but You can't have fun going broke, and knowing exactly where you are will take a HUGE weight off of you and really enable you to maximize your work and fun like never before.

Next thread will be on the "Expenses" page! And I promise, all the rest of the pages are much easier....

TreverT · Post by **TreverT** » Sat Jun 02, 2007 1:37 pm

An important postscript regarding taxes!!

You are all in different countries, and will need to adapt the above page to reflect your own country's income taxation.

For myself, it's actually rather simple - we pay set fees each month as part of our national health care & welfare taxes, so those are all figured for on the forthcoming "Expenses" page. Since moving to France, I have never yet made enough income to qualify to pay income tax (Seriously! A very large percentage of people in France pay no income tax - below a certain threshhold, you just don't pay). So, with my compulsory taxes coming out as monthly bills and no income tax yet to worry about, this form handles all the taxation bookkeeping I need. Unfortunately, I suspect that next year will finally moved me into the income tax-paying range, and I'll have to start making income deductions to account for this.

US artisans in particular need to take out your own income taxes from your income. When your paychecks are handed to you in a salaried form, all your taxes are deducted by your employer, but when you're self-employed, you have to set aside enough cash to pay your income taxes in full each year. I'd suggest making another page similar to the material costs page and use it to accumulate income tax estimations. IIRC, I used to end up paying about 30% of my US income each year when tax time came due, so you could account for this as simply as setting up an extra field after the Paypal/CC deductions, and subtracting your own estimation of what your income tax withholding should be. That way you'll have a handy pile of cash to pay the GOV with at the end of the year.

Just don't freak too much when you watch $400 become $384 after Paypal fee deductions, then $364 after material cost deductions, and then $254 after income tax deductions.

This will give you a whole new perspective when you hear part-timers sitting around talking about their pipe and how they've got fifteen hours in a pipe, and they're charging $150 for it 'cuz that's ten bucks an hour and they think that's darned fair pricing...

StephenDownie · Post by **StephenDownie** » Sat Jun 02, 2007 3:04 pm

Thanks for all this work Trever. It's going to take me a while to get this all worked out, but it would have taken much longer without your help.

TreverT · Post by **TreverT** » Sat Jun 02, 2007 3:33 pm

No prob, and don't hesitate to ask if you've got questions or something isn't clear! I think it's best, when building your own variant of this, to view it as a work-in progress rather than something that must be dropped from the sky fully functioning. You'll need to adjust and tweak as you go to get it running more in tune with your needs, especially regarding taxes. But, I would say to go ahead and build the first two pages and start using the thing now - just work out a good "jumping in" point based on your checkbook (as in, pick a time when you can easily and accurately start marking money that has all arrived and so forth). Here at the start of the month should be a good time to start a June worksheet. Just plug in an estimate number for the "Expenses" amount now and work from that till we move on to the rest of the pages.