Money matters

[TreverT]

A while back, I mentioned something about the database I've built for tracking my own production and pipe income, and the idea that it might be useful to others. I thought about posting the file, but quickly realized that the time involved in going back and blanking all my personal history to create a "vanilla" file would be exorbitant, plus a fair bit of my setup is very specific to my needs anyway (Many of you won't need built-in currency floating calculations, for instance).

Thinking about it, I thought that it would be better to just screen cap a single blank page of each screen of the thing, with explanations of the fields, and let you guys build your own version each, using whatever database program you prefer (I use OpenOffice). This way I can stagger out the whole thing (better for my working time) by doing it one sheet at a time, we can cover each page fully, and probably we can all learn and improve during the questions & answers.

I don't know if this will be interesting or useful. If no one seems interested, I'll just let it mercifully die. But, a lot of you seem to be selling at least part-time-serious if not full-time, so it's really crucial to know what you're *really* making. Plus, it's an incredibly handy record for tax time and accountants, as you can easily just plug values from this into your tax forms - though some numbers will need real-world exact corrections, which we'll get into as we go.

[TreverT]

For starters - Open your favorite database program and make yourself a new file. My file has seven pages, as can be seen in the tabs at the bottom of the opening page:



These tabs are:

Weekly Targets - for setting weekly production goals, tracking your current inventory, and keeping running tabs on how much you need to make this week to meet your monthly production goal.

Incoming Cash & Labor Costs - this is the "main page" and tracks your income from month to month, including your cost of materials, taxes collected, and labor hours involved. Here you will see what you really make after tax and CC and material cost deductions, how many hours you work per month, and how many hours you average per pipe.

Expenses - Here you will keep a running tally of your month's expenses, including both regular bills and daily misc. This is constantly compared to the Incoming Cash sheet to tell you how well you're paying your bills. Those who are NOT full-timers will have to do some adjusting here to figure out how to incorporate your "real job" income into the picture.

Debts - Yep, we've all got them. This page tracks current debts and how well you're paying them down month by month.

Graphs & Tracking - This page compares key figures side-by-side from month to month. Here is where you will look back on a year's output and plot graphs that will tell you exactly which grades of your pipes make the best profit, when you made the most and least (and why), etc.

Savings & Profit - Here we track your accumulated materials savings. We take a bit out of the price of every pipe to set aside here and accumulate, to provide a running pool of cash to use for buying new briar, tools, sandpaper, etc. If the business isn't at least paying for its own costs, you're in trouble.

Stuff to Replace - You may not need or want this. I just use this as a memory jog for things around the house that need doing. I put down stuff here, with estimated cost and time (like replacing a broken shower faucet) and then tackle the jobs when I have the matching free time and cash.

[TreverT]

Now, on to page 1, Weekly Targets!



Starting top left, your "Target Estimate" field needs to be the sum of all the numbers down in the bottom box labeled "Target Estimate is:" Down there, you should enter your average monthly bill totals. You may not know this but after a few months of maintaining the "Expenses" page, you will. The crucial things to plug in are your baseline expenses figure (the sum of all your regular bills, like house payment, car payment, insurance, power, etc). Then drop in a figure for your typical monthly cost of living extra, over and above your regular bills. This means what you spend on food, gas, toys, etc. I can usually count on needing at least 600-800 € over and above my bill total to get through a month, between groceries and gas and such.

The last line down there is "Desired profit" and you need to put in something, because if you aren't making anything beyond your expenses, you're not doing too well! As an example, say you'd like to make enough pipes this month to generate at least $400 extra by the end of the month.

Back at the top left, "# of Weeks" is the number of weeks in the month, and "Initial Goal/Week" is the Target Estimate divided by the number of weeks. That will tell you how much ££ value of pipes you need to produce (NOT sell, just produce - We'll get to sales later). This way you have a clear weekly goal to hit... You'll KNOW you need to make at last $900 worth of pipes this week, or whatever, to be on track.

The fields over in the top right require formulas to be entered to calculate as required. "Currently Needed' is your up-to-date money goal, with the sum of the weekly production totals subtracted from your monthly goal. You'll tick the "Remaining Weeks" field down as the month progresses, and the formula will divide what's currently needed this week for you. Thus, missing your weekly goals will make things worse as the month progresses.

The middle-left bow headlined "Current Week's Total" is where you'll keep track of what you make during the week. Just plug in the dollar value of each pipe you complete and set the formula so it will add them all up and subtract the current total from the weekly goal you need, so you'll always have a handy reference to see just how much more you need to make this week.

Finally, the "Unsold Stock" box is where you'll enter a description of each pipe, the pipe's price, and your material cost from the pipe. I typically set aside between 8 and 20 € from the price of every pipe for the accumulating material costs fund. The date is optional, but can be useful just to see how long something has sat unsold. This box does not figure into the formulas of anything else on this page.

Right, that's page 1! I'll post the next page when I have the time, depending on whether anyone even finds this useful or not.

[Nick]

Very slick speadsheeting buddy! I have no use for it, but I do love a good spreadsheet.

Hehehehe! My buddy and I always joked that the Accountant's Porn mag would be called "Spreadsheet."

[TreverT]

Very slick speadsheeting buddy! I have no use for it, but I do love a good spreadsheet.

Hehehehe! My buddy and I always joked that the Accountant's Porn mag would be called "Spreadsheet."

It sounds quite sick, but I have greatly enjoyed tinkering with it over the years. A little change here, a little adjustment there. Being self-employed can sometimes create the sensation that you have no idea if you're making any money at all but just seem to work all the time and the bills never stop, so it's really quite handy to have a reference to calculate by which keeps me posted on exactly where I stand. It does take out a lot of the panic factor! Although, given the initial voting on this, it looks like I likely won't be following it up with further posts... Just as well, really, as it will save me lost working time. I did think it would be overkill for the casual carvers, but just wasn't sure how many full-timers frequent this place.

[geigerpipes]

Trever

I'm intrested...a spreadsheet like that would be very handy indeed..just to see what a lousy sallery we really have

Hope to see you in Rheinbach this august Finally

[KurtHuhn]

Thanks Trever. This helps give me the starting point I was looking for to create a similar doohicky for myself. Like you, I'll be doing it in OpenOffice.

[kbadkar]

Even though I can't afford to take on pipe making full time, I do appreciate all the time and effort you have put into making your spreadsheet available to other pipers. As if sharing your experience and knowledge isn't enough, this is a truly generous offer to contribute to the community.

[sethile]

Wow, this looks great, Trever! Thanks for the head start with what you've posted here, and if you keep posting on this thread I'll certainly enjoy the discussion, and I know I would learn a lot.

I'm very interested in making this profitable at some point. My goal is to make enough with pipe making to justify retiring a little early and going full time. Right now retiring early and going full time seems like a pretty loft goal, but I at least want to do a much better job offsetting expenses and replacing some of the extra work I do now moonlighting with pipe making.

Your help through your particiaption here, as well as with your blog and website are a huge help, thanks!

[StephenDownie]

I have to admit that I've been working on the premise that ignorance is bliss. I have a goal that I try to hit every month as far as income is concerned, but I've known for quite some time that I need to keep better records and be a lot more aware of where my time and money is going. I'd be interested in any further discussion on the topic.

[TreverT]

OK, I will definitely continue this then. The next part will be the second, and main, page of the database - the Incoming Cash & Labor sheet that tracks every sale, material costs, taxes, buyers, and labor hours in comparison to your expenses. It's a lot more complex than the Weekly Targets page was, and will require more explanation and probably questions and answers. Also, unlike the Weekly Targets page which is designed to be kept for a month, then cleared off and used again at the start of a new month, the Incoming Cash page will become one huge scrolling record of month-by-month income.

If I had to contrast these first two pages simply, I'd say that the Weekly Targets page is for your ideals - ie, "I want to make $3000 this month" - and how well you're achieving them, and the Incoming Cash page is for telling you where you actually stand in regard to your bills.

I've got two pipes to finish and post before the end of the month, so I doubt I'll have the time to post the next page discussion until either tomorrow evening or Friday, but I'll get it up ASAP.

[souljer]

... Being self-employed can sometimes create the sensation that you have no idea if you're making any money at all but just seem to work all the time and the bills never stop, so it's really quite handy to have a reference to calculate by which keeps me posted on exactly where I stand. It does take out a lot of the panic factor!

Unfortunately for me "working all the time" and "panic factor" pretty much sums up my existence. I'd kill myself to end the misery, but I have too much to do already!

I'm ashamed to admit that I am totally clueless as to what you are getting at here and have no idea how to make something like this for myself although I understand the need and see how it would be a useful tool.

Reading this makes me feel like there is ANOTHER FUQING THING TO DO that I have to learn from scratch when I have so much that already needs doing ... which is just depressing.

[geigerpipes]

Trever,

I'm a total Database beginner

I've managed to set up the first sheet "weekly Targets" pretty much like your pic and added the remaining pages in exel but can't figure out how to add formulas so it will automatically add or subtract numbers from spesific feilds

Man should have chosen the accountant/toolmaker class back in school...well well too late for that now...

[hazmat]

Love.. check the help files of whatever application you're using for spreadsheets. For basic operations, the formulas are pretty straightforward and the help files should lay them out for you. I'm a spreadsheet dunce but have managed to cobble a couple together over the years that worked out surprisingly well, so I'm convinced anyone can do it. Good luck!

Matt

[TreverT]

Yes, what he said! The formulas are pretty easy to enter, and nothing in my setup uses anything very complicated (no "If/Ors" or such). In both Excel and OpenOffice, you can enter formulas as simply as type = into a box and following it with clicks on the data fields, plus your math. Like, if you want a formula that will subtract Expenses from Income, you just pick the box where you want the sum, and type:

=
(click the Income box)
-
(click the Expenses box)

and then hit Enter
C'est simple! OpenOffice will plug in the exact data field that you clicked on into the formula, so after your clicking, the actual visible formula will look something like =B587-B589

It is the same for the pages that use values from other pages - You type in = and then flip to the other page, click the box desired, and go back to the page where your formula is to enter the rest of it.

I would suggest looking around online, like on the OpenOffice site, for a nice basic tutorial on working with their database program, and spending an afternoon going through that just to get familiar with what a database prog can do.

The only area of my database that gets a bit tricky is the "Graphs & Tracking" page, where you can produce some extremely handy comparative graphs of your output and expenses and other data over the course of years, but for this you WILL need to learn how to make graphs in your database prog of choice. I had a hell of a time learning how to do it in OpenOffice, but did eventually get it figured out. Be prepared for a lot of blundering at first, though.

[TreverT]

Oh, in addition, the sum fields (which add up lots of numbers in a column) are created by typing

=sum( click and drag to select all the boxes you want included in your sum )
*enter*

Note that you actually do type the () marks there, I was not putting those in to mark my comment. The formula you create should end up looking like =sum(D354:D380)
If you don't include the (), it will just give you a fussy error message, probably =NAME??