Database for a small merchant
October 2, 2006 3:19 PM Subscribe
Help, business owners! I run a mail order business -- about 3-5 orders a day, with about 15 items in stock. For years I have used an Excel spreadsheet to keep track of orders. Is there a better way?
My spreadsheet works great because I can see everything at a glance. However it's an ordeal trying to make any kind of graph showing trends and summaries of overall income and income from each product. This is because:
(1) my spreadsheet has one ORDER per row. Any sort of analysis requires one DATE per row (with a summation of order totals on that date). So I have to write a script to compute this day by day on 6 years worth of data.
(2) there's no good way of representing what items people bought and at what price... I can do that column by column but it makes an unreadable mess, so my technique has just been to put the items in a single text field (like 3PRODUCTA 1PRODUCTB) and notate any unusual price paid. It's so easy to see what people ordered at a glance.
Now I've seen some programs out there that might help me, but they come with $300+ price tags and may be overkill. My fear is (a) paying too much for something that isn't right, (b) going through 4 pages just to enter one order, and (c) dead-ending myself with a program that uses a proprietary DB table.
What do you use? I'd welcome any advice for optimizing my Excel spreadsheet or pointing me to a more appropriate program. Please no suggestions to use an online shopping cart system or a CC gateway's package -- I get lots of phone, mail, and fax orders and run all CC's by hand.
My spreadsheet works great because I can see everything at a glance. However it's an ordeal trying to make any kind of graph showing trends and summaries of overall income and income from each product. This is because:
(1) my spreadsheet has one ORDER per row. Any sort of analysis requires one DATE per row (with a summation of order totals on that date). So I have to write a script to compute this day by day on 6 years worth of data.
(2) there's no good way of representing what items people bought and at what price... I can do that column by column but it makes an unreadable mess, so my technique has just been to put the items in a single text field (like 3PRODUCTA 1PRODUCTB) and notate any unusual price paid. It's so easy to see what people ordered at a glance.
Now I've seen some programs out there that might help me, but they come with $300+ price tags and may be overkill. My fear is (a) paying too much for something that isn't right, (b) going through 4 pages just to enter one order, and (c) dead-ending myself with a program that uses a proprietary DB table.
What do you use? I'd welcome any advice for optimizing my Excel spreadsheet or pointing me to a more appropriate program. Please no suggestions to use an online shopping cart system or a CC gateway's package -- I get lots of phone, mail, and fax orders and run all CC's by hand.
It sounds like you have a wealth of information that you can't access/analyze because it is relational data stored in tabular format. Honestly, spending $300 on Filemaker or a similar product would likely pay for itself really quickly (both in your time spend muddling in Excel, as well as allowing you to analyze your past sales).
Why not download a free 30 day trial of Filemaker and see if it meets your needs? (or some other product -- Filemaker is just the only product I am familiar with)
posted by misterbrandt at 4:42 PM on October 2, 2006
Why not download a free 30 day trial of Filemaker and see if it meets your needs? (or some other product -- Filemaker is just the only product I am familiar with)
posted by misterbrandt at 4:42 PM on October 2, 2006
I have a similarly sized mail order business (11 items in stock) and have been running it for years with Quickbooks Pro, which is about $170 street/Internet price. I can write up a new customer order in about two minutes; a reorder in about 30 seconds. I also use it to keep my checking and credit accounts, my purchase orders, my inventory and just about every other item related to running a business. Everything is exportable to Word and Excel, but it has 100+ internal reports that tell me just about anything I need to know. I used to use Excel and Access for inventory, and Quicken to keep my books, now I have it all in one program. Yes, it has many features I don't need and don't use, but it does whatever I want very quickly so I don't have to make a database jump through hoops. I think they have a money-back guarantee (don't know about a free trial), so it is absolutely worth testing to see if it works for you.
posted by bigmuffindaddy at 6:31 PM on October 2, 2006
posted by bigmuffindaddy at 6:31 PM on October 2, 2006
I also have a similar sized business and use only Excel. I found Quickbooks confusing and so I just didn't keep track, so that had to go.
Most of my orders are online so I don't have to actually record the clients info, and I don't usually have a need for it later anyway. I get my orders through PayPal mostly (some phone orders which I do write down); I print a copy of the invoice (to keep a record of the particular transaction), and then put the numbers into my Excel sheet.
My sheet is simple:
Product Qty Cost Price COGS Total
Widget 5 $10 $20 $50 $100
(COGS=Cost of Goods Sold)
At the bottom I have a totall for the COGS and Total columns. I also have a running total for the year in addition to the total for the quarter. I have a separate sheet for each quarter. And then I have a separate sheet for inventory and another for expenses.
I think with that info you can make the graphs you need. I don't really use graphs....I'm not a numbers person but I find that I can visualize the numbers pretty well without it.
posted by kenzi23 at 7:25 PM on October 2, 2006
Most of my orders are online so I don't have to actually record the clients info, and I don't usually have a need for it later anyway. I get my orders through PayPal mostly (some phone orders which I do write down); I print a copy of the invoice (to keep a record of the particular transaction), and then put the numbers into my Excel sheet.
My sheet is simple:
Product Qty Cost Price COGS Total
Widget 5 $10 $20 $50 $100
(COGS=Cost of Goods Sold)
At the bottom I have a totall for the COGS and Total columns. I also have a running total for the year in addition to the total for the quarter. I have a separate sheet for each quarter. And then I have a separate sheet for inventory and another for expenses.
I think with that info you can make the graphs you need. I don't really use graphs....I'm not a numbers person but I find that I can visualize the numbers pretty well without it.
posted by kenzi23 at 7:25 PM on October 2, 2006
I'm not sure exactly what analyses you are trying to run, but it sounds like Autofilter could help with a lot of what you've described.
You can filter a data set by person, date, price paid, etc. And you can use custom filters (one of the most powerful, I find, is "contains = ..." . Check out the custom options - they are useful.
Select the columns in question, Tools|Filter|Autofilter. If you select the rows in the filter, you can manipulate them easily in a second worksheet. Be careful when deleting from this mode, though, as it can delete rows in between (I think Autofilter works by hiding non-matching rows, assigning them zero height).
posted by sagwalla at 6:00 AM on October 3, 2006
You can filter a data set by person, date, price paid, etc. And you can use custom filters (one of the most powerful, I find, is "contains = ..." . Check out the custom options - they are useful.
Select the columns in question, Tools|Filter|Autofilter. If you select the rows in the filter, you can manipulate them easily in a second worksheet. Be careful when deleting from this mode, though, as it can delete rows in between (I think Autofilter works by hiding non-matching rows, assigning them zero height).
posted by sagwalla at 6:00 AM on October 3, 2006
This thread is closed to new comments.
posted by flabdablet at 3:49 PM on October 2, 2006